-
Bug
-
Resolution: Fixed
-
Minor
-
4.2
-
MOODLE_402_STABLE
-
MOODLE_402_STABLE
-
MDL-78313-blocks-performance -
Before patch
- Create a new Moodle installation.
- Enable developer mode (e.g. using MDK: mdk run mindev)
- Download blocks_generator_cli.php
to your Moodle installation's root folder and run it on the terminal.
php blocks_generator_cli.phpThis will create 50 users and 50 courses on your site with a bunch of block instances.
- Log in to your test site as an admin
- Go to "Site administration > Plugins > Manage blocks"
- Reload the "Manage blocks" page a few times. For each reload, take note of the "DB queries time" information at the bottom of the page.
- Take note of the numbers on the instances. (You may want to take a screenshot of the page as a reference for later comparison)
After the patch
- With the patch applied, reload the "Manage blocks" page a few times. For each reload, take note of the "DB queries time" information at the bottom of the page.
- Confirm that the page now reloads significantly faster than before the patch.
- Check the number of instances for each block.
- Confirm that they match the numbers from before.
- Click on a link on an instance count.
- Confirm that you are redirected to a page with a list of courses. (before the patch, it would just be a blank screen with no courses listed)
Before patch Create a new Moodle installation. Enable developer mode (e.g. using MDK: mdk run mindev ) Download blocks_generator_cli.php to your Moodle installation's root folder and run it on the terminal. php blocks_generator_cli.php This will create 50 users and 50 courses on your site with a bunch of block instances. Log in to your test site as an admin Go to " Site administration > Plugins > Manage blocks " Reload the " Manage blocks " page a few times. For each reload, take note of the " DB queries time " information at the bottom of the page. Take note of the numbers on the instances. (You may want to take a screenshot of the page as a reference for later comparison) After the patch With the patch applied, reload the " Manage blocks " page a few times. For each reload, take note of the " DB queries time " information at the bottom of the page. Confirm that the page now reloads significantly faster than before the patch. Check the number of instances for each block. Confirm that they match the numbers from before. Click on a link on an instance count. Confirm that you are redirected to a page with a list of courses. (before the patch, it would just be a blank screen with no courses listed)
Doing some testing of Moodle 4.2 we kept failing to load the admin/blocks.php page in sites with real data. works fine in clean install for some reason.
Traced it back to this query - https://github.com/moodle/moodle/blob/MOODLE_402_STABLE/admin/classes/table/block_management_table.php#L44-L56 that was implemented in MDL-75667 and moved around into a different file in MDL-76867
The query just never completes when run on the database directly.
Doing a few tests with the query, it seems the individual left joins work fine but it's when both are included in the query that things fall over.
i.e
This works fine
SELECT b.name,b.id,b.name
|
FROM mdl_block b
|
LEFT JOIN mdl_block_instances bcinst ON bcinst.blockname = b.name AND bcinst.pagetypepattern = 'course-view-*' |
GROUP BY b.id, b.name, b.visible
|
ORDER BY b.name ASC
|
and so does this
SELECT b.name,b.id,b.name
|
FROM mdl_block b
|
LEFT JOIN mdl_block_instances binst ON binst.blockname = b.name
|
GROUP BY b.id, b.name, b.visible
|
ORDER BY b.name ASC
|
but this does not
SELECT b.name,b.id,b.name
|
FROM mdl_block b
|
LEFT JOIN mdl_block_instances binst ON binst.blockname = b.name
|
LEFT JOIN mdl_block_instances bcinst ON bcinst.blockname = b.name AND bcinst.pagetypepattern = 'course-view-*' |
GROUP BY b.id, b.name, b.visible
|
ORDER BY b.name ASC
|
For reference, our site has 200 000 block instances.
This is in MySQL 8.0 on AWS RDS serverless v2 but we have also tested the query in MySQL5.7 Enterprise on traditional hardware with the same outcome.
- is a regression caused by
-
MDL-75667 Improve speed of admin/blocks.php by combining db counts
-
- Closed
-
