Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.17
-
None
-
None
Description
After upgrading some of our servers from 10.1.48 to 10.6.17, we noticed soem serious performance regressions on some rather infrequent but quite important count
queries.
MariaDB 10.6.17 ANALYZE output:
MariaDB [marvin_production]> analyze select count(*) from ecommerce_support_events\G;
|
*************************** 1. row *************************** |
id: 1 |
select_type: SIMPLE
|
table: ecommerce_support_events
|
type: index
|
possible_keys: NULL
|
key: index_ecommerce_support_events_on_assignee_id
|
key_len: 5 |
ref: NULL
|
rows: 19463651 |
r_rows: 19867588.00 |
filtered: 100.00 |
r_filtered: 100.00 |
Extra: Using index
|
1 row in set (8.725 sec) |
|
|
ERROR: No query specified
|
MariaDB 10.1.48 ANALYZE output:
MariaDB [marvin_production]> analyze select count(*) from ecommerce_support_events\G;
|
*************************** 1. row *************************** |
id: 1 |
select_type: SIMPLE
|
table: ecommerce_support_events
|
type: index
|
possible_keys: NULL
|
key: index_ecommerce_support_events_on_assignee_id
|
key_len: 5 |
ref: NULL
|
rows: 21512006 |
r_rows: 19867586.00 |
filtered: 100.00 |
r_filtered: 100.00 |
Extra: Using index
|
1 row in set (3.08 sec) |
|
|
ERROR: No query specified
|
ANALYZE otuputs don't change between the versions but the query times have increased across all count queries by 1.5-2 times after the upgrade. We've found an interesting Percona article mentioning the following:
All the recent minor MariaDB versions are slow in the separate transaction context, while the impact is lower in some
|
very old minor versions, like 10.2.14.
|
|
|
Unfortunately, since major version 10.3, all MariaDB versions are also slow when the count query is executed within the
|
same transaction, making the MVCC impact very high; for example, MariaDB 10.3+ is ~37000% slower than 10.2 and
|
earlier in that context!
|
For what it's worth, trying to tweak a few optimizer switches didn't show any significant improvements.
Is this issue known? Are there any workarounds or shortcuts to making these queries as fast as before?