axel reproduced one more hang related to innodb_undo_log_truncate=ON, similar to MDEV-30180. Here is a description of a hang that was reproduced with innodb_use_native_aio=0:
trx_purge_truncate_history() writes the message InnoDB: Truncating and is about to truncate an undo log tablespace.
trx_purge_truncate_history() is busy-looping in a scan of buf_pool.flush_list because one of the pages belonging to the undo tablespace is write-fixed.
During the time trx_purge_truncate_history() releases and re-acquires buf_pool.flush_list_mutex, other threads that are waiting for it cannot grab it, in this version of GNU libc. This is similar to MDEV-30180, which could only be reproduced in the same particular environment.
buf_dblwr_t::flush_buffered_writes_completed() was waiting for log_sys.mutex in log_write_up_to(), while trying to write the block that trx_purge_truncate_history() is trying to lock.
log_sys.mutex was be held by buf_flush_page_cleaner(), which is waiting for buf_pool.flush_list_mutex.
A possible fix would be that trx_purge_truncate_history() buffer-fixes the block, releases buf_pool.flush_list_mutex, waits for an exclusive latch on the block and finally reacquire buf_pool.flush_list_mutex. In that way, the blocking of other threads is minimized. The buffer-fix will prevent the eviction or relocation of the block in the buffer pool while no mutex is held by trx_purge_truncate_history().
Commit f410444a76b from the bb-10.6-MDEV-31343 branch survived a 1 hour run of sysbench-tpcc. So the fix is most probably complete (without it the server hung within few minutes).
It still has severe impact of performance. Enabling innodb_undo_log_truncate=ON has lead to 50% performance loss (~3000 tps vs. ~6000 tps) in my benchmark:
Axel Schwenke
added a comment - Commit f410444a76b from the bb-10.6- MDEV-31343 branch survived a 1 hour run of sysbench-tpcc. So the fix is most probably complete (without it the server hung within few minutes).
It still has severe impact of performance. Enabling innodb_undo_log_truncate=ON has lead to 50% performance loss (~3000 tps vs. ~6000 tps) in my benchmark:
Also in MDEV-29401 and MDEV-30628 we concluded that something needs to be done about the history list length. One possibility would be that any thread that is acquiring an exclusive latch on an index page for other reasons will attempt to remove any purgeable history, so that the actual purge threads will have less work to do.
Marko Mäkelä
added a comment - Also in MDEV-29401 and MDEV-30628 we concluded that something needs to be done about the history list length. One possibility would be that any thread that is acquiring an exclusive latch on an index page for other reasons will attempt to remove any purgeable history, so that the actual purge threads will have less work to do.
The history list length issue was greatly improved by MDEV-32050.
We experienced a similar problem again in this same environment, in MDEV-33009.
Marko Mäkelä
added a comment - The history list length issue was greatly improved by MDEV-32050 .
We experienced a similar problem again in this same environment, in MDEV-33009 .
People
Matthias Leich
Marko Mäkelä
Votes:
0Vote for this issue
Watchers:
4Start 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":1154.1000003814697,"ttfb":388.5,"pageVisibility":"visible","entityId":122260,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"fdd6708b-6ab0-421d-b901-d52972613897","navigationType":0,"readyForUser":1314,"redirectCount":0,"resourceLoadedEnd":1335.7000007629395,"resourceLoadedStart":394.5,"resourceTiming":[{"duration":243,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":394.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":394.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":637.5,"responseStart":0,"secureConnectionStart":0},{"duration":243,"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":394.80000019073486,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":394.80000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":637.8000001907349,"responseStart":0,"secureConnectionStart":0},{"duration":252.4000005722046,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":395,"connectEnd":395,"connectStart":395,"domainLookupEnd":395,"domainLookupStart":395,"fetchStart":395,"redirectEnd":0,"redirectStart":0,"requestStart":395,"responseEnd":647.4000005722046,"responseStart":647.3000001907349,"secureConnectionStart":395},{"duration":321.19999980926514,"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":395.20000076293945,"connectEnd":395.20000076293945,"connectStart":395.20000076293945,"domainLookupEnd":395.20000076293945,"domainLookupStart":395.20000076293945,"fetchStart":395.20000076293945,"redirectEnd":0,"redirectStart":0,"requestStart":395.20000076293945,"responseEnd":716.4000005722046,"responseStart":716.4000005722046,"secureConnectionStart":395.20000076293945},{"duration":324.9000005722046,"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":395.5,"connectEnd":395.5,"connectStart":395.5,"domainLookupEnd":395.5,"domainLookupStart":395.5,"fetchStart":395.5,"redirectEnd":0,"redirectStart":0,"requestStart":395.5,"responseEnd":720.4000005722046,"responseStart":720.4000005722046,"secureConnectionStart":395.5},{"duration":325.30000019073486,"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":395.6000003814697,"connectEnd":395.6000003814697,"connectStart":395.6000003814697,"domainLookupEnd":395.6000003814697,"domainLookupStart":395.6000003814697,"fetchStart":395.6000003814697,"redirectEnd":0,"redirectStart":0,"requestStart":395.6000003814697,"responseEnd":720.9000005722046,"responseStart":720.9000005722046,"secureConnectionStart":395.6000003814697},{"duration":326,"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":395.80000019073486,"connectEnd":395.80000019073486,"connectStart":395.80000019073486,"domainLookupEnd":395.80000019073486,"domainLookupStart":395.80000019073486,"fetchStart":395.80000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":395.80000019073486,"responseEnd":721.8000001907349,"responseStart":721.8000001907349,"secureConnectionStart":395.80000019073486},{"duration":403.30000019073486,"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":396.1000003814697,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":396.1000003814697,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":799.4000005722046,"responseStart":0,"secureConnectionStart":0},{"duration":326.19999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":396.20000076293945,"connectEnd":396.20000076293945,"connectStart":396.20000076293945,"domainLookupEnd":396.20000076293945,"domainLookupStart":396.20000076293945,"fetchStart":396.20000076293945,"redirectEnd":0,"redirectStart":0,"requestStart":396.20000076293945,"responseEnd":722.4000005722046,"responseStart":722.4000005722046,"secureConnectionStart":396.20000076293945},{"duration":403.19999980926514,"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":396.4000005722046,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":396.4000005722046,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":799.6000003814697,"responseStart":0,"secureConnectionStart":0},{"duration":326.3999996185303,"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":396.6000003814697,"connectEnd":396.6000003814697,"connectStart":396.6000003814697,"domainLookupEnd":396.6000003814697,"domainLookupStart":396.6000003814697,"fetchStart":396.6000003814697,"redirectEnd":0,"redirectStart":0,"requestStart":396.6000003814697,"responseEnd":723,"responseStart":723,"secureConnectionStart":396.6000003814697},{"duration":461.19999980926514,"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":402.80000019073486,"connectEnd":402.80000019073486,"connectStart":402.80000019073486,"domainLookupEnd":402.80000019073486,"domainLookupStart":402.80000019073486,"fetchStart":402.80000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":402.80000019073486,"responseEnd":864,"responseStart":864,"secureConnectionStart":402.80000019073486},{"duration":507,"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":409,"connectEnd":409,"connectStart":409,"domainLookupEnd":409,"domainLookupStart":409,"fetchStart":409,"redirectEnd":0,"redirectStart":0,"requestStart":409,"responseEnd":916,"responseStart":916,"secureConnectionStart":409},{"duration":56.80000019073486,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":813.1000003814697,"connectEnd":813.1000003814697,"connectStart":813.1000003814697,"domainLookupEnd":813.1000003814697,"domainLookupStart":813.1000003814697,"fetchStart":813.1000003814697,"redirectEnd":0,"redirectStart":0,"requestStart":813.1000003814697,"responseEnd":869.9000005722046,"responseStart":869.9000005722046,"secureConnectionStart":813.1000003814697},{"duration":214.70000076293945,"initiatorType":"link","name":"https://jira.mariadb.org/s/d5715adaadd168a9002b108b2b039b50-CDN/lu2cib/820016/12ta74/be4b45e9cec53099498fa61c8b7acba4/_/download/contextbatch/css/jira.project.sidebar,-_super,-project.issue.navigator,-jira.general,-jira.browse.project,-jira.view.issue,-jira.global,-atl.general,-com.atlassian.jira.projects.sidebar.init/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":888.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":888.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1103.2000007629395,"responseStart":0,"secureConnectionStart":0},{"duration":439.80000019073486,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/e65b778d185daf5aee24936755b43da6/_/download/contextbatch/js/browser-metrics-plugin.contrib,-_super,-project.issue.navigator,-jira.view.issue,-atl.general/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":889.4000005722046,"connectEnd":889.4000005722046,"connectStart":889.4000005722046,"domainLookupEnd":889.4000005722046,"domainLookupStart":889.4000005722046,"fetchStart":889.4000005722046,"redirectEnd":0,"redirectStart":0,"requestStart":889.4000005722046,"responseEnd":1329.2000007629395,"responseStart":1329.2000007629395,"secureConnectionStart":889.4000005722046},{"duration":445.80000019073486,"initiatorType":"script","name":"https://jira.mariadb.org/s/097ae97cb8fbec7d6ea4bbb1f26955b9-CDN/lu2cib/820016/12ta74/be4b45e9cec53099498fa61c8b7acba4/_/download/contextbatch/js/jira.project.sidebar,-_super,-project.issue.navigator,-jira.general,-jira.browse.project,-jira.view.issue,-jira.global,-atl.general,-com.atlassian.jira.projects.sidebar.init/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true&whisper-enabled=true","startTime":889.9000005722046,"connectEnd":889.9000005722046,"connectStart":889.9000005722046,"domainLookupEnd":889.9000005722046,"domainLookupStart":889.9000005722046,"fetchStart":889.9000005722046,"redirectEnd":0,"redirectStart":0,"requestStart":889.9000005722046,"responseEnd":1335.7000007629395,"responseStart":1335.7000007629395,"secureConnectionStart":889.9000005722046},{"duration":268.1000003814697,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1147.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1147.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1415.6000003814697,"responseStart":0,"secureConnectionStart":0},{"duration":247.19999980926514,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1155.2000007629395,"connectEnd":1155.2000007629395,"connectStart":1155.2000007629395,"domainLookupEnd":1155.2000007629395,"domainLookupStart":1155.2000007629395,"fetchStart":1155.2000007629395,"redirectEnd":0,"redirectStart":0,"requestStart":1155.2000007629395,"responseEnd":1402.4000005722046,"responseStart":1402.4000005722046,"secureConnectionStart":1155.2000007629395}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":150,"responseStart":389,"responseEnd":409,"domLoading":392,"domInteractive":1443,"domContentLoadedEventStart":1443,"domContentLoadedEventEnd":1503,"domComplete":1939,"loadEventStart":1939,"loadEventEnd":1940,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1368.9000005722046},{"name":"bigPipe.sidebar-id.end","time":1369.8000001907349},{"name":"bigPipe.activity-panel-pipe-id.start","time":1370},{"name":"bigPipe.activity-panel-pipe-id.end","time":1376.4000005722046},{"name":"activityTabFullyLoaded","time":1531.4000005722046}],"measures":[],"correlationId":"21b73747d2e766","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":182,"dbReadsTimeInMs":39,"dbConnsTimeInMs":50,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Commit f410444a76b from the bb-10.6-
MDEV-31343branch survived a 1 hour run of sysbench-tpcc. So the fix is most probably complete (without it the server hung within few minutes).It still has severe impact of performance. Enabling innodb_undo_log_truncate=ON has lead to 50% performance loss (~3000 tps vs. ~6000 tps) in my benchmark: