技术分享 | mysql与StoneDB的GTID主从复制集群搭建

测试背景

从MySQL5.6开始增加了强大的GTID（Global Transaction ID，全局事务ID）这个特性，用来强化数据库的主备一致性， 故障恢复， 以及容错能力。用于取代过去传统的主从复制（即：基于binlog和position的异步复制）。借助GTID，在发生主备切换的情况下，MySQL的其他slave可以自动在新主上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制position发生误操作的风险。另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。

GTID(Global Transaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识，保存在mysql数据目录下的auto.cnf文件里。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。下面是一个GTID的具体形式：3E11FA47-71CA-11E1-9E33-C80AA9429562:23。

GTID是由server_uuid和事务id组成的，即GTID=server_uuid:transaction_id。 server_uuid，是在MySQL第一次启动时自动生成并持久化到auto.cnf文件（存放在数据目录下，每台机器的server_uuid都不一样。

实验环境

IP	软件版本	作用	引擎
192.168.178.129	5.7.34-MySQL Community	主机	innodb
192.168.178.130	5.7.36-StoneDB-v1.0.4	从机	TIANMU

主机配置

[my.cnf]配置文件里面加入以入4个参数
log_bin=mysql-bin
server-id=1
gtid_mode = on
enforce_gtid_consistency = 1

重启mysql


mysql> CREATE USER 'stonedb'@'192.168.178.130' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'stonedb'@'192.168.178.130';


mysql> create  database  mysql2stone;
Query OK, 1 row affected (0.17 sec)

mysql> use mysql2stone;
Database changed
mysql> create table  mystone(name varchar(50));
Query OK, 0 rows affected (1.16 sec)

从机配置

/opt/stonedb57/install/my.cnf配置文件里面加入以入3个参数

server-id = 2
#开启GTID模式
gtid_mode = on
enforce_gtid_consistency = 1


基于从机对接上主机的配置信息
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.178.129',
MASTER_PORT=3306,
MASTER_USER='stonedb',
MASTER_PASSWORD='P@ssw0rd',
MASTER_AUTO_POSITION = 1575;


启动从机线程
mysql> start slave;

检验主从复制配置状态

主要看关键的两个线程有没有在干活

• Slave_IO_State：从库当前的状态。
• Slave_IO_Running：从库的I/O线程是否运行，通常情况为Yes.
• Slave_SOL_Running：从库的SQL线程是否运行，通常情况为Yes.
• Last_IO_Error,Last_SQL_Error：最近I/O线程和SQL线程的错误，通常情况下应该为空，代表复制正常运行。
• Seconds_Behind_Master：从库落后主库的秒数

mysql> select * from performance_schema.replication_connection_status\G;
*************************** 1. row ***************************
             CHANNEL_NAME:
               GROUP_NAME:
              SOURCE_UUID: 35ba7ca7-4b1e-11ee-92b9-000c29f88f2f
                THREAD_ID: 37
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 74
 LAST_HEARTBEAT_TIMESTAMP: 2023-09-04 09:57:21
 RECEIVED_TRANSACTION_SET: 35ba7ca7-4b1e-11ee-92b9-000c29f88f2f:1-4
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE:
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.01 sec)

mysql> select * from performance_schema.replication_applier_status_by_worker\G;
*************************** 1. row ***************************
         CHANNEL_NAME:
            WORKER_ID: 0
            THREAD_ID: 38
        SERVICE_STATE: ON
LAST_SEEN_TRANSACTION: 35ba7ca7-4b1e-11ee-92b9-000c29f88f2f:4
    LAST_ERROR_NUMBER: 0
   LAST_ERROR_MESSAGE:
 LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | a1e317a1-447d-11ee-8c3a-000c29aa4114 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.01 sec)

测试用例insert

通过insert的方式持续不断往主机上面插入数据，统计一共插入40010010条数据。过程中，同量的数据落地到stoneDB，通过group by统计查询对比两个数据库的性能。

mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql> select name,count(*)  from mystone group by name;
+----------+----------+
| name     | count(*) |
+----------+----------+
| hello1   | 35380844 |
| 'hello1' |  4629166 |
+----------+----------+
2 rows in set (6.63 sec)

mysql>  show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.051737 | 0.000000 |   0.000119 |            0 |             0 |
| checking permissions | 0.000018 | 0.000000 |   0.000014 |            0 |             0 |
| Opening tables       | 0.000038 | 0.000000 |   0.000039 |            0 |             0 |
| System lock          | 0.000035 | 0.000000 |   0.000035 |            0 |             0 |
| init                 | 0.000264 | 0.000000 |   0.000263 |            0 |             0 |
| optimizing           | 0.000486 | 0.000000 |   0.000485 |            0 |             0 |
| update multi-index   | 0.000475 | 0.000000 |   0.000485 |            0 |             8 |
| aggregation          | 6.579651 | 0.629508 |  26.047125 |        12128 |           160 |
| query end            | 0.000087 | 0.000043 |   0.000005 |            0 |             0 |
| closing tables       | 0.000134 | 0.000131 |   0.000000 |            0 |             0 |
| freeing items        | 0.000110 | 0.000110 |   0.000000 |            0 |             0 |
| logging slow query   | 0.000171 | 0.000172 |   0.000000 |            0 |             8 |
| cleaning up          | 0.000037 | 0.000036 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
13 rows in set, 1 warning (0.00 sec)





mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select name,count(*)  from mystone group by name;
+----------+----------+
| name     | count(*) |
+----------+----------+
| 'hello1' |  4629166 |
| hello1   | 35380844 |
+----------+----------+
2 rows in set (1 min 1.54 sec)

mysql> show profile cpu,block io for query 1;
+----------------------+-----------+-----------+------------+--------------+---------------+
| Status               | Duration  | CPU_user  | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+-----------+-----------+------------+--------------+---------------+
| starting             |  0.000818 |  0.000128 |   0.000065 |            0 |             0 |
| checking permissions |  0.000042 |  0.000026 |   0.000013 |            0 |             0 |
| Opening tables       |  0.000057 |  0.000038 |   0.000020 |            0 |             0 |
| init                 |  0.000094 |  0.000063 |   0.000031 |            0 |             0 |
| System lock          |  0.000017 |  0.000010 |   0.000006 |            0 |             0 |
| optimizing           |  0.000005 |  0.000003 |   0.000001 |            0 |             0 |
| statistics           |  0.000015 |  0.000010 |   0.000005 |            0 |             0 |
| preparing            |  0.000028 |  0.000019 |   0.000010 |            0 |             0 |
| Creating tmp table   |  0.000052 |  0.000034 |   0.000017 |            0 |             0 |
| Sorting result       |  0.000005 |  0.000003 |   0.000002 |            0 |             0 |
| executing            |  0.000003 |  0.000002 |   0.000001 |            0 |             0 |
| Sending data         | 61.540422 | 56.784511 |   6.823924 |            0 |             0 |
| Creating sort index  |  0.000145 |  0.000110 |   0.000031 |            0 |             0 |
| end                  |  0.000006 |  0.000004 |   0.000001 |            0 |             0 |
| query end            |  0.000027 |  0.000021 |   0.000006 |            0 |             0 |
| removing tmp table   |  0.000012 |  0.000009 |   0.000003 |            0 |             0 |
| query end            |  0.000003 |  0.000002 |   0.000000 |            0 |             0 |
| closing tables       |  0.000031 |  0.000025 |   0.000007 |            0 |             0 |
| freeing items        |  0.000064 |  0.000000 |   0.000064 |            0 |             0 |
| cleaning up          |  0.000053 |  0.000000 |   0.000052 |            0 |             0 |
+----------------------+-----------+-----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)

第一回合，StoneDB胜，几乎比Innodb要快了10倍。

测试用例bulk load

一物有其正，必有其反。通过bulk load的方式主机导入数据，也是导入40010010条数据。最后通过group by统计查询对比两个数据库的性能。

40010010一下子所有的数据不消一会儿就到主机，当我们可以在MySQL上面查询数据，StoneDB还分文未进，一直过了一个小时后，它才把所有的数据进行入库。这一个小时发生了什么事情 ？

主从复制的工作原理是主机的事务已经提交了，它才会把数据同步到从机。IO线程就把事件日件搬到从机的中继日志， SQL线程再把中继日志进行回放，一边回放，一边往从机同步数据。

我结合网络看了IO线程的工作时间，IO把事件数据搬到从机仅花了2秒钟，也就是主要问题是在SQL线程对数据进行回放上。我提高了slave的并发，设置slave-parallel-workers=10，但还是导入数据缓慢 ，查看CPU、内存、硬存都没有到达瓶颈，也没有单线程读取中继日志缓慢的事，因为数据都是写入，更不可能与慢查询有关系。所以初步判断是批量写入时，StoneDB要进行频繁的合并、压缩会比较慢。

简言之，StoneDB一口气把40010010条数据导入进去，花了1个多小时。这局，StoneDB败！

总结

正式的业务场景，应该很少业务场景会往InnoDB插入那么多的数据，InnoDB大多是用来TP的比较多，这样一来，StoneDB还是有自己的特色的。InnoDB作为出色的TP处理，适合频繁更新数据的业务场景，TP后的数据通过GTID流复制到AP，短流量两个节点可以快速数据同步，而TIANMU引擎带来优秀的分析功能，再反哺给应用。