When InnoDB starts up, it can be limited by innodb_io_capacity (or innodb_io_capacity_max). Currently, users can increase these temporarily at startup, but this is not ideal, and neither should be left elevated.
The change (removing the function buf_load_throttle_if_needed()) looks OK to me, but I think that we need to test the performance before applying this change.
I would not target 10.3, but rather 10.5 or 10.6. The buffer pool I/O layer works quite differently before 10.5 switched to a single buffer pool (MDEV-15058) and optimized the page cleaner thread (MDEV-23399, MDEV-23855).
Marko Mäkelä
added a comment - The change (removing the function buf_load_throttle_if_needed() ) looks OK to me, but I think that we need to test the performance before applying this change.
I would not target 10.3, but rather 10.5 or 10.6. The buffer pool I/O layer works quite differently before 10.5 switched to a single buffer pool ( MDEV-15058 ) and optimized the page cleaner thread ( MDEV-23399 , MDEV-23855 ).
Being single threaded in 10.3, 10.4 (before MDEV-26547) there was only page request in transit at a time and saw this as a lower impact. The original user in SO post was using 10.4 (based on other questions about the same time).
Daniel Black
added a comment - Being single threaded in 10.3, 10.4 (before MDEV-26547 ) there was only page request in transit at a time and saw this as a lower impact. The original user in SO post was using 10.4 (based on other questions about the same time).
MariaDB-10.6 @ 6d40274f65b, no buffer pool preloading (A)
same commit, preloading 90% of buffer pool (B)
PR#2339 on top of that commit, preloading 90% of buffer pool (C)
The buffer pool was 32G (with 16G redo) holding 128M rows in 32 tables, totaling 30GB of raw data. The pool was once created with slow shutdown, hence it was completely merged and purged. The pool was recreated from a tarfile for each run.
I tested 4 different workload scenarios:
how it takes to complete the loading of the buffer pool without load
how long it takes to mysqldump the OLTP database (to /dev/null)
throughput for OLTP read/write with 1 thread over 15 minutes
throughput for OLTP read/write with 32 threads over 15 minutes
Numbers:
variant
time to load buffer pool
A
n/a
B
4:07 min
C
4:05 min
variant
time to dump
A
274 s
B
213 s
C
214 s
sysbench OLTP read/write is better viewed as time series plot:
Axel Schwenke
added a comment - - edited Benchmark results. I have tested 3 setups:
MariaDB-10.6 @ 6d40274f65b, no buffer pool preloading (A)
same commit, preloading 90% of buffer pool (B)
PR#2339 on top of that commit, preloading 90% of buffer pool (C)
The buffer pool was 32G (with 16G redo) holding 128M rows in 32 tables, totaling 30GB of raw data. The pool was once created with slow shutdown, hence it was completely merged and purged. The pool was recreated from a tarfile for each run.
I tested 4 different workload scenarios:
how it takes to complete the loading of the buffer pool without load
how long it takes to mysqldump the OLTP database (to /dev/null)
throughput for OLTP read/write with 1 thread over 15 minutes
throughput for OLTP read/write with 32 threads over 15 minutes
Numbers:
variant
time to load buffer pool
A
n/a
B
4:07 min
C
4:05 min
variant
time to dump
A
274 s
B
213 s
C
214 s
sysbench OLTP read/write is better viewed as time series plot:
time series. For 32 thd only the first 5 min are shown
Verdict: the server behaves very similar with or without PR#2339. OK to go.
Axel Schwenke
added a comment - The same data for the datadir on SSD:
variant
time to load buffer pool
A
n/a
B
33 s
C
32 s
variant
time to dump
A
204 s
B
191 s
C
190 s
time series. For 32 thd only the first 5 min are shown
Verdict: the server behaves very similar with or without PR#2339. OK to go.
Thank you, danblack and axel! In hdd_32_thd.png the right column is for loading the buffer pool with the patched version. Both those graphs and hdd_1_thd.png are only indicating an improvement, never any serious regression. So, removing the throttling seemed to be the right thing to do.
Marko Mäkelä
added a comment - Thank you, danblack and axel ! In hdd_32_thd.png the right column is for loading the buffer pool with the patched version. Both those graphs and hdd_1_thd.png are only indicating an improvement, never any serious regression. So, removing the throttling seemed to be the right thing to do.
People
Daniel Black
Ian Gilfillan
Votes:
0Vote for this issue
Watchers:
5Start 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":944.9000000953674,"ttfb":216.60000014305115,"pageVisibility":"visible","entityId":98816,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"6f06c483-7857-4015-85c6-55d69e1bc96d","navigationType":0,"readyForUser":1009.3000001907349,"redirectCount":0,"resourceLoadedEnd":878.6000001430511,"resourceLoadedStart":222.10000014305115,"resourceTiming":[{"duration":248.20000004768372,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bsh/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":222.10000014305115,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":222.10000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":470.30000019073486,"responseStart":0,"secureConnectionStart":0},{"duration":247.90000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bsh/820016/12ta74/eb142f92e4bd16bd1ef8b08c1b9d5d56/_/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":222.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":222.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":470.40000009536743,"responseStart":0,"secureConnectionStart":0},{"duration":258,"initiatorType":"script","name":"https://jira.mariadb.org/s/c54b129276d75dc2a3460e1d78f37913-CDN/lu2bsh/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":222.5,"connectEnd":222.5,"connectStart":222.5,"domainLookupEnd":222.5,"domainLookupStart":222.5,"fetchStart":222.5,"redirectEnd":0,"redirectStart":0,"requestStart":222.5,"responseEnd":480.5,"responseStart":480.5,"secureConnectionStart":222.5},{"duration":320,"initiatorType":"script","name":"https://jira.mariadb.org/s/f867843cd2fdb209d4d1d4f760f86346-CDN/lu2bsh/820016/12ta74/eb142f92e4bd16bd1ef8b08c1b9d5d56/_/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":222.60000014305115,"connectEnd":222.60000014305115,"connectStart":222.60000014305115,"domainLookupEnd":222.60000014305115,"domainLookupStart":222.60000014305115,"fetchStart":222.60000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":222.60000014305115,"responseEnd":542.6000001430511,"responseStart":542.6000001430511,"secureConnectionStart":222.60000014305115},{"duration":323.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/ffdb17665775c5b4d6f097f3974ee359-CDN/lu2bsh/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":222.70000004768372,"connectEnd":222.70000004768372,"connectStart":222.70000004768372,"domainLookupEnd":222.70000004768372,"domainLookupStart":222.70000004768372,"fetchStart":222.70000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":222.70000004768372,"responseEnd":546.2000000476837,"responseStart":546.1000001430511,"secureConnectionStart":222.70000004768372},{"duration":323.89999985694885,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bsh/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":222.80000019073486,"connectEnd":222.80000019073486,"connectStart":222.80000019073486,"domainLookupEnd":222.80000019073486,"domainLookupStart":222.80000019073486,"fetchStart":222.80000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":222.80000019073486,"responseEnd":546.7000000476837,"responseStart":546.7000000476837,"secureConnectionStart":222.80000019073486},{"duration":324.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bsh/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":222.90000009536743,"connectEnd":222.90000009536743,"connectStart":222.90000009536743,"domainLookupEnd":222.90000009536743,"domainLookupStart":222.90000009536743,"fetchStart":222.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":222.90000009536743,"responseEnd":547.2000000476837,"responseStart":547.2000000476837,"secureConnectionStart":222.90000009536743},{"duration":324.7000000476837,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bsh/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":223,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":223,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":547.7000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":324.5,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":223.10000014305115,"connectEnd":223.10000014305115,"connectStart":223.10000014305115,"domainLookupEnd":223.10000014305115,"domainLookupStart":223.10000014305115,"fetchStart":223.10000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":223.10000014305115,"responseEnd":547.6000001430511,"responseStart":547.6000001430511,"secureConnectionStart":223.10000014305115},{"duration":324.7999999523163,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bsh/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":223.20000004768372,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":223.20000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":548,"responseStart":0,"secureConnectionStart":0},{"duration":324.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/81b5d7c27af3ebc078cc4a36383678ba-CDN/lu2bsh/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":223.5,"connectEnd":223.5,"connectStart":223.5,"domainLookupEnd":223.5,"domainLookupStart":223.5,"fetchStart":223.5,"redirectEnd":0,"redirectStart":0,"requestStart":223.5,"responseEnd":548.2000000476837,"responseStart":548.2000000476837,"secureConnectionStart":223.5},{"duration":653.6999998092651,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bsh/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":224.30000019073486,"connectEnd":224.30000019073486,"connectStart":224.30000019073486,"domainLookupEnd":224.30000019073486,"domainLookupStart":224.30000019073486,"fetchStart":224.30000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":224.30000019073486,"responseEnd":878,"responseStart":878,"secureConnectionStart":224.30000019073486},{"duration":649.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bsh/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":229.10000014305115,"connectEnd":229.10000014305115,"connectStart":229.10000014305115,"domainLookupEnd":229.10000014305115,"domainLookupStart":229.10000014305115,"fetchStart":229.10000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":229.10000014305115,"responseEnd":878.6000001430511,"responseStart":878.6000001430511,"secureConnectionStart":229.10000014305115},{"duration":156.70000004768372,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":692.9000000953674,"connectEnd":692.9000000953674,"connectStart":692.9000000953674,"domainLookupEnd":692.9000000953674,"domainLookupStart":692.9000000953674,"fetchStart":692.9000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":692.9000000953674,"responseEnd":849.6000001430511,"responseStart":849.6000001430511,"secureConnectionStart":692.9000000953674},{"duration":165.20000004768372,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":919.6000001430511,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":919.6000001430511,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1084.8000001907349,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":64,"responseStart":217,"responseEnd":222,"domLoading":220,"domInteractive":1077,"domContentLoadedEventStart":1077,"domContentLoadedEventEnd":1119,"domComplete":1393,"loadEventStart":1393,"loadEventEnd":1393,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1046.4000000953674},{"name":"bigPipe.sidebar-id.end","time":1047.2000000476837},{"name":"bigPipe.activity-panel-pipe-id.start","time":1047.3000001907349},{"name":"bigPipe.activity-panel-pipe-id.end","time":1049.7000000476837},{"name":"activityTabFullyLoaded","time":1139.1000001430511}],"measures":[],"correlationId":"1e5052c3c6661a","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":105,"dbReadsTimeInMs":28,"dbConnsTimeInMs":38,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
The change (removing the function buf_load_throttle_if_needed()) looks OK to me, but I think that we need to test the performance before applying this change.
I would not target 10.3, but rather 10.5 or 10.6. The buffer pool I/O layer works quite differently before 10.5 switched to a single buffer pool (
MDEV-15058) and optimized the page cleaner thread (MDEV-23399,MDEV-23855).