[MDL-69641] Fix Course gradebook slow query due to cross join on full user table (backport of MDL-69190) - Moodle Tracker

XML

Word

Printable

Details

Type: Bug
Status: Closed
Priority: Minor
Resolution: Fixed
Affects Version/s: 3.8.4, 3.8.5, 3.9.1, 3.9.2
Fix Version/s: 3.8.6, 3.9.3
Component/s: Gradebook, Performance
Labels:

Affected Branches:
MOODLE_38_STABLE, MOODLE_39_STABLE
Fixed Branches:
MOODLE_38_STABLE, MOODLE_39_STABLE
Pull from Repository:
https://github.com/dmitriim/moodle
Pull 3.9 Branch:
~~MDL-69641~~-MOODLE_39_STABLE
Pull 3.9 Diff URL:
https://github.com/moodle/moodle/compare/MOODLE_39_STABLE...dmitriim:MDL-69641-MOODLE_39_STABLE
Testing Instructions:
Hide

Enable debugging

Enable perf debugging

Edit config.php and add the following line above require_once(_DIR_ . '/lib/setup.php');

ini_set('memory_limit', '-1');

Create a Large course (Site admin -> Development -> Make test course)

Navigate to the course -> Grades

Refresh the page a couple of times

Note the DB query time in the footer

Open a terminal and find the commit:

git log --oneline --grep="MDL-69641"

Grab the commit hash and revert the commit, i.e.

git revert 68115bf9870

Navigate to the course -> Grades again

Refresh the page a couple of times

Confirm that the times are slower than the times noted earlier

Please test in all supported databases._
Show
Enable debugging Enable perf debugging Edit config.php and add the following line above require_once(_ DIR _ . '/lib/setup.php'); ini_set('memory_limit', '-1'); Create a Large course (Site admin -> Development -> Make test course) Navigate to the course -> Grades Refresh the page a couple of times Note the DB query time in the footer Open a terminal and find the commit: git log --oneline --grep="MDL-69641" Grab the commit hash and revert the commit, i.e. git revert 68115bf9870 Navigate to the course -> Grades again Refresh the page a couple of times Confirm that the times are slower than the times noted earlier Please test in all supported databases._

Description

Moodle 3.9 is LTS and most of the big instances will be on this version for some time and won't upgrade to 3.10 or 3.11. Would be good to help them to improve the performance by backporting ~~MDL-69190~~.

Also it's still a kind of mystery why it wasn't integrated before. Seems like during the integration dobedobedoh had seen some issues with the patch. However, there is no actual description of what those issues were. Would be good to figure it out and backport SQL improvement for earlier versions.

Attachments

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

screenshot-1.png
95 kB
06/Oct/20 3:21 PM

Issue Links

will be (partly) resolved by

MDL-69190 Course gradebook slow query due to cross join on full user table.

Closed

Activity

People

Assignee:: Dmitrii Metelkin

Reporter:: Dmitrii Metelkin

Peer reviewer:: Dan Marsden

Integrator:: Sara Arjona (@sarjona)

Tester:: Janelle Barcega

Participants:: CiBoT, Dan Marsden, Dmitrii Metelkin, Eloy Lafuente (stronk7), Janelle Barcega, Sara Arjona (@sarjona)

Component watchers:: Adrian Greeve, Ilya Tregubov, Kevin Percy, Mathew May, Mihail Geshoski, Shamim Rezaie, Matteo Scaramuccia, David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo

Votes:: 1 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 07/Sep/20 7:48 AM

Updated:: 04/Nov/20 3:49 PM

Resolved:: 06/Oct/20 4:46 PM

Fix Release Date:: 9/Nov/20

Time Tracking

Estimated:

Not Specified

Remaining:

Logged:

3h 30m