-
Bug
-
Resolution: Fixed
-
Major
-
4.1.4, 4.2.1
-
MySQL, PostgreSQL
-
MOODLE_401_STABLE, MOODLE_402_STABLE
-
MOODLE_401_STABLE, MOODLE_402_STABLE
-
MDL-78618_401 -
MDL-78618_402 -
-
Setup
- Login as admin.
- Navigate to Site Administration > Development > Debugging.
- Turn on the Performance info (checked).
- Copy maketestcategoriesandusers.php
to admin/tool/generator/cli/maketestcategoriesandusers.php - Run CLI:
php admin/tool/generator/cli/maketestcategoriesandusers.phpto create ~4000 course categories and 1000 test users. The script automatically assigns all users but the first one (testuser1) to a new test role in each course category.
Testing
- Login as testuser1 with password 123456.
- Run CLI: php admin/cli/purge_caches.php.
- Navigate to /my/courses.php.
- Note the time used in the footer. Example: 3.305101 secs.
- Logout.
- Apply the patch.
- Login as testuser1 with password 123456.
- Run CLI: php admin/cli/purge_caches.php.
- Navigate to /my/courses.php.
- Note the time used in the footer. Example: 0.480186 secs.
- Verify that the time used with patch is smaller than the time used without patch.
Setup Login as admin. Navigate to Site Administration > Development > Debugging. Turn on the Performance info (checked). Copy maketestcategoriesandusers.php to admin/tool/generator/cli/maketestcategoriesandusers.php Run CLI: php admin/tool/generator/cli/maketestcategoriesandusers.php to create ~4000 course categories and 1000 test users. The script automatically assigns all users but the first one (testuser1) to a new test role in each course category. Testing Login as testuser1 with password 123456. Run CLI: php admin/cli/purge_caches.php. Navigate to /my/courses.php. Note the time used in the footer. Example: 3.305101 secs. Logout. Apply the patch. Login as testuser1 with password 123456. Run CLI: php admin/cli/purge_caches.php. Navigate to /my/courses.php. Note the time used in the footer. Example: 0.480186 secs. Verify that the time used with patch is smaller than the time used without patch .
We are facing a very poor performance on my/courses.php for users with role student or teacher, in general, with those users without the capability to create/manage courses.
In our test environment with Moodle 4.1.4+ (Build: 20230623) (2022112804.03), including the MDL-76174 from May 30th, 2023, when a user without the 'create' capability goes to my/courses.php, in line 74 it calls to core_course_category::get_nearest_editable_subcategory($coursecat, ['create'])). Then, this method resolves after 3 minuts of execution time this SQL, generated by the course/classes/category.php:3199 (aprox).
As a sample SQL:
SELECT cc.id,
|
cc.name,
|
cc.idnumber,
|
cc.parent,
|
cc.sortorder,
|
cc.coursecount,
|
cc.visible,
|
cc.depth,
|
cc.path,
|
ctx.id AS ctxid,
|
ctx.path AS ctxpath,
|
ctx.depth AS ctxdepth,
|
ctx.contextlevel AS ctxlevel,
|
ctx.instanceid AS ctxinstance,
|
ctx.locked AS ctxlocked
|
FROM mdl_course_categories cc
|
JOIN mdl_context ctx ON cc.id = ctx.instanceid AND ctx.contextlevel = '40'
|
LEFT JOIN mdl_role_assignments ra ON ra.contextid = ctx.id
|
LEFT JOIN mdl_role_capabilities rc ON rc.contextid = ctx.id
|
LEFT JOIN mdl_role_assignments rc_ra ON rc_ra.roleid = rc.roleid
|
LEFT JOIN mdl_context rc_ra_ctx ON rc_ra_ctx.id = rc_ra.contextid
|
WHERE ctx.path LIKE '/1/%'
|
AND (
|
ra.userid = '110092'
|
OR (
|
rc_ra.userid = '110092'
|
AND (ctx.path = rc_ra_ctx.path OR ctx.path LIKE CONCAT(rc_ra_ctx.path, '/%'))
|
)
|
)
|
In production, with Moodle 3.11.15+ (Build: 20230623) (2021051715.02), the same SQL resolves after 2 minutes.
In both cases, it is a MySQL 5.7 database.
In production, and so in our test environment, we have these numbers:
mysql> select count(*) number_of_categories from mdl_course_categories;
|
+----------------------+
|
| number_of_categories |
|
+----------------------+
|
| 205 |
|
+----------------------+
|
1 row in set (0,00 sec)
|
|
|
mysql> select count(*) number_of_courses from mdl_course;
|
+-------------------+
|
| number_of_courses |
|
+-------------------+
|
| 21423 |
|
+-------------------+
|
1 row in set (0,02 sec)
|
|
|
mysql>
|
I think they are not so huge to explain this poor performance.
