-
Bug
-
Resolution: Unresolved
-
Major
-
None
-
1.9.2
-
None
-
Moodle 1.9.2, MySQL 5.0.77, PHP 5.1.6, Apache 2.2.3, Centos 5 on VMWare
-
MySQL
-
MOODLE_19_STABLE
If a user views a wiki and clicks on "Wiki changes" link and then the "New pages" tab, the page takes a long time to load. At the same time, the server load goes up (with most of the CPU time spent on MySQL) and so do the numbers of MySQL and Apache processes. We have tracked down the problem to a query inside the ouwiki_get_subwiki_recentpages function in mod/ouwiki/ouwiki.php. (I believe the function has been move to locallib.php in later versions of Moodle.) The query looks like this:
SELECT p.id AS pageid, p.subwikiid, p.title, p.currentversionid, v.id AS versionid, v.timecreated, v.userid, u.firstname, u.lastname, u.username |
FROM mdl_ouwiki_versions v |
INNER JOIN mdl_ouwiki_pages p ON v.pageid = p.id |
LEFT JOIN mdl_user u ON v.userid = u.id |
WHERE v.id |
IN ( |
SELECT MIN( v2.id ) |
FROM mdl_ouwiki_pages p2 |
INNER JOIN mdl_ouwiki_versions v2 ON v2.pageid = p2.id |
WHERE p2.subwikiid =790 |
GROUP BY p2.id |
)
|
ORDER BY v.id DESC |
LIMIT 51
|
The query looks the same in the latest version of OU wiki in CVS. The MySQL EXPLAIN looks like this:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | PRIMARY | v | index | mdl_ouwivers_pag_ix | PRIMARY | 8 | NULL | 11483 | Using where |
| 1 | PRIMARY | p | eq_ref | PRIMARY | PRIMARY | 8 | moodle.v.pageid | 1 | |
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 8 | moodle.v.userid | 1 | |
| 2 | DEPENDENT SUBQUERY | p2 | index | PRIMARY | PRIMARY | 8 | NULL | 3027 | Using where; Using filesort |
| 2 | DEPENDENT SUBQUERY | v2 | ref | mdl_ouwivers_pag_ix | mdl_ouwivers_pag_ix | 8 | moodle.p2.id | 12 |
11483 is the number of records in our mdl_ouwiki_versions table, so MySQL is effectively doing a table scan on the table, and this explains the high load.
Has anyone else come across this problem?