WL#7318: Delayed Replication: GTID based and relative to immediate master commit
Affects: Server-8.0 — Status: Complete
Delayed replication was introduced by WL#344. Currently there are following drawbacks with the implementation: 1. In a chained replication topology, the delay is measured relative to the originating master. If two upstream masters are making updates, and there is a lag between the masters, then this will cause the actual delay relative to each master to fluctuate. 2. The delay is relative to when the transaction started, not to when it was committed. 3. The delay is checked per event, not per transaction. We introduce a new timestamp in the binary log: the immediate_commit_timestamp which is the number of microseconds since epoch when the transaction was written to the binary log of the immediate master. Delayed replication will be measured relative to this timestamp if both the immediate master and current server (slave) support immediate_commit_timestamp. If any one of immediate master or slave doesn't, we keep the old implementation of delayed replication. Related Bugs: ============ 1) BUG#57514: RPL_DELAYED_SLAVE FAILS SPORADICALLY IN PB
FUNCTIONAL REQUIREMENTS: ======================= F1) The delayed replication should be expressed with reference to immediate master. F2) The delay begins at commit on master, not beginning of transaction as was the case before this worklog. F3) At slave, the delay happens at beginning of transaction and not events. So all events in the transaction either wait or apply without waiting the delay period in between. Precisely the wait happens at GTID_LOG_EVENT or ANONYMOUS_LOG_EVENT whichever be the case. F4) The delay configuration and status should be available in SHOW SLAVE STATUS as well as replication P_S tables. F5) The delay is expressed in seconds. This applies to: F5.1) CHANGE MASTER TO MASTER_DELAY= XYZ; F5.2) SHOW SLAVE STATUS F5.6) Replication P_S tables. F6) 5.7->8.0 delayed replication should work similar to 5.7->5.7. NON-FUNCTIONAL REQUIREMENTS: =========================== NF1) Delayed replication should be allowed on every slave in any replication topology.
There are no changes in the interface except for those mentioned in the functional requirements. The interfaces specification of delayed replication should be as follows: 1) configuration to be done with CHANGE MASTER TO MASTER_DELAY= XYZ; where time is expressed in seconds. 2) The delay expressed (as in (1)) is measured with respect to commit at immediate master. Talking of the whole 2 PC commit, we assume that commit timestamp = time of writing to binlog (MYSQL_BIN_LOG::write_gtid()) 2) The delay happens per transaction (not event). So the delay is imposed only on gtid_log_event or anonymous_gtid_log_event. The other events in the transaction always follow these event without any waiting time imposed on them. 3) The delay is interpreted as follows: 3.1: gtid_log_event case: (time of execution of gtid_log_event of the transaction on slave) - (commit timestamp at immediate master). 3.2: anonymous_gtid_log_event case: For the purpose of delay calculations gtid_log_event can just be replaced by anonymous_gtid_log_event in (3.1). 4) The monitoring is achieved via: 4.1 SHOW SLAVE STATUS 4.1.1 SQL_Delay 4.1.2 SQL_Remaining_Delay 4.2 Replication P_S Tables 4.2.1 Table: replication_execute_configuration column: DESIRED_DELAY 4.2.2 Table: replication_execute_status column: REMAINING_DELAY The unit of time is in seconds in all monitoring infrastructure. UPGRADE SCENARIO ================ The 8.0 slave needs commit timestamp from the master to work in the way this worklog describes. Since 5.7 master doesn’t send the commit timestamps we can not use the new code to handle this code. So the delayed replication from a 5.7 master to a 8.0 slave works the same way as 5.7->5.7 delayed replication.
L1) This worklog builds on top of the patch for WL#7319. The new delayed replication works as follows: L2) Makes changes so that wait happens at GTID_LOG_EVENT or ANONYMOUS_GTID_LOG_EVENT only. L3) The time delay is now calculated as: time_t sql_delay_end= Gtid_log_event->(immediate_commit_timestamp / 1000000) + sql_delay; We don't remove the old code because we have to use it in 5.7->8.0 delayed replication. We split delayed replication in two branches: Branch 1 handles the 5.8->8.0 delayed replication (new logic as in L2 and L3 here). Branch 2 handles the 5.7->8.0 replication (keep old code).
Copyright (c) 2000, 2022, Oracle Corporation and/or its affiliates. All rights reserved.