MySQL性能测试

sysbench

sysbench是基于LuaJIT的可编写多线程基准工具，可用于数据库基准测试，了解系统及数据库的基础性能相关信息，项目地址：sysbench。sysbench包含以下基准测试：

oltp_*.lua：模拟OLTP下的数据库基准测试的集合
fileio：文件系统基准测试
cpu；简单的CPU基准测试
memory：内存访问基准测试
threads：基于线程的调度程序基准测试
mutex：POSIX互斥基准测试

安装相关依赖及编译程序包

$ yum -y install make automake libtool pkgconfig libaio-devel mariadb-devel openssl-devel
$ ./autogen.sh
$ ./configure
$ make && make install -j 4
$ ln -s /usr/local/mysql/lib/libmysqlclient.so.20 /usr/lib64

sysbench性能基准测试三大步骤：

prepare：初始化测试需要的数据。例如文件系统测试中的文件或数据库测试数据填充
run：运行指定的测试内容
clean：测试运行完成后清理测试临时数据

文件系统测试

查看fileio选项

[root@t-luhx03-v-szzb service]# sysbench fileio help
sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)

fileio options:
  --file-num=N                  number of files to create [128]
  --file-block-size=N           block size to use in all IO operations [16384]
  --file-total-size=SIZE        total size of files to create [2G]
  --file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
  --file-io-mode=STRING         file operations mode {sync,async,mmap} [sync]
  --file-async-backlog=N        number of asynchronous operatons to queue per thread [128]
  --file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []
  --file-fsync-freq=N           do fsync() after this number of requests (0 - don't use fsync()) [100]
  --file-fsync-all[=on|off]     do fsync() after each write operation [off]
  --file-fsync-end[=on|off]     do fsync() at the end of test [on]
  --file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync]
  --file-merged-requests=N      merge at most this number of IO requests if possible (0 - don't merge) [0]
  --file-rw-ratio=N             reads/writes ratio for combined test [1.5]

初始化测试文件

1	sysbench fileio --file-total-size=5G --file-num=1 --file-block-size=16384 --file-test-mode=rndrw prepare

执行性能测试

sysbench fileio --file-total-size=5G --file-test-mode=rndrw --time=300 --file-test-mode=rndrw --file-num=1 run

File operations:
    reads/s:                      385.54
    writes/s:                     257.02
    fsyncs/s:                     6.43

Throughput:
    read, MiB/s:                  6.02
    written, MiB/s:               4.02

General statistics:
    total time:                          300.0150s
    total number of events:              194744

Latency (ms):
         min:                                    0.00
         avg:                                    1.54
         max:                                  378.55
         95th percentile:                        6.67
         sum:                               299695.52

Threads fairness:
    events (avg/stddev):           194744.0000/0.00
    execution time (avg/stddev):   299.6955/0.00

注：上述随机读写测试后IOPS=(6.02+4.02)*1024/16=642.56

清除测试文件

1	sysbench fileio --file-total-size=5G --file-test-mode=rndrw --time=300 --file-test-mode=rndrw --file-num=1 clean

mysql测试

常用数据库选项

选项	描述	默认值
–mysql-host	mysql host
–mysql-port	mysql port
–mysql-user	mysql连接用户
–mysql-password	mysql连接用户密码
–mysql-db	mysql测试数据库名
–oltp-tables-count	测试表数量
–oltp-table-size	每张测试表数据量
–report-interval	指定测试进度报告输出的间隔，单位为秒
–oltp-dist-type	指定随机取样类型，可选值有uniform(均匀分布)，Gaussian(高斯分布)，special(空间分布)	special
–rand-init	否随机初始化数据，如果不随机则除了主键不一致其它字段全一致
–events	压力测试产生请求的总数,为0则不限制，由time去控制
–oltp-test-mode	执行模式，可选值有simple(简单查询)、complex(事务模式)、nontrx(非事务模式)	complex
–oltp-read-only	是否设置为只读模式	OFF
–time	压力测试的持续时间，单位为秒
–threads	并发线程数

只读测试：需要将oltp-read-only设置为on的，也可以使用select.lua
混合读写测试：需要把oltp-read-only设置为off，通过分析oltp.lua可以看出单个事务操作的比例select:update_key:update_non_key:delete:insert = 14:1:1:1:1。可通过oltp-point-selects、oltp-simple-ranges、oltp-sum-ranges、oltp-order-ranges、oltp-distinct-ranges，oltp-index-updates、oltp-non-index-updates这些选项去调整读写权重
更新测试：如果只想比较update或insert，可以使用update_index.lua脚本，这里是更新索引列上的值

测试导入数据

/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=localhost \
--mysql-port=33006 \
--mysql-user=root \
--mysql-password=Abcd123# \
--mysql-db=sysbench \
--db-driver=mysql \
--oltp-test-mode=complex \
--oltp-tables-count=10 \
--oltp-table-size=1000000 \
--threads=100 \
--report-interval=10 \
--rand-init=on \
--time=300 \
--warmup-time=10 \
prepare

执行后可能存在如下错误

1	/usr/local/sysbench/bin/sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory

解决方案

1
2
3

ln -s /usr/local/mysql/lib/libmysqlclient.so.20 /usr/lib64/libmysqlclient.so.20
echo "/usr/lib64" >> /etc/ld.so.conf
ldconfig -v

执行测试

/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=localhost \
--mysql-port=33006 \
--mysql-user=root \
--mysql-password=Abcd123# \
--mysql-db=sysbench \
--db-driver=mysql \
--oltp-test-mode=complex \
--oltp-tables-count=10 \
--oltp-table-size=1000000 \
--threads=100 \
--report-interval=10 \
--rand-init=on \
--time=120 \
--warmup-time=10 \
run

执行后会出现如下输出

sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 12
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 12 tps: 656.94 qps: 13161.57 (r/w/o: 9213.94/2632.55/1315.08) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 12 tps: 374.98 qps: 7498.46 (r/w/o: 5249.49/1499.01/749.96) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 12 tps: 658.05 qps: 13156.58 (r/w/o: 9209.76/2630.72/1316.11) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 12 tps: 394.16 qps: 7886.18 (r/w/o: 5519.93/1577.94/788.32) lat (ms,95%): 57.87 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 12 tps: 422.07 qps: 8443.87 (r/w/o: 5910.53/1689.19/844.15) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 12 tps: 556.50 qps: 11130.28 (r/w/o: 7791.06/2226.22/1113.01) lat (ms,95%): 47.47 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 12 tps: 369.30 qps: 7383.39 (r/w/o: 5168.50/1476.40/738.50) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 12 tps: 632.60 qps: 12647.50 (r/w/o: 8852.80/2529.60/1265.10) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 12 tps: 350.77 qps: 7019.60 (r/w/o: 4914.41/1403.46/701.73) lat (ms,95%): 58.92 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 12 tps: 487.93 qps: 9757.51 (r/w/o: 6831.13/1950.52/975.86) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 12 tps: 491.07 qps: 9824.38 (r/w/o: 6876.37/1965.88/982.14) lat (ms,95%): 50.11 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 12 tps: 352.50 qps: 7050.88 (r/w/o: 4935.09/1410.80/705.00) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            804832
        write:                           229952
        other:                           114976
        total:                           1149760
    transactions:                        57488  (478.77 per sec.)
    queries:                             1149760 (9575.33 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0737s
    total number of events:              57488

Latency (ms):
         min:                                    3.80
         avg:                                   25.06
         max:                                  276.01
         95th percentile:                       51.94
         sum:                              1440372.83

Threads fairness:
    events (avg/stddev):           4790.6667/54.38
    execution time (avg/stddev):   120.0311/0.01

注：read表示查询数量，write表示DML数量，transactions则为TPS，Latency为响应时间，queries表示QPS

清理测试数据

/usr/local/sysbench/bin/sysbench /usr/local/sysbench/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=localhost \
--mysql-port=33006 \
--mysql-user=root \
--mysql-password=Abcd123# \
--mysql-db=sysbench \
--db-driver=mysql \
--oltp-test-mode=complex \
--oltp-tables-count=10 \
--oltp-table-size=1000000 \
--threads=100 \
--report-interval=10 \
--rand-init=on \
--time=120 \
--warmup-time=10 \
cleanup

TPCC-MYSQL

tpcc-mysql是percona基于TCP-C衍生的产品，专门用于MySQL TPCC测试，项目地址：tpcc-mysql。tpcc-mysql内部模拟电商环境数据，其测试表包括仓库、订单、客户，其与sysbench最大的区别在于tpcc-mysql测试过程会不断增加测试数据，而sysbench数据量则保持不变，因此能更好模拟业务不断增长下的性能表现。

安装依赖包及编译程序包

1
2
3

$ yum install bzr -y
$ cd ./src
$ make

初始化测试数据

root@(none) 22:21:  create database tpcc;
Query OK, 1 row affected (0.04 sec)

$ mysql -uroot -p tpcc < create_table.sql 
$ ./tpcc_load -h localhost -P 33006 -d tpcc -u root -p Abcd123# -w 2

注：-w表示warehouse表的数据量，其它表会按照与这张表的比例关系插入测试数据

创建索引

1	mysql -uroot -p tpcc < add_fkey_idx.sql

启动测试

1	tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -c connections -r warmup_time -l running_time -i report_interval -f report_file -t trx_file

-w：指定仓库数量
-c：指定并发连接数
-r：定开始测试前进行warmup的时间，进行数据预热
-l：测试时长
-i：生成报告间隔时长
-f：指定生成的报告文件名，内容为timestamp对应新订单业务耗时
-t：指定生成的报告文件名，内容为线程对应的CPU TIME

报告分析

$ ./tpcc_start -h localhost -P33006 -d tpcc -uroot -p'Abcd123#' -S /service/mysql/data/mysqld.sock -w 2 -c 32 -r 10 -l 60 -f tpcc_mysql_report

***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value 'localhost'
option P with value '33006'
option d with value 'tpcc'
option u with value 'root'
option p with value 'Abcd123#'
option S (socket) with value '/service/mysql/data/mysqld.sock'
option w with value '2'
option c with value '32'
option r with value '10'
option l with value '60'
option f with value 'tpcc_mysql_report'
<Parameters>
     [server]: localhost
     [port]: 33006
     [DBname]: tpcc
       [user]: root
       [pass]: Abcd123#
  [warehouse]: 2
 [connection]: 32
     [rampup]: 10 (sec.)
    [measure]: 60 (sec.)

RAMP-UP TIME.(10 sec.)

MEASURING START.

  10, trx: 2499, 95%: 24.037, 99%: 35.546, max_rt: 163.790, 2499|231.605, 250|15.369, 250|266.731, 249|42.192
  20, trx: 2526, 95%: 23.857, 99%: 32.561, max_rt: 48.550, 2523|222.804, 252|16.321, 253|114.683, 253|58.269
  30, trx: 2473, 95%: 23.002, 99%: 31.497, max_rt: 68.122, 2477|226.836, 248|20.634, 247|193.410, 248|50.240
  40, trx: 2433, 95%: 24.065, 99%: 38.022, max_rt: 94.433, 2433|270.165, 243|20.539, 244|134.019, 243|42.868
  50, trx: 2478, 95%: 22.715, 99%: 30.568, max_rt: 69.342, 2475|246.118, 248|13.359, 247|110.248, 247|50.651
  60, trx: 2469, 95%: 22.981, 99%: 31.122, max_rt: 65.746, 2471|257.489, 247|21.066, 247|145.311, 247|52.866

STOPPING THREADS................................

<Raw Results>
  [0] sc:314 lt:14564  rt:0  fl:0 avg_rt: 14.5 (5)
  [1] sc:25 lt:14853  rt:0  fl:0 avg_rt: 124.9 (5)
  [2] sc:1154 lt:334  rt:0  fl:0 avg_rt: 4.7 (5)
  [3] sc:1062 lt:426  rt:0  fl:0 avg_rt: 85.1 (80)
  [4] sc:1159 lt:328  rt:0  fl:0 avg_rt: 19.4 (20)
 in 60 sec.

<Raw Results2(sum ver.)>
  [0] sc:314  lt:14564  rt:0  fl:0 
  [1] sc:25  lt:14853  rt:0  fl:0 
  [2] sc:1154  lt:334  rt:0  fl:0 
  [3] sc:1062  lt:426  rt:0  fl:0 
  [4] sc:1159  lt:328  rt:0  fl:0 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 2.11%  [NG] *
        Payment: 0.17%  [NG] *
   Order-Status: 77.55%  [NG] *
       Delivery: 71.37%  [NG] *
    Stock-Level: 77.94%  [NG] *

<TpmC>
                 14878.000 TpmC

MEASURING START：第一列为10秒间隔时间，第二列为10秒内执行新订单交易数，第三列为95%新订单交易平均时间，第四列为99%新订单交易平均时间，第五列为新订单交易最长响应时间，第五列为支付业务的结果：吞吐量|最大响应时间，第六列为发货业务的结果，第七列为库存业务的结果
Raw Results：分别对应五个业务模块，依次是新订单、支付、订单查询、发货、库存。sc:success表示操作成功的量，rt:retry表示操作重试的量，fl:failure为操作失败的量，avg_rt为平均重试次数
transaction percentage：各个业务的占比
response time：各个业务的响应时间

注：tpcc-mysql测试指标为TPMC，即一分钟的事务量，换算成TPS也就是248

mysqlslap

mysqlslap是mysql自带的基准测试功能，可以通过工具自动生成测试数据测试，也可以自身业务内容进行压力测试。详情参考：mysqlslap

[root@t-luhx03-v-szzb ~]# mysqlslap --user=root --password=Abcd123# --concurrency=100 --number-int-cols=5 --number-char-cols=20 --auto-generate-sql
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
	Average number of seconds to run all queries: 0.490 seconds
	Minimum number of seconds to run all queries: 0.490 seconds
	Maximum number of seconds to run all queries: 0.490 seconds
	Number of clients running queries: 100
	Average number of queries per client: 0

我们可以通过自定义表数据，并通过–query选项来指定业务操作脚本，其中我们可以利用substring(MD5(rand()) from 1 for 32)来随机生成字符串，模拟业务增长场景。

当然我们可以看到mysqlslap的输出结果过于简单，这里我们可以通过NMON收集测试过程中的系统状态信息，通过mysqladmin -uroot -p extended-status输出数据库状态信息，对结果进行筛选汇总能得到一个更全面的信息。