[MDL-30643] Daily Statistics queries are too slow - Moodle Tracker

XML

Word

Printable

Details

Type: Sub-task
Status: Closed
Priority: Blocker
Resolution: Fixed
Affects Version/s: 2.0.3, 2.1, 2.4
Fix Version/s: 2.4
Component/s: Administration, Libraries, Performance
Labels:
- api_change
- netspot
- partner
- patch
- remote-learner
- triaged

Database:

MySQL
Affected Branches:
MOODLE_20_STABLE, MOODLE_21_STABLE, MOODLE_24_STABLE
Fixed Branches:
MOODLE_24_STABLE
Pull from Repository:
git://github.com/tbannister/moodle.git
Pull Master Branch:
~~MDL-30643~~_master-log-speed
Pull Master Diff URL:
https://github.com/tbannister/moodle/compare/master...MDL-30643_master-log-speed
Testing Instructions:

Hide

Run daily statistics with major log entries for a day.

With more than 150 000 entries, daily statistics generation can take 2 hours to resolve.

Also run phpunit tests across 5 DB's

Show
Run daily statistics with major log entries for a day. With more than 150 000 entries, daily statistics generation can take 2 hours to resolve. Also run phpunit tests across 5 DB's

Description

Daily statistics generation is really slow because SQL queries are not optimized properly. The propblem occurs principaly when you have a lots of entries in the log table (150 000 and more per day). We also observed that the server is responding slowly for users in Moodle while the daily statistics generation is running (Every inserts in log table take about 40-50 seconds).

December 5th cron log from our server

Running daily statistics gathering, starting at 1322974800:

................  finished until 1323061200: lundi 5 décembre 2011, 00:00 (in 6872 s)

...completed 1 days of statistics.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

MDL-30643_pu.log
25 kB
04/Sep/12 10:12 AM
MDL-30643-TZ_phpunit.rtf
67 kB
13/Nov/12 9:15 AM
mssql_phpunit_output.txt
49 kB
13/Nov/12 12:13 AM
mssql_phpunit_output2.txt
49 kB
13/Nov/12 10:58 PM
mssql_phpunit_output3.txt
57 kB
14/Nov/12 9:37 PM
oracle_phpunit_output.txt
49 kB
13/Nov/12 12:13 AM
oracle_phpunit_output2.txt
49 kB
13/Nov/12 10:58 PM
oracle_phpunit_output3.txt
55 kB
14/Nov/12 9:37 PM
phpunit_MDL-30643_debug_n.txt
54 kB
14/Nov/12 8:31 PM
stats-v2.patch
17 kB
30/Mar/12 12:38 PM

Issue Links

caused a regression

MDL-46773 Midnight failure in test_statslib_cron_daily

Closed

has a non-specific relationship to

MDL-36410 Extend our DB API to support temporary tables usage to allow for creation of more efficiently performing but not often components

Closed

has been marked as being related by

MDL-18484 Statistics are impossibly slow

Closed

MDL-36799 Cannot generate weekly and monthly statistics reports

Closed

MDL-36411 Allow asserts for phpunit tests during setUp() stage for setup failures.

Closed

MDL-36416 test_statslib_get_action_names() has the hard coded actions used in logging, perhaps these could be constants in stats lib or collated somewhere

Closed

(1 has been marked as being related by)

Activity

People

Assignee:: Tyler Bannister

Reporter:: Jean-Philippe Gaudreau

Integrator:: Aparup Banerjee

Tester:: Michael de Raadt

Participants:: Aparup Banerjee, CiBoT, Dan Marsden, Dan Poltawski, Eloy Lafuente (stronk7), Gerard Caulfield, Jean-Philippe Gaudreau, Justin Filip, Marina Glancy, Martin Dougiamas, Matteo Scaramuccia, Michael de Raadt, Mike Churchward, Petr Skoda, Sam Hemelryk, Tony Levi, Tyler Bannister

Component watchers:: David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo, David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo, Matteo Scaramuccia, David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo

Votes:: 31 Vote for this issue

Watchers:: 31 Start watching this issue

Dates

Created:: 08/Dec/11 10:21 AM

Updated:: 09/Oct/15 1:02 AM

Resolved:: 18/Nov/12 4:29 AM

Fix Release Date:: 3/Dec/12