Semi-sync Wait Point AFTER_COMMIT Slow on Workloads with Heavy Concurrency
When using semi-sync replication with rpl_semi_sync_master_wait_point=AFTER_COMMIT, the performance of the primary can significantly reduce to about 10% of AFTER_SYNC's performance for workloads with many concurrent users executing transactions. See the attached graph, where the "Async" bar reports the performance of a primary using asynchronous replication, the "Semi-sync AFTER_COMMIT" and "Semi-sync AFTER_SYNC" reports the performance of the primary using the respective rpl_semi_sync_master_wait_point variable, and "Semi-sync GROUP_ACK" is a prototype of the work outlined by MDEV-33491.
It can be seen that the performance of the AFTER_COMMIT mode of semi-sync is many times worse than that of the AFTER_SYNC mode. This is because all connections on the primary share the same cond_wait variable/mutex pair, so any time an ACK is recieved from a replica, all waiting connections are awoken to check if the ACK was for itself, which is done in a mutual exclusion zone. See the attached Semi-sync Group Ack Proposal PDF for more details.
Instead, each connection should use its own condition variable (e.g. THD::COND_wakeup_ready), and the ACK receiver thread should only signal connections which have been ACKed for wakeup.
Let the group-leader thread handle also the AFTER_COMMIT, in which case there is only one thread waiting for the ACK.
Instead of requesting an ACK for each commit, only request an ACK for the whole group.
Why not going with the this solution? It will be much faster and use less bandwidth (as there is only one ACK per group).
Michael Widenius
added a comment - The solution Kristian and I come up with is:
Let the group-leader thread handle also the AFTER_COMMIT, in which case there is only one thread waiting for the ACK.
Instead of requesting an ACK for each commit, only request an ACK for the whole group.
Why not going with the this solution? It will be much faster and use less bandwidth (as there is only one ACK per group).
The following graph shows the speedup with this patch (the MDEV_33551_AFTER_COMMIT bar):
Brandon Nesterenko
added a comment - - edited Hi knielsen !
This is ready for review: PR 3089 .
The following graph shows the speedup with this patch (the MDEV_33551_AFTER_COMMIT bar):
monty the semi-sync group ACK work is tracked by MDEV-33491 (with its description updated per our last discussion)
Brandon Nesterenko
added a comment - monty the semi-sync group ACK work is tracked by MDEV-33491 (with its description updated per our last discussion)
knielsen, I've addressed your review comments from the mailing list, and force-pushed to PR 3089 with the latest updates. Can you give it another review?
Brandon Nesterenko
added a comment - knielsen , I've addressed your review comments from the mailing list, and force-pushed to PR 3089 with the latest updates. Can you give it another review?
Indicates if this thread is suspended due to awaiting an ACK from a
replica. True if suspended, false otherwise.
Note that this variable is protected by Repl_semi_sync_master::LOCK_binlog
*/
bool is_awaiting_semisync_ack;
This patch removed the is_awaiting_semisync_ack variable, so to resolve, just remove that variable.
Otherwise no merge issues should be present.
Final performance figures as follows:
In summary, this patch changes the way that semi-sync connections wait for ACKs as follows. Previously, when an ACK would arrive from a replica, all user connection threads currently awaiting an ACK for its transaction would be awoken, but each thread could only check if the ACK was for it one at a time. With the patch, instead of waking up all threads awaiting ACKs, only those which are relevant to the received ACK are awoken.
As an example, say there are 500 transactions on the primary server awaiting an ACK. Previously, when an ACK would come in for the next transaction in the binlog, all 500 threads would be signalled to wake up and check if that ACK was for it, yet each thread could only check one at a time (not in parallel), and only one of the 500 could move on (the other 499 would go back to waiting for their respective ACK). However, with the patch, now only the thread associated with that ACK (or potentially any earlier binlogged transactions) are awoken.
Brandon Nesterenko
added a comment - - edited Pushed into 10.6 as 75c7c6dc3 .
Merge conflict observed in 10.11 with a fix in branch bb-10.11-MDEV-33551-mergefix .
Looks like another merge conflict may exist in sql/sql_class.h in 11.X with chunk:
bool check_slave_ignored_db_with_error(const Lex_ident_db &db) const;
/*
Indicates if this thread is suspended due to awaiting an ACK from a
replica. True if suspended, false otherwise.
Note that this variable is protected by Repl_semi_sync_master::LOCK_binlog
*/
bool is_awaiting_semisync_ack;
This patch removed the is_awaiting_semisync_ack variable, so to resolve, just remove that variable.
Otherwise no merge issues should be present.
Final performance figures as follows:
In summary, this patch changes the way that semi-sync connections wait for ACKs as follows. Previously, when an ACK would arrive from a replica, all user connection threads currently awaiting an ACK for its transaction would be awoken, but each thread could only check if the ACK was for it one at a time. With the patch, instead of waking up all threads awaiting ACKs, only those which are relevant to the received ACK are awoken.
As an example, say there are 500 transactions on the primary server awaiting an ACK. Previously, when an ACK would come in for the next transaction in the binlog, all 500 threads would be signalled to wake up and check if that ACK was for it, yet each thread could only check one at a time (not in parallel), and only one of the 500 could move on (the other 499 would go back to waiting for their respective ACK). However, with the patch, now only the thread associated with that ACK (or potentially any earlier binlogged transactions) are awoken.
People
Brandon Nesterenko
Brandon Nesterenko
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":1293,"ttfb":280.59999990463257,"pageVisibility":"visible","entityId":128104,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"16f0c0f3-5970-4eb5-9333-eb8b6384701a","navigationType":0,"readyForUser":1375.7999999523163,"redirectCount":0,"resourceLoadedEnd":972.5,"resourceLoadedStart":327.5,"resourceTiming":[{"duration":263.2000000476837,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":327.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":327.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":590.7000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":263.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":327.7999999523163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":327.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":591.7000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":509.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":328,"connectEnd":328,"connectStart":328,"domainLookupEnd":328,"domainLookupStart":328,"fetchStart":328,"redirectEnd":0,"redirectStart":0,"requestStart":594.2999999523163,"responseEnd":837.5,"responseStart":605.7999999523163,"secureConnectionStart":328},{"duration":644.2999999523163,"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":328.2000000476837,"connectEnd":328.2000000476837,"connectStart":328.2000000476837,"domainLookupEnd":328.2000000476837,"domainLookupStart":328.2000000476837,"fetchStart":328.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":595.4000000953674,"responseEnd":972.5,"responseStart":608.4000000953674,"secureConnectionStart":328.2000000476837},{"duration":289.7999999523163,"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":328.40000009536743,"connectEnd":328.40000009536743,"connectStart":328.40000009536743,"domainLookupEnd":328.40000009536743,"domainLookupStart":328.40000009536743,"fetchStart":328.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":597.0999999046326,"responseEnd":618.2000000476837,"responseStart":610.9000000953674,"secureConnectionStart":328.40000009536743},{"duration":292.2000000476837,"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":328.59999990463257,"connectEnd":328.59999990463257,"connectStart":328.59999990463257,"domainLookupEnd":328.59999990463257,"domainLookupStart":328.59999990463257,"fetchStart":328.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":598.0999999046326,"responseEnd":620.7999999523163,"responseStart":618.2999999523163,"secureConnectionStart":328.59999990463257},{"duration":293.7000000476837,"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":328.7999999523163,"connectEnd":328.7999999523163,"connectStart":328.7999999523163,"domainLookupEnd":328.7999999523163,"domainLookupStart":328.7999999523163,"fetchStart":328.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":599.2000000476837,"responseEnd":622.5,"responseStart":621.0999999046326,"secureConnectionStart":328.7999999523163},{"duration":268.59999990463257,"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":329,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":329,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":597.5999999046326,"responseStart":0,"secureConnectionStart":0},{"duration":300.60000014305115,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":329.09999990463257,"connectEnd":329.09999990463257,"connectStart":329.09999990463257,"domainLookupEnd":329.09999990463257,"domainLookupStart":329.09999990463257,"fetchStart":329.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":603.7999999523163,"responseEnd":629.7000000476837,"responseStart":625.5999999046326,"secureConnectionStart":329.09999990463257},{"duration":272.90000009536743,"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":329.2999999523163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":329.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":602.2000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":310.09999990463257,"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":329.5,"connectEnd":329.5,"connectStart":329.5,"domainLookupEnd":329.5,"domainLookupStart":329.5,"fetchStart":329.5,"redirectEnd":0,"redirectStart":0,"requestStart":606.5999999046326,"responseEnd":639.5999999046326,"responseStart":637.5999999046326,"secureConnectionStart":329.5},{"duration":638,"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":330.59999990463257,"connectEnd":330.59999990463257,"connectStart":330.59999990463257,"domainLookupEnd":330.59999990463257,"domainLookupStart":330.59999990463257,"fetchStart":330.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":676,"responseEnd":968.5999999046326,"responseStart":957.4000000953674,"secureConnectionStart":330.59999990463257},{"duration":633.8999998569489,"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":336.2000000476837,"connectEnd":336.2000000476837,"connectStart":336.2000000476837,"domainLookupEnd":336.2000000476837,"domainLookupStart":336.2000000476837,"fetchStart":336.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":719.0999999046326,"responseEnd":970.0999999046326,"responseStart":960.2999999523163,"secureConnectionStart":336.2000000476837},{"duration":223.40000009536743,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":998.5999999046326,"connectEnd":998.5999999046326,"connectStart":998.5999999046326,"domainLookupEnd":998.5999999046326,"domainLookupStart":998.5999999046326,"fetchStart":998.5999999046326,"redirectEnd":0,"redirectStart":0,"requestStart":1189.2000000476837,"responseEnd":1222,"responseStart":1221.4000000953674,"secureConnectionStart":998.5999999046326}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":70,"responseStart":281,"responseEnd":345,"domLoading":298,"domInteractive":1528,"domContentLoadedEventStart":1528,"domContentLoadedEventEnd":1599,"domComplete":1956,"loadEventStart":1957,"loadEventEnd":1957,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1494.5999999046326},{"name":"bigPipe.sidebar-id.end","time":1495.2999999523163},{"name":"bigPipe.activity-panel-pipe-id.start","time":1495.5},{"name":"bigPipe.activity-panel-pipe-id.end","time":1497.7999999523163},{"name":"activityTabFullyLoaded","time":1618.4000000953674}],"measures":[],"correlationId":"8739ad92a43e98","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":109,"dbReadsTimeInMs":17,"dbConnsTimeInMs":26,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Bug
Major
MDEV-33491 Semi-sync Replication Group ACK
The solution Kristian and I come up with is:
Why not going with the this solution? It will be much faster and use less bandwidth (as there is only one ACK per group).