Lister performance

[Webrocker]

Hi,

currently I'm testing a rebuild of an 18yr old forum.

basically it's a a pet project of mine, to get to know ProcessWire better, but I'm sure it could work, since the core functionality of the forums original script is fairly simple and the added functionality could very well be represented with the cms parts…

For testing's sake I have imported the data of about 4000 users via the API, using a custom template file (by using the approach shown here: https://processwire.com/blog/posts/processwire-core-updates-2.5.14/#multiple-templates-or-parents-for-users ) with some additional fields with some meta data from the old forum's user settings. this worked out pretty good, only that I had to split the import in chunks of about 500 entries, b/c otherwise the database connection would time out (or maybe the php script runtime is/was to blame).

now I have these users in my PW install, and viewing them via the pages tree is possible.

but once I switch to the lister, the complete website will stall, front- and backend, and after some minutes a SQLSTATE error will be thrown:

Warning: Error while sending QUERY packet. PID=10464 in processwire/wire/core/Pages.php on line 1992

2015-08-03 12:50:46 Exception: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away (in [...]processwire/wire/core/Pages.php line 1992)
#0 PDOStatement->execute()
#1 processwire/wire/core/Pages.php(477): Pages->executeQuery(Object(PDOStatement))
#2 processwire/wire/core/PagesType.php(258): Pages->getById(Array, Array) #3 processwire/wire/core/Users.php(29): PagesType->get(41)
#4 processwire/wire/core/Session.php(96): Users->get(41)
#5 processwire/wire/core/ProcessWire.php(259): Session->__construct()
#6 processwire/wire/core/ProcessWire.php(84): ProcessWire->load(Obje

4000 entries shouldn't be a problem, or is it?

I know that this is hard to tele-diagnose, so before going deeper into it, I wanted to ask if there are limits performance-wise on what the API can handle? I'm asking because we plan to use the PW backend with listerPro for a project where we expect 10.000  to 20.000 entries which should be search- and filterable in the backend.

The above error will be thrown if I try to set a second filter besides the "template" like "forum_user" - in my testcase there's a "legacy_userlevel" field which is an integer and has values through the import of "-100","-1","0", "1", "2", "3". (that's only temporary, I plan to switch to roles based on those old settings).

The lister tries to query this field in order to make a select input where I could then select one of those values… this times out.

In the "forum_user" template fields I have no settings like "global" or "autojoin" or else…

I'm on the ProcessWire 2.6.9 dev version and currently in "advanced" modus.

Update: Even if I try to switch to "access" or "roles", the backend will time out. This started to happen with the addition of the many users… without altering the set up, everything worked when there were only the first twenty users.

Even though I'm on a managed root server with this testing project, I suspect that the script settings may be a bit… sparse.

What ini settings should I look out for to counter this error?

cheers,

Tom

[Webrocker]

Next update: I moved the test install to another host, which usually performs nicely.

Here the same thing happens - with 3897 user entries the server will time out and throws 500 error.

Adding 500 users slowed the lister's (or the servers?) response by about 12 seconds per set (i started with 500, which took the lister about 24 seconds to show, and added more step by step), the last working amound was around 2300 users with a response time of over 80 seconds - the next batch of imported users made the thing quit.

I don't think I've any special fields or data stored at those users, it seems that the pure amount of user/pages brings the lister to its knees.

Here's what currently is stored at the user's template (name / fieldtype):

pass Password
email Email
title Title
roles Page
admin_theme Module
language Page
as_legacy_id Integer
as_legacy_pass Text
as_legacy_registered Datetime
as_legacy_userlevel Integer
as_legacy_numposts Integer
as_legacy_color Text

interestingly, even though the error states that the "server has gone", the backend is still workable - when the error has been thrown, the "pages/tree" section will work normally. it's just the lister and the access/settings sections that will time out.

[thetuningspoon]

Hi Webrocker,

I have a site with more than 3,000 pages representing individuals which are displayed in a Lister, and it seems to handle it without issue. What is your limit set to in the Lister's settings? If you try to display all of those pages at once you will definitely have a problem. But if set to ~50 you should be good, and Lister will automatically paginate the results for you.

I find that filtering the pages is still very quick even with all those potential results.

[Webrocker]

Hi thetuningspoon,

thanks for answering and even more thanks for confirming that this shouldn't be a problem with PW, but must have something to do with factors that hopefully are in my domain

I haven't change the lister's settings, it'll display 25 items per page.

I have a hunch that this maybe has something to do with the "users" - these 4000 entries would be users who'd be able to log in somehow, and/or with me borking something while playing around with custom user templates. I'm wondering because the ~4000 "user" pages behave fine and are really quick to browse/page as long as I am in the regular "tree" section.

cheers,

Tom

[thetuningspoon]

Loading users shouldn't be any different from any other page. The 3,000 individuals I'm storing are not users, though. So I can't confirm for sure.

[Webrocker]

… for testing purposes I changed the

wire('config')->maxPageNum = 99999; 

(at the very end of the ProcessPageLister.module) to

wire('config')->maxPageNum = 1;

Now I get a "maximum execution time exceeded" error back. Aha! As I suspected, it was at the "30 seconds" default, so I raised this to 360 seconds. Now after about a minute or so, the "Pages: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away" error returns.

Checking with phpinfo I see that the "mysql.connect_timeout" is at 60 seconds. I raised this, too, and now have 600 seconds there.

Now it takes considerably longer until the above "server has gone" error will be shown, and afterwards the complete 3897 user entries are there, neatly paged in 25-per-page pages.

So it seems that the script execution time and the mysql connection timeout are the bottlenecks. But even if I get this to work with even larger limits - the lister is way too slow to be of use in that scenario, esp. since this will again be fired if I'm going back to the previous view if I had clicked a result in the lister. :-/

I just raised the limits to 2400 seconds in php.ini and then timed the response in the backend - after 1:55:90 (nearly two minutes) the script will be interupted and the "server gone away" error appears. So I searched for "120" entries in the phpinfo output and found:

[Core]
realpath_cache_ttl    120
...
[Environment]
DBENTRY__CPU 120
...
[PHP Variables]
_SERVER["DBENTRY__CPU"]120
...

Does anyone know what these are, and if they may be related to my problem?

thanks,

Tom

[Webrocker]

… and yet another update

this time I imported "my" 4000 users as normal pages, no roles, no password, just the data. And guess what? The lister is running fine and snappy.

Next up is a test how the "users" behave in the lister if I use the systems "user" template, not a custom one, maybe I can narrow down what's causing the timeout.

[Webrocker]

Hi guys, just a quick follow-up:

I now have imported the 4000+ users in PW; using the "normal" user template, spiced up with the custom data fields I need for the user-data stuff from the old forum.

This worked, and the lister instance which does put out the user data in the backend  is running fine and without problems.

So I have no idea what exactly the difference is, since I copied my custom user template off the internal "user" template - but this way now, everything works smoothly.

cheers
Tom

update: (just an aside: i have currently 40,000 posts imported as pages and no hickup in the backend.) So I'll mark this as resolved, since this is not a performance issue of the lister, but something about the combination of a large amount of users, custom-template, and the lister.