文档章节

Bullet:MySQL增强半同步参数rpl_semi_sync_master_wait_point值AFTER_SYNC和AFTER_COMMIT的对比实验

o
 osc_pn11u1x9
发布于 2018/08/06 11:10
字数 1643
阅读 5
收藏 0

精选30+云产品,助力企业轻松上云!>>>

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在哪个点开始等待。

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。

暂无文章

Saga分布式事务框架

1优点 1、避免服务之间的循环依赖,因为saga协调器会调用saga参与者,但参与者不会调用协调器 2、集中分布式事务编排 3、降低参与者的复杂性 4、回滚更容易管理 Saga模式的一大优势是它支持长...

战略板儿砖
35分钟前
11
0
为什么要使用static_cast (x)而不是(int)x? - Why use static_cast(x) instead of (int)x?

问题: I've heard that the static_cast function should be preferred to C-style or simple function-style casting. 我听说static_cast函数应该比C样式或简单的函数样式转换更可取。 Is......

fyin1314
37分钟前
16
0
最难的几道Java面试题,看看你跪在第几个?

这是我收集的10个最棘手的Java面试问题列表。这些问题主要来自 Java 核心部分 ,不涉及 Java EE 相关问题。你可能知道这些棘手的 Java 问题的答案,或者觉得这些不足以挑战你的 Java 知识,但...

码农突围
38分钟前
13
0
浅谈Spring核心技术 IOC与AOP

IOC: IOC(Inversion Of Controll,控制反转)是一种设计思想,将原本在程序中手动创建对象的控制权,交由给Spring框架来管理。IOC容器是Spring用来实现IOC的载体,IOC容器实际上就是一个M...

创业789
39分钟前
13
0
智能金融丨神州信息助某省联社实现一体化智能运维建设

近日,由神州信息实施的某省联社“IT服务管理平台项目”顺利通过验收,并获得客户的高度认可。该项目是神州信息在农信领域打造的又一标杆项目,为客户实现了IT运维流程标准化及运维系统一体化...

脉脉小达人
41分钟前
20
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部