MySQL Innodb Cluster(MGR)压力测试

发表于 | 分类于 | 0 Comments
阅读时长 ≈ 8 mins.

之前一直对腾讯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%