MySQL_sysbench基准测试

如果你没有真正的对服务器上的MySQL进行基准测试，就无法了解其真实情况到底是如何。

基准测试是数据库工程师必备的技能之一，否则你如何知道自己真的在优化数据库？

为什么需要基测？

基测可以观察系统在不同压力下的行为：

• 验证基于系统的一些假设是否符合实际情况。
• 测试当前的运行情况，如果连这都不知道你如何进行优化？
• 模拟比当前系统更高的负载，找出系统的平静。
• 测试不同的硬件、软件操作系统配置。
• 证明新采购的设备是否正确配置。

基测工具

针对MySQL的基准测试工具就有很多了，但是我们推荐使用sysbench（较为简单）和Percona的TPCC-MySQL（面向复杂的场景）。

Sysbech测试案例

接下来的本章将着重讲解如何使用sysbench测试MySQL实例。

下载

• Debian/Ubuntu

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

• RHEL/Centos

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

• macOS

# Add --with-postgresql if you need PostgreSQL support
brew install sysbench

查看脚本

sysbench支持更加复杂的测试，同时自身也内嵌了一些关于OLTP系统的测试，我们可以使用find / -name oltp*.lua来查找目录，然后跳转至相应目录查看全部脚本

OLTP(Online Transaction Processing) 一般指我们

$ find / -name oltp*.lua  #查找sysbench自带的数据写入脚本的路径,后面执行命令需要用到

/usr/share/sysbench/oltp_delete.lua
/usr/share/sysbench/oltp_update_non_index.lua
/usr/share/sysbench/oltp_read_write.lua
/usr/share/sysbench/oltp_update_index.lua
/usr/share/sysbench/oltp_read_only.lua
/usr/share/sysbench/oltp_common.lua
/usr/share/sysbench/oltp_write_only.lua
/usr/share/sysbench/oltp_point_select.lua
/usr/share/sysbench/oltp_insert.lua
/usr/share/sysbench/tests/include/oltp_legacy/oltp_simple.lua
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua

$ ll /usr/share/sysbench/
total 64
-rwxr-xr-x 1 root root  1452 Apr 25  2020 bulk_insert.lua
-rw-r--r-- 1 root root 14369 Apr 25  2020 oltp_common.lua
-rwxr-xr-x 1 root root  1290 Apr 25  2020 oltp_delete.lua
-rwxr-xr-x 1 root root  2415 Apr 25  2020 oltp_insert.lua
-rwxr-xr-x 1 root root  1265 Apr 25  2020 oltp_point_select.lua
-rwxr-xr-x 1 root root  1649 Apr 25  2020 oltp_read_only.lua
-rwxr-xr-x 1 root root  1824 Apr 25  2020 oltp_read_write.lua
-rwxr-xr-x 1 root root  1118 Apr 25  2020 oltp_update_index.lua
-rwxr-xr-x 1 root root  1127 Apr 25  2020 oltp_update_non_index.lua
-rwxr-xr-x 1 root root  1440 Apr 25  2020 oltp_write_only.lua
-rwxr-xr-x 1 root root  1919 Apr 25  2020 select_random_points.lua
-rwxr-xr-x 1 root root  2118 Apr 25  2020 select_random_ranges.lua
drwxr-xr-x 4 root root  4096 Nov  4 16:02 tests

小试牛刀

接下来我们将sysbench正式作用于我们的服务器， 笔者准备了一台2C4G的云服务器、MySQL5.7、默认MySQL配置。
首先我们需要进入MySQL创建一个测试数据库名为sysbench_test：

$ mysql -uroot -p

mysql> create database sysbench_test

mysql> quit

对于数据库的测试，我们一般会使用到sysbench的oltp脚本， 而其中分为读、写、读写混合等多种场景。

oltp测试步骤基本上分为：准备数据(prepare) - 执行测试(run) - 清理数据(cleanup) 三个步骤。而一般我们只需要用相同的参数运行不同的命令即可。

准备数据：

sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sysbench_test --mysql-user=root --mysql-password=xxxxx --table_size=100 --tables=10 --threads=20 --report-interval=10 --time=120 prepare

# oltp_read_write 指定sysbench内嵌的测试脚本
### 准备时参数
# --mysql-host/db/user/passowrd/port 测试数据库地址/名称/用户名/密码/端口
# --tables 指定生成的表数量　
# --table_size 指定每张表表的数据量

### 运行时桉树
# --thread 指定测试时的线程数
# --report-interval 指定运行时日志打印间隔
# --time 指定测试时长

执行测试：

$ sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sysbench_test --mysql-user=root --mysql-password=xxx --table_size=100 --tables=10 --threads=20 --report-interval=10 --time=120 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

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


Initializing worker threads...

Threads started!

[ 10s ] thds: 20 tps: 298.33 qps: 6897.07 (r/w/o: 4973.02/1270.50/653.55) lat (ms,95%): 161.51 err/s: 54.99 reconn/s: 0.00
[ 20s ] thds: 20 tps: 325.41 qps: 7474.29 (r/w/o: 5380.34/1384.24/709.72) lat (ms,95%): 147.61 err/s: 58.80 reconn/s: 0.00
[ 30s ] thds: 20 tps: 289.30 qps: 6699.44 (r/w/o: 4828.16/1236.99/634.29) lat (ms,95%): 164.45 err/s: 55.70 reconn/s: 0.00
[ 40s ] thds: 20 tps: 326.50 qps: 7444.30 (r/w/o: 5354.60/1380.80/708.90) lat (ms,95%): 150.29 err/s: 55.90 reconn/s: 0.00
[ 50s ] thds: 20 tps: 326.40 qps: 7480.10 (r/w/o: 5383.30/1385.90/710.90) lat (ms,95%): 150.29 err/s: 58.10 reconn/s: 0.00
[ 60s ] thds: 20 tps: 338.10 qps: 7669.44 (r/w/o: 5508.03/1429.91/731.50) lat (ms,95%): 147.61 err/s: 55.30 reconn/s: 0.00
[ 70s ] thds: 20 tps: 320.50 qps: 7305.44 (r/w/o: 5251.46/1358.39/695.59) lat (ms,95%): 153.02 err/s: 54.70 reconn/s: 0.00
[ 80s ] thds: 20 tps: 361.80 qps: 8212.92 (r/w/o: 5901.02/1528.60/783.30) lat (ms,95%): 137.35 err/s: 59.60 reconn/s: 0.00
[ 90s ] thds: 20 tps: 345.80 qps: 7903.20 (r/w/o: 5684.10/1467.20/751.90) lat (ms,95%): 139.85 err/s: 60.30 reconn/s: 0.00
[ 100s ] thds: 20 tps: 351.00 qps: 8032.68 (r/w/o: 5779.16/1489.81/763.71) lat (ms,95%): 134.90 err/s: 61.70 reconn/s: 0.00
[ 110s ] thds: 20 tps: 348.10 qps: 7964.29 (r/w/o: 5730.19/1476.70/757.40) lat (ms,95%): 137.35 err/s: 61.20 reconn/s: 0.00
[ 120s ] thds: 20 tps: 343.40 qps: 7899.49 (r/w/o: 5690.09/1459.50/749.90) lat (ms,95%): 134.90 err/s: 63.10 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            654696
        write:                           168754
        other:                           86531
        total:                           909981
    transactions:                        39767  (331.22 per sec.)
    queries:                             909981 (7579.29 per sec.)
    ignored errors:                      6997   (58.28 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0600s
    total number of events:              39767

Latency (ms):
         min:                                    4.99
         avg:                                   60.36
         max:                                  407.99
         95th percentile:                      147.61
         sum:                              2400503.17

Threads fairness:
    events (avg/stddev):           1988.3500/25.31
    execution time (avg/stddev):   120.0252/0.02

重要的参数指标为SQL statistics下的queries(即QPS)：达到了7579 per sec.
也就是说当前MySQL在10张表100条数据的情况下面对20个线程的读写吞吐量达到了7579QPS。
当然这只是在小数据小压力的情况下 ，并不能直接作为线上环境的参考。

那么接下来让我们清理数据准备下一轮测试

加大压力

刚刚只是尝试一下sysbench的功能，这次我们开始使用较大的压力对MySQL进行读测试（一般对MySQL的读写测试应该分开单独的进行以更好的评估MySQL哪些方面需要提升）。

• 测试操作：读
• 线程数：100
• 表大小：100w
• 表数量：10
• 测试时长：5分钟

对应执行命令：

sysbench oltp_read_only --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sysbench_test --mysql-user=root --mysql-password=[yourpassowrd] --table_size=1000000 --tables=10 --threads=100 --report-interval=10 --time=300 [command]

测试结果：

可以看到我们的MySQL在100个并发访问线程下对于10张表，每张表100w数据进行5分钟的读写测试。能够达到1w的读性能， 还是非常不错的。

测试写性能

现在让我们清理一下测试数据，最后测试一轮写性能

• 测试操作：写
• 线程数：100
• 表大小：100w
• 表数量：10
• 测试时长：3分钟

测试命令：

sysbench oltp_write_only --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sysbench_test --mysql-user=root --mysql-password=[yourpassowrd] --table_size=1000000 --tables=10 --threads=100 --report-interval=10 --time=300 [command]

测试结果：

可以看到测试机器上的这个MySQL能够达到9k接近1w的写QPS。

总结

基本测试是我们了解MySQL所要掌握的必备技能之一，而sysbench是一款多线程的性能测试工具。 使用它我们就能够对MySQL的读写QPS有一个非常好的了解。