The relatively new innodb_stats_traditional variable allows the number of pages used while calculating statistics to scale with table size to give more accurate statistics. However, it only works with transient statistics. It would be nice to have that variable also affect persistent statistics or create a new variable to use the same behavior with persistent statistics.
Attachments
Issue Links
relates to
MDEV-7084innodb index stats inadequate using constant innodb_stats_sample_pages
Closed
MDEV-7196ANALYZE TABLE should do complete table scan
Open
MDEV-7197different random pages pulled for each index when updating stats
Alternately with MDEV-7901 Has analyze table with low impact now so perhaps time for MDEV-7196 Analyze table to do full table scan?
Daniel Black
added a comment - The algorithm for number of pages scanned is significantly different https://github.com/MariaDB/server/blob/10.2/storage/innobase/dict/dict0stats.cc#L2193 and appears be increased with the number of levels. It doesn't seem to be critical to log scale it. Have you observed a problem?
Alternately with MDEV-7901 Has analyze table with low impact now so perhaps time for MDEV-7196 Analyze table to do full table scan?
People
Unassigned
Brad Jorgensen
Votes:
0Vote for this issue
Watchers:
4Start watching this issue
Dates
Created:
Updated:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
{"report":{"fcp":1127.8000001907349,"ttfb":301.5,"pageVisibility":"visible","entityId":57353,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"4a8cd426-daca-4298-a878-96255295f964","navigationType":0,"readyForUser":1240.2000002861023,"redirectCount":0,"resourceLoadedEnd":1306.5,"resourceLoadedStart":307,"resourceTiming":[{"duration":281.1000003814697,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":307,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":307,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":588.1000003814697,"responseStart":0,"secureConnectionStart":0},{"duration":281.2999997138977,"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":307.2000002861023,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":307.2000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":588.5,"responseStart":0,"secureConnectionStart":0},{"duration":290.09999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":307.40000009536743,"connectEnd":307.40000009536743,"connectStart":307.40000009536743,"domainLookupEnd":307.40000009536743,"domainLookupStart":307.40000009536743,"fetchStart":307.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":307.40000009536743,"responseEnd":597.5,"responseStart":597.5,"secureConnectionStart":307.40000009536743},{"duration":334.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":321.7000002861023,"connectEnd":321.7000002861023,"connectStart":321.7000002861023,"domainLookupEnd":321.7000002861023,"domainLookupStart":321.7000002861023,"fetchStart":321.7000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":321.7000002861023,"responseEnd":656.2000002861023,"responseStart":656.2000002861023,"secureConnectionStart":321.7000002861023},{"duration":337.69999980926514,"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":322.1000003814697,"connectEnd":322.1000003814697,"connectStart":322.1000003814697,"domainLookupEnd":322.1000003814697,"domainLookupStart":322.1000003814697,"fetchStart":322.1000003814697,"redirectEnd":0,"redirectStart":0,"requestStart":322.1000003814697,"responseEnd":659.8000001907349,"responseStart":659.8000001907349,"secureConnectionStart":322.1000003814697},{"duration":338.19999980926514,"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":322.2000002861023,"connectEnd":322.2000002861023,"connectStart":322.2000002861023,"domainLookupEnd":322.2000002861023,"domainLookupStart":322.2000002861023,"fetchStart":322.2000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":322.2000002861023,"responseEnd":660.4000000953674,"responseStart":660.4000000953674,"secureConnectionStart":322.2000002861023},{"duration":338.40000009536743,"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":322.40000009536743,"connectEnd":322.40000009536743,"connectStart":322.40000009536743,"domainLookupEnd":322.40000009536743,"domainLookupStart":322.40000009536743,"fetchStart":322.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":322.40000009536743,"responseEnd":660.8000001907349,"responseStart":660.8000001907349,"secureConnectionStart":322.40000009536743},{"duration":417.8999996185303,"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":322.6000003814697,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":322.6000003814697,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":740.5,"responseStart":0,"secureConnectionStart":0},{"duration":338.5,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":322.80000019073486,"connectEnd":322.80000019073486,"connectStart":322.80000019073486,"domainLookupEnd":322.80000019073486,"domainLookupStart":322.80000019073486,"fetchStart":322.80000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":322.80000019073486,"responseEnd":661.3000001907349,"responseStart":661.3000001907349,"secureConnectionStart":322.80000019073486},{"duration":417.7000002861023,"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":322.90000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":322.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":740.6000003814697,"responseStart":0,"secureConnectionStart":0},{"duration":338.69999980926514,"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":323.2000002861023,"connectEnd":323.2000002861023,"connectStart":323.2000002861023,"domainLookupEnd":323.2000002861023,"domainLookupStart":323.2000002861023,"fetchStart":323.2000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":323.2000002861023,"responseEnd":661.9000000953674,"responseStart":661.9000000953674,"secureConnectionStart":323.2000002861023},{"duration":981.7999997138977,"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":324.2000002861023,"connectEnd":324.2000002861023,"connectStart":324.2000002861023,"domainLookupEnd":324.2000002861023,"domainLookupStart":324.2000002861023,"fetchStart":324.2000002861023,"redirectEnd":0,"redirectStart":0,"requestStart":324.2000002861023,"responseEnd":1306,"responseStart":1306,"secureConnectionStart":324.2000002861023},{"duration":982.1999998092651,"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":324.30000019073486,"connectEnd":324.30000019073486,"connectStart":324.30000019073486,"domainLookupEnd":324.30000019073486,"domainLookupStart":324.30000019073486,"fetchStart":324.30000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":324.30000019073486,"responseEnd":1306.5,"responseStart":1306.5,"secureConnectionStart":324.30000019073486},{"duration":153,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":780.3000001907349,"connectEnd":780.3000001907349,"connectStart":780.3000001907349,"domainLookupEnd":780.3000001907349,"domainLookupStart":780.3000001907349,"fetchStart":780.3000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":780.3000001907349,"responseEnd":933.3000001907349,"responseStart":933.3000001907349,"secureConnectionStart":780.3000001907349}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":154,"responseStart":302,"responseEnd":305,"domLoading":305,"domInteractive":1343,"domContentLoadedEventStart":1343,"domContentLoadedEventEnd":1384,"domComplete":1776,"loadEventStart":1776,"loadEventEnd":1776,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1311.6000003814697},{"name":"bigPipe.sidebar-id.end","time":1312.5},{"name":"bigPipe.activity-panel-pipe-id.start","time":1313.1000003814697},{"name":"bigPipe.activity-panel-pipe-id.end","time":1314.6000003814697},{"name":"activityTabFullyLoaded","time":1397}],"measures":[],"correlationId":"ef8b33756e1a0e","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":92,"dbReadsTimeInMs":11,"dbConnsTimeInMs":19,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Task
Minor
The algorithm for number of pages scanned is significantly different https://github.com/MariaDB/server/blob/10.2/storage/innobase/dict/dict0stats.cc#L2193 and appears be increased with the number of levels. It doesn't seem to be critical to log scale it. Have you observed a problem?
Alternately with
MDEV-7901Has analyze table with low impact now so perhaps time for MDEV-7196 Analyze table to do full table scan?