Sort Users with many users – unresponsive website
hi,
we installed Admin Columns Pro in order to sort our large users list. I followed the instructions to sort users by comments, and then did the sort, and our website has become unresponsive, like the sort is consuming all server resources (for a long time).
what is going on? site has shown 503 service unavailable, and 504 gateway timeout.
hello again,
the website became responsive after about 20 minutes, and I immediately deactivated the Admin Columns Pro plugin. the users list is not extraordinarily large, with about 18,000 users. the plugin seems to be choking on this.
any help is appreciated. thank you.
Hi Guy, sorry to hear you experienced this problem. Let me help you.
Sorting users can take quite some resources depending on which column you sort. We optimise all queries as best as we can, but for some queries it’s necessairy to fire a lot of queries to get the correct sorting result. Sometimes this means going through all (18.000) users.
What column were your trying to sort when you had the time-out? And what is the memory limit of your server?
I will have a look at the query and see if there is a solution.
Best,
Tobias
hi Tobias,
(btw, this is Mo writing you here, Guy’s web admin/tech person).
just checked with phpinfo, and it is reporting memory_limit 256M.
I added the comment count to the Users view in WP, using Admin Columns. (I had moved it to the second column in the admin Users view).
I turned on sorting for comment count with Admin Columns Pro, and then clicked the top of the column in the Users view. and then the loooong consumption of all server resources started, causing total non-responsiveness of the website.
after the first period of non-responsiveness (with 503, 504, etc) ended after maybe 15 minutes, I thought “great, the sort is done!” and I went into WP admin to check my Users view, and see the result.
as soon as I clicked “Users” the whole thing started again – the website became non-responsive for another 15 minutes or more.
I figured the sort could take some resources, but as you could imagine, this is not the kind of thing we can allow for the website (at least not more than once, maybe late at night). if I could know that there would be a viewable (and workable) sort to look at after a pause, it might be OK. but I would have to know that I could easily page through the Users list, without triggering another loooong delay.
thanks again!
~mo
one quick question: would having Comment Count as the very first column in the view make the sort go much faster?
best,
~mo
Hi Mo,
The colum sort you are doing is indeed going through all the users and will run an additional query to get the comment count for each user, meaning 18k< queries in your case. It’s quite complex to optimise this. One of the solutions would be for us to write a custom SQL query which does all that, which will probably still strain the performance but perhaps a little less. I will have another look at the query and get back to you on this.
The position of the columns have no effect on the performance.
Best,
Tobias
hi Tobias,
thank you for all your efforts on this! it is very appreciated. right now, I am wondering about these questions:
1) if I set up Admin Columns Pro to sort the Users list based on Comment Count, then go into the Users view and click the top of the column to run the sort, wouldn’t the sort be completed after this ~15 minute chunk of time? when I can access WP again, it seems like I should be able to view the sorted list?
2) I thought I would be able to view the sorted list as above, but when I clicked “Users” to open the view, the whole process seemed to start all over again (as mentioned above). I had the view set to show 100 users at a time. nothing extraordinary. what exactly was going on with this second long delay?
it was after the second delay that I decided to deactivate the plugin. if I knew what to expect, as far as when I could actually see and use my sorted list without any further delays, this would allow me to possibly think about using the plugin as I intended. your thoughts?
thanks again!
~mo
Everytime you visit the page, the sorting will run again, even if the page has completed the sorting succesfull. The query isn’t being cached by WordPress.
We’ve been working on the sorting query, and we’re running a few test on them now. I will keep you updated.
I just send an optimized version to Guy for you to use.
hi Tobias,
I just sent you an email on this as well, but trying to install this version, with the zip file you sent us, gets:
“The package could not be installed. No valid plugins were found.
Plugin install failed.”
best,
~mo
You got mail :)
Hello. Can I get this optimize version as well? I’m geting timeout.
You must be logged in to reply to this topic.