关于MySQL的基准测试

当我们对数据库进行优化后，只有进行测量系统性能才能知道优化是否有效，这种测量的方式就是基准测试。基准测试的定义如下：

基准测试是一种测量和评估软件性能指标的活动用于建立某个时刻的性能基准，以便当系统发生软/硬件变化时，重新进行基准测试以评估变化对性能的影响

我们可以这样认为：

基准测试是针对系统设置的一种压力测试，可以用来观察系统在不同压力下的行为。评估系统的容量，观察系统如何处理不同的数据。

但是基于测试不等同于压力测试：

• 基于测试：直接、简单、易于比较，用于评估服务器的处理能力。基准测试可能不关心业务逻辑，所使用的查询和业务的真实性可以和业务环节没有关系
• 压力测试：对真实的业务数据进行测试，获得真实系统所能承受的压力。压力测试需要针对不同的主题，所使用的数据和查询也是真实用到的

对MySQL进行基准测试的目的：

• 1、建立MySQL服务器的性能基准线，确定当前MySQL服务器的运行情况
• 2、模拟比当前系统更高的负载，以找出系统的扩展瓶颈。增加数据库的并发，观察QPS、TPS的变化，以确定并发量与性能最优的关系
• 3、测试不同的硬件、软件和操作系统配置
• 4、证明新的硬件设备是否配置正确

如何进行基准测试

通常来说，基准测试有两种方式：

• 1、对整个系统进行基准测试：从系统的入口进行测试（网站Web前端，手机APP前端）
  • 优点：能够测试整个系统的性能，包括web服务器缓存、数据库等；能反映出系统中各个组件接口之间的性能问题，体现真实性能状况
  • 缺点：测试用例设计复杂，消耗时间长
• 2、单独对MySQL进行基准测试：仅测试系统中的MySQL服务
  • 优点：测试用例设计简单，所需耗时少
  • 缺点：无法全面了解整个系统的性能基线

MySQL基准测试的常见指标：

• 单位时间内所处理的事务数（TPS）
• 单位时间内所处理的查询数（QPS）
• 并发数量：同时处理的查询请求数量
• 单个测试任务的响应时间
  • 包含：平均响应时间、最小响应时间、最大响应时间及各时间所占百分比

基准测试中容易忽略的问题：

• 使用生产环境数据进行测试时，只使用了部分数据，可能会导致测试结果不准确
  • 推荐：使用数据库的全量备份数据来进行测试
• 在多用户场景中，只做了单用户的测试
  • 推荐：使用多线程并发测试
• 在单服务器上测试分布式应用
  • 推荐：应使用相同的架构进行测试
• 反复执行同一查询，问题在于同样的SQL语句容易缓存命中，无法反应真实查询性能。而在实际的环境中查询方式可能是不尽相同的

基准测试的步骤：

• 1、选择是对整个系统还是某一个组件进行测试，还需要确定使用什么样的数据进行测试
• 2、准备测试数据的收集脚本，通常需要尽可能多的收集当前系统的信息，例如CPU使用率、IO、网络流量、状态与计数器信息等
• 3、编写脚本分析第二步所收集的基准测试信息，最后得出测试结果

收集脚本和分析脚本示例

基准测试数据的收集脚本：

#!/bin/bash
# 脚本的运行间隔
INTERVAL=5
# 创建数据文件的存储目录
STORE_DIR=/home/mysql/benchmarks
mkdir -p $STORE_DIR
# 将收集的数据存储到哪个目录下，以及文件前缀
PREFIX=$STORE_DIR/$INTERVAL-sec-status
# 设置脚本的运行标识
RUNFILE=$STORE_DIR/running
touch $RUNFILE && echo "1" > $RUNFILE
# mysql的用户名和密码
USER=root
PWD=123456
# mysql命令所在的路径
MYSQL="/usr/local/mysql/bin/mysql -u$USER -p$PWD"
# 记录当前mysql的全局变量
$MYSQL -e "show global variables" >> mysql-variables

# 运行标识文件存在就会一直循环
while test -e $RUNFILE; do
    # 定义脚本运行时的当前时间
    file=$(date +%F_%I)
    # 实现间隔运行该循环
    sleep=$(date +%s.%N | awk ‘{print 5 - ($1 % 5)}‘)
    sleep $sleep

    ts="$(date +"TS %s.%N %F %T")"
    # 获取系统负载信息
    loadavg="$(uptime)"
    # 记录系统负载信息
    echo "$ts $loadavg" >> $PREFIX-${file}-status
    # 记录当前mysql的全局变量信息
    $MYSQL -e "show global status" >> $PREFIX-${file}-status &
    echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
    # 记录当前 innodb 的状态信息
    $MYSQL -e "show engine innodb status" >> $PREFIX-${file}-innodbstatus &
    echo "$ts $loadavg" >> $PREFIX-${file}-processlist
    # 记录当前mysql全部连接信息列表
    $MYSQL -e "show full processlist\G" >> $PREFIX-${file}-processlist &
    echo $ts
done

echo Exiting because $RUNFILE does not exists

测试数据分析脚本：

#!/bin/bash
awk ‘
   BEGIN {
     printf "#ts date time load QPS";
     fmt=" %.2f";
   }
   /^TS/ {
   ts = substr($2,1,index($2,".")-1);
   load = NF -2;
   diff = ts - prev_ts;
   printf "\n%s %s %s %s",ts,$3,$4,substr($load,1,length($load)-1);
   prev_ts=ts;
   }
   /Queries/{
   printf fmt,($2-Queries)/diff;
   Queries=$2
   }
   ‘ ""

### 使用示例 ###
# sh ./analyze.sh ${测试数据文件路径}
# 如：sh ./analyze.sh /home/mysql/benchmarks/5-sec-status-2020-01-11_04-status

Mysql基准测试工具之mysqlslap

一般来说，我们都不会自己去编写基准测试的脚本，因为都有现成的工具。例如，mysql 5.1以上版本都自带了mysqlslap这个基准测试工具。由于是自带的也无需进行单独的安装，该工具位于mysql的bin目录下。

功能简介：

• 模拟服务器负载，并输出相关统计信息
• 可以指定测试的查询语句，也可以使用该工具自动生成的测试语句

常用参数说明：

• 更多参数详见官方文档

使用示例：

[ ~]# mysqlslap -uroot -p123456 --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=test

运行该测试后，截取的部分输出片段如下：

Benchmark
    # 运行的是myisam引擎的测试
    Running for engine myisam
    # 运行所有查询的平均秒数
    Average number of seconds to run all queries: 0.020 seconds
    # 运行所有查询的最小秒数
    Minimum number of seconds to run all queries: 0.018 seconds
    # 运行所有查询的最大秒数
    Maximum number of seconds to run all queries: 0.022 seconds
    # 运行查询的客户端数
    Number of clients running queries: 1
    # 每个客户端的平均查询数 
    Average number of queries per client: 10

Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 0.049 seconds
    Minimum number of seconds to run all queries: 0.042 seconds
    Maximum number of seconds to run all queries: 0.059 seconds
    Number of clients running queries: 1
    Average number of queries per client: 10

Mysql基准测试工具之sysbench

除了 mysqlslap 这个mysql自带的基准测试工具之外，还有一个常用的工具就是 sysbench。sysbench的功能比mysqlslap更多一些，能测试的点也更全面一些。sysbench除了可以测试MySQL外，还能测试系统的CPU、IO、内存等方面。

安装sysbench

sysbench的GitHub仓库地址及源码安装文档如下：

• https://github.com/akopytov/sysbench
• Building and Installing From Source

一般系统中不会自带sysbench工具，需要我们自行安装，我这里采用源码编译安装的方式进行安装。首先到如下地址复制源码包的下载链接：

• https://github.com/akopytov/sysbench/releases

然后到Linux上使用wget命令进行下载：

[ ~]# cd /usr/local/src
[ /usr/local/src]# wget https://github.com/akopytov/sysbench/archive/1.0.19.tar.gz

安装编译sysbench时所依赖的一些库：

[ /usr/local/src]# yum -y install make automake libtool pkgconfig libaio-devel mariadb-devel openssl-devel

接着将下载好的源码包进行解压缩，并进入到解压后的目录：

[ /usr/local/src]# tar -zxvf 1.0.19.tar.gz
[ /usr/local/src]# cd sysbench-1.0.19/

最后参考以下步骤完成编译安装：

[ /usr/local/src/sysbench-1.0.19]# ./autogen.sh
[ /usr/local/src/sysbench-1.0.19]# ./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
[ /usr/local/src/sysbench-1.0.19]# make && make install

等待完成编译安装后，测试是否安装成功。我这里报了个找不到库文件的错误：

[ /usr/local/src/sysbench-1.0.19]# sysbench --version
sysbench: error while loading shared libraries: libmysqlclient.so.21: cannot open shared object file: No such file or directory

解决方式是建立一个该文件的软连接到/usr/lib64/目录下：

[ /usr/local/src/sysbench-1.0.19]# ln -s /usr/local/mysql/lib/libmysqlclient.so.21  /usr/lib64/

再次测试，执行成功：

[ /usr/local/src/sysbench-1.0.19]# sysbench --version
sysbench 1.0.19

sysbench的使用

sysbench的命令格式如下：

sysbench [options]... [testname] [command]

1、options，是指定测试参数，以下是使用sysbench测试MySQL时常用的参数：

• --mysql-db：用于指定基准测试的数据库名称，该数据库必须是已存在的
• --mysql_storage_engine：指定测试的MySQL存储引擎
• --tables：执行测试的表的数量
• --table_size：指定每个表中的数据量
• --threads：指定测试的并发线程数量
• --max-time：指定最大测试时间，单位为秒
• --report-interval：指定间隔多长时间就输出一次统计信息，单位为秒
• --mysql-socket：指定mysql的.sock文件路径，用于连接mysql
• --mysql-user：指定用于测试时连接mysql的用户
• --mysql-password：指定mysql用户的密码
• 关于其他参数详见官方文档

2、testname，是指定内置的测试类型或测试脚本。内置的测试类型有如下取值：

• fileio：测试文件系统I/O性能
• cpu：测试cpu性能
• memory：测试内存性能
• threads：测试线程性能
• mutex：测试互斥锁性能
• 执行sysbench [testname] help，可以查看各个测试类型所支持的参数

除内置测试类型以外的测试，就需要指定测试脚本。sysbench的测试脚本需要使用lua语言编写，可以根据实际需求编写自已的测试脚本，也可以使用sysbench自带的测试脚本。sysbench自带的lua脚本如下：

[ ~]# ls /usr/local/share/sysbench
bulk_insert.lua  # 用于测试大量的insert操作性能
oltp_common.lua  # 公共文件，给其他脚本引用的，不可直接使用
oltp_delete.lua  # 用于测试oltp下的delete操作性能
oltp_insert.lua  # 用于测试oltp下的insert操作性能
oltp_point_select.lua   # 用于测试oltp下的定点查询性能，例如根据主键查询
oltp_read_only.lua   # 用于测试oltp下的只读性能
oltp_read_write.lua  # 用于测试oltp下的读写性能
oltp_update_index.lua  # 用于测试更新索引字段的性能
oltp_update_non_index.lua  # 用于测试更新无索引字段操作的性能
oltp_write_only.lua  # 用于测试oltp下的写操作性能
select_random_points.lua  # 用于测试随机定点查询的性能
select_random_ranges.lua  # 用于测试随机范围读取的性能
...

• Tips：不同版本的sysbench存放测试脚本的路径可能不太一样，需要自行确认

3、command，则是指定一些测试操作，例如运行测试、清除数据或准备数据等：

• prepare：用于测试开始前准备测试数据
• run：用于测试数据准备完成后，执行测试
• cleanup：用于测试执行完成后清理测试数据

sysbench基准测试示例

1、测试CPU性能

以下命令可以让cpu找出10000以内的素数，并统计计算所需耗时，以此得知cpu的性能。需要注意的是，这种方式测试的是单核心性能，而非多核性能：

[ ~]# sysbench --cpu-max-prime=10000 cpu run

测试结果如下：

# CPU速度
CPU speed:
    # 每秒事件数
    events per second:  1039.79

General statistics:
    # 计算出所有素数的总耗时
    total time:                          10.0005s  
    # 总的事件数
    total number of events:              10400

Latency (ms):
         # 计算出一个素数的最低耗时
         min:                                    0.94
         # 平均计算出一个素数的耗时
         avg:                                    0.96
         # 计算出一个素数的最长耗时
         max:                                    9.43
         # 95%情况是1.01毫秒计算出一个素数
         95th percentile:                        1.01
         # 合计耗时
         sum:                                 9994.05

# 线程公平性
Threads fairness:
    events (avg/stddev):           10400.0000/0.00
    execution time (avg/stddev):   9.9941/0.00

2、测试系统I/O性能

测试I/O性能需要先准备测试数据，测试数据的大小需要大于物理内存。命令如下：

# 该命令会生成127个测试文件，这些文件合计4G大小
[ ~]# sysbench --file-total-size=4G fileio prepare
...
Creating file test_file.125
Creating file test_file.126
Creating file test_file.127
4294967296 bytes written in 39.00 seconds (105.04 MiB/sec).
[ ~]#

数据准备好后，执行测试：

[ ~]# sysbench --threads=8 --file-total-size=4G --file-test-mode=rndrw --report-interval=1 fileio run

参数说明：

• --threads=8：设置并发线程数为8
• --file-test-mode=rndrw：指定文件测试模式为随机读写，即测试磁盘I/O的随机读写性能
• --report-interval=1：指定每秒输出一次统计信息

测试结果如下：

File operations:
    reads/s:                      2089.75
    writes/s:                     1392.67
    fsyncs/s:                     4553.64

Throughput:
    # 每秒读取
    read, MiB/s:                  32.65
    # 
    written, MiB/s:               21.76

General statistics:
    total time:                          10.0778s
    total number of events:              79973

Latency (ms):
         min:                                    0.00
         avg:                                    1.00
         max:                                   58.42
         95th percentile:                        3.13
         sum:                                79895.62

Threads fairness:
    events (avg/stddev):           9996.6250/43.23
    execution time (avg/stddev):   9.9870/0.01

最后执行cleanup操作，清除测试数据：

[ ~]# sysbench --file-total-size=4G fileio cleanup

3、测试MySQL性能

由于sysbench内置的测试类型没有MySQL，所以在使用sysbench测试MySQL时，testname需要指定为测试脚本的路径。sysbench自带了一些MySQL的测试脚本，所以无特殊需求的情况下，我们也无需自己去编写测试脚本。接下来，进行一个简单的演示，看看如何使用测试脚本来测试MySQL的性能。

首先也是需要准备数据，命令如下：

[ ~]# sysbench --mysql_storage_engine=innodb --table_size=10000 --mysql-db=test --tables=10 --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password=123456 /usr/local/share/sysbench/oltp_read_write.lua prepare

• Tips：该脚本用于测试OLTP下的读写性能。另外，笔者这里为了方便就直接使用root账户，在实际测试中，务必创建一个用于测试的账户

执行测试：

[ ~]# sysbench --mysql_storage_engine=innodb --table_size=10000 --mysql-db=test --tables=10 --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password=123456 /usr/local/share/sysbench/oltp_read_write.lua run

测试结果如下：

SQL statistics:
    queries performed:
        # 读取操作的总数
        read:                            10444
        # 写操作的总数
        write:                           2984
        # 其他操作的总数，例如commit等
        other:                           1492
        # 总共执行的操作数量
        total:                           14920
    # 每秒执行的事务数，即TPS，这里为每秒74.48次
    transactions:                        746    (74.48 per sec.)
    # 每秒查询的数量，即QPS，这里为每秒1489.53次
    queries:                             14920  (1489.53 per sec.)
    # 忽略的错误次数
    ignored errors:                      0      (0.00 per sec.)
    # 重连次数
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0150s
    total number of events:              746

Latency (ms):
         min:                                    8.86
         avg:                                   13.42
         max:                                   99.97
         95th percentile:                       20.00
         sum:                                10009.79

Threads fairness:
    events (avg/stddev):           746.0000/0.00
    execution time (avg/stddev):   10.0098/0.00

同样，最后需要清理测试数据：

[ ~]# sysbench --mysql_storage_engine=innodb --table_size=10000 --mysql-db=test --tables=10 --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password=123456 /usr/local/share/sysbench/oltp_read_write.lua cleanup

如果想要知道测试脚本具体支持哪些参数，其查看方式和内置测试类型是一样的，只不过把测试类型的名称换成了脚本而已：

[ ~]# sysbench /usr/local/share/sysbench/oltp_read_write.lua help