Using parallel replication second behind master is wrongly reporting 0 when SQL thread is stopped and restarted long time after.
This happen by design
https://lists.launchpad.net/maria-developers/msg08958.html
but is really a show stopper for most proxy that send traffic to such slave thinking it's in sync with master.
My understanding is that slave_behind_master is computed after first commit so in this case the master is 2 days in advance and on a fresh restarted slave we get this
| 30 | system user | | tsce_unedic | Connect | 2211 | altering table | OPTIMIZE TABLE `requetes` | 0.000 |
|
And we can see wrong second behind master
Seconds_Behind_Master: 0
|
Using_Gtid: Slave_Pos
|
Gtid_IO_Pos: 0-21-28557589
|
Parallel_Mode: conservative
|
but on his master
gtid_current_pos | 0-21-28570301
|
A possible solution would be to update Seconds_Behind_Master by injecting a fake event in start slave with the max timestamp of all events read by the leader thread and send to to the worker threads .
To reproduce :
--source include/have_innodb.inc
|
--source include/have_binlog_format_mixed.inc
|
--let $rpl_topology=1->2
|
--source include/rpl_init.inc
|
|
# Test various aspects of parallel replication.
|
|
--connection server_1
|
ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
|
--save_master_pos
|
|
--connection server_2
|
--sync_with_master
|
--source include/stop_slave_sql_thread.inc
|
SET GLOBAL slave_parallel_threads=4;
|
|
--connection server_2
|
--sync_with_master
|
--source include/stop_slave.inc
|
SET GLOBAL slave_parallel_threads=1;
|
|
--connection server_1
|
--disable_warnings
|
INSERT INTO t1 VALUES (1, SLEEP(100));
|
--wait 100s
|
INSERT INTO t1 VALUES (1, SLEEP(1));
|
|
--connection server_2
|
--source include/start_slave.inc
|
--let $status_items= Seconds_Behind_Master
|
--source include/show_slave_status.inc
|
--sync_with_master
|
--let $status_items= Seconds_Behind_Master
|
--source include/show_slave_status.inc
|
{"report":{"fcp":813.5,"ttfb":149.20000076293945,"pageVisibility":"visible","entityId":70393,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"1ce8038a-b0a2-4812-90c3-9c203d600592","navigationType":0,"readyForUser":893.2000007629395,"redirectCount":0,"resourceLoadedEnd":725.3000001907349,"resourceLoadedStart":154.5,"resourceTiming":[{"duration":10.600000381469727,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":154.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":154.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":165.10000038146973,"responseStart":0,"secureConnectionStart":0},{"duration":159.60000038146973,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/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":154.80000019073486,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":154.80000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":314.4000005722046,"responseStart":0,"secureConnectionStart":0},{"duration":168.89999961853027,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":154.9000005722046,"connectEnd":154.9000005722046,"connectStart":154.9000005722046,"domainLookupEnd":154.9000005722046,"domainLookupStart":154.9000005722046,"fetchStart":154.9000005722046,"redirectEnd":0,"redirectStart":0,"requestStart":154.9000005722046,"responseEnd":323.80000019073486,"responseStart":323.80000019073486,"secureConnectionStart":154.9000005722046},{"duration":252.5999994277954,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/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":154.9000005722046,"connectEnd":154.9000005722046,"connectStart":154.9000005722046,"domainLookupEnd":154.9000005722046,"domainLookupStart":154.9000005722046,"fetchStart":154.9000005722046,"redirectEnd":0,"redirectStart":0,"requestStart":154.9000005722046,"responseEnd":407.5,"responseStart":407.5,"secureConnectionStart":154.9000005722046},{"duration":255.9000005722046,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":155,"connectEnd":155,"connectStart":155,"domainLookupEnd":155,"domainLookupStart":155,"fetchStart":155,"redirectEnd":0,"redirectStart":0,"requestStart":155,"responseEnd":410.9000005722046,"responseStart":410.9000005722046,"secureConnectionStart":155},{"duration":256.19999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":155.20000076293945,"connectEnd":155.20000076293945,"connectStart":155.20000076293945,"domainLookupEnd":155.20000076293945,"domainLookupStart":155.20000076293945,"fetchStart":155.20000076293945,"redirectEnd":0,"redirectStart":0,"requestStart":155.20000076293945,"responseEnd":411.4000005722046,"responseStart":411.4000005722046,"secureConnectionStart":155.20000076293945},{"duration":256.80000019073486,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":155.4000005722046,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":155.4000005722046,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":412.20000076293945,"responseStart":0,"secureConnectionStart":0},{"duration":256.3999996185303,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":155.4000005722046,"connectEnd":155.4000005722046,"connectStart":155.4000005722046,"domainLookupEnd":155.4000005722046,"domainLookupStart":155.4000005722046,"fetchStart":155.4000005722046,"redirectEnd":0,"redirectStart":0,"requestStart":155.4000005722046,"responseEnd":411.80000019073486,"responseStart":411.70000076293945,"secureConnectionStart":155.4000005722046},{"duration":256.70000076293945,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":155.5,"connectEnd":155.5,"connectStart":155.5,"domainLookupEnd":155.5,"domainLookupStart":155.5,"fetchStart":155.5,"redirectEnd":0,"redirectStart":0,"requestStart":155.5,"responseEnd":412.20000076293945,"responseStart":412.20000076293945,"secureConnectionStart":155.5},{"duration":257.1000003814697,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/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":155.60000038146973,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":155.60000038146973,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":412.70000076293945,"responseStart":0,"secureConnectionStart":0},{"duration":257.19999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/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":155.70000076293945,"connectEnd":155.70000076293945,"connectStart":155.70000076293945,"domainLookupEnd":155.70000076293945,"domainLookupStart":155.70000076293945,"fetchStart":155.70000076293945,"redirectEnd":0,"redirectStart":0,"requestStart":155.70000076293945,"responseEnd":412.9000005722046,"responseStart":412.9000005722046,"secureConnectionStart":155.70000076293945},{"duration":560.1999998092651,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":161.60000038146973,"connectEnd":161.60000038146973,"connectStart":161.60000038146973,"domainLookupEnd":161.60000038146973,"domainLookupStart":161.60000038146973,"fetchStart":161.60000038146973,"redirectEnd":0,"redirectStart":0,"requestStart":161.60000038146973,"responseEnd":721.8000001907349,"responseStart":721.8000001907349,"secureConnectionStart":161.60000038146973},{"duration":563.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":161.80000019073486,"connectEnd":161.80000019073486,"connectStart":161.80000019073486,"domainLookupEnd":161.80000019073486,"domainLookupStart":161.80000019073486,"fetchStart":161.80000019073486,"redirectEnd":0,"redirectStart":0,"requestStart":161.80000019073486,"responseEnd":725.3000001907349,"responseStart":725.3000001907349,"secureConnectionStart":161.80000019073486},{"duration":163.80000019073486,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":549.3000001907349,"connectEnd":549.3000001907349,"connectStart":549.3000001907349,"domainLookupEnd":549.3000001907349,"domainLookupStart":549.3000001907349,"fetchStart":549.3000001907349,"redirectEnd":0,"redirectStart":0,"requestStart":549.3000001907349,"responseEnd":713.1000003814697,"responseStart":713.1000003814697,"secureConnectionStart":549.3000001907349},{"duration":2.3000001907348633,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":738,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":738,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":740.3000001907349,"responseStart":0,"secureConnectionStart":0},{"duration":172.60000038146973,"initiatorType":"link","name":"https://jira.mariadb.org/s/d5715adaadd168a9002b108b2b039b50-CDN/lu2bu7/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","startTime":814.6000003814697,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":814.6000003814697,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":987.2000007629395,"responseStart":0,"secureConnectionStart":0},{"duration":172.19999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/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":815.6000003814697,"connectEnd":815.6000003814697,"connectStart":815.6000003814697,"domainLookupEnd":815.6000003814697,"domainLookupStart":815.6000003814697,"fetchStart":815.6000003814697,"redirectEnd":0,"redirectStart":0,"requestStart":815.6000003814697,"responseEnd":987.8000001907349,"responseStart":987.8000001907349,"secureConnectionStart":815.6000003814697},{"duration":176.20000076293945,"initiatorType":"script","name":"https://jira.mariadb.org/s/f51ef5507eea4c158f257c66c93b2a3f-CDN/lu2bu7/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","startTime":816,"connectEnd":816,"connectStart":816,"domainLookupEnd":816,"domainLookupStart":816,"fetchStart":816,"redirectEnd":0,"redirectStart":0,"requestStart":816,"responseEnd":992.2000007629395,"responseStart":992.2000007629395,"secureConnectionStart":816}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":14,"responseStart":150,"responseEnd":161,"domLoading":153,"domInteractive":964,"domContentLoadedEventStart":964,"domContentLoadedEventEnd":1012,"domComplete":1260,"loadEventStart":1260,"loadEventEnd":1260,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":937.5},{"name":"bigPipe.sidebar-id.end","time":938.3000001907349},{"name":"bigPipe.activity-panel-pipe-id.start","time":938.4000005722046},{"name":"bigPipe.activity-panel-pipe-id.end","time":940.2000007629395},{"name":"activityTabFullyLoaded","time":1030.2000007629395}],"measures":[],"correlationId":"5288b97136bb47","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":81,"dbReadsTimeInMs":17,"dbConnsTimeInMs":24,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
"memorize the last gained SBM", non valid for stop slave with no delay but the next event is a long query
To preserve the definition of SBM = time difference of last event in queue and oldest event in the queue being COMMITTED,
i'm still curious why not using a heartbeat from the leader enrich with timestamp of last binary log event would be more accurate, a slave would not start fetching event before first heartbeat ? And SBM definition become time difference last event in the leader and oldest event COMMITTED in the queue