|
[
Permalink
| « Hide
]
Anthony Borrow added a comment - 16/Sep/09 12:41 AM
Thanks Dakota for creating this issue. I am now watching this and hope to look into what needs to be done to resolve it. Peace - Anthony
I look a quick look and did not notice anything obvious to me as to what may be causing this to lock up as it is. I suspect there is a way to improve the performance but at this point I am going to move on to other issues. Peace - Anthony
While not necessarily related to the database activity module, here is another example of where sql inefficiency could bring a site to a halt (and has). In this case however it is only the admin that can really do it so it is less of a security concern. Nevertheless, if someone wants to take a crack at improving getting the user profile fields that would be great. I would think that for exporting purposes we should be able to get away with a single query to user_info_data per user and to have an $data_fields variable holding the contents of user_info_field and work through things differently. It has been a while since I have looked at the current algorithm but Dakota Duff of Remote-Learner did some testing for one of my sites and confirmed that exporting the users was creating an excessively high number of queries on a site with about 6000 users. Peace - Anthony
Hello, I work with Dakota and I've been doing some testing on this. It looks like this is directly related to the number of custom profile fields a site has.
On the site Dakota mentioned in the description, they have 18 custom profile fields. When downloading 2137 users (below the point at which it starts failing) in Excel format, the MySQL server received 81385 queries in 26 seconds. In the immediately following 5 minutes, there were only 7174 queries. The queries per second since startup is 8.96 for about 31 days, but during those 26 seconds, the queries per second was about 3130! On another site, with 11 custom profile fields, I downloaded 2700 users (also below the point at which it starts failing) in Excel format. The MySQL server received 59480 queries in 45 seconds. And in the immediately following 5 minutes, there were only 2340 queries. That server has also been up for about 31 days, during which time it has received 6.21 queries per second. It received about 1322 queries per seconds during the test. I didn't really stress test the first site, but the second site I mentioned starts failing at about 2722 users. Jonathan - Thanks for the data. Better understanding what is happening and the impact on the database is helpful. Speaking theoretically and without looking at code, I think I would like to see that we obtain all of the fields and data for a given user with a single query and then process the data in the code. We are are retrieving the user data in blocks (say 100 users at a time), then we could have one query from mdl_user and another to get the user profile data for all of those users. Memory wise it will be more expensive but database wise we can make some improvements. I'll put this back on to my active radar of things to play with when I have time since it sounds like you have other folks running into this issue as well. Peace - Anthony
|
|||||||||||||||||||||||||||||||||||||||||||||||||
Bug
Open
Minor
Bulk user actions -> Download as Excel/ODS fails with custom fields
 |
|
