关于mysql:ClickHouse和他的朋友们9MySQL实时复制与实现

本文转自我司大神 BohuTANG的博客。

很多人看到题目还认为本人走错了早场，其实没有。

ClickHouse 能够挂载为 MySQL 的一个从库，先全量再增量的实时同步 MySQL 数据，这个性能能够说是往年最亮眼、最刚需的性能，基于它咱们能够轻松的打造一套企业级解决方案，让 OLTP 和 OLAP 的交融从此不再头疼。

目前反对 MySQL 5.6/5.7/8.0 版本，兼容 Delete/Update 语句，及大部分罕用的 DDL 操作。
代码曾经合并到 upstream master 分支，预计在20.8版本作为experimental 性能公布。

毕竟是两个异构生态的交融，依然有不少的工作要做，同时也期待着社区用户的反馈，以减速迭代。

代码获取

获取 clickhouse/master 代码编译即可，办法见 ClickHouse和他的敌人们（1）编译、开发、测试…

MySQL Master

咱们须要一个开启 binlog 的 MySQL 作为 master:

docker run -d -e MYSQL_ROOT_PASSWORD=123 mysql:5.7 mysqld --datadir=/var/lib/mysql --server-id=1 --log-bin=/var/lib/mysql/mysql-bin.log --gtid-mode=ON --enforce-gtid-consistency

创立数据库和表，并写入数据:

mysql> create database ckdb;mysql> use ckdb;mysql> create table t1(a int not null primary key, b int);mysql> insert into t1 values(1,1),(2,2);mysql> select * from t1;+---+------+| a | b    |+---+------+| 1 |    1 || 2 |    2 |+---+------+2 rows in set (0.00 sec)

ClickHouse Slave

目前以 database 为单位进行复制，不同的 database 能够来自不同的 MySQL master，这样就能够实现多个 MySQL 源数据同步到一个 ClickHouse 做 OLAP 剖析性能。

首先开启体验开关:

clickhouse :) SET allow_experimental_database_materialize_mysql=1;

创立一个复制通道：

clickhouse :) CREATE DATABASE ckdb ENGINE = MaterializeMySQL('172.17.0.2:3306', 'ckdb', 'root', '123');clickhouse :) use ckdb;clickhouse :) show tables;┌─name─┐│ t1   │└──────┘clickhouse :) select * from t1;┌─a─┬─b─┐│ 1 │ 1 │└───┴───┘┌─a─┬─b─┐│ 2 │ 2 │└───┴───┘2 rows in set. Elapsed: 0.017 sec.

看下 ClickHouse 的同步位点：
cat ckdatas/metadata/ckdb/.metadata

Version:    1Binlog File:    mysql-bin.000001Binlog Position:    913Data Version:    0

Delete

首先在 MySQL Master 上执行一个删除操作：

mysql> delete from t1 where a=1;Query OK, 1 row affected (0.01 sec)

而后在 ClickHouse Slave 侧查看记录：

clickhouse :) select * from t1;SELECT *FROM t1┌─a─┬─b─┐│ 2 │ 2 │└───┴───┘1 rows in set. Elapsed: 0.032 sec.

此时的 metadata 里 Data Version 曾经递增到 2:

cat ckdatas/metadata/ckdb/.metadata Version:    1Binlog File:    mysql-bin.000001Binlog Position:    1171Data Version:    2

Update

MySQL Master:

mysql> select * from t1;+---+------+| a | b    |+---+------+| 2 |    2 |+---+------+1 row in set (0.00 sec)mysql> update t1 set b=b+1;mysql> select * from t1;+---+------+| a | b    |+---+------+| 2 |    3 |+---+------+1 row in set (0.00 sec)

ClickHouse Slave:

clickhouse :) select * from t1;SELECT *FROM t1┌─a─┬─b─┐│ 2 │ 3 │└───┴───┘1 rows in set. Elapsed: 0.023 sec.

性能测试

测试环境

MySQL          8C16G 云主机, 192.168.0.3，根底数据 10188183 条记录ClickHouse     8C16G 云主机, 192.168.0.4benchyou       8C8G  云主机,  192.168.0.5, 256并发写, https://github.com/xelabs/benchyou

性能测试跟硬件环境有较大关系，这里应用的是云主机模式，数据供参考。

全量性能

8c16G-vm :) create database sbtest engine=MaterializeMySQL('192.168.0.3:3306', 'sbtest', 'test', '123');8c16G-vm :) watch lv1;WATCH lv1┌─count()─┬───────────────now()─┬─_version─┐│       0 │ 2020-07-29 06:36:04 │        1 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│ 1113585 │ 2020-07-29 06:36:05 │        2 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│ 2227170 │ 2020-07-29 06:36:07 │        3 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│ 3340755 │ 2020-07-29 06:36:10 │        4 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│ 4454340 │ 2020-07-29 06:36:13 │        5 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│ 5567925 │ 2020-07-29 06:36:16 │        6 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│ 6681510 │ 2020-07-29 06:36:18 │        7 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│ 7795095 │ 2020-07-29 06:36:22 │        8 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│ 8908680 │ 2020-07-29 06:36:25 │        9 │└─────────┴─────────────────────┴──────────┘┌──count()─┬───────────────now()─┬─_version─┐│ 10022265 │ 2020-07-29 06:36:28 │       10 │└──────────┴─────────────────────┴──────────┘┌──count()─┬───────────────now()─┬─_version─┐│ 10188183 │ 2020-07-29 06:36:28 │       11 │└──────────┴─────────────────────┴──────────┘← Progress: 11.00 rows, 220.00 B (0.16 rows/s., 3.17 B/s.)

在这个硬件环境下，全量同步性能大略是 424507/s，42w 事务每秒。
因为全量的数据之间没有依赖关系，能够进一步优化成并行，减速同步。
全量的性能间接决定 ClickHouse slave 坏掉后重建的速度，如果你的 MySQL 有 10 亿条数据，大略 40 分钟就能够重建实现。

增量性能(实时同步)

在以后配置下，ClickHouse slave 单线程回放生产能力大于 MySQL master 256 并发下生产能力，通过测试能够看到它们放弃实时同步。

benchyou 压测数据，2.1w 事务/秒(MySQL 在以后环境下TPS上不去):

./bin/benchyou --mysql-host=192.168.0.3 --mysql-user=test --mysql-password=123 --oltp-tables-count=1 --write-threads=256 --read-threads=0time            thds               tps     wtps    rtps[13s]        [r:0,w:256,u:0,d:0]  19962    19962   0    time            thds               tps     wtps    rtps[14s]        [r:0,w:256,u:0,d:0]  20415    20415   0 time            thds               tps     wtps    rtps[15s]        [r:0,w:256,u:0,d:0]  21131    21131   0time            thds               tps     wtps    rtps[16s]        [r:0,w:256,u:0,d:0]  21606    21606   0time            thds               tps     wtps    rtps[17s]        [r:0,w:256,u:0,d:0]  22505    22505   0

ClickHouse 侧单线程回放能力，2.1w 事务/秒，实时同步：

┌─count()─┬───────────────now()─┬─_version─┐│  150732 │ 2020-07-30 05:17:15 │       17 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│  155477 │ 2020-07-30 05:17:16 │       18 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│  160222 │ 2020-07-30 05:17:16 │       19 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│  164967 │ 2020-07-30 05:17:16 │       20 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│  169712 │ 2020-07-30 05:17:16 │       21 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│  174457 │ 2020-07-30 05:17:16 │       22 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│  179202 │ 2020-07-30 05:17:17 │       23 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│  183947 │ 2020-07-30 05:17:17 │       24 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│  188692 │ 2020-07-30 05:17:17 │       25 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│  193437 │ 2020-07-30 05:17:17 │       26 │└─────────┴─────────────────────┴──────────┘┌─count()─┬───────────────now()─┬─_version─┐│  198182 │ 2020-07-30 05:17:17 │       27 │└─────────┴─────────────────────┴──────────┘

实现机制

在探讨机制之前，首先须要理解下 MySQL 的 binlog event ，次要有以下几种类型：

1. MYSQL_QUERY_EVENT　　　　-- DDL2. MYSQL_WRITE_ROWS_EVENT　-- insert数据3. MYSQL_UPDATE_ROWS_EVENT -- update数据4. MYSQL_DELETE_ROWS_EVENT -- delete数据

当一个事务提交后，MySQL 会把执行的 SQL 解决成相应的 binlog event，并长久化到 binlog 文件。

binlog 是 MySQL 对外输入的重要途径，只有你实现 MySQL Replication Protocol，就能够流式的生产MySQL 生产的 binlog event，具体协定见 Replication Protocol。

因为历史起因，协定繁琐而诡异，这不是本文重点。

对于 ClickHouse 生产 MySQL binlog 来说，次要有以下３个难点：

DDL 兼容
Delete/Update 反对
Query 过滤

DDL

DDL 兼容破费了大量的代码去实现。

首先，咱们看看 MySQL 的表复制到 ClickHouse 后会变成什么样子。

MySQL master:

mysql> show create table t1\G;*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `a` int(11) NOT NULL,  `b` int(11) DEFAULT NULL,  PRIMARY KEY (`a`)) ENGINE=InnoDB DEFAULT CHARSET=latin1

ClickHouse slave:

ATTACH TABLE t1(    `a` Int32,    `b` Nullable(Int32),    `_sign` Int8,    `_version` UInt64)ENGINE = ReplacingMergeTree(_version)PARTITION BY intDiv(a, 4294967)ORDER BY tuple(a)SETTINGS index_granularity = 8192

能够看到：

默认减少了 2 个暗藏字段：_sign(-1删除, 1写入) 和 _version(数据版本)
引擎转换成了 ReplacingMergeTree，以 _version 作为 column version
原主键字段 a 作为排序和分区键

这只是一个表的复制，其余还有十分多的DDL解决，比方减少列、索引等，感兴趣能够观摩 Parsers/MySQL 下代码。

Update和Delete

当咱们在 MySQL master 执行：

mysql> delete from t1 where a=1;mysql> update t1 set b=b+1;

ClickHouse t1数据（把 _sign 和 _version 一并查问）：

clickhouse :) select a,b,_sign, _version from t1;SELECT     a,    b,    _sign,    _versionFROM t1┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │     1 │        1 ││ 2 │ 2 │     1 │        1 │└───┴───┴───────┴──────────┘┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │    -1 │        2 │└───┴───┴───────┴──────────┘┌─a─┬─b─┬─_sign─┬─_version─┐│ 2 │ 3 │     1 │        3 │└───┴───┴───────┴──────────┘

依据返回后果，能够看到是由 3 个 part 组成。

part1 由 mysql> insert into t1 values(1,1),(2,2) 生成：

┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │     1 │        1 ││ 2 │ 2 │     1 │        1 │└───┴───┴───────┴──────────┘

part2 由 mysql> delete from t1 where a=1 生成：

┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │    -1 │        2 │└───┴───┴───────┴──────────┘阐明：_sign = -1表明处于删除状态

part3 由 update t1 set b=b+1 生成：

┌─a─┬─b─┬─_sign─┬─_version─┐│ 2 │ 3 │     1 │        3 │└───┴───┴───────┴──────────┘

应用 final 查问：

clickhouse :) select a,b,_sign,_version from t1 final;SELECT     a,    b,    _sign,    _versionFROM t1FINAL┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │    -1 │        2 │└───┴───┴───────┴──────────┘┌─a─┬─b─┬─_sign─┬─_version─┐│ 2 │ 3 │     1 │        3 │└───┴───┴───────┴──────────┘2 rows in set. Elapsed: 0.016 sec.

能够看到 ReplacingMergeTree 曾经依据 _version 和 OrderBy 对记录进行去重。

Query

MySQL master:

mysql> select * from t1;+---+------+| a | b    |+---+------+| 2 |    3 |+---+------+1 row in set (0.00 sec)

ClickHouse slave:

clickhouse :) select * from t1;SELECT *FROM t1┌─a─┬─b─┐│ 2 │ 3 │└───┴───┘clickhouse :) select *,_sign,_version from t1;SELECT     *,    _sign,    _versionFROM t1┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │    -1 │        2 ││ 2 │ 3 │     1 │        3 │└───┴───┴───────┴──────────┘阐明：这里还有一条删除记录，_sign为-1

MaterializeMySQL 被定义成一种存储引擎，所以在读取的时候，会依据 _sign 状态进行判断，如果是-1则是曾经删除，进行过滤。

并行回放

为什么 MySQL 须要并行回放？

假如 MySQL master 有 1024 个并发同时写入、更新数据，霎时产生大量的 binlog event ，MySQL slave 上只有一个线程一个 event 接着一个 event 式回放，于是 MySQL 实现了并行回放性能！

那么，MySQL slave 回放时是否齐全(或靠近)模拟出 master 过后的 1024 并发行为呢？

要想并行首先要解决的就是依赖问题：咱们须要 master 标记出哪些 event 能够并行，哪些 event 有先后关系，因为它是第一现场。

MySQL 通过在 binlog 里减少:

last_committed，雷同则能够并行
sequece_number，较小先执行，形容先后依赖

last_committed=3   sequece_number=4   -- event1last_committed=4   sequece_number=5   -- event2last_committed=4   sequece_number=6   -- event3last_committed=5   sequece_number=7   -- event4

event2 和 event3 则能够并行，event4 须要期待后面 event 实现才能够回放。
以上只是一个大体原理，目前 MySQL 有３种并行模式能够抉择：

基于 database 并行
基于 group commit 并行
基于主键不抵触的 write set 并行

最大水平上让 MySQL slave减速回放，整套机制还是异样简单的。

回到 ClickHouse slave 问题，咱们采纳的单线程回放，提早曾经不是次要问题，这是由它们的机制决定的：
MySQL slave 回放时，须要把 binlog event 转换成 SQL，而后模仿 master 的写入，这种逻辑复制是导致性能低下的最重要起因。
而 ClickHouse 在回放上，间接把 binlog event 转换成底层 block 构造，而后间接写入底层的存储引擎，靠近于物理复制，能够了解为把 binlog event 间接回放到 InnoDB 的 page。

读取最新

尽管 ClickHouse slave 回放十分快，靠近于实时，如何在ClickHouse slave上总是读取到最新的数据呢？

其实非常简单，借助 MySQL binlog GTID 个性，每次读的时候，咱们跟ｍaster 做一次 executed_gtid 同步，而后期待这些 executed_gtid 回放结束即可。

数据一致性

对一致性要求较高的场景，咱们怎么验证 MySQL master 的数据和 ClickHouse slave 的数据一致性呢？

这块初步想法是提供一个兼容 MySQL checksum 算法的函数，咱们只需比照两边的 checksum 值即可。

总结

ClickHouse 实时复制同步 MySQL 数据是 upstream 2020 的一个 roadmap，在整体构架上比拟有挑战始终无人接单，挑战次要来自两方面：

对 MySQL 复制通道与协定十分相熟
对 ClickHouse 整体机制十分相熟

这样，在两个原本有点边远的山头两头架起了一座高速，这条 10851号高速由 zhang1024(ClickHouse侧) 和 BohuTANG(MySQL复制) 两个修路工联结承建，目前曾经合并到 upstream 分支。

对于同步 MySQL 的数据，目前大家的计划根本都是在两头安置一个 binlog 生产工具，这个工具对 event 进行解析，而后再转换成 ClickHouse 的 SQL 语句，写到 ClickHouse server，链路较长，性能损耗较大。

10851号高速是在 ClickHouse 外部实现一套 binlog 生产计划，而后依据 event 解析成 ClickHouse 外部的 block 构造，再间接回写到底层存储引擎，简直是最高效的一种实现形式，实现与 MySQL 实时同步的能力，让剖析更靠近事实。

基于 database 级的复制，实现了多源复制的性能，如果复制通道坏掉，咱们只需在 ClickHouse 侧删掉 database 再重建一次即可，十分疾速、不便，OLTP+OLAP 就是这么简略！

要想富，先修路！

专栏：

欢送关注我的微信公众号【MySQL数据库技术】。