Details
Critical Description
Since upgrading from 10.2 to 10.5.13 queries using IN clauses with many values have performance issues.
This is the same issue reported under MDEV-24117 whichi s fixed in 10.5.8. Even though it's fixed in 10.5.8, I can reproduce the same issue in 10.5.9 and 10.5.13 with the attached mariadbbug.sql in the MDEV-24117.
Attachments
Issue Links
- blocks
-
MDEV-24117 Memory management problem in statistics state for queries that have a large field IN (values) part
-
- Closed
-
-
-
- Closed
-
- duplicates
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue blocks |
| Link |
This issue blocks |
| Description |
Since upgrading from 10.2 to 10.5.13 queries using IN clauses with many values have performance issues.
This is the same issue reported under [ |
Since upgrading from 10.2 to 10.5.13 queries using IN clauses with many values have performance issues.
This is the same issue reported under |
| Affects Version/s | 10.5 [ 23123 ] | |
| Affects Version/s | 10.6 [ 24028 ] | |
| Affects Version/s | 10.7 [ 24805 ] |
| Priority | Blocker [ 1 ] | Major [ 3 ] |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.7 [ 24805 ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Assignee | Sergei Petrunia [ psergey ] |
caused by
commit c36720388d5
|
Author: Sergei Petrunia <psergey@askmonty.org>
|
Date: Thu Jan 28 21:43:55 2021 +0300
|
|
|
MDEV-9750: Quick memory exhaustion with 'extended_keys=on' ...
|
|
(Variant #5, full patch, for 10.5)
|
|
Do not produce SEL_ARG graphs that would yield huge numbers of ranges.
|
Introduce a concept of SEL_ARG graph's "weight". If we are about to
|
produce a graph whose "weight" exceeds the limit, remove the parts
|
of SEL_ARG graph that represent the biggest key parts. Do so until
|
the graph's is within the limit.
|
|
Includes
|
- debug code to verify SEL_ARG graph weight
|
- A user-visible @@optimizer_max_sel_arg_weight to control the optimization
|
- Logging the optimization into the optimizer trace.
|
| Link |
This issue relates to |
| Link |
This issue duplicates |
| Link |
This issue relates to |
| Priority | Major [ 3 ] | Critical [ 2 ] |
Testcase:
CREATE TABLE IF NOT EXISTS `mariadb_bug` ( |
`SubID` int(7) NOT NULL, |
`Token` bigint(20) unsigned NOT NULL, |
`Date` datetime NOT NULL DEFAULT current_timestamp(), |
`SteamID` varchar(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '', |
`IpAddress` varchar(40) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL, |
UNIQUE KEY `SubID` (`SubID`,`Token`,`SteamID`) USING BTREE |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; |
SELECT `SubID`, `Token` |
FROM `mariadb_bug` |
WHERE `SubID` IN (<13800 constants in quotes>); |
There seem to be no multi-part SEL_ARG trees...
| Component/s | Optimizer [ 10200 ] | |
| Fix Version/s | 10.5.17 [ 27509 ] | |
| Fix Version/s | 10.6.9 [ 27507 ] | |
| Fix Version/s | 10.7.5 [ 27505 ] | |
| Fix Version/s | 10.8.4 [ 27503 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.7 [ 24805 ] | |
| Resolution | Duplicate [ 3 ] | |
| Status | Confirmed [ 10101 ] | Closed [ 6 ] |
Does this apply to, and was it fixed in, the 10.4 line? I ask because once upon a time I was running 10.4.14 and experienced OOM failures we traced to MDEV-24117. I am now running 10.4.22 and my OOMs have returned. Note I have been running 10.4.22 for some time, and the OOMs only recently returned. I do not know what confluence of events is triggering the memory increase now leading to the OOM, but since this is apparently a regression of MDEV-24117 I am wondering if it could be impacting 10.4.22.
| Zendesk Related Tickets | 101714 |
Thanks for the report!
I could repeat the performance regression on 10.5-10.7 (0.1 in 10.4 vs 3.8 in 10.5), there is some problem with large IN (varchars)