MySQL:如何压测出漂亮的性能数据

老板让你做一个MySQL的性能基准测试，测来测去发现明明机器配置很高，但 tps 就是上不去，为什么？首先我们很容易想到的就是InnoDB缓冲池设置的不够大、redo log太小、数据没有充分预热、sysbench 线程数开的太少...这些是很常见的原因，今天我们来看一些不那么显而易见的情况。

以下案例的硬件配置为：64core、256Gmemory、raid 10 15K机械盘。

网络瓶颈

一次压测结果是这样的：

sysbench oltp_read_write --mysql-host=10.18x.xx.104 --mysql-port=3308  \
--mysql-user=sysbench --mysql-password=sysbench --mysql-db=sbtest --tables=10 \
--table-size=10000000 --report-interval=5 --threads=200 --time=600 run

##结果：
SQL statistics:
    queries performed:
        read:                            4682958
        write:                           1337988
        other:                           668994
        total:                           6689940
    transactions:                        334497 (2783.82 per sec.)
    queries:                             6689940 (55676.32 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

结果 tps 只有 2800，显然对不起这么高的硬件，这时候就得观察负载了，一般最明显的一点就是 CPU 使用率低，比如这个案例在我的环境上 CPU 使用率只有36%，而网络流量很高124MB/s，差不多就是千兆的带宽了(1000Mb/s)：

这时候就得意识到“我是不是用错网卡了”？为啥这么说呢，因为一般都会配两块网卡分别访问内外网，快速查看网络接口：ifconfig -a|grep -B 1 inet。立马就看到确实是双网卡，我们压测用的是em3，还有另外一个网络接口vlan2：

然后再确认下带宽：ethtool {dev_name} |grep Speed。em3是千兆带宽，压测时124MB/s的流量已经打满了：

所以在sysbench压测时换成 vlan2 万兆网络接口后（即修改 --mysql-host=10.0.xx.104），轻松到1万。如果实在没有万兆网卡怎么办？那就在本地使用回环接口进行测试吧。下图是使用3个网口测试的网络流量图，明显可以看出千兆网口的网络瓶颈：

SSL

MySQL8.0或者MySQL5.7企业版压测时会遇到一个坑：默认开启SSL，压测结果 tps 只有3700：

sysbench oltp_read_write --mysql-host=10.18x.xx.104 --mysql-port=3308  \
--mysql-user=sysbench --mysql-password=sysbench --mysql-db=sbtest --tables=10 \
--table-size=10000000 --report-interval=5 --threads=200 --time=600 run

##结果：
SQL statistics:
    queries performed:
        read:                            6303388
        write:                           1800968
        other:                           900484
        total:                           9004840
    transactions:                        450242 (3747.71 per sec.)
    queries:                             9004840 (74954.25 per sec.)
Latency (ms):
         min:                                    6.35
         avg:                                   53.31
         max:                                  542.71
         95th percentile:                      104.84
         sum:                             24004566.42

和前面说的一样，观察系统负载，发现CPU使用率已经用到了80%，但是CPU system 时间反常的高，再去看压测结果95%的响应时间也很高需要100多毫秒，正是因为需要消耗大量的系统资源进行加密连接：

解决办法就是配置文件写入 skip-ssl 重启 MySQL。其实 sysbench 有个参数 --mysql-ssl[=on|off]，看说明只要设置为 off 就可以关闭 ssl 连接，但实测并没有用，通过select * from status_by_thread where VARIABLE_NAME like '%ssl%';查看连接还是开启 ssl 的，对 ssl 机制比较懵逼，欢迎有了解的大佬留言。

sort_buffer_size

sort_buffer_size 会影响 sysbench 性能吗？官方文档有这样的描述：

On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values.

也许你会觉得没啥影响，但实际看下面这组测试结果：11000tps VS 930tps

## sort_buffer_size=31M
SQL statistics:
    queries performed:
        read:                            16358846
        write:                           4673956
        other:                           2336978
        total:                           23369780
    transactions:                        1168489 (11678.75 per sec.)
    queries:                             23369780 (233574.94 per sec.)

## sort_buffer_size=32M
SQL statistics:
    queries performed:
        read:                            392182
        write:                           112052
        other:                           56026
        total:                           560260
    transactions:                        28013  (930.07 per sec.)
    queries:                             560260 (18601.38 per sec.)

事实就是这么不可思议，当 sort_buffer_size 达到阈值32M后（我测试的阈值和文档给出的阈值2M不一样），内存分配算法发生改变，内存分配效率变低，CPU system 时间剧增：

总结

当压测结果不乐观，第一时间去看CPU使用率，只要总使用率低，或者 iowait、system 高，都是异常情况，需要去排查原因。my.cnf 规范模板可以解决大部分的压测结果异常的问题，另一方面则需要我们掌握基本的分析方法，再配合一些过往经验，就能测出理想的数据了。