Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Major
-
Resolution: Won't Fix
-
Affects Version/s: 1.9.4
-
Fix Version/s: None
-
Component/s: Unknown
-
Labels:None
-
Environment:Ubuntu 8.04, MySQL 5.1.33
-
Database:MySQL
-
Affected Branches:MOODLE_19_STABLE
Description
After I upgrade to moodle 1.9+, I found our website stalls every morning. And when I check mysql-slow log, I found this:
- Time: 090410 9:33:05
- User@Host: moodle[moodle] @ localhost []
- Query_time: 26566.176123 Lock_time: 0.000264 Rows_sent: 0 Rows_examined: 3633693755
use moodle;
SET timestamp=1239327185;
UPDATE mdl_stats_daily
SET stat2 = (SELECT COUNT(DISTINCT ra.userid)
FROM mdl_role_assignments ra JOIN mdl_context ctx
ON ctx.id = ra.contextid
CROSS JOIN mdl_course c
JOIN mdl_role_capabilities rc
ON rc.roleid = ra.roleid LEFT OUTER JOIN mdl_course_categories cc1
ON cc1.id = c.category
WHERE ra.roleid = mdl_stats_daily.roleid AND
c.id = mdl_stats_daily.courseid AND
((rc.capability = 'moodle/course:view' )
AND rc.permission = 1 AND rc.contextid = 1
AND (ctx.contextlevel = 10
OR (c.id = ctx.instanceid AND ctx.contextlevel = 50) OR (cc1.id = ctx.instanceid AND ctx.contextlevel = 40))) AND
EXISTS (SELECT 'x'
FROM mdl_log l
WHERE l.course = mdl_stats_daily.courseid AND
l.userid = ra.userid AND l.time >= 1239033600 AND l.time < 1239120000))
WHERE mdl_stats_daily.stattype = 'enrolments' AND
mdl_stats_daily.timeend = 1239120000 AND
mdl_stats_daily.courseid IN
(SELECT DISTINCT l.course
FROM mdl_log l
WHERE l.time >= 1239033600 AND l.time < 1239120000);
Is one of my table too big or it's missing some index? I have emptied log table before I upgrade because updating log uses too many time.
We have the same problem here with moodle 1.9 running on PostgreSQL.
The same "UPDATE mdl_stats_daily" query locks up one entire postgres process at nearly 100% and we have to terminate the query manually.
Usually the stats processing takes about 200 seconds per day, but some days the query will run forever.