-
Bug
-
Resolution: Unresolved
-
Major
-
None
-
3.7.7, 3.8.4, 3.9.4, 3.10.1
-
None
-
PostgreSQL
-
MOODLE_310_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
A timeout and poor performance is still encountered on Moodle User Report Logs. This happens on Site-wide log report and course with large number of enrolled users/lots of activities.
Example for site-wide report log: total mdl_logstore_standard_log records = 1,863,847,613 (Postgresql 11.7 version) another site with total of 196,823,363 (Postgresql 9.6.12) gives timeout error when viewing the site logs.
Going to Site administration / Reports / Logs and displaying all the dates report result into time out.
Example for course report log where timeout is also experienced.
EXPLAIN ANALYSE SELECT * FROM mdl_logstore_standard_log WHERE courseid = 12345 AND anonymous = 0 ORDER BY timecreated DESC, id DESC; |
QUERY PLAN
|
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|
Sort (cost=785132.38..785681.12 rows=219496 width=213) (actual time=215704.564..215798.965 rows=249815 loops=1) |
Sort Key: timecreated DESC, id DESC
|
Sort Method: external merge Disk: 54616kB
|
-> Bitmap Heap Scan on mdl_logstore_standard_log (cost=8090.40..743150.37 rows=219496 width=213) (actual time=2830.373..215110.653 rows=249815 loops=1) |
Recheck Cond: ((courseid = 12345) AND (anonymous = 0)) |
Rows Removed by Index Recheck: 3451937 |
Heap Blocks: exact=25900 lossy=108313 |
-> Bitmap Index Scan on mdl_logsstanlog_couanotim_ix (cost=0.00..8035.53 rows=219496 width=0) (actual time=2823.003..2823.003 rows=249815 loops=1) |
Index Cond: ((courseid = 12345) AND (anonymous = 0)) |
Planning time: 0.085 ms |
---> Execution time: 215818.557 ms (3.59 in Minutes) |
Additional note:
- Timeout was increased on load balancer but did not fixed the issue.
- Code fix from
MDL-52482and/orMDL-44998did not fixed the issue.
Thank you.
