Details
Critical Description
There is a significant difference in query performance with MariaDB 10.6 running on CentOS (2 seconds) compared to on Rocky (48 seconds). When setting split_materialized=off, the query executes a bit faster (7 seconds) for this particular query, but it is still slower than on CentOS.
Query used to reproduce:
set session optimizer_switch='split_materialized=on'; |
|
|
use b_test; |
|
|
select this_.`RMD_CURRENT_STATUS_VALUE` as y0_, count(*) as y1_ |
from `TEST_MAU_REQUEST_RECORD_VIEW` this_ |
where (lower(this_.`UAR_ALL_EMAILS`) like '%AbeFrazer8@example.com%' escape '!' |
or lower(this_.`UAR_APPROVER_STLS`) like '%AbeFrazer8@example.com%' escape '!' |
or lower(this_.`REQUEST_BY`) like '%AbeFrazer8@example.com%' escape '!') |
group by this_.`RMD_CURRENT_STATUS_VALUE` |
order by y0_ asc |
limit 50;
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Attachment | b_test.sql.zip [ 73458 ] |
| Attachment | b_test.sql.zip [ 73458 ] |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] |
| Assignee | Dave Gosselin [ JIRAUSER52216 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Description |
There is a significant difference in query performance with MariaDB 10.6 running on CentOS (2 seconds) compared to on Rocky (48 seconds). When setting split_materialized=off, the query executes a bit faster (7 seconds) for this particular query, but it is still slower than on CentOS.
Query used to reproduce: {quote}set session optimizer_switch='split_materialized=off'; use b_test; select this_.`RMD_CURRENT_STATUS_VALUE` as y0_, count(*) as y1_ from `TEST_MAU_REQUEST_RECORD_VIEW` this_ where (lower(this_.`UAR_ALL_EMAILS`) like '%AbeFrazer8@example.com%' escape '!' or lower(this_.`UAR_APPROVER_STLS`) like '%AbeFrazer8@example.com%' escape '!' or lower(this_.`REQUEST_BY`) like '%AbeFrazer8@example.com%' escape '!') group by this_.`RMD_CURRENT_STATUS_VALUE` order by y0_ asc limit 50;{quote} |
There is a significant difference in query performance with MariaDB 10.6 running on CentOS (2 seconds) compared to on Rocky (48 seconds). When setting split_materialized=off, the query executes a bit faster (7 seconds) for this particular query, but it is still slower than on CentOS.
Query used to reproduce: {code:sql}set session optimizer_switch='split_materialized=off'; use b_test; select this_.`RMD_CURRENT_STATUS_VALUE` as y0_, count(*) as y1_ from `TEST_MAU_REQUEST_RECORD_VIEW` this_ where (lower(this_.`UAR_ALL_EMAILS`) like '%AbeFrazer8@example.com%' escape '!' or lower(this_.`UAR_APPROVER_STLS`) like '%AbeFrazer8@example.com%' escape '!' or lower(this_.`REQUEST_BY`) like '%AbeFrazer8@example.com%' escape '!') group by this_.`RMD_CURRENT_STATUS_VALUE` order by y0_ asc limit 50;{code} |
| Assignee | Dave Gosselin [ JIRAUSER52216 ] | Marko Mäkelä [ marko ] |
| Labels | optimizer option query | innodb optimizer option query |
| Labels | innodb optimizer option query | innodb option query |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] |
| Status | In Progress [ 3 ] | Needs Feedback [ 10501 ] |
| Status | Needs Feedback [ 10501 ] | Open [ 1 ] |
| Assignee | Marko Mäkelä [ marko ] | Axel Schwenke [ axel ] |
| Assignee | Axel Schwenke [ axel ] | |
| Status | Open [ 1 ] | Needs Feedback [ 10501 ] |
| Assignee | Sergei Golubchik [ serg ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Status | Needs Feedback [ 10501 ] | Open [ 1 ] |
| Attachment | mdev-34043_analyze_rl9.json [ 73669 ] |
| Attachment | mdev-34043_analyze_rl9_ext4.json [ 73670 ] |
| Attachment | mdev-34043_analyze_centos7.json [ 73698 ] |
| Assignee | Sergei Golubchik [ serg ] | Dave Gosselin [ JIRAUSER52216 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Attachment | rl9_analyze_json_10.6.txt [ 73704 ] | |
| Attachment | rl9_analyze_json_11.5.txt [ 73705 ] |
| Zendesk Related Tickets | 201748 | |
| Zendesk active tickets | 201748 |
| Attachment | mdev-34043.tgz [ 73907 ] |
| Assignee | Dave Gosselin [ JIRAUSER52216 ] | Sergei Petrunia [ psergey ] |
| Assignee | Sergei Petrunia [ psergey ] | Dave Gosselin [ JIRAUSER52216 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Assignee | Dave Gosselin [ JIRAUSER52216 ] | Sergei Petrunia [ psergey ] |
| Assignee | Sergei Petrunia [ psergey ] | Michael Widenius [ monty ] |
| Assignee | Michael Widenius [ monty ] | Sergei Petrunia [ psergey ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
| Description |
There is a significant difference in query performance with MariaDB 10.6 running on CentOS (2 seconds) compared to on Rocky (48 seconds). When setting split_materialized=off, the query executes a bit faster (7 seconds) for this particular query, but it is still slower than on CentOS.
Query used to reproduce: {code:sql}set session optimizer_switch='split_materialized=off'; use b_test; select this_.`RMD_CURRENT_STATUS_VALUE` as y0_, count(*) as y1_ from `TEST_MAU_REQUEST_RECORD_VIEW` this_ where (lower(this_.`UAR_ALL_EMAILS`) like '%AbeFrazer8@example.com%' escape '!' or lower(this_.`UAR_APPROVER_STLS`) like '%AbeFrazer8@example.com%' escape '!' or lower(this_.`REQUEST_BY`) like '%AbeFrazer8@example.com%' escape '!') group by this_.`RMD_CURRENT_STATUS_VALUE` order by y0_ asc limit 50;{code} |
There is a significant difference in query performance with MariaDB 10.6 running on CentOS (2 seconds) compared to on Rocky (48 seconds). When setting split_materialized=off, the query executes a bit faster (7 seconds) for this particular query, but it is still slower than on CentOS.
Query used to reproduce: {code:sql}set session optimizer_switch='split_materialized=on'; use b_test; select this_.`RMD_CURRENT_STATUS_VALUE` as y0_, count(*) as y1_ from `TEST_MAU_REQUEST_RECORD_VIEW` this_ where (lower(this_.`UAR_ALL_EMAILS`) like '%AbeFrazer8@example.com%' escape '!' or lower(this_.`UAR_APPROVER_STLS`) like '%AbeFrazer8@example.com%' escape '!' or lower(this_.`REQUEST_BY`) like '%AbeFrazer8@example.com%' escape '!') group by this_.`RMD_CURRENT_STATUS_VALUE` order by y0_ asc limit 50;{code} |
| Attachment | b_test.sql.zip [ 73969 ] |
| Link | This issue blocks TODO-4816 [ TODO-4816 ] |
| Assignee | Sergei Petrunia [ psergey ] | Michael Widenius [ monty ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| issue.field.resolutiondate | 2024-08-22 07:11:06.0 | 2024-08-22 07:11:05.871 |
| Component/s | Storage Engine - Aria [ 10126 ] | |
| Fix Version/s | 10.5.27 [ 29902 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Fix Version/s | 10.6.20 [ 29903 ] | |
| Fix Version/s | 10.11.10 [ 29904 ] | |
| Fix Version/s | 11.2.6 [ 29906 ] | |
| Fix Version/s | 11.4.4 [ 29907 ] |
| Link | This issue blocks MENT-2125 [ MENT-2125 ] |