Thank you for confirming my analysis.
Compared to innodb_doublewrite=0 and reading the recently written page identifiers from the redo log (starting from a recent or the latest checkpoint), the reduced innodb_doublewrite=2 mode could have the benefit that it might be able to record a larger number of recently written pages, especially if the most recent workload involves writing a few pages over and over, so that the redo log would only contain the few page identifiers of the busy pages.
There is another corruption mode, of which I have some anecdotal evidence: spontaneous corruption of data that was not accessed (read or written) for a long time. As far as I understand, neither the reduced doublewrite buffer nor the redo logs would help there.
I believe that before introducing the new innodb_doublewrite=2 mode, we should spend more effort on improving the corruption handling of InnoDB.
Marko Mäkelä
added a comment - Thank you for confirming my analysis.
Compared to innodb_doublewrite=0 and reading the recently written page identifiers from the redo log (starting from a recent or the latest checkpoint), the reduced innodb_doublewrite=2 mode could have the benefit that it might be able to record a larger number of recently written pages, especially if the most recent workload involves writing a few pages over and over, so that the redo log would only contain the few page identifiers of the busy pages.
There is another corruption mode, of which I have some anecdotal evidence: spontaneous corruption of data that was not accessed (read or written) for a long time. As far as I understand, neither the reduced doublewrite buffer nor the redo logs would help there.
I believe that before introducing the new innodb_doublewrite=2 mode, we should spend more effort on improving the corruption handling of InnoDB.
Instead of doing this, maybe we should disable the doublewrite buffering for pages that are being written for the first time. Once MDEV-12699 is implemented, we can also disable doublewrite buffering for pages that were (re)initialized by a mini-transaction.
Marko Mäkelä
added a comment - Instead of doing this, maybe we should disable the doublewrite buffering for pages that are being written for the first time. Once MDEV-12699 is implemented, we can also disable doublewrite buffering for pages that were (re)initialized by a mini-transaction.
Furthermore, MDEV-23855 greatly reduced the latency of the doublewrite buffer, by issuing a 128-page asynchronous write request for the entire doublewrite buffer, and filling another 128-byte doublewrite buffer in memory while one copy is being written.
Marko Mäkelä
added a comment - I think that MDEV-12699 , MDEV-19738 and MDEV-15528 are already improving this as much as is feasible.
Furthermore, MDEV-23855 greatly reduced the latency of the doublewrite buffer, by issuing a 128-page asynchronous write request for the entire doublewrite buffer, and filling another 128-byte doublewrite buffer in memory while one copy is being written.
Marko Mäkelä
added a comment - For the record, MySQL 8.0.30 includes WL#14710: InnoDB: Introducing REDUCED double write mode (Facebook Contribution)
I do not think that MySQL implements any of the following MariaDB recovery improvements yet: MDEV-12699 , MDEV-19738 , MDEV-15528 , MDEV-24626 .
I believe that MariaDB should handle corrupted pages gracefully during recovery. This was recently extensively tested in combination with MDEV-13542 and some related changes.
Marko Mäkelä
added a comment - I believe that MariaDB should handle corrupted pages gracefully during recovery. This was recently extensively tested in combination with MDEV-13542 and some related changes.
People
Marko Mäkelä
Jan Lindström (Inactive)
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":892.8000001907349,"ttfb":264,"pageVisibility":"visible","entityId":58661,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"a70a5d25-9275-40bc-bfbd-bc7c23a873c9","navigationType":0,"readyForUser":963,"redirectCount":0,"resourceLoadedEnd":604.8000001907349,"resourceLoadedStart":271.19999980926514,"resourceTiming":[{"duration":21,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":271.19999980926514,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":271.19999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":292.19999980926514,"responseStart":0,"secureConnectionStart":0},{"duration":21.299999713897705,"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":271.40000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":271.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":292.69999980926514,"responseStart":0,"secureConnectionStart":0},{"duration":185.2000002861023,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":271.59999990463257,"connectEnd":294.69999980926514,"connectStart":294.69999980926514,"domainLookupEnd":294.69999980926514,"domainLookupStart":294.69999980926514,"fetchStart":271.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":295.09999990463257,"responseEnd":456.80000019073486,"responseStart":309.80000019073486,"secureConnectionStart":294.69999980926514},{"duration":317.59999990463257,"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":271.80000019073486,"connectEnd":271.80000019073486,"connectStart":271.80000019073486,"domainLookupEnd":271.80000019073486,"domainLookupStart":271.80000019073486,"fetchStart":271.80000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":297.30000019073486,"responseEnd":589.4000000953674,"responseStart":320.5,"secureConnectionStart":271.80000019073486},{"duration":51.19999980926514,"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":272,"connectEnd":272,"connectStart":272,"domainLookupEnd":272,"domainLookupStart":272,"fetchStart":272,"redirectEnd":0,"redirectStart":0,"requestStart":298.19999980926514,"responseEnd":323.19999980926514,"responseStart":319.59999990463257,"secureConnectionStart":272},{"duration":50.09999990463257,"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":272.09999990463257,"connectEnd":272.09999990463257,"connectStart":272.09999990463257,"domainLookupEnd":272.09999990463257,"domainLookupStart":272.09999990463257,"fetchStart":272.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":297.90000009536743,"responseEnd":322.19999980926514,"responseStart":315.80000019073486,"secureConnectionStart":272.09999990463257},{"duration":53.39999961853027,"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":272.30000019073486,"connectEnd":272.30000019073486,"connectStart":272.30000019073486,"domainLookupEnd":272.30000019073486,"domainLookupStart":272.30000019073486,"fetchStart":272.30000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":298.80000019073486,"responseEnd":325.69999980926514,"responseStart":323.40000009536743,"secureConnectionStart":272.30000019073486},{"duration":25.699999809265137,"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":272.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":272.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":298.19999980926514,"responseStart":0,"secureConnectionStart":0},{"duration":53.59999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":272.59999990463257,"connectEnd":272.59999990463257,"connectStart":272.59999990463257,"domainLookupEnd":272.59999990463257,"domainLookupStart":272.59999990463257,"fetchStart":272.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":301,"responseEnd":326.19999980926514,"responseStart":323.90000009536743,"secureConnectionStart":272.59999990463257},{"duration":26.59999990463257,"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":272.80000019073486,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":272.80000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":299.40000009536743,"responseStart":0,"secureConnectionStart":0},{"duration":56.5,"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":273,"connectEnd":273,"connectStart":273,"domainLookupEnd":273,"domainLookupStart":273,"fetchStart":273,"redirectEnd":0,"redirectStart":0,"requestStart":302.90000009536743,"responseEnd":329.5,"responseStart":326.5,"secureConnectionStart":273},{"duration":324.5,"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":277.59999990463257,"connectEnd":277.59999990463257,"connectStart":277.59999990463257,"domainLookupEnd":277.59999990463257,"domainLookupStart":277.59999990463257,"fetchStart":277.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":324.69999980926514,"responseEnd":602.0999999046326,"responseStart":597.1999998092651,"secureConnectionStart":277.59999990463257},{"duration":323.40000009536743,"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":281.40000009536743,"connectEnd":281.40000009536743,"connectStart":281.40000009536743,"domainLookupEnd":281.40000009536743,"domainLookupStart":281.40000009536743,"fetchStart":281.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":331.90000009536743,"responseEnd":604.8000001907349,"responseStart":599.3000001907349,"secureConnectionStart":281.40000009536743},{"duration":68.19999980926514,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":604.9000000953674,"connectEnd":604.9000000953674,"connectStart":604.9000000953674,"domainLookupEnd":604.9000000953674,"domainLookupStart":604.9000000953674,"fetchStart":604.9000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":639.6999998092651,"responseEnd":673.0999999046326,"responseStart":672.1999998092651,"secureConnectionStart":604.9000000953674}],"fetchStart":0,"domainLookupStart":50,"domainLookupEnd":74,"connectStart":74,"connectEnd":93,"secureConnectionStart":82,"requestStart":94,"responseStart":264,"responseEnd":281,"domLoading":267,"domInteractive":1018,"domContentLoadedEventStart":1018,"domContentLoadedEventEnd":1066,"domComplete":1399,"loadEventStart":1399,"loadEventEnd":1400,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":995},{"name":"bigPipe.sidebar-id.end","time":995.9000000953674},{"name":"bigPipe.activity-panel-pipe-id.start","time":996.0999999046326},{"name":"bigPipe.activity-panel-pipe-id.end","time":997.8000001907349},{"name":"activityTabFullyLoaded","time":1083.1999998092651}],"measures":[],"correlationId":"f7fc604c02d738","effectiveType":"4g","downlink":9.6,"rtt":0,"serverDuration":105,"dbReadsTimeInMs":17,"dbConnsTimeInMs":26,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Thank you for confirming my analysis.
Compared to innodb_doublewrite=0 and reading the recently written page identifiers from the redo log (starting from a recent or the latest checkpoint), the reduced innodb_doublewrite=2 mode could have the benefit that it might be able to record a larger number of recently written pages, especially if the most recent workload involves writing a few pages over and over, so that the redo log would only contain the few page identifiers of the busy pages.
There is another corruption mode, of which I have some anecdotal evidence: spontaneous corruption of data that was not accessed (read or written) for a long time. As far as I understand, neither the reduced doublewrite buffer nor the redo logs would help there.
I believe that before introducing the new innodb_doublewrite=2 mode, we should spend more effort on improving the corruption handling of InnoDB.