MySQL 数据库集群-PXC 方案（三）

MySQL 数据库集群-PXC 方案（三）

什么是基准测试

基准测试是针对系统的一种压力测试，但基准测试不关心业务逻辑，更加简单、直接、易于测试，不要求数据的真实性和逻辑关系。

基准测试的指标

Sysbench 简介

Sysbench 是一个模块化的、跨平台、多线程基准测试工具，主要用于测试系统及数据库的性能。它主要包括以下几种方式的测试：

• CPU 性能（系统级别）
• 磁盘 IO 性能（系统级别）
• 调度程序性能（系统级别）
• 内存分配及传输速度（系统级别）
• POSIX 线程性能（系统级别）
• 数据库性能(OLTP 基准测试)

目前 Sysbench 主要支持 MySQL,pgsql,oracle 这 3 种数据库。

安装 Sysbench

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

安装完成后查看是否安装成功

sysbench --version

Sysbench 基本语法

sysbench script [option] [command]

option 连接信息参数

参数名称	功能意义
–mysql-host	IP 地址
–mysql-port	端口号
–mysql-user	用户名
–mysql-password	密码

option 执行参数

参数名称	功能意义
–oltp-test-mode	执行模式(simple、nontrx、complex)
–oltp-tables-count	测试表的数量
–oltp-table-size	测试表的记录数
–threads	并发连接数
–time	测试执行时间（秒）
–report-interval	生成报告单的间隔时间（秒）

执行模式：

• simple： 测试查询 不测试写入
• nontrx：测试无事务的增删改查
• complex：测试有事务的增删改查

command 命令

命令名称	功能意义
prepare	准备测试数据
run	执行测试
cleanup	清除测试数据

准备测试数据

在准备之前我们先修改一下haproxy.cfg文件，之前我们配置的是 MyCat 集群的负载均衡，现在改为某一个分片的 PXC 集群即可。

vim /etc/haproxy/haproxy.cfg

server mysql_1 192.168.3.137:3306 check port 3306 weight 1 maxconn 2000
server mysql_1 192.168.3.138:3306 check port 3306 weight 1 maxconn 2000
server mysql_1 192.168.3.139:3306 check port 3306 weight 1 maxconn 2000

保存之后执行命令重启

service haproxy restart

可以看到下图没有问题：

之后我们新建一个测试逻辑库sbtest

接着我们创建测试数据

sysbench  /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.3.146 --mysql-port=3306 --mysql-user=admin --mysql-password=Abc_123456 --oltp-tables-count=10 --oltp-table-size=100000 prepare

• /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua ： 生成测试数据的脚本 sysbench 自带
• –mysql-host ：数据库连接地址
• –mysql-port ： 端口
• –mysql-user：用户名
• –mysql-password：密码
• –oltp-tables-count：测试 10 个数据表
• –oltp-table-size：每张表 10 万条数据
• prepare：准备测试数据

创建完成后我们执行测试

sysbench  /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.3.146 --mysql-port=3306 --mysql-user=admin --mysql-password=Abc_123456 --oltp-test-mode=complex --threads=10 --time=300 --report-interval=10 run >> /home/mysysbench.log

• –oltp-test-mode=complex：测试有事务的增删改查
• –threads：并发连接数
• –time：测试时长，测试的时长更长一些，比如 24 小时，测试的结果会更加准确
• –report-interval=10 ：每隔 10 秒回报一次数据
• run >> /home/mysysbench.log：输入测试日志报告文件位置

等待 5 分钟执行完成后，我们查看 /home/mysysbench.log。

• queries performed ： 执行测试的次数
• read : 读操作执行了 442176 次
• write ： 写操作执行了 117484 次
• other：其他操作执行了 66275 次
• total：总共执行了 625935 次
• transcations（TPS）：执行的事务次数 28415 次，PXC 集群每秒可以执行的事务操作 94.67 次
• queries(QPS)：处理的请求书 625935，PXC 集群每秒钟可以执行 2085.35 次增删改查操作
• ignored errors： 忽略的错误数量 3169，每秒钟平均错误数量 10.56 次，可能是节点之间冲突造成
• reconnects：数据库重新连接的次数，0 代表没有发生数据库连接断开的情况

清理测试数据：

sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.3.146 --mysql-port=3306 --mysql-user=admin --mysql-password=Abc_123456 --oltp-tables-count=10 cleanup

小结

基准测试是对单张表进行的读写测试，因为不涉及表连接外键约束，索引等操作，所以单纯体现的是数据库硬件性能。如果想知道数据库集群在真实业务中的实际性能，需要使用压力测试。

tpcc-mysql 简介

tpcc-mysql 是 percona 基于 tpcc 规范衍生出来的产品，专门用于 mysql 压力测试 。

tpcc 是一种测试标准，明确规定了数据模型和检测是指标，而且检测的标准对数据库集群来说很苛刻，tpcc-mysql 的测试库能覆盖大多数的业务场景，测试的结果也能反映出真实业务中数据库的实际性能。

tpcc 测试问题

tpcc 的检测标准是针对单节点的 mysql 数据库，对 sql 的执行时间有严格的规定，我们要测试的 PXC 集群是以牺牲插入速度为代价换取的同步强一致性，假如 tpcc 要执行一个 insert 语句不能超过 100ms，但是 PXC 集群只是写入速度慢，插入执行了 300ms。这个检测点就没有通过，所以拿 tpcc 测试 PXC 集群不太适合，检测的标准太苛刻了一些，但是因为数据库集群在真实业务下实际的读写性能，每秒钟能执行多少次读操作，多少次写操作。至于由于插入速度慢测试报告中测试没有通过，可以不予理会。

测试方案

我们还是以 haproxy+三个 mysql 节点的 PXC 集群来进行测试。

准备工作（一）

关闭我们的 PXC 集群。对应关闭操作在第二篇文章中写的很清楚。

然后打开 vim /etc/my.cnf

把 PXC_strict_mode 的值改成 DISABLED
原来默认的参数值是不允许我们执行不符合规范的操作，比如创建出没有主键的数据表，tpcc 数据库脚本里边有一个表没有主键，所以为了 tpcc 测试能进行下去我们要修改 PXC_strict_mode 的值。

三个 PXC 节点都需要修改

修改完 my.cnf 文件之后再重新启动 PXC 集群。

准备工作（二）

Haproxy 对应的文件进行修改，由于我们之前已经修改过了这里就不用再修改了。

server mysql_1 192.168.3.137:3306 check port 3306 weight 1 maxconn 2000
server mysql_1 192.168.3.138:3306 check port 3306 weight 1 maxconn 2000
server mysql_1 192.168.3.139:3306 check port 3306 weight 1 maxconn 2000

准备工作（三）

安装环境包。

yum install -y gcc
yum install -y mysql-devel

安装 tpcc-mysql

下载安装包。

https://codeload.github.com/Percona-Lab/tpcc-mysql/zip/master

解压然后上传到服务器。

进入 src 目录，再使用 make 命令编译。

cd src
make

创建测试库

到 PXC 集群的节点上创建数据库tpcc，我们在 Haproxy 的节点上创建，那么 PXC 集群的 mysql 库也会自动同步。

然后我们进入 tpcc-mysql-master 目录下执行：

ls *.sql

• create_table.sql 是创建表的 sql 文件
• add_fkey_idx.sql 是索引等约束文件

我们将这两个文件复制出来，然后在我们新建的 tpcc 库中运行。

准备测试数据

./tpcc_load -h 192.168.3.146 -d tpcc -u admin -p Abc_123456 -w 1

• -h 192.168.3.146 数据库 ip 地址
• -d tpcc 数据库名字
• -u admin 用户名
• -p Abc_123456 密码
• -w 1 仓库数量，由于数量庞大，插入时间较长，所以这里使用 1 个仓库数量，如果使用多个仓库，耗时很长。

执行测试

./tpcc_start -h 192.168.3.146 -d tpcc -u admin -p Abc_123456 -w 1 -c 5 -r 300 -l 600 - >tpcc-outpit.log

• -h 192.168.3.146 数据库 ip 地址
• -d tpcc 数据库名字
• -u admin 用户名
• -p Abc_123456 密码
• -w 1 仓库数量
• -c 5 并发线程数
• -r 300 数据库预热时间 单位秒
• -l 600 测试时间单位秒
• tpcc-outpit.log 测试结果输出到文件

为了真实性可以将-r 和-l 时间设置长一些，比如预热 1 个小时，测试 24 小时。

查看日志日志出现了大量的死锁异常，执行压力测试的时候，事务执行的时间太久，没有及时提交事务，于是出现了锁冲突。让 PXC 集群的锁冲突降到最低，将并发的线程数改为 1。

./tpcc_start -h 192.168.3.146 -d tpcc -u admin -p Abc_123456 -w 1 -c 1 -r 300 -l 600 - >tpcc-outpit.log

查看测试结果

• sc: 成功执行的次数

• lt: 超时执行的次数

• rt: 重试执行的次数

• fl: 失败执行的次数

第一行是新订单执行的测试结果，tpcc 在规定时间内成功执行了 0 条记录，由于 tpcc 测试指标是非常苛刻的，虽然我们执行了操作，但是执行时间没有达到 tpcc 的要求，所以不能算为执行成功，只能算做是超时执行。PXC 集群是以牺牲速度为代价换取数据同步的强一致性。虽热增删改成都能执行，但是达不到 tpcc 的指标。rt(重试执行的次数)和 fl(失败执行的次数)是 0 次。

第二行是支付业务的测试结果。成功执行了 0 次，超时执行了 3587 次，重试执行 0 次，失败 0 次。

第三行是订单状态的测试结果。成功执行了 195 次，超时执行了 163 次，重试执行 0 次，失败 0 次。

第四行是发货业务的测试结果。成功执行了 0 次，超时执行了 358 次，重试执行 0 次，失败 0 次

第五行是库存业务的测试结果。成功执行了 0 次，超时执行了 359 次，重试执行 0 次，失败 0 次。

尽管达不到 tpcc 的测试指标，但是没有失败执行的。以上是各种业务下的增删改查操作。

下面这个是事务的操作状态，测试报告也是通过的。

响应时间的一个测试，NG 代表没有通过，OK 代表的是测试通过。用 tpcc 测试 PXC 集群有些不合理，tpcc 测试是按照单节点 mysql 读写速度和响应时间来制定的指标。所以 PXC 集群很难能达到 tpcc 的指标，所以这里很多响应时间的指标都是 NG 没通过的。

最后的 TpmC 是，每分钟 PXC 集群执行的事务数量。

binlog 简介

MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句 select、show 等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL 的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

binlog 日志的两个最重要的使用场景：

• MySQL 主从复制
• 数据恢复

binlog 文件种类

• 二进制日志索引文件（文件名后缀为.index）用于记录所有有效的的二进制文件
• 二进制日志文件（文件名后缀为.00000*）记录数据库所有的 DDL 和 DML 语句事件

binlog 是一个二进制文件集合，每个 binlog 文件以一个 4 字节的魔数开头，接着是一组 Events:

• 魔数：0xfe62696e 对应的是 0xfebin；
• Event：每个 Event 包含 header 和 data 两个部分；header 提供了 Event 的创建时间，哪个服务器等信息，data 部分提供的是针对该 Event 的具体信息，如具体数据的修改；
• 第一个 Event 用于描述 binlog 文件的格式版本，这个格式就是 event 写入 binlog 文件的格式；
• 其余的 Event 按照第一个 Event 的格式版本写入；
• 最后一个 Event 用于说明下一个 binlog 文件；
• binlog 的索引文件是一个文本文件，其中内容为当前的 binlog 文件列表

当遇到以下 3 种情况时，MySQL 会重新生成一个新的日志文件，文件序号递增：

• MySQL 服务器停止或重启时
• 使用 flush logs 命令
• 当 binlog 文件大小超过 max_binlog_size 变量的值时

max_binlog_size 的最小值是 4096 字节，最大值和默认值是 1GB (1073741824 字节)。事务被写入到 binlog 的一个块中，所以它不会在几个二进制日志之间被拆分。因此，如果你有很大的事务，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的日志都记录到当前日志文件中，直到事务结束，你可能会看到 binlog 文件大于 max_binlog_size 的情况。

Binlog 的日志格式

记录在二进制日志中的事件的格式取决于二进制记录格式。支持三种格式类型：

• STATEMENT：基于 SQL 语句的复制（statement-based replication, SBR）
• ROW：基于行的复制（row-based replication, RBR）
• MIXED：混合模式复制（mixed-based replication, MBR）

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT