The following test demonstrates that while InnoDB is collecting statistics are collected on a large table, any access from SQL to that table will be unnecessarily blocked by dict_table_stats_lock():
We can allow multiple threads to collect statistics for the same table in parallel, but only one thread at a time may be allowed to update the statistics. To prevent unnecessary work for multiple threads collecting statistics for the same table, we might introduce a counter of pending statistics-gathering operations. MySQL 5.7.32 preferred to introduce a lazily initialized mutex, which would not be easy to merge to 10.4.
Attachments
Issue Links
blocks
MDEV-23989Merge new release of InnoDB 5.7.32 to 10.2
Closed
causes
MDEV-24275InnoDB persistent stats analyze forces full scan forcing lock crash
I think that we should properly separate the statistics collection and the updates. If we do that, updating and accessing the statistics in dict_table_t and dict_index_t should be quick enough to be protected by dict_sys->mutex. Updating the persistent statistics tables will be holding it already.
Collecting that statistics can be protected by normal dict_index_t::lock and buf_block_t::lock. I do not think that multiple concurrent threads collecting statistics on the same index are going to be an issue.
Marko Mäkelä
added a comment - I think that we should properly separate the statistics collection and the updates. If we do that, updating and accessing the statistics in dict_table_t and dict_index_t should be quick enough to be protected by dict_sys->mutex . Updating the persistent statistics tables will be holding it already.
Collecting that statistics can be protected by normal dict_index_t::lock and buf_block_t::lock . I do not think that multiple concurrent threads collecting statistics on the same index are going to be an issue.
The fix so far looks good, except that in a few places we seem to be violating the latching order, by acquiring dict_sys->mutex while holding an index->lock. Be sure to release the latch via mtr_t::commit() or similar, to fix this. Run tests with ./mtr --mysqld=--loose-innodb-debug-sync to catch this.
Marko Mäkelä
added a comment - The fix so far looks good, except that in a few places we seem to be violating the latching order, by acquiring dict_sys->mutex while holding an index->lock . Be sure to release the latch via mtr_t::commit() or similar, to fix this. Run tests with ./mtr --mysqld=--loose-innodb-debug-sync to catch this.
People
Eugene Kosov (Inactive)
Marko Mäkelä
Votes:
0Vote for this issue
Watchers:
2Start 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":1780.199999988079,"ttfb":807.3000000119209,"pageVisibility":"visible","entityId":92918,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"6841eec9-5a8d-4f45-85bb-d8f38e8d086b","navigationType":0,"readyForUser":1861.199999988079,"redirectCount":0,"resourceLoadedEnd":2336.5,"resourceLoadedStart":813.1000000238419,"resourceTiming":[{"duration":430,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":813.1000000238419,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":813.1000000238419,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1243.1000000238419,"responseStart":0,"secureConnectionStart":0},{"duration":430.10000002384186,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/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","startTime":813.3999999761581,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":813.3999999761581,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1243.5,"responseStart":0,"secureConnectionStart":0},{"duration":504.19999998807907,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":813.5,"connectEnd":813.5,"connectStart":813.5,"domainLookupEnd":813.5,"domainLookupStart":813.5,"fetchStart":813.5,"redirectEnd":0,"redirectStart":0,"requestStart":813.5,"responseEnd":1317.699999988079,"responseStart":1317.699999988079,"secureConnectionStart":813.5},{"duration":572.4000000357628,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/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","startTime":813.6999999880791,"connectEnd":813.6999999880791,"connectStart":813.6999999880791,"domainLookupEnd":813.6999999880791,"domainLookupStart":813.6999999880791,"fetchStart":813.6999999880791,"redirectEnd":0,"redirectStart":0,"requestStart":813.6999999880791,"responseEnd":1386.1000000238419,"responseStart":1386.1000000238419,"secureConnectionStart":813.6999999880791},{"duration":506,"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":813.8999999761581,"connectEnd":813.8999999761581,"connectStart":813.8999999761581,"domainLookupEnd":813.8999999761581,"domainLookupStart":813.8999999761581,"fetchStart":813.8999999761581,"redirectEnd":0,"redirectStart":0,"requestStart":813.8999999761581,"responseEnd":1319.8999999761581,"responseStart":1319.8999999761581,"secureConnectionStart":813.8999999761581},{"duration":506.5,"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":814,"connectEnd":814,"connectStart":814,"domainLookupEnd":814,"domainLookupStart":814,"fetchStart":814,"redirectEnd":0,"redirectStart":0,"requestStart":814,"responseEnd":1320.5,"responseStart":1320.5,"secureConnectionStart":814},{"duration":575.6999999880791,"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":814.1999999880791,"connectEnd":814.1999999880791,"connectStart":814.1999999880791,"domainLookupEnd":814.1999999880791,"domainLookupStart":814.1999999880791,"fetchStart":814.1999999880791,"redirectEnd":0,"redirectStart":0,"requestStart":814.1999999880791,"responseEnd":1389.8999999761581,"responseStart":1389.8999999761581,"secureConnectionStart":814.1999999880791},{"duration":648.5999999642372,"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":814.3000000119209,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":814.3000000119209,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1462.8999999761581,"responseStart":0,"secureConnectionStart":0},{"duration":576.4000000357628,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":814.3999999761581,"connectEnd":814.3999999761581,"connectStart":814.3999999761581,"domainLookupEnd":814.3999999761581,"domainLookupStart":814.3999999761581,"fetchStart":814.3999999761581,"redirectEnd":0,"redirectStart":0,"requestStart":814.3999999761581,"responseEnd":1390.800000011921,"responseStart":1390.800000011921,"secureConnectionStart":814.3999999761581},{"duration":648.3999999761581,"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":814.6000000238419,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":814.6000000238419,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1463,"responseStart":0,"secureConnectionStart":0},{"duration":577.0999999642372,"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":814.8000000119209,"connectEnd":814.8000000119209,"connectStart":814.8000000119209,"domainLookupEnd":814.8000000119209,"domainLookupStart":814.8000000119209,"fetchStart":814.8000000119209,"redirectEnd":0,"redirectStart":0,"requestStart":814.8000000119209,"responseEnd":1391.8999999761581,"responseStart":1391.8999999761581,"secureConnectionStart":814.8000000119209},{"duration":1276.3999999761581,"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":825.5,"connectEnd":825.5,"connectStart":825.5,"domainLookupEnd":825.5,"domainLookupStart":825.5,"fetchStart":825.5,"redirectEnd":0,"redirectStart":0,"requestStart":825.5,"responseEnd":2101.899999976158,"responseStart":2101.899999976158,"secureConnectionStart":825.5},{"duration":1508.3999999761581,"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":828.1000000238419,"connectEnd":828.1000000238419,"connectStart":828.1000000238419,"domainLookupEnd":828.1000000238419,"domainLookupStart":828.1000000238419,"fetchStart":828.1000000238419,"redirectEnd":0,"redirectStart":0,"requestStart":828.1000000238419,"responseEnd":2336.5,"responseStart":2336.5,"secureConnectionStart":828.1000000238419},{"duration":630.3000000119209,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1474.699999988079,"connectEnd":1474.699999988079,"connectStart":1474.699999988079,"domainLookupEnd":1474.699999988079,"domainLookupStart":1474.699999988079,"fetchStart":1474.699999988079,"redirectEnd":0,"redirectStart":0,"requestStart":1474.699999988079,"responseEnd":2105,"responseStart":2105,"secureConnectionStart":1474.699999988079},{"duration":598.5,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1773.699999988079,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1773.699999988079,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":2372.199999988079,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":568,"domainLookupEnd":624,"connectStart":624,"connectEnd":644,"secureConnectionStart":632,"requestStart":644,"responseStart":807,"responseEnd":828,"domLoading":811,"domInteractive":2375,"domContentLoadedEventStart":2375,"domContentLoadedEventEnd":2417,"domComplete":2831,"loadEventStart":2831,"loadEventEnd":2831,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":2355.899999976158},{"name":"bigPipe.sidebar-id.end","time":2356.699999988079},{"name":"bigPipe.activity-panel-pipe-id.start","time":2356.800000011921},{"name":"bigPipe.activity-panel-pipe-id.end","time":2357.899999976158},{"name":"activityTabFullyLoaded","time":2430.699999988079}],"measures":[],"correlationId":"2c00b0e4135a97","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":99,"dbReadsTimeInMs":13,"dbConnsTimeInMs":21,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
I think that we should properly separate the statistics collection and the updates. If we do that, updating and accessing the statistics in dict_table_t and dict_index_t should be quick enough to be protected by dict_sys->mutex. Updating the persistent statistics tables will be holding it already.
Collecting that statistics can be protected by normal dict_index_t::lock and buf_block_t::lock. I do not think that multiple concurrent threads collecting statistics on the same index are going to be an issue.