Type:
Priority:
Resolution:
Fixed
Affects Version/s:
10.0(EOL) , 10.1(EOL) , 10.2(EOL) , 10.3(EOL) , 10.4(EOL) , 10.5
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() :
diff a/mysql-test/suite/innodb/t/stats_persistent.test b/mysql-test/suite/innodb/t/stats_persistent.test
new file mode 100644
--- /dev/null
+++ b/mysql-test/suite/innodb/t/stats_persistent.test
@@ -0,0 +1,26 @@
+--source include/have_innodb.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+--source include/count_sessions.inc
+
+CREATE TABLE t1(a int) ENGINE=INNODB STATS_PERSISTENT=1;
+
+SET DEBUG_SYNC='dict_stats_update_persistent SIGNAL stop WAIT_FOR go';
+send ANALYZE TABLE t1;
+
+--connect(con1, localhost, root)
+SET DEBUG_SYNC='now WAIT_FOR stop';
+
+SELECT * FROM information_schema.TABLES WHERE ENGINE='InnoDB';
+
+SET DEBUG_SYNC='now SIGNAL go';
+--disconnect con1
+
+--connection default
+--reap
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE t1;
+
+--source include/wait_until_count_sessions.inc
diff a/storage/innobase/dict/dict0stats.cc b/storage/innobase/dict/dict0stats.cc
--- a/storage/innobase/dict/dict0stats.cc
+++ b/storage/innobase/dict/dict0stats.cc
@@ -2207,6 +2207,8 @@ dict_stats_update_persistent(
dict_table_stats_lock(table, RW_X_LATCH);
+ DEBUG_SYNC_C("dict_stats_update_persistent");
+
/* analyze the clustered index first */
index = dict_table_get_first_index(table);
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.
{"report":{"fcp":1791.9000000357628,"ttfb":775.3000000119209,"pageVisibility":"visible","entityId":92918,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"ab6e1cc8-7cd6-48e0-905a-683620b4a4f8","navigationType":0,"readyForUser":1872.6000000238419,"redirectCount":0,"resourceLoadedEnd":2295.300000011921,"resourceLoadedStart":780.6000000238419,"resourceTiming":[{"duration":604.5999999642372,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":780.6000000238419,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":780.6000000238419,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1385.199999988079,"responseStart":0,"secureConnectionStart":0},{"duration":604.5999999642372,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/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":780.9000000357628,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":780.9000000357628,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1385.5,"responseStart":0,"secureConnectionStart":0},{"duration":613.6999999880791,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":781.1000000238419,"connectEnd":781.1000000238419,"connectStart":781.1000000238419,"domainLookupEnd":781.1000000238419,"domainLookupStart":781.1000000238419,"fetchStart":781.1000000238419,"redirectEnd":0,"redirectStart":0,"requestStart":781.1000000238419,"responseEnd":1394.800000011921,"responseStart":1394.800000011921,"secureConnectionStart":781.1000000238419},{"duration":639.2000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/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":781.1999999880791,"connectEnd":781.1999999880791,"connectStart":781.1999999880791,"domainLookupEnd":781.1999999880791,"domainLookupStart":781.1999999880791,"fetchStart":781.1999999880791,"redirectEnd":0,"redirectStart":0,"requestStart":781.1999999880791,"responseEnd":1420.4000000357628,"responseStart":1420.4000000357628,"secureConnectionStart":781.1999999880791},{"duration":643,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":781.4000000357628,"connectEnd":781.4000000357628,"connectStart":781.4000000357628,"domainLookupEnd":781.4000000357628,"domainLookupStart":781.4000000357628,"fetchStart":781.4000000357628,"redirectEnd":0,"redirectStart":0,"requestStart":781.4000000357628,"responseEnd":1424.4000000357628,"responseStart":1424.4000000357628,"secureConnectionStart":781.4000000357628},{"duration":643.3000000119209,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":781.6999999880791,"connectEnd":781.6999999880791,"connectStart":781.6999999880791,"domainLookupEnd":781.6999999880791,"domainLookupStart":781.6999999880791,"fetchStart":781.6999999880791,"redirectEnd":0,"redirectStart":0,"requestStart":781.6999999880791,"responseEnd":1425,"responseStart":1425,"secureConnectionStart":781.6999999880791},{"duration":643.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":781.9000000357628,"connectEnd":781.9000000357628,"connectStart":781.9000000357628,"domainLookupEnd":781.9000000357628,"domainLookupStart":781.9000000357628,"fetchStart":781.9000000357628,"redirectEnd":0,"redirectStart":0,"requestStart":781.9000000357628,"responseEnd":1425.4000000357628,"responseStart":1425.4000000357628,"secureConnectionStart":781.9000000357628},{"duration":645.1000000238419,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":782,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":782,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1427.1000000238419,"responseStart":0,"secureConnectionStart":0},{"duration":644,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":782.1999999880791,"connectEnd":782.1999999880791,"connectStart":782.1999999880791,"domainLookupEnd":782.1999999880791,"domainLookupStart":782.1999999880791,"fetchStart":782.1999999880791,"redirectEnd":0,"redirectStart":0,"requestStart":782.1999999880791,"responseEnd":1426.199999988079,"responseStart":1426.199999988079,"secureConnectionStart":782.1999999880791},{"duration":644.8999999761581,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/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":782.3000000119209,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":782.3000000119209,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1427.199999988079,"responseStart":0,"secureConnectionStart":0},{"duration":644.3999999761581,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/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":782.4000000357628,"connectEnd":782.4000000357628,"connectStart":782.4000000357628,"domainLookupEnd":782.4000000357628,"domainLookupStart":782.4000000357628,"fetchStart":782.4000000357628,"redirectEnd":0,"redirectStart":0,"requestStart":782.4000000357628,"responseEnd":1426.800000011921,"responseStart":1426.800000011921,"secureConnectionStart":782.4000000357628},{"duration":870.1999999880791,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":787.5,"connectEnd":787.5,"connectStart":787.5,"domainLookupEnd":787.5,"domainLookupStart":787.5,"fetchStart":787.5,"redirectEnd":0,"redirectStart":0,"requestStart":787.5,"responseEnd":1657.699999988079,"responseStart":1657.699999988079,"secureConnectionStart":787.5},{"duration":1507.800000011921,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":787.5,"connectEnd":787.5,"connectStart":787.5,"domainLookupEnd":787.5,"domainLookupStart":787.5,"fetchStart":787.5,"redirectEnd":0,"redirectStart":0,"requestStart":787.5,"responseEnd":2295.300000011921,"responseStart":2295.300000011921,"secureConnectionStart":787.5},{"duration":680.8999999761581,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1612.9000000357628,"connectEnd":1612.9000000357628,"connectStart":1612.9000000357628,"domainLookupEnd":1612.9000000357628,"domainLookupStart":1612.9000000357628,"fetchStart":1612.9000000357628,"redirectEnd":0,"redirectStart":0,"requestStart":1612.9000000357628,"responseEnd":2293.800000011921,"responseStart":2293.800000011921,"secureConnectionStart":1612.9000000357628},{"duration":516.8000000119209,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1786,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1786,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":2302.800000011921,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":518,"responseStart":776,"responseEnd":783,"domLoading":779,"domInteractive":2355,"domContentLoadedEventStart":2355,"domContentLoadedEventEnd":2393,"domComplete":3642,"loadEventStart":3642,"loadEventEnd":3644,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":2303.800000011921},{"name":"bigPipe.sidebar-id.end","time":2304.600000023842},{"name":"bigPipe.activity-panel-pipe-id.start","time":2304.800000011921},{"name":"bigPipe.activity-panel-pipe-id.end","time":2307.199999988079},{"name":"activityTabFullyLoaded","time":2398.900000035763}],"measures":[],"correlationId":"f9d9f6bc600e1d","effectiveType":"4g","downlink":9,"rtt":0,"serverDuration":191,"dbReadsTimeInMs":12,"dbConnsTimeInMs":21,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Bug
Blocker
