MySQL Innodb Cluster(MGR)压力测试

之前一直对腾讯TDSQL的实现原理比较感兴趣,号称使用Raft来保障数据的一致性。MySQL官方推荐使用MGR的方案来搭建高可用集群,基于Paxos的MGR毕竟是官方的解决方案,也很想知道其性能到底如何。

另一方面,在同城两个容灾数据中心之间,由于测试出来只有4ms的时延,理论上将MGR的一个节点放到容灾数据中心也是没有问题的。这样可以解决异步复制和半同步复制都无法解决的数据一致性问题。

资源准备

主机

需要至少有四台主机,其中:三台作为集群节点,一台安装mysqlrouter(同时部署mysql-shell)
机器的配置都是:4核16G

1
2
3
4
5
6
7
8
9
10
11
# 修改主机名
hostnamectl set-hostname sh-mrouter

# 修改hosts文件,便于使用主机名互访
cat /etc/hosts

10.254.200.13 sh-mrouter
10.254.200.5 sh-mgr1
10.254.200.8 sh-mgr2
# 注意,最后一个节点的网段和其他节点不一样,因为它在同城的另一个机房
10.254.201.70 sh-mgr4

yum源

慢!真的很慢!官方的yum源安装起来能够让人崩溃。所以,我找了清华的源,然后自己改了配置。由于我只关注8.x的版本,因此只修改了这部分,要使用5.7的请自行修改。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
cat mysql.repo

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql80-community-el7/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-tools-community-el7/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

安装服务

mysqld

1
2
yum install -y mysql-server
systemctl start mysqld

mysql-shell

1
yum install -y mysql-shell

mysql-router

1
yum install -y mysql-router

配置集群

密码与权限

1
2
3
4
5
6
7
8
9
10
11
12
13
# 从日志从获取系统生成的
grep 'A temporary password is generated for root@localhost' /var/log/mysqld.log | awk -F ' ' '{print $(NF)}'

# 使用密码登录到mysql
mysql -uroot -p

# 更改密码和权限
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Mysql,123';
use mysql;
update user set host='%' where user='root';
# 注意这里的mysql_native_password,不修改会导致集群登录有问题
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Mysql,123';
FLUSH PRIVILEGES;

my.cnf配置更新

1
2
3
4
5
6
7
8
9
10
# 在my.cnf添加一下配置,注意这里的server_id每台节点需要不一样
cat /etc/my.cnf

enforce_gtid_consistency=1
gtid_mode=on
server_id=1
# 当你有节点跨网段时,mysql自动的白名单没法加入跨网段的IP, 需要手动修改 group_replication_ip_whitelist
group_replication_ip_whitelist="10.254.200.13/24,10.254.201.70/24"

systemctl restart mysqld

mysqlsh创建集群

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
# 连接到节点
\connect root@sh-mgr1:3306
# 检查当前配置是否能够加入集群
dba.configureInstance('root@sh-mgr1:3306')
# 创建集群
dba.createCluster('cluster_1')
# 加入其他节点
var cluster = dba.getCluster()
cluster.addInstance('root@sh-mgr2:3306')
cluster.addInstance('root@sh-mgr4:3306')
# 查看节点拓扑
cluster.status()
# 以下信息显示有三个节点ONLINE,当前集群可以容忍有一个节点挂掉
{
"clusterName": "cluster_1",
"defaultReplicaSet": {
"name": "default",
"primary": "sh-mgr1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"sh-mgr1:3306": {
"address": "sh-mgr1:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"sh-mgr2:3306": {
"address": "sh-mgr2:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
},
"sh-mgr4:3306": {
"address": "sh-mgr4:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE",
"version": "8.0.17"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "sh-mgr1:3306"
}

配置router

在安装了mysql-router的节点上直接执行一下命令,会提示输入密码,输入之后系统会告知已经创建配置文件。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[root@mrouter ~]# mysqlrouter --bootstrap root@sh-mgr1:3306 --user=root
Please enter MySQL password for root:
# Bootstrapping system MySQL Router instance...

- Checking for old Router accounts
- No prior Router accounts found
- Creating mysql account mysql_router1_175w87hctokv@'%' for cluster management
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /etc/mysqlrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB cluster 'cluster_1'

After this MySQL Router has been started with the generated configuration

$ /etc/init.d/mysqlrouter restart
or
$ systemctl start mysqlrouter
or
$ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

the cluster 'cluster_1' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447

## MySQL X protocol

- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470


Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'

不知道是什么原因,我通过系统命令启动服务一直失败,索性直接使用一下命令启动服务。

1
nohup mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf &

服务对应的读写端口都有自动列出来,大概也可以去手动修改,后面我们做压力测试主要使用R/W的6446端口。

压力测试

sysbench安装

1
2
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

压力测试

测试主要针对于三节点在单个机房的测试,以及其中一个节点在另外一个机房的测试。对应的测试参数和命令如下,大概是先分别压了50万条记录到5张表里面,然后开启100个线程,总共花1分钟时间去读写数据。

1
2
3
4
5
sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --table_size=500000 --tables=5 --time=60 --threads=100 --mysql-host=sh-mgr1 --mysql-port=3306  --mysql-db=test --mysql-user=root --mysql-password=Mysql,123 prepare

sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --table_size=500000 --tables=5 --time=60 --threads=100 --mysql-host=sh-mgr1 --mysql-port=3306 --mysql-db=test --mysql-user=root --mysql-password=Mysql,123 run

sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --table_size=500000 --tables=5 --time=60 --threads=100 --mysql-host=sh-mgr1 --mysql-port=3306 --mysql-db=test --mysql-user=root --mysql-password=Mysql,123 cleanup

测试结果

  • 第一组
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL statistics:
queries performed:
read: 803544
write: 229584
other: 114792
total: 1147920
transactions: 57396 (955.34 per sec.)
queries: 1147920 (19106.81 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 60.0773s
total number of events: 57396

Latency (ms):
min: 11.32
avg: 104.60
max: 505.75
95th percentile: 193.38
sum: 6003761.80

Threads fairness:
events (avg/stddev): 573.9600/29.45
execution time (avg/stddev): 60.0376/0.02
  • 第二组
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL statistics:
queries performed:
read: 793646
write: 226756
other: 113378
total: 1133780
transactions: 56689 (943.96 per sec.)
queries: 1133780 (18879.11 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 60.0529s
total number of events: 56689

Latency (ms):
min: 11.02
avg: 105.88
max: 384.31
95th percentile: 189.93
sum: 6002003.55

Threads fairness:
events (avg/stddev): 566.8900/13.72
execution time (avg/stddev): 60.0200/0.02
  • 第三组
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL statistics:
queries performed:
read: 804482
write: 229852
other: 114926
total: 1149260
transactions: 57463 (957.06 per sec.)
queries: 1149260 (19141.25 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 60.0392s
total number of events: 57463

Latency (ms):
min: 12.81
avg: 104.44
max: 450.94
95th percentile: 189.93
sum: 6001272.40

Threads fairness:
events (avg/stddev): 574.6300/20.70
execution time (avg/stddev): 60.0127/0.01
0%