MDEV-12353 introduces a purely physical redo log format.
For btr_page_reorganize_low(), there is no optimization as of now. It is essentially copying the page payload to the redo log, as if all records were inserted anew. A better approach would be to compare the old page contents to the reorganized one, and only emit the minimal amount of WRITE or MEMMOVE records to cover the changes.
This affects especially the innodb_defragment feature, because it is invoking btr_page_reorganize_low() extensively. The test used to work with the mysql-test/include/default_mysqld.cnf se{{page_delete_rec_list_end()}}tting of innodb_log_buffer_size=1M, but that had to be increased to innodb_log_buffer_size=6M to avoid warnings about the buffer being extended.
Related to this, page_delete_rec_list_end() and page_cur_delete_rec() should be rewritten so that they will decrement PAGE_HEAP_TOP when deleting the very last records of a page, instead of prepending the deleted record to the PAGE_FREE list. That should reduce the need of page reorganize operations.
It could be worthwhile to always reorganize the page as part of page_delete_rec_list_start() and page_delete_rec_list_end().
Marko Mäkelä
added a comment - Related to this, page_delete_rec_list_end() and page_cur_delete_rec() should be rewritten so that they will decrement PAGE_HEAP_TOP when deleting the very last records of a page, instead of prepending the deleted record to the PAGE_FREE list. That should reduce the need of page reorganize operations.
It could be worthwhile to always reorganize the page as part of page_delete_rec_list_start() and page_delete_rec_list_end() .
I pushed an initial fix that will still copy the entire changed page payload area to the redo log. A complete fix should make use of MEMMOVE records to minimize the redo log volume.
Marko Mäkelä
added a comment - I pushed an initial fix that will still copy the entire changed page payload area to the redo log. A complete fix should make use of MEMMOVE records to minimize the redo log volume.
People
Marko Mäkelä
Marko Mäkelä
Votes:
1Vote for this issue
Watchers:
3Start 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":810.4000000953674,"ttfb":234.7999997138977,"pageVisibility":"visible","entityId":83214,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"ec07ad8a-f8c3-4873-a8a1-a4809d8d0d08","navigationType":0,"readyForUser":891.7999997138977,"redirectCount":0,"resourceLoadedEnd":659.6999998092651,"resourceLoadedStart":240.90000009536743,"resourceTiming":[{"duration":54.39999961853027,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":240.90000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":240.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":295.2999997138977,"responseStart":0,"secureConnectionStart":0},{"duration":54.90000009536743,"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":241.19999980926514,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":241.19999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":296.09999990463257,"responseStart":0,"secureConnectionStart":0},{"duration":112.09999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":241.40000009536743,"connectEnd":241.40000009536743,"connectStart":241.40000009536743,"domainLookupEnd":241.40000009536743,"domainLookupStart":241.40000009536743,"fetchStart":241.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":299.5,"responseEnd":353.5,"responseStart":311.5,"secureConnectionStart":241.40000009536743},{"duration":148.30000019073486,"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":241.59999990463257,"connectEnd":241.59999990463257,"connectStart":241.59999990463257,"domainLookupEnd":241.59999990463257,"domainLookupStart":241.59999990463257,"fetchStart":241.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":299.90000009536743,"responseEnd":389.90000009536743,"responseStart":313.90000009536743,"secureConnectionStart":241.59999990463257},{"duration":71,"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":241.7999997138977,"connectEnd":241.7999997138977,"connectStart":241.7999997138977,"domainLookupEnd":241.7999997138977,"domainLookupStart":241.7999997138977,"fetchStart":241.7999997138977,"redirectEnd":0,"redirectStart":0,"requestStart":301,"responseEnd":312.7999997138977,"responseStart":312.19999980926514,"secureConnectionStart":241.7999997138977},{"duration":76,"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":241.90000009536743,"connectEnd":241.90000009536743,"connectStart":241.90000009536743,"domainLookupEnd":241.90000009536743,"domainLookupStart":241.90000009536743,"fetchStart":241.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":302,"responseEnd":317.90000009536743,"responseStart":317.19999980926514,"secureConnectionStart":241.90000009536743},{"duration":88.69999980926514,"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":242.09999990463257,"connectEnd":242.09999990463257,"connectStart":242.09999990463257,"domainLookupEnd":242.09999990463257,"domainLookupStart":242.09999990463257,"fetchStart":242.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":303.90000009536743,"responseEnd":330.7999997138977,"responseStart":330.09999990463257,"secureConnectionStart":242.09999990463257},{"duration":60.09999990463257,"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":242.19999980926514,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":242.19999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":302.2999997138977,"responseStart":0,"secureConnectionStart":0},{"duration":95,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":242.40000009536743,"connectEnd":242.40000009536743,"connectStart":242.40000009536743,"domainLookupEnd":242.40000009536743,"domainLookupStart":242.40000009536743,"fetchStart":242.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":306.5,"responseEnd":337.40000009536743,"responseStart":336.7999997138977,"secureConnectionStart":242.40000009536743},{"duration":61.69999980926514,"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":242.59999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":242.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":304.2999997138977,"responseStart":0,"secureConnectionStart":0},{"duration":93.90000009536743,"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":242.7999997138977,"connectEnd":242.7999997138977,"connectStart":242.7999997138977,"domainLookupEnd":242.7999997138977,"domainLookupStart":242.7999997138977,"fetchStart":242.7999997138977,"redirectEnd":0,"redirectStart":0,"requestStart":306.59999990463257,"responseEnd":336.69999980926514,"responseStart":336.09999990463257,"secureConnectionStart":242.7999997138977},{"duration":324,"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":252.40000009536743,"connectEnd":252.40000009536743,"connectStart":252.40000009536743,"domainLookupEnd":252.40000009536743,"domainLookupStart":252.40000009536743,"fetchStart":252.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":387.90000009536743,"responseEnd":576.4000000953674,"responseStart":574.1999998092651,"secureConnectionStart":252.40000009536743},{"duration":407.09999990463257,"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":252.59999990463257,"connectEnd":252.59999990463257,"connectStart":252.59999990463257,"domainLookupEnd":252.59999990463257,"domainLookupStart":252.59999990463257,"fetchStart":252.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":647.1999998092651,"responseEnd":659.6999998092651,"responseStart":659,"secureConnectionStart":252.59999990463257},{"duration":219.7000002861023,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":554.7999997138977,"connectEnd":554.7999997138977,"connectStart":554.7999997138977,"domainLookupEnd":554.7999997138977,"domainLookupStart":554.7999997138977,"fetchStart":554.7999997138977,"redirectEnd":0,"redirectStart":0,"requestStart":737.6999998092651,"responseEnd":774.5,"responseStart":773.4000000953674,"secureConnectionStart":554.7999997138977}],"fetchStart":0,"domainLookupStart":48,"domainLookupEnd":57,"connectStart":57,"connectEnd":82,"secureConnectionStart":65,"requestStart":83,"responseStart":235,"responseEnd":251,"domLoading":239,"domInteractive":952,"domContentLoadedEventStart":952,"domContentLoadedEventEnd":997,"domComplete":1803,"loadEventStart":1803,"loadEventEnd":1803,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":930.4000000953674},{"name":"bigPipe.sidebar-id.end","time":931.4000000953674},{"name":"bigPipe.activity-panel-pipe-id.start","time":931.5},{"name":"bigPipe.activity-panel-pipe-id.end","time":932.5},{"name":"activityTabFullyLoaded","time":1014.4000000953674}],"measures":[],"correlationId":"6110cadfc0e57e","effectiveType":"4g","downlink":9.8,"rtt":0,"serverDuration":93,"dbReadsTimeInMs":12,"dbConnsTimeInMs":21,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Related to this, page_delete_rec_list_end() and page_cur_delete_rec() should be rewritten so that they will decrement PAGE_HEAP_TOP when deleting the very last records of a page, instead of prepending the deleted record to the PAGE_FREE list. That should reduce the need of page reorganize operations.
It could be worthwhile to always reorganize the page as part of page_delete_rec_list_start() and page_delete_rec_list_end().