SELECT COUNT query really bringing down performance

SELECT COUNT query really bringing down performance

by Shimron Trammell -
Number of replies: 8
Hello Everyone,

We are running moodle 1.95 for 28 different school districts on 2 servers. The servers are fairly over spec-ed as HP DL380 G6's 2xQuad Core in each with 32GB of ram each. One for the MySQL server, and the other one is running Apache. Every once and a while we are running into a problem with the MySQL server taking a really long time to run certain queries (like 6000 seconds long). The latest one seems to be a SELECT COUNT query:

SELECT COUNT( DISTINCT c.recordid )
FROM mdl_data_content c, mdl_data_records r, mdl_data_content cs,


I know I'm missing the end of that query... the other admin that works on moodle here got angry and killed the process to free up that district. It is rather annoying though because it'll lock up a district and then eventually kills the apache server because everyone from that districts requests are waiting. Any help would be appreciated.
Average of ratings: -
In reply to Shimron Trammell

Re: SELECT COUNT query really bringing down performance

by Shimron Trammell -
Actually got the full query this time. Happened again this morning. Anyone know of an easy way to track down where certain queries come from?

SELECT  COUNT(DISTINCT c.recordid)  FROM mdl_data_content c,mdl_data_records r,mdl_data_content cs, mdl_user u , mdl_data_content c167 , mdl_data_content c166 , mdl_data_content c165 , mdl_data_content c164 , mdl_data_content c163 , mdl_data_content c171 , mdl_data_content c170 , mdl_data_content c169 , mdl_data_content c168  WHERE c.recordid = r.id
                        AND r.dataid = 21
                        AND r.userid = u.id
                        AND cs.recordid = r.id  AND u.id = 1332 AND c167.recordid = r.id AND c166.recordid = r.id AND c165.recordid = r.id AND c164.recordid = r.id AND c163.recordid = r.id AND c171.recordid = r.id AND c170.recordid = r.id AND c169.recordid = r.id AND c168.recordid = r.id
Average of ratings: -
In reply to Shimron Trammell

Re: SELECT COUNT query really bringing down performance

by Henning Bostelmann -
Picture of Core developers Picture of Plugin developers
This sounds like the "Advanced Search" screen in the database activity module;

mod/data/view.php around line 500.
Average of ratings: -
In reply to Henning Bostelmann

Re: SELECT COUNT query really bringing down performance

by Shimron Trammell -
Thanks! Sure enough went through and found a course with that module. Clicking on the course would basically lock the db. I would like to know how did you know it was there? Was that just a matter of happening to know where that particular query or is their a big master list somewhere that I can reference?
Average of ratings: -
In reply to Shimron Trammell

Re: SELECT COUNT query really bringing down performance

by Henning Bostelmann -
Picture of Core developers Picture of Plugin developers
I'm not aware of any master list of queries. I just searched for fragments of the query in the source code (which is a matter of seconds if you have the development environment set up). "COUNT(DISTINCT c.recordid)" gives you hits in only one file.

This method is not always guaranteed to work, but the more complicated queries are usually assembled literally in the source code.
Average of ratings: -
In reply to Shimron Trammell

Re: SELECT COUNT query really bringing down performance

by James McLean -
That is one messy query. Can your DBA generate some Indexes to help with it at all?
edit: actually, 6000 seconds, I read that as milliseconds.. that's a bloody long time!

Anything in the MySQL logs at all?
Average of ratings: -
In reply to Shimron Trammell

Re: SELECT COUNT query really bringing down performance

by Taylor Judd -
Are you running INNODB? (I do and recommend it.) The advantages you get with row locking and other efficiencies are great. However you do take a performance hit in count() functions.

The core developers could better speak to this better, but it is my understanding that most of the Moodle core and modules were developed with the notion that mysql myisam would be the default engine. This worked well until some of the larger sites started having locking issues. We switched to INNODB for this reason but now see issues with the count() functions which are used often in Moodle. In some cases used unnecessarily. Unfortunately in many cases there are no good work-around other than rewriting the quarry/function or disabling that module.

Here is a good article about the problems with count() in InnoDB:

http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/
Average of ratings: -
In reply to Taylor Judd

Re: SELECT COUNT query really bringing down performance

by Henning Bostelmann -
Picture of Core developers Picture of Plugin developers
While COUNT() is generally a quite slow function on relational databases, I rather doubt that the problem here is with the count statement as such. I would rather suspect the complicated (implied) JOINs in the query to cause the issue, with 9 instances of mdl_data_content. This may be just a bit too much for MySQL. (Remove some of these instances from the query, and it actually runs quite nicely.)

So, I would rather call this a bug in Moodle, since I can't see why the statement needs to be as complicated as it is. That said, if the query really needs to be that complicated, there seems to be an index missing on "mdl_data_content.recordid". (But this alone doesn't fix the problem, for all I can see.)

It is probably best if one of the developers has a look at this - should we open a ticket in the Moodle Tracker?
Average of ratings: -