Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.11
-
None
-
None
-
Debian package (bookworm), 10.11.11 since 03-15-2025
Description
Hello,
We are a university and have been using MariaDB version 10.11.9 for the past few months for our largest application: our LMS platform, Moodle (moodle.org).
Our database is 150GB and we average 3000 requests per second. Everything was going well until the last update, to version 10.11.11. Since then, we have noticed very high CPU usage (100% of our 26 CPUs), and our platform is no longer responding.
We have observed that this query keeps appearing in the slowqueries.log:
# Time: 250317 15:46:56 |
# Thread_id: 1182650 Schema: arche_prod QC_hit: No |
# Query_time: 6.400572 Lock_time: 0.000114 Rows_sent: 0 Rows_examined: 2976870 |
# Rows_affected: 0 Bytes_sent: 621 |
SET timestamp=1742222816; |
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' |
JOIN mdl_role_assignments ra ON ra.contextid = ctx.id
|
WHERE ctx.path LIKE '/1/%' |
AND ra.userid = '321938' |
UNION
|
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' |
JOIN mdl_role_capabilities rc ON rc.contextid = ctx.id
|
JOIN mdl_role_assignments rc_ra ON rc_ra.roleid = rc.roleid
|
JOIN mdl_context rc_ra_ctx ON rc_ra_ctx.id = rc_ra.contextid
|
WHERE ctx.path LIKE '/1/%' |
AND rc_ra.userid = '321938' |
AND (ctx.path = rc_ra_ctx.path OR ctx.path LIKE CONCAT(rc_ra_ctx.path, '/%')); |
When we revert to version 10.11.9, the issue disappears. Are you aware of any bug introduced in version 10.11.10 or 10.11.11 that could explain this performance anomaly?
We tried reindexing our tables, it didn't help.
Thanks for your help.
Attachments
Activity
Hello,
Thanks for the report.
Would it be possible for you to run the following and share the output (it may be large):
set optimizer_trace=1; |
set @@optimizer_adjust_secondary_key_costs='fix_card_multiplier,fix_reuse_range_for_ref'; |
ANALYZE FORMAT=JSON SELECT ... (the problem query); |
select * from information_schema.optimizer_trace\G |
|
|
set @@optimizer_adjust_secondary_key_costs='fix_card_multiplier'; |
ANALYZE FORMAT=JSON SELECT ... (the problem query); |
select * from information_schema.optimizer_trace\G |
Hello Sergei,
Here is the output asked, feel free to ask if you need something else.
FYI :
set @@optimizer_adjust_secondary_key_costs='fix_card_multiplier'; => the query took 0,018 sec
set @@optimizer_adjust_secondary_key_costs='fix_card_multiplier,fix_reuse_range_for_ref'; => the query took 0,669 sec
We found the origin of the problem :
In 10.11.9, the default value for optimizer_adjust_secondary_key_costs is empty, which works fine.
In 10.11.10, this new default value is optimizer_adjust_secondary_key_costs=fix_reuse_range_for_ref,fix_card_multiplier.
We have to remove the fix_reuse_range_for_ref to get our server running well again.