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.
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)
Alice Sherepa
added a comment - 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)
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.
Sergei Golubchik
added a comment - 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.
Sergei Petrunia
added a comment - 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...
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.
Mark Reibert
added a comment - - edited 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.
People
Sergei Petrunia
Thilanka
Votes:
1Vote for this issue
Watchers:
8Start watching this issue
Dates
Created:
Updated:
Resolved:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
{"report":{"fcp":1645,"ttfb":778.0999999046326,"pageVisibility":"visible","entityId":106269,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"a1caecf6-0897-49d0-a34c-e1c047942ae3","navigationType":0,"readyForUser":1790.5999999046326,"redirectCount":0,"resourceLoadedEnd":1336.3000001907349,"resourceLoadedStart":788.2000002861023,"resourceTiming":[{"duration":212,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":788.2000002861023,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":788.2000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1000.2000002861023,"responseStart":0,"secureConnectionStart":0},{"duration":213.40000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":788.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":788.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1001.9000000953674,"responseStart":0,"secureConnectionStart":0},{"duration":435.19999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":788.9000000953674,"connectEnd":788.9000000953674,"connectStart":788.9000000953674,"domainLookupEnd":788.9000000953674,"domainLookupStart":788.9000000953674,"fetchStart":788.9000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":1006.7000002861023,"responseEnd":1224.0999999046326,"responseStart":1031.5999999046326,"secureConnectionStart":788.9000000953674},{"duration":546.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true&whisper-enabled=true","startTime":789.8000001907349,"connectEnd":1005.7000002861023,"connectStart":1005.7000002861023,"domainLookupEnd":1005.7000002861023,"domainLookupStart":1005.7000002861023,"fetchStart":789.8000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":1006.2000002861023,"responseEnd":1336.3000001907349,"responseStart":1018.9000000953674,"secureConnectionStart":1005.7000002861023},{"duration":232.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":790,"connectEnd":790,"connectStart":790,"domainLookupEnd":790,"domainLookupStart":790,"fetchStart":790,"redirectEnd":0,"redirectStart":0,"requestStart":1006.7000002861023,"responseEnd":1022.5,"responseStart":1021.5,"secureConnectionStart":790},{"duration":235.39999961853027,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":790.2000002861023,"connectEnd":790.2000002861023,"connectStart":790.2000002861023,"domainLookupEnd":790.2000002861023,"domainLookupStart":790.2000002861023,"fetchStart":790.2000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":1007.9000000953674,"responseEnd":1025.5999999046326,"responseStart":1023.8000001907349,"secureConnectionStart":790.2000002861023},{"duration":234.80000019073486,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":790.4000000953674,"connectEnd":790.4000000953674,"connectStart":790.4000000953674,"domainLookupEnd":790.4000000953674,"domainLookupStart":790.4000000953674,"fetchStart":790.4000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":1007.7000002861023,"responseEnd":1025.2000002861023,"responseStart":1023.3000001907349,"secureConnectionStart":790.4000000953674},{"duration":215.89999961853027,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":790.7000002861023,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":790.7000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1006.5999999046326,"responseStart":0,"secureConnectionStart":0},{"duration":266.19999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":790.8000001907349,"connectEnd":790.8000001907349,"connectStart":790.8000001907349,"domainLookupEnd":790.8000001907349,"domainLookupStart":790.8000001907349,"fetchStart":790.8000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":1009.5,"responseEnd":1057,"responseStart":1053.0999999046326,"secureConnectionStart":790.8000001907349},{"duration":217.90000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":791,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":791,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1008.9000000953674,"responseStart":0,"secureConnectionStart":0},{"duration":244.19999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":791.3000001907349,"connectEnd":791.3000001907349,"connectStart":791.3000001907349,"domainLookupEnd":791.3000001907349,"domainLookupStart":791.3000001907349,"fetchStart":791.3000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":1012,"responseEnd":1035.5,"responseStart":1033,"secureConnectionStart":791.3000001907349},{"duration":534.6999998092651,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":792.4000000953674,"connectEnd":792.4000000953674,"connectStart":792.4000000953674,"domainLookupEnd":792.4000000953674,"domainLookupStart":792.4000000953674,"fetchStart":792.4000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":1313.2000002861023,"responseEnd":1327.0999999046326,"responseStart":1325,"secureConnectionStart":792.4000000953674},{"duration":532.9000000953674,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":793.5999999046326,"connectEnd":793.5999999046326,"connectStart":793.5999999046326,"domainLookupEnd":793.5999999046326,"domainLookupStart":793.5999999046326,"fetchStart":793.5999999046326,"redirectEnd":0,"redirectStart":0,"requestStart":1313.9000000953674,"responseEnd":1326.5,"responseStart":1324.2000002861023,"secureConnectionStart":793.5999999046326},{"duration":169.90000009536743,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1361.4000000953674,"connectEnd":1361.4000000953674,"connectStart":1361.4000000953674,"domainLookupEnd":1361.4000000953674,"domainLookupStart":1361.4000000953674,"fetchStart":1361.4000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":1499.4000000953674,"responseEnd":1531.3000001907349,"responseStart":1530.7000002861023,"secureConnectionStart":1361.4000000953674}],"fetchStart":0,"domainLookupStart":581,"domainLookupEnd":581,"connectStart":581,"connectEnd":600,"secureConnectionStart":590,"requestStart":601,"responseStart":778,"responseEnd":791,"domLoading":782,"domInteractive":1884,"domContentLoadedEventStart":1884,"domContentLoadedEventEnd":1948,"domComplete":2631,"loadEventStart":2631,"loadEventEnd":2632,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1846.5999999046326},{"name":"bigPipe.sidebar-id.end","time":1847.4000000953674},{"name":"bigPipe.activity-panel-pipe-id.start","time":1847.5},{"name":"bigPipe.activity-panel-pipe-id.end","time":1851},{"name":"activityTabFullyLoaded","time":1971.9000000953674}],"measures":[],"correlationId":"f542075e2b63d1","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":107,"dbReadsTimeInMs":16,"dbConnsTimeInMs":27,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Critical MDEV-24117 Memory management problem in statistics state for queries that have a large field IN (values) part
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)