Bullet:MySQL增强半同步参数rpl_semi_sync_master_wait_point值AFTER_SYNC和AFTER_COMMIT的对比实验
MySQL 5.7.22
启用增强半同步复制
MySQL对该参数值的描述
Semisync can wait for slave ACKs at one of two points, AFTER_SYNC or AFTER_COMMIT. AFTER_SYNC is the default value. AFTER_SYNC means that semisynchronous replication waits just after the binary log file is flushed, but before the engine commits, and so guarantees that no other sessions can see the data before replicated to slave. AFTER_COMMIT means that semisynchronous replication waits just after the engine commits. Other sessions may see the data before it is replicated, even though the current session is still waiting for the commit to end successfully.
From: Source Code mysql-5.7.22\plugin\semisync\semisync_master_plugin.cc
Replication: Semisynchronous replication master servers now use a different wait point by default in communicating wih slaves. This is the point at which the master waits for acknowledgment of transaction receipt by a slave before returning a status to the client that committed the transaction. The wait point is controlled by the new rpl_semi_sync_master_wait_point system variable. These values are permitted:
AFTER_SYNC (the default): The master writes each transaction to its binary log and the slave, and syncs the binary log to disk. The master waits for slave acknowledgment of transaction receipt after the sync. Upon receiving acknowledgment, the master commits the transaction to the storage engine and returns a result to the client, which then can proceed.
AFTER_COMMIT: The master writes each transaction to its binary log and the slave, syncs the binary log, and commits the transaction to the storage engine. The master waits for slave acknowledgment of transaction receipt after the commit. Upon receiving acknowledgment, the master returns a result to the client, which then can proceed.
For older versions of MySQL, semisynchronous master behavior is equivalent to a setting of AFTER_COMMIT.
The replication characteristics of these settings differ as follows:
With AFTER_SYNC, all clients see the committed transaction at the same time: After it has been acknowledged by the slave and committed to the storage engine on the master. Thus, all clients see the same data on the master.
In the event of master failure, all transactions committed on the master have been replicated to the slave (saved to its relay log). A crash of the master and failover to the slave is lossless because the slave is up to date.
With AFTER_COMMIT, the client issuing the transaction gets a return status only after the server commits to the storage engine and receives slave acknowledgment. After the commit and before slave acknowledgment, other clients can see the committed transaction before the committing client.
If something goes wrong such that the slave does not process the transaction, then in the event of a master crash and failover to the slave, it is possible that such clients will see a loss of data relative to what they saw on the master.
The new wait point is a behavior change, but requires no reconfiguration. The change does introduce a version compatibility constraint because it increments the semisynchronous interface version: Servers for MySQL 5.7.2 and up do not work with semisynchronous replication plugins from older versions, nor do servers from older versions work with semisynchronous replication plugins for MySQL 5.7.2 and up.
From：https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html
测试表
Create Table: CREATE TABLE `syk`.`t` ( `t` datetime DEFAULT NULL, `a` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; insert into syk.t (t,a) values(now(),1); commit;
准备3个会话窗口，A,B,C
A窗口连接主库，负责更改主库的参数及表更新操作
B窗口负责监视主库数据的变化，执行下述命令
while (true);do mysql -uroot -pmysql-001 -e "select now(),t,a from syk.t" -s --skip-column-names 2>&1 | grep -v Warning sleep 1 done
C窗口负责从库，slave的重启操作，模拟从库的slave停止
AFTER_SYNC
5.7.2及以上默认值
A:
mysql> show variables like '%rpl%point%'; +---------------------------------+------------+ | Variable_name | Value | +---------------------------------+------------+ | rpl_semi_sync_master_wait_point | AFTER_SYNC | +---------------------------------+------------+ mysql> show global status like '%rpl%master%status%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | Rpl_semi_sync_master_status | ON | +-----------------------------+-------+ mysql> select * from syk.t; +---------------------+------+ | t | a | +---------------------+------+ | 2018-08-02 14:57:15 | 1 | +---------------------+------+
B:
此时B窗口一直在查询表数据
C:
停止slave
A:
mysql> update syk.t set t=now() where a=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (10.00 sec) mysql> select * from syk.t; +---------------------+------+ | t | a | +---------------------+------+ | 2018-08-02 15:21:51 | 1 | +---------------------+------+ 1 row in set (0.00 sec)
A窗口的commit延迟了10秒才提交成功。受rpl_semi_sync_master_timeout参数影响，默认100000毫秒，即10秒。
B：
2018-08-02 15:21:50 2018-08-02 14:57:15 1 2018-08-02 15:21:51 2018-08-02 14:57:15 1 <========= 2018-08-02 15:21:52 2018-08-02 14:57:15 1 2018-08-02 15:21:53 2018-08-02 14:57:15 1 2018-08-02 15:21:54 2018-08-02 14:57:15 1 2018-08-02 15:21:55 2018-08-02 14:57:15 1 2018-08-02 15:21:56 2018-08-02 14:57:15 1 2018-08-02 15:21:57 2018-08-02 14:57:15 1 2018-08-02 15:21:58 2018-08-02 14:57:15 1 2018-08-02 15:21:59 2018-08-02 14:57:15 1 2018-08-02 15:22:00 2018-08-02 14:57:15 1 2018-08-02 15:22:01 2018-08-02 14:57:15 1 2018-08-02 15:22:02 2018-08-02 14:57:15 1 2018-08-02 15:22:03 2018-08-02 14:57:15 1 2018-08-02 15:22:04 2018-08-02 14:57:15 1 2018-08-02 15:22:05 2018-08-02 15:21:51 1 <=========
可以看到，主库的其他会话需要等待提交显示OK之后，才能看得到。
AFTER_COMMIT
5.7.2之前（不含5.7.2）的版本，的版本默认行为
A:
set global rpl_semi_sync_master_wait_point=AFTER_COMMIT;
C:
重启slave
A:
mysql> show variables like '%rpl%point%'; +---------------------------------+--------------+ | Variable_name | Value | +---------------------------------+--------------+ | rpl_semi_sync_master_wait_point | AFTER_COMMIT | +---------------------------------+--------------+ 1 row in set (0.00 sec) mysql> show global status like '%rpl%master%status%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | Rpl_semi_sync_master_status | ON | +-----------------------------+-------+ 1 row in set (0.00 sec) mysql> select * from syk.t; +---------------------+------+ | t | a | +---------------------+------+ | 2018-08-02 15:21:51 | 1 | +---------------------+------+ 1 row in set (0.00 sec)
B:
此时B窗口一直在查询表数据
C:
停止slave
A:
mysql> update syk.t set t=now() where a=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (10.01 sec) mysql> select * from syk.t; +---------------------+------+ | t | a | +---------------------+------+ | 2018-08-02 15:38:42 | 1 | +---------------------+------+ 1 row in set (0.00 sec)
A窗口的commit同样延迟了10秒才提交成功。受rpl_semi_sync_master_timeout参数影响，默认100000毫秒，即10秒。
B：
2018-08-02 15:38:41 2018-08-02 15:21:51 1 2018-08-02 15:38:42 2018-08-02 15:21:51 1 2018-08-02 15:38:43 2018-08-02 15:38:42 1 <========= 2018-08-02 15:38:44 2018-08-02 15:38:42 1 2018-08-02 15:38:45 2018-08-02 15:38:42 1 2018-08-02 15:38:46 2018-08-02 15:38:42 1 2018-08-02 15:38:47 2018-08-02 15:38:42 1 2018-08-02 15:38:48 2018-08-02 15:38:42 1 2018-08-02 15:38:49 2018-08-02 15:38:42 1 2018-08-02 15:38:50 2018-08-02 15:38:42 1 2018-08-02 15:38:51 2018-08-02 15:38:42 1 2018-08-02 15:38:52 2018-08-02 15:38:42 1 2018-08-02 15:38:53 2018-08-02 15:38:42 1 2018-08-02 15:38:54 2018-08-02 15:38:42 1 2018-08-02 15:38:55 2018-08-02 15:38:42 1 2018-08-02 15:38:56 2018-08-02 15:38:42 1
虽然A的提交延迟了10秒，但其他会话已经看到已经提交的数据。
总结：
实验的对比，可以发现与源码中的描述是一致的。
AFTER_SYNC意味着半同步复制，在binary log被flush之后，在存储引擎commit前进入等待，这可以保证数据在被复制到从库前不被其他会话可见；
AFTER_COMMIT意味着半同步复制在存储引擎commit之后进入等待，尽管发起commit的会话还未收到commit成功的提示，其他的会话已经可以看到commit后的数据。
rpl_semi_sync_master_wait_point，该参数也正如其字面意思，master在哪个点开始等待。
