We are about to enable sync-binlog by default in 10.2. Please check how it affects performance on some standard read-write load with binary log enabled. No need for accurate numbers, but we should have at least a rough picture.
I've run a series of OLTP benchmarks with varying write intensity (10, 22, 80 and 100% writes). I tested 3 different log settings:
innodb_flush_log_at_trx_commit=0, sync-binlog=0
innodb_flush_log_at_trx_commit=1, sync-binlog=0
innodb_flush_log_at_trx_commit=1, sync-binlog=1
Naturally there is a big difference between 1. and 2., but also between 2. and 3. Here I see a performance drop between 10% and 50%. The effect is more pronounced when the datadir resides on a hard (rotating) disk. But even on a (fast) SSD the effect is clearly visible. For details see the attached spread sheets.
I tested MariaDB 10.1 and 10.2 and also MySQL 5.7. The numbers are in the same ballpark for all 3 of them with some slight advantage for 10.2. This however is no reason to celebrate, because in absolute numbers 10.2 comes out at the bottom.
I also spotted an irregularity here: it seems that MariaDB 10.2 does two fsyncs per commit on the InnoDB redo log as soon as one sets innodb_flush_log_at_trx_commit=1 and enables the binlog (no matter if sync-binlog=0 or 1). This is someting to investigate in a separate task.
I also checked the bytes written per transaction to the redo log and the binlog. There is a moderate increase of ~6 bytes per binlog event in 10.2 compared to 10.1. No numbers here for MySQL (it does not have a status variable for binlog writes).
Axel Schwenke
added a comment - I've run a series of OLTP benchmarks with varying write intensity (10, 22, 80 and 100% writes). I tested 3 different log settings:
innodb_flush_log_at_trx_commit=0, sync-binlog=0
innodb_flush_log_at_trx_commit=1, sync-binlog=0
innodb_flush_log_at_trx_commit=1, sync-binlog=1
Naturally there is a big difference between 1. and 2., but also between 2. and 3. Here I see a performance drop between 10% and 50%. The effect is more pronounced when the datadir resides on a hard (rotating) disk. But even on a (fast) SSD the effect is clearly visible. For details see the attached spread sheets.
I tested MariaDB 10.1 and 10.2 and also MySQL 5.7. The numbers are in the same ballpark for all 3 of them with some slight advantage for 10.2. This however is no reason to celebrate, because in absolute numbers 10.2 comes out at the bottom.
I also spotted an irregularity here: it seems that MariaDB 10.2 does two fsyncs per commit on the InnoDB redo log as soon as one sets innodb_flush_log_at_trx_commit=1 and enables the binlog (no matter if sync-binlog=0 or 1). This is someting to investigate in a separate task.
I also checked the bytes written per transaction to the redo log and the binlog. There is a moderate increase of ~6 bytes per binlog event in 10.2 compared to 10.1. No numbers here for MySQL (it does not have a status variable for binlog writes).
I'd say 50% is not too bad, that's something that I would initially expect, my results were much worse (but they aren't reliable).
I don't see it mentioned anywhere, did you run with the default statement binlog_format, or were there different ones? I wonder if it might make a difference.
Our new default is expected to be mixed, which can easily switch to row.
Elena Stepanova
added a comment - I'd say 50% is not too bad, that's something that I would initially expect, my results were much worse (but they aren't reliable).
I don't see it mentioned anywhere, did you run with the default statement binlog_format, or were there different ones? I wonder if it might make a difference.
Our new default is expected to be mixed, which can easily switch to row.
People
Axel Schwenke
Elena Stepanova
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":1274.9000000059605,"ttfb":432.20000000298023,"pageVisibility":"visible","entityId":59950,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"fdecf8e4-6e51-41a5-a13b-b75256dfa21b","navigationType":0,"readyForUser":1328.2000000029802,"redirectCount":0,"resourceLoadedEnd":1354.1000000014901,"resourceLoadedStart":441,"resourceTiming":[{"duration":116.5,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":441,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":441,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":557.5,"responseStart":0,"secureConnectionStart":0},{"duration":116.60000000149012,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/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":441.30000000447035,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":441.30000000447035,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":557.9000000059605,"responseStart":0,"secureConnectionStart":0},{"duration":185.40000000596046,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":441.5,"connectEnd":559.5,"connectStart":559.5,"domainLookupEnd":559.5,"domainLookupStart":559.5,"fetchStart":441.5,"redirectEnd":0,"redirectStart":0,"requestStart":559.8000000044703,"responseEnd":626.9000000059605,"responseStart":572,"secureConnectionStart":559.5},{"duration":342.3999999985099,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/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":442.1000000014901,"connectEnd":442.1000000014901,"connectStart":442.1000000014901,"domainLookupEnd":442.1000000014901,"domainLookupStart":442.1000000014901,"fetchStart":442.1000000014901,"redirectEnd":0,"redirectStart":0,"requestStart":719.1000000014901,"responseEnd":784.5,"responseStart":734.9000000059605,"secureConnectionStart":442.1000000014901},{"duration":296.19999999552965,"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":442.30000000447035,"connectEnd":442.30000000447035,"connectStart":442.30000000447035,"domainLookupEnd":442.30000000447035,"domainLookupStart":442.30000000447035,"fetchStart":442.30000000447035,"redirectEnd":0,"redirectStart":0,"requestStart":720.8000000044703,"responseEnd":738.5,"responseStart":737.1000000014901,"secureConnectionStart":442.30000000447035},{"duration":310.6000000014901,"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":442.5,"connectEnd":442.5,"connectStart":442.5,"domainLookupEnd":442.5,"domainLookupStart":442.5,"fetchStart":442.5,"redirectEnd":0,"redirectStart":0,"requestStart":742.3000000044703,"responseEnd":753.1000000014901,"responseStart":752.1000000014901,"secureConnectionStart":442.5},{"duration":783.6000000014901,"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":442.70000000298023,"connectEnd":442.70000000298023,"connectStart":442.70000000298023,"domainLookupEnd":442.70000000298023,"domainLookupStart":442.70000000298023,"fetchStart":442.70000000298023,"redirectEnd":0,"redirectStart":0,"requestStart":1214.3000000044703,"responseEnd":1226.3000000044703,"responseStart":1225.6000000014901,"secureConnectionStart":442.70000000298023},{"duration":307.79999999701977,"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":442.90000000596046,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":442.90000000596046,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":750.7000000029802,"responseStart":0,"secureConnectionStart":0},{"duration":300.80000000447035,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/f66960d7757d5f06891021cb34673e32/shortcuts.js?context=issuenavigation&context=issueaction","startTime":443,"connectEnd":443,"connectStart":443,"domainLookupEnd":443,"domainLookupStart":443,"fetchStart":443,"redirectEnd":0,"redirectStart":0,"requestStart":724.5,"responseEnd":743.8000000044703,"responseStart":742.4000000059605,"secureConnectionStart":443},{"duration":307.6000000014901,"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":443.20000000298023,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":443.20000000298023,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":750.8000000044703,"responseStart":0,"secureConnectionStart":0},{"duration":302.3999999985099,"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":443.20000000298023,"connectEnd":443.20000000298023,"connectStart":443.20000000298023,"domainLookupEnd":443.20000000298023,"domainLookupStart":443.20000000298023,"fetchStart":443.20000000298023,"redirectEnd":0,"redirectStart":0,"requestStart":725.9000000059605,"responseEnd":745.6000000014901,"responseStart":744.2000000029802,"secureConnectionStart":443.20000000298023},{"duration":419.8999999985099,"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":447.40000000596046,"connectEnd":447.40000000596046,"connectStart":447.40000000596046,"domainLookupEnd":447.40000000596046,"domainLookupStart":447.40000000596046,"fetchStart":447.40000000596046,"redirectEnd":0,"redirectStart":0,"requestStart":855.8000000044703,"responseEnd":867.3000000044703,"responseStart":866.6000000014901,"secureConnectionStart":447.40000000596046},{"duration":699.4000000059605,"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":447.5,"connectEnd":447.5,"connectStart":447.5,"domainLookupEnd":447.5,"domainLookupStart":447.5,"fetchStart":447.5,"redirectEnd":0,"redirectStart":0,"requestStart":1135.9000000059605,"responseEnd":1146.9000000059605,"responseStart":1146.2000000029802,"secureConnectionStart":447.5},{"duration":142.29999999701977,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":758.7000000029802,"connectEnd":758.7000000029802,"connectStart":758.7000000029802,"domainLookupEnd":758.7000000029802,"domainLookupStart":758.7000000029802,"fetchStart":758.7000000029802,"redirectEnd":0,"redirectStart":0,"requestStart":869.9000000059605,"responseEnd":901,"responseStart":900.2000000029802,"secureConnectionStart":758.7000000029802},{"duration":153.5,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1067.9000000059605,"connectEnd":1067.9000000059605,"connectStart":1067.9000000059605,"domainLookupEnd":1067.9000000059605,"domainLookupStart":1067.9000000059605,"fetchStart":1067.9000000059605,"redirectEnd":0,"redirectStart":0,"requestStart":1186.7000000029802,"responseEnd":1221.4000000059605,"responseStart":1220.8000000044703,"secureConnectionStart":1067.9000000059605},{"duration":130.19999999552965,"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","startTime":1223.9000000059605,"connectEnd":1223.9000000059605,"connectStart":1223.9000000059605,"domainLookupEnd":1223.9000000059605,"domainLookupStart":1223.9000000059605,"fetchStart":1223.9000000059605,"redirectEnd":0,"redirectStart":0,"requestStart":1342.7000000029802,"responseEnd":1354.1000000014901,"responseStart":1353.2000000029802,"secureConnectionStart":1223.9000000059605},{"duration":106.89999999850988,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1268.4000000059605,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1268.4000000059605,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1375.3000000044703,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":223,"domainLookupEnd":243,"connectStart":243,"connectEnd":266,"secureConnectionStart":253,"requestStart":266,"responseStart":433,"responseEnd":447,"domLoading":436,"domInteractive":1381,"domContentLoadedEventStart":1381,"domContentLoadedEventEnd":1421,"domComplete":1539,"loadEventStart":1539,"loadEventEnd":1540,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1359.7000000029802},{"name":"bigPipe.sidebar-id.end","time":1360.5},{"name":"bigPipe.activity-panel-pipe-id.start","time":1360.7000000029802},{"name":"bigPipe.activity-panel-pipe-id.end","time":1361.9000000059605},{"name":"activityTabFullyLoaded","time":1439.1000000014901}],"measures":[],"correlationId":"b2581c95551cd8","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":94,"dbReadsTimeInMs":9,"dbConnsTimeInMs":16,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
I've run a series of OLTP benchmarks with varying write intensity (10, 22, 80 and 100% writes). I tested 3 different log settings:
Naturally there is a big difference between 1. and 2., but also between 2. and 3. Here I see a performance drop between 10% and 50%. The effect is more pronounced when the datadir resides on a hard (rotating) disk. But even on a (fast) SSD the effect is clearly visible. For details see the attached spread sheets.
I tested MariaDB 10.1 and 10.2 and also MySQL 5.7. The numbers are in the same ballpark for all 3 of them with some slight advantage for 10.2. This however is no reason to celebrate, because in absolute numbers 10.2 comes out at the bottom.
I also spotted an irregularity here: it seems that MariaDB 10.2 does two fsyncs per commit on the InnoDB redo log as soon as one sets innodb_flush_log_at_trx_commit=1 and enables the binlog (no matter if sync-binlog=0 or 1). This is someting to investigate in a separate task.
I also checked the bytes written per transaction to the redo log and the binlog. There is a moderate increase of ~6 bytes per binlog event in 10.2 compared to 10.1. No numbers here for MySQL (it does not have a status variable for binlog writes).