0%

MySQL性能测试

sysbench

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

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

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

1
2
3
4
5
$ 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选项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[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

执行性能测试

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
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脚本,这里是更新索引列上的值

测试导入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/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

执行测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/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

执行后会出现如下输出

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
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

清理测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/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数据量则保持不变,因此能更好模拟业务不断增长下的性能表现。
tpcc-table
安装依赖包及编译程序包

1
2
3
$ yum install bzr -y
$ cd ./src
$ make

初始化测试数据

1
2
3
4
5
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

报告分析

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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
$ ./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

1
2
3
4
5
6
7
8
[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输出数据库状态信息,对结果进行筛选汇总能得到一个更全面的信息。