MySQL技术论坛大会笔记

2017年7月27，我参加MySQL技术论坛大会，我见到了姜成尧、吴炳锡等MySQL知名人士。

以下是我在该大会上学到的知识，整理成笔记，以供参考。

1 MySQL 5.7发型版的特性

2 MySQL 5.7和其他版本读写性能比较

3 MySQL 8.0新特性

4 MySQL 复制技术

上图中的lossless replication是无损复制的意思，无损复制会和半同步一样在出问题时会切换为异步复制。

基于SQL语句的复制(statement-based replication, SBR)；

基于行的复制(row-based replication, RBR)；

混合模式复制(mixed-based replication, MBR)。

MySQL 5.6提供了两个参数来控制binlog group commit：

binlog_max_flush_queue_time

单位为微妙，用于从flush队列中取事务的超时时间，这主要是防止并发事务过高，导致某些事务的RT上升。

可以阅读函数MYSQL_BIN_LOG::process_flush_stage_queue 来理解其功能

binlog_order_commits

当设置为0时，事务可能以和binlog不相同的顺序被提交，从下面的测试也可以看出，这会稍微提升点性能，但并不是特别明显.

relay_log_recovery：从MySQL5.5.X版本开始，增加了relay_log_recovery参数，这个参数的作用是：当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。

4.1 半同步复制：

在半同步中，至少有一个Slave节点收到binlog后再返回，不能完全避免数据丢失，超时后，切回异步复制。在事物提交的过程中，在InnoDB层的 commit log 阶段后，Master 节点需要收到至少一个Slave节点回复的ACK后，才能继续下一个事物。

4.2 无损复制（lossless replication）：

在无损复制中，master把binlog发送给slave，只有在slave把binlog写到本地的relay-log里，master才会将事务提交到存储引擎层，然后把请求返回给客户端，客户端才可以看见刚才提交的事务。在一个事物提交的过程中，在MySQL Server 层的 binlog阶段后，Master节点需要收到至少一个Slave节点回复的ACK后，才能继续下一个事务。

半同步复制与无损复制的对比

1）ACK的时间点不同

半同步复制在InnoDB层的Commit Log后，等待ACK。

无损复制在MySQL Server层的Write binlog后，等待ACK。

2）主从数据一致性的不同

半同步复制意味着在Master节点上，这个刚刚提交的事物对数据库的修改，对其他事物是可见的。

无损复制在write binlog完成后，就传输binlog，但还没有去写commit log，意味着当前这个事物对数据库的修改，其他事物也是不可见的。

4.3 Group commit

组提交(group commit)是MYSQL处理日志的一种优化方式，主要为了解决写日志时频繁刷磁盘的问题。组提交伴随着MYSQL的发展不断优化，从最初只支持redo log 组提交，到目前5.6官方版本同时支持redo log 和binlog组提交。组提交的实现大大提高了mysql的事务处理性能。

4.4 基于事务的并行复制

众所周知，MySQL的复制延迟是一直被诟病的问题之一，然而在Inside君之前的两篇博客中（1，2）中都已经提到了mysql 5.7版本已经支持“真正”的并行复制功能，官方称为为enhanced multi-threaded slave（简称MTS），因此复制延迟问题已经得到了极大的改进，甚至在Inside君所在的网易电商应用中已经完全消除了之前延迟长达几小时的问题。然而，Inside君发现还是有很部分小伙伴不了解这个足以载入史册的“伟大”的特性，故作分享。总之， 5.7版本后，复制延迟问题永不存在 。

并行复制分为基于库级别的并行复制和基于事务级别的并行复制。

4.4.1 MySQL 5.6并行复制架构

诚然，MySQL 5.6版本也支持所谓的并行复制，但是其并行只是基于schema的，也就是基于库的。如果用户的MySQL数据库实例中存在多个schema，对于从机复制的速度的确可以有比较大的帮助。MySQL 5.6并行复制的架构如下所示：

在上图的红色框框部分就是实现并行复制的关键所在。在MySQL 5.6版本之前，Slave服务器上有两个线程I/O线程和SQL线程。I/O线程负责接收二进制日志（更准确的说是二进制日志的event），SQL线程进行回放二进制日志。如果在MySQL 5.6版本开启并行复制功能，那么SQL线程就变为了coordinator线程，coordinator线程主要负责以前两部分的内容：

若判断可以并行执行，那么选择worker线程执行事务的二进制日志
若判断不可以并行执行，如该操作是DDL，亦或者是事务跨schema操作，则等待所有的worker线程执行完成之后，再执行当前的日志

这意味着 coordinator线程并不是仅将日志发送给worker线程，自己也可以回放日志，但是所有可以并行的操作交付由worker线程完成。coordinator线程与worker是典型的生产者与消费者模型。

（疑问：这里是在判断不可以并行执行时，在等待所有的worker线程执行完成后，是由coordinator执行还是由worker进程非并行进行。从上面两句话中看不出coordinator进程回放日志。）

上述机制实现了基于schema的并行复制存在两个问题，首先是crash safe功能不好做，因为可能之后执行的事务由于并行复制的关系先完成执行，那么当发生crash的时候，这部分的处理逻辑是比较复杂的。从代码上看，5.6这里引入了Low-Water-Mark标记来解决该问题，从设计上看（ WL#5569 ），其是希望借助于日志的幂等性来解决该问题，不过5.6的二进制日志回放还不能实现幂等性。另一个最为关键的问题是这样设计的并行复制效果并不高，如果用户实例仅有一个库，那么就无法实现并行回放，甚至性能会比原来的单线程更差。而 单库多表是比多库多表更为常见的一种情形 。

4.4.2 MySQL 5.7并行复制原理

MySQL 5.7基于组提交的并行复制

MySQL 5.7才可称为真正的并行复制，这其中最为主要的原因就是slave服务器的回放与主机是一致的即master服务器上是怎么并行执行的slave上就怎样进行并行回放。不再有库的并行复制限制，对于二进制日志格式也无特殊的要求（基于库的并行复制也没有要求）。

从MySQL官方来看，其并行复制的原本计划是支持表级的并行复制和行级的并行复制，行级的并行复制通过解析ROW格式的二进制日志的方式来完成， WL#4648 。但是最终出现给小伙伴的确是在开发计划中称为：MTS: Prepared transactions slave parallel applier，可见： WL#6314 。该并行复制的思想最早是由MariaDB的Kristain提出，并已在MariaDB 10中出现，相信很多选择MariaDB的小伙伴最为看重的功能之一就是并行复制。

MySQL 5.7并行复制的思想简单易懂，一言以蔽之： 一个组提交的事务都是可以并行回放 ，因为这些事务都已进入到事务的prepare阶段，则说明事务之间没有任何冲突（否则就不可能提交）。

为了兼容MySQL 5.6基于库的并行复制，5.7引入了新的变量slave-parallel-type，其可以配置的值有：

DATABASE：默认值，基于库的并行复制方式
LOGICAL_CLOCK：基于组提交的并行复制方式

支持并行复制的GTID

如何知道事务是否在一组中，又是一个问题，因为原版的MySQL并没有提供这样的信息。在MySQL 5.7版本中，其设计方式是将组提交的信息存放在GTID中。那么如果用户没有开启GTID功能，即将参数gtid_mode设置为OFF呢？故MySQL 5.7又引入了称之为Anonymous_Gtid的二进制日志event类型，如：

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000006';

+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+

| mysql-bin.000006 | 4 | Format_desc | 88 | 123 | Server ver: 5.7.7-rc-debug-log, Binlog ver: 4 |

| mysql-bin.000006 | 123 | Previous_gtids | 88 | 194 | f11232f7-ff07-11e4-8fbb-00ff55e152c6:1-2 |

| mysql-bin.000006 | 194 | Anonymous_Gtid | 88 | 259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000006 | 259 | Query | 88 | 330 | BEGIN |

| mysql-bin.000006 | 330 | Table_map | 88 | 373 | table_id: 108 (aaa.t) |

| mysql-bin.000006 | 373 | Write_rows | 88 | 413 | table_id: 108 flags: STMT_END_F |

......

这意味着在 MySQL 5.7版本中即使不开启GTID，每个事务开始前也是会存在一个Anonymous_Gtid ，而这GTID中就存在着组提交的信息。

LOGICAL_CLOCK

然而，通过上述的SHOW BINLOG EVENTS，我们并没有发现有关组提交的任何信息。但是通过mysqlbinlog工具，用户就能发现组提交的内部信息：

root@localhost:~# mysqlbinlog mysql-bin.0000006 | grep last_committed

#150520 14:23:11 server id 88 end_log_pos 259 CRC32 0x4ead9ad6 GTID last_committed=0 sequence_number=1

#150520 14:23:11 server id 88 end_log_pos 1483 CRC32 0xdf94bc85 GTID last_committed=0 sequence_number=2

#150520 14:23:11 server id 88 end_log_pos 2708 CRC32 0x0914697b GTID last_committed=0 sequence_number=3

#150520 14:23:11 server id 88 end_log_pos 3934 CRC32 0xd9cb4a43 GTID last_committed=0 sequence_number=4

#150520 14:23:11 server id 88 end_log_pos 5159 CRC32 0x06a6f531 GTID last_committed=0 sequence_number=5

#150520 14:23:11 server id 88 end_log_pos 6386 CRC32 0xd6cae930 GTID last_committed=0 sequence_number=6

#150520 14:23:11 server id 88 end_log_pos 7610 CRC32 0xa1ea531c GTID last_committed=6 sequence_number=7

#150520 14:23:11 server id 88 end_log_pos 8834 CRC32 0x96864e6b GTID last_committed=6 sequence_number=8

#150520 14:23:11 server id 88 end_log_pos 10057 CRC32 0x2de1ae55 GTID last_committed=6 sequence_number=9

#150520 14:23:11 server id 88 end_log_pos 11280 CRC32 0x5eb13091 GTID last_committed=6 sequence_number=10

#150520 14:23:11 server id 88 end_log_pos 12504 CRC32 0x16721011 GTID last_committed=6 sequence_number=11

#150520 14:23:11 server id 88 end_log_pos 13727 CRC32 0xe2210ab6 GTID last_committed=6 sequence_number=12

#150520 14:23:11 server id 88 end_log_pos 14952 CRC32 0xf41181d3 GTID last_committed=12 sequence_number=13

...

可以发现较之原来的二进制日志内容多了last_committed和sequence_number，last_committed表示事务提交的时候，上次事务提交的编号，如果事务具有相同的last_committed，表示这些事务都在一组内，可以进行并行的回放。例如上述last_committed为0的事务有6个，表示组提交时提交了6个事务，而这6个事务在从机是可以进行并行回放的。

上述的last_committed和sequence_number代表的就是所谓的LOGICAL_CLOCK。先来看源码中对于LOGICAL_CLOCK的定义：

class Logical_clock

{

private:

int64 state;

/*

Offset is subtracted from the actual "absolute time" value at

logging a replication event. That is the event holds logical

timestamps in the "relative" format. They are meaningful only in

the context of the current binlog.

The member is updated (incremented) per binary log rotation.

*/

int64 offset;

......

state是一个自增的值，offset在每次二进制日志发生rotate时更新，记录发生rotate时的state值。其实state和offset记录的是全局的计数值，而存在二进制日志中的仅是当前文件的相对值。使用LOGICAL_CLOCK的场景如下：

class MYSQL_BIN_LOG: public TC_LOG

{

...

public:

/* Committed transactions timestamp */

Logical_clock max_committed_transaction;

/* "Prepared" transactions timestamp */

Logical_clock transaction_counter;

...

可以看到在类MYSQL_BIN_LOG中定义了两个Logical_clock的变量：

max_committed_transaction：记录上次组提交时的logical_clock，代表上述mysqlbinlog中的last_committed
transaction_counter：记录当前组提交中各事务的logcial_clock，代表上述mysqlbinlog中的sequence_number

4.4.3 并行复制测试

下图显示了开启MTS后，slave服务器的QPS。测试的工具是sysbench的单表全update测试，测试结果显示在16个线程下的性能最好，从机的QPS可以达到25000以上，进一步增加并行执行的线程至32并没有带来更高的提升。而原单线程回放的QPS仅在4000左右，可见MySQL 5.7 MTS带来的性能提升，而由于测试的是单表，所以MySQL 5.6的MTS机制则完全无能为力了。

4.4.4 并行复制配置与调优

master_info_repository

开启MTS功能后，务必将参数master_info_repostitory设置为TABLE，这样性能可以有50%~80%的提升。这是因为并行复制开启后对于元master.info这个文件的更新将会大幅提升，资源的竞争也会变大。在之前 InnoSQL 的版本中，添加了参数来控制刷新master.info这个文件的频率，甚至可以不刷新这个文件。因为刷新这个文件是没有必要的，即根据master-info.log这个文件恢复本身就是不可靠的。在MySQL 5.7中，Inside君推荐将master_info_repository设置为TABLE，来减小这部分的开销。

slave_parallel_workers

若将slave_parallel_workers设置为0，则MySQL 5.7退化为原单线程复制，但将slave_parallel_workers设置为1，则SQL线程功能转化为coordinator线程，但是只有1个worker线程进行回放，也是单线程复制。然而，这两种性能却又有一些的区别，因为多了一次coordinator线程的转发，因此slave_parallel_workers=1的性能反而比0还要差，在Inside君的测试下还有20%左右的性能下降，如下图所示：

这里其中引入了另一个问题，如果主机上的负载不大，那么组提交的效率就不高，很有可能发生每组提交的事务数量仅有1个，那么在从机的回放时，虽然开启了并行复制，但会出现性能反而比原先的单线程还要差的现象，即延迟反而增大了。聪明的小伙伴们，有想过对这个进行优化吗？

Enhanced Multi-Threaded Slave配置

说了这么多，要开启enhanced multi-threaded slave其实很简单，只需根据如下设置：

# slave

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=16

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

slave_preserve_commit_order=on

4.4.5 并行复制监控

复制的监控依旧可以通过SHOW SLAVE STATUS\G，但是MySQL 5.7在performance_schema架构下多了这些表，用户可以更细力度的进行监控：

mysql> show tables like 'replication%';

+---------------------------------------------+

| Tables_in_performance_schema (replication%) |

+---------------------------------------------+

| replication_applier_configuration |

| replication_applier_status |

| replication_applier_status_by_coordinator |

| replication_applier_status_by_worker |

| replication_connection_configuration |

| replication_connection_status |

| replication_group_member_stats |

| replication_group_members |

+---------------------------------------------+

8 rows in set (0.00 sec)

4.4.6 总结

MySQL 5.7推出的Enhanced Multi-Threaded Slave解决了困扰MySQL长达数十年的复制延迟问题，再次提醒一些无知的PostgreSQL用户，不要再停留在之前对于MySQL的印象，物理复制也不一定肯定比逻辑复制有优势，而MySQL 5.7的MTS已经完全可以解决延迟问题。anyway，和Inside君一起见证划时代MySQL 5.7 GA版本的降临吧.

注：

1、Coordinator thread on slave dispatches work across several worker threads。Each worker thread commits trx in isolation。

2、mysql 5.6的MTS是基于库级别的并行，当有多个数据库时，可以将slave_parallel_workers设置为数据库的数量，为了避免新建库后来回修改，也可以将该参数设置的大一些。设置为库级别的事务时，不允许这样做，会报错。

3、mysql 5.7 后的MTS可以实现更小粒度的并行复制，但需要将slave_parallel_type设置为LOGICAL_CLOCK,但仅仅设置为LOGICAL_CLOCK也会存在问题，因为此时在slave上应用事务的顺序是无序的，和relay log中记录的事务顺序不一样，这样数据一致性是无法保证的，为了保证事务是按照relay log中记录的顺序来回放，就需要开启参数slave_preserve_commit_order。开启该参数后，the executing thread waits until all previous transactions are committed before committing. While the slave thread is waiting for other workers to commit their transactions it reports its status as Waiting for preceding transaction to commit.

所以虽然mysql5.7添加MTS后，虽然slave可以并行应用relay log，但commit部分仍然是顺序提交，其中可能会有等待的情况。

当开启slave_preserve_commit_order参数后，slave_parallel_type只能是LOGICAL_CLOCK，如果你有使用级联复制，那LOGICAL_CLOCK可能会使离master越远的slave并行性越差。

Regardless of the value of this variable, there is no special configuration required on the master. When slave_preserve_commit_order=1, you can only use LOGICAL_CLOCK. If your replication topology uses multiple levels of slaves, LOGICAL_CLOCK may achieve less parallelization for each level the slave is away from the master.

4、5.7中会在binlog中额外加入元数据，来划分那些事务可以并行执行。

Additional metadata is stored in the binlogs to identify transactions that can be applied in parallel

而slave则通过 coordinator 线程从relay log中抽取元数据，进而分发给worker线程并行执行的事务组。

The coordinator thread is able to extract the metadata from the relay logs to dispatch the transactions across workers

5、SHOW SLAVE STATUS适用于single sql thread，但在使用了MTS后，如果多个线程出现问题时，会不方便查看。在5.7的performance_schema数据库中，新加了一个监控replication的部分表：

mysql> show tables like 'replication%';

+---------------------------------------------+

| Tables_in_performance_schema (replication%) |

+---------------------------------------------+

| replication_applier_configuration |

| replication_applier_status |

| replication_applier_status_by_coordinator |

| replication_applier_status_by_worker |

| replication_connection_configuration |

| replication_connection_status |

| replication_group_member_stats |

| replication_group_members |

+---------------------------------------------+

8 rows in set (0.00 sec)

通过replication_applier_status_by_worker可以看到worker进程的工作情况：

mysql> mysql> select * from replication_applier_status_by_worker;

+--------------+-----------+-----------+---------------+--------------------------------------------+-------------------+--------------------+----------------------+

+--------------+-----------+-----------+---------------+--------------------------------------------+-------------------+--------------------+----------------------+

| | 1 | 32 | ON | 0d8513d8-00a4-11e6-a510-f4ce46861268:96604 | 0 | | 0000-00-00 00:00:00 |

| | 2 | 33 | ON | 0d8513d8-00a4-11e6-a510-f4ce46861268:97760 | 0 | | 0000-00-00 00:00:00 |

+--------------+-----------+-----------+---------------+--------------------------------------------+-------------------+--------------------+----------------------+

2 rows in set (0.00 sec)

其他各表的使用参考官方文档。

6、group commit：

This optimization reduces the number of operations needed to produce the binary logs by grouping transactions. When transactions are committing at the same time, they are written to the binary log in a single operation. But if transactions commit at the same time, then they are not sharing any locks, which means they are not conflicting thus can be executed in parallel on slaves. So by adding group commit information in the binary logs on the master, the slaves can safely run transactions in parallel.

7、涉及到组提交的一个延时控制参数：

binlog_group_commit_sync_delay

Controls how many microseconds the binary log commit waits before synchronizing the binary log file to disk. By default binlog-group-commit-sync-delay is set to 0, meaning that there is no delay. Setting binlog-group-commit-sync-delay to a microsecond delay enables more transactions to be synchronized together to disk at once, reducing the overall time to commit a group of transactions because the larger groups require fewer time units per group. With the correct tuning, this can increase slave performance without compromising the master's throughput.

binlog_group_commit_sync_delay

全局动态变量，单位微妙，默认0，范围：0～1000000（1秒）

表示binlog提交后等待延迟多少时间再同步到磁盘，默认0，不延迟。设置延迟可以让多个事务在用一时刻提交，提高binlog组提交的并发数和效率，提高slave的吞吐量。

8、讨论技术就讨论技术，没必要踩低别人来抬高自己，而且不了解pg就妄论pg是可笑的。类比Linus那句话：“Talk is cheap，show your test.”

参考：

http://mp.weixin.qq.com/s?__biz=MjM5MjIxNDA4NA==&mid=205236417&idx=1&sn=15281c834348911cea106478aa819175&scene=23&srcid=0525zwrE6gRYCIPgKxoq40iN#rd

http://blog.booking.com/better_parallel_replication_for_mysql.html

https://www.percona.com/resources/technical-presentations/multi-threaded-replication-mysql-56-and-57-percona-technical-mysql

https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#option_mysqld_slave-parallel-type

https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#sysvar_slave_preserve_commit_order

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_group_commit_sync_delay

4.5 group replication

5 Mysql router

MySQL Router是mysql官方推出的一个轻量级MySQL中间件是处于应用client和dbserver之间的轻量级代理程序，它能检测，分析和转发查询到后端数据库实例，并把结果返回给client。是mysql-proxy的一个替代品.但是和mysql-proxy有区别。mysqlroute使用了两个端口进行读写分离

默认端口号 作用说明

7001 默认的是读写端口 mode = read-write默认情况下第一台主数据库为写主库，当第一台主数据库DOWN机后，第二台数据库被提升为主库,如果第一台不出问题，第二台是不会被使用的。可以作高可用使用

7002 默认是读端口如果设置以后，根据设置的列表进行轮询使用

MySQL Router接受前端应用程序请求后，根据不同的端口来区分读写，把连接读写端口的所有写和查询发往主库，把连接只读端口的查询以轮询方式发往多个从库，从而实现读写分离的目的。读写返回的结果会交给MySQL Router,由MySQL Router返回给客户端的应用程序。

我理解的是mysql-proxy在使用的时候与连接单台数据库操作一样，而mysql route则是把多台主机的读写集中在一起，进行读写分离，在使用的时候还是需要程序进行改动。具体性能测试，在接下的文章中会慢慢测试

ITPub博客

MySQL技术论坛大会笔记

1 MySQL 5.7发型版的特性

2 MySQL 5.7和其他版本读写性能比较

3 MySQL 8.0新特性

4 MySQL 复制技术

4.1 半同步复制：

4.2 无损复制（lossless replication）：

4.3 Group commit

4.4 基于事务的并行复制

4.4.1 MySQL 5.6并行复制架构

4.4.2 MySQL 5.7并行复制原理

4.4.3 并行复制测试

4.4.4 并行复制配置与调优

4.4.5 并行复制监控

4.4.6 总结

4.5 group replication

5 Mysql router

6 唯一索引产生的死锁

7 金融级要求的数据库特性

8 分布式应用架构VS传统IT架构

最新文章