转载

MySQL数据库压力测试报告

MySQL数据库压力测试报告

1 压力测试环境

1.1 服务器配置

类别	名称
OS	虚拟机 CentOS release 6.5 (Final)
DISK	765GB
MySQLl	v5.6.27
Sysbench	v0.5

2 性能需求

测试innodb buffer pool设置为24G和44G这两种情形下的性能差距；
测试操作系统cpu个数在8和12这两种情形下的性能差距；
测试表加索引和不加索引时数据库性能差距；
测试硬盘的随机读、随机写、随机读写、顺序写、顺序读、顺序读写等所有模式的iops、吞吐量。

3 准备工作

利用现在生产MySQL备库搭建压力测试环境，在测试时，停掉备库的slave复制：

MySQL数据库压力测试报告

说明：本次测试，只测试在不同情况下的select查询，用来测试的sql如下：

SELECT pad FROM test.sbtest1 where k = ‘xxxxxx;

表结构为：

MySQL数据库压力测试报告

数据量为100万行：

MySQL数据库压力测试报告

4 专业术语

QPS：Queries Per Second意思是“每秒查询率”，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。

    cpu负载：通过top命令的load average获取1分钟内的平均值，它代表了任务队列的平均长度。

5 测试过程

执行下面的命令准备测试数据：

sysbench --test=/usr/local/src/sysbench-0.5/sysbench/tests/db/oltp.lua /

--mysql-host=localhost /

--mysql-user=root /

--mysql-password=xxxxx /

--mysql-db=test /

--oltp-tables-count=1 /

--oltp-table-size=1000000 /

--num-threads=50 /

--max-requests=1000000 /

--report-interval=1 /

prepare

上述命令会在MySQL的test数据库里面创建sbtest1表，数据量为100w行。

参数说明：

--mysql-host=locahost #数据库host

--mysql-port=3306 #数据库端口

--mysql-user=your_username #数据库用户名

--mysql-password=your_password #数据库密码

--mysql-db=your_db_for_test #数据库名

--oltp-tables-count=1 #模拟的表的个数，规格越高该值越大

--oltp-table-size=1000000 #模拟的每张表的行数，规格越高该值越大

--num-threads=50 #模拟的并发数量，规格越高该值越大

--max-requests=100000000 #最大请求次数

--report-interval=1 #每1秒打印一次当前的QPS等值

--test=/usr/local/src/sysbench-0.5/sysbench/tests/db/oltp.lua#选用的测试脚本(lua)，此脚本可以从sysbench-0.5源代码文件目录下找 [prepare | run | cleanup] #prepare准备数据，run执行测试，cleanup清理数据

5.1 当buffer pool =24G和cpu= 8时的压力测试情况

sysbench --test=/usr/local/src/sysbench-0.5/sysbench/tests/db/select.lua /

--mysql-host=localhost /

--mysql-user=root /

--mysql-password=xxxx /

--mysql-db=test /

--oltp-tables-count=1 /

--oltp-table-size=1000000 /

--num-threads=16 /

--max-requests=1000000 /

--report-interval=1 /

--max-time=60 /

run

说明：--num-threads=16 #模拟数据库线程并发数量，规格越高该值越大

--max-time=60#最大测试时间（与--max-requests只要有一个超过，则退出）。

利用sysbench测试了并发线程个数不同的情况下，分别执行最大请求次数为100w的 select操作,通过修改--num-threads可以获得不同并发线程数。
测试有索引和无索引这两种情况下的 QPS（QPS越大，系统性能越好），每条sql平均执行时间（每条sql执行时间越小，系统性能越好）， cpu负载，每组数据重复测试三次后取平均值，具体数据对比如下表所示：

buffer_pool：24G cpu个数：8	线程数	请求数	数据量	cpu负载	qps(r/s)	min(ms)	avg(ms)	max(ms)	95%
有索引	16	701116	100万行	3.21	11680	0.09	1.37	1274.83	0.73
没有索引	16	720	100万行	11	11	278	1345	5997	2373
有索引	32	707720	100万行	4.46	11737	0.08	2.71	1995	1.38
没有索引	32	688	100万行	21	11	686	2829	12666	5700
有索引	64	746723	100万行	18	12416	0.09	5.15	2253	9.52
没有索引	64	726	100万行	38	11	1587	5430	19856	11000
有索引	128	910519	100万行	36	15145	0.09	8.43	3147	20.17
没有索引	128	804	100万行	43	12	1525	10379	68659	58056
有索引	256	896962	100万行	80	14932	0.09	17.14	4945	38.97
没有索引	256	917	100万行	52	11	1673	20022	81671	77778
有索引	512	850414	100万行	194	14161	0.1	36.15	5750	321
没有索引	512	1133	100万行	59	11	1252	38431	107995	103269
有索引	1024	818863	100万行	252	13629	0.09	75	17780	401
没有索引	1024	1667	100万行	63	11	1535	66188	153542	147680
有索引	2048	799571	100万行	652	13282	0.1	153	9599	563
没有索引	2048	2595	100万行	72	10	1325	132090	265936	255631

上表对应的QPS折线图如下所示：

MySQL数据库压力测试报告

从QPS折线图可以看出，当sysbench的并发测试线程数小于128时，有索引的QPS在1万2左右。这主要是因为当sysbench并发线程少时，数据库性能没有得到充分的发挥。

当sysbench的并发测试线程到128时，此时MySQL的性能就得到了充分的发挥，有索引的QPS达到了1万5左右。如果继续增加并发测试线程数，有索引的QPS稍有下降，但是还在1万3左右，还是不错的。

这时，我们再观察无索引的情况,无论并发测试线程数是多少，无索引的QPS都是11，也就是，无索引时数据库每秒只能处理11个select查询，这对高并发的业务简直不可接受。这说明了索引对数据库的性能影响是多么巨大。

上表对应的每条SQL执行时间折线图如下所示：

MySQL数据库压力测试报告

从每条SQL执行时间的折线图来看，无索引的sql执行时间随着并发测试线程数的增加而增加。也就是说，本来单条sql执行时间是1s，但是线程数越多，其执行时间越长，如上图，当线程数128时，其执行时间已经由1s升到10.379s了。

这时，我们再观察有索引的每条sql执行时间，不论线程数是多少，其执行时间都不会超过1s，可见有索引和无索引的性能差距太大了。

上表对应的cpu负载折线图如下所示：

MySQL数据库压力测试报告

从cpu折线图来看，在无索引情况下，当线程数128时，cpu负载为43，这和我们生产系统发生故障情况是吻合的，即当我们数据库cpu负载在40~50时，确实有100左右的并发线程在数据库里面执行。

再来看有索引情况下cpu的负载情况，可以看到，当并发线程数128以上时，有索引的cpu负载骤然升高，甚至高于无索引的。关于这个现象，出乎我的预料，甚至很不理解。

后来，我仔细分析了linux 里面的cpu负载的含义，CPU负载显示的是一段时间内正在使用和等待使用CPU的平均任务数。不过，我也不好解释上述现象，只能列在这，供人参考。

小知识：

CPU负载怎么理解？是不是CPU利用率？
这里要区别CPU负载和CPU利用率，它们是不同的两个概念，但它们的信息可以在同一个top命令中进行显示。CPU利用率显示的是程序在运行期间实时占用的CPU百分比，而CPU负载显示的是一段时间内正在使用和等待使用CPU的平均任务数。CPU利用率高，并不意味着负载就一定大。网上有篇文章举了一个有趣比喻，拿打电话来说明两者的区别，我按自己的理解阐述一下。
某公用电话亭，有一个人在打电话，四个人在等待，每人限定使用电话一分钟，若有人一分钟之内没有打完电话，只能挂掉电话去排队，等待下一轮。电话在这里就相当于CPU，而正在或等待打电话的人就相当于任务数。
在电话亭使用过程中，肯定会有人打完电话走掉，有人没有打完电话而选择重新排队，更会有新增的人在这儿排队，这个人数的变化就相当于任务数的增减。为了统计平均负载情况，我们5秒钟统计一次人数，并在第1、5、15分钟的时候对统计情况取平均值，从而形成第1、5、15分钟的平均负载。
有的人拿起电话就打，一直打完1分钟，而有的人可能前三十秒在找电话号码，或者在犹豫要不要打，后三十秒才真正在打电话。如果把电话看作CPU，人数看作任务，我们就说前一个人（任务）的CPU利用率高，后一个人（任务）的CPU利用率低。
当然， CPU并不会在前三十秒工作，后三十秒歇着，只是说，有的程序涉及到大量的计算，所以CPU利用率就高，而有的程序牵涉到计算的部分很少，CPU利用率自然就低。但无论CPU的利用率是高是低，跟后面有多少任务在排队没有必然关系。

5.2 当buffer pool =24G和cpu= 12时的压力测试情况

下面我们把cpu由8加到12，其他配置都不变，再进行压力测试，看有什么变化。

buffer_pool：24G cpu个数：12	线程数	请求数	数据量	cpu负载	qps(r/s)	min(ms)	avg(ms)	max(ms)	95%
有索引	16	716917	100万行	3.4	11948	0.09	1.34	944	1.02
没有索引	16	873	100万行	10.34	14	282	1104	3224	2474
有索引	32	708405	100万行	7.48	11787	0.09	2.71	1159	1.71
没有索引	32	888	100万行	20	14	292	2193	7901	4749
有索引	64	719369	100万行	18.84	11920	0.08	5.37	1156	15
没有索引	64	898	100万行	40	14	638	4416	15063	9432
有索引	128	696889	100万行	10	11614	0.09	11	1157	29.32
没有索引	128	943	100万行	42	14	817	8686	67268	49821
有索引	256	681509	100万行	59	11588	0.1	22.53	2495	55
没有索引	256	1051	100万行	47	13	730	16978	78149	75079
有索引	512	704611	100万行	78	11730	0.1	43.63	2800	413
没有索引	512	1267	100万行	54	13	822	31718	96230	92691
有索引	1024	593684	100万行	204	9868	0.1	103	6522	545
没有索引	1024	1764	100万行	59	12	662	58212	139380	133509
有索引	2048	571730	100万行	196	8898	0.09	225	8748	948
没有索引	2048	2769	100万行	116	12	606	103518	214829	205576

上表对应的QPS折线图如下所示：

MySQL数据库压力测试报告

从上图可以看到，当线程数为512时，有索引的qps开始骤降；但无索引的qps不论线程数是多少，都是14。

上表对应的每条SQL执行时间折线图如下所示：

MySQL数据库压力测试报告

从上图可以看到，随着线程数的增加，无索引的每条sql执行时间在增加；而有索引的每条sql平均执行时间不到1s。

上表对应的cpu负载折线图如下所示：

MySQL数据库压力测试报告

从上图可以看到，随着线程数的增加，cpu负载也在增加，但是当线程数为256时，有索引的cpu负载要比无索引的高，这个暂时没法解释。

5.3 当buffer pool =44G和cpu= 12时的压力测试情况

下面我们把innodb buffer pool由24G升至44G，其他配置都不变，再进行压力测试，看有什么变化。

buffer_pool：44G cpu个数：12	线程数	请求数	数据量	cpu负载	qps(r/s)	min(ms)	avg(ms)	max(ms)	95%
有索引	16	709587	100万行	3.56	11826	0.09	1.35	544	1.06
没有索引	16	894	100万行	11	14	279	1087	3211	2471
有索引	32	693606	100万行	3.14	11503	0.08	2.77	1121	1.79
没有索引	32	907	100万行	20	14	549	2146	9473	4504
有索引	64	681148	100万行	10.21	11352	0.08	5.64	1880	16
没有索引	64	813	100万行	42	14	722	4828	15398	9626
有索引	128	654430	100万行	24	10906	0.09	11	1641	39
没有索引	128	934	100万行	40	14	774	12272	114780	61804
有索引	256	652889	100万行	67	10828	0.09	23	2561	90
没有索引	256	1091	100万行	47	14	770	16203	76963	72930
有索引	512	651804	100万行	143	10800	0.09	47	2816	448
没有索引	512	1325	100万行	88	14	506	30334	95839	90471
有索引	1024	466049	100万行	220	7109	0.1	140	18282	573
没有索引	1024	1818	100万行	68	14	730	54924	132208	126847
有索引	2048	578978	100万行	247	9574	0.09	212	5251	771
没有索引	2048	2807	100万行	105	14	616	100284	209988	200952

上表对应的QPS折线图如下所示：

MySQL数据库压力测试报告

从上图可以看到，当线程数为512时，有索引的qps开始骤降；但无索引的qps不论线程数是多少，都是14。

上表对应的每条SQL执行时间折线图如下所示：

MySQL数据库压力测试报告

从上图可以看到，随着线程数的增加，无索引的每条sql平均执行时间在增加；而有索引的每条sql平均执行时间不到1s。

上表对应的cpu负载折线图如下所示：

MySQL数据库压力测试报告

从上图可以看到，随着线程数的增加，cpu负载也在增加，但是当线程数为256时，有索引的cpu负载要比无索引的高，这种现象暂时没法解释。

5.4 磁盘io

io测试脚本：

[root@Mysql03 test]# cat iotest.sh

#!/bin/sh

set -u

set -x

set -e

for size in 2G ;do

for mode in seqrd seqrw rndrd rndwr rndrw;do

for blksize in 16384;do

sysbench --test=fileio --file-num=64 --file-total-size=$size prepare

for threads in 1 16 32 64 128 512 1024 2048;do

echo "====== testing $blksize in $threads threads"

echo PARAMS $size $mode $threads $blksize > sysbench-size-$size-mode-$mode-threads-$threads-blksz-$blksize

for i in 1 ;do

sysbench --test=fileio --file-total-size=$size --file-test-mode=$mode --max-time=180 --max-requests=100000000/

--num-threads=$threads --init-rng=on --file-num=64 --file-extra-flags=direct --file-fsync-freq=0/

--file-block-size=$blksize run | tee -a sysbench-size-$size-mode-$mode-threads-$threads-blksz-$blksize 2>&1

done

sysbench --test=fileio --file-total-size=$size cleanup

done

得到如下数据：

线程数	模式	数据块大小	吞吐量（Mb/s）	IOPS
1	顺序读	16k	59.639	3816.87
16	顺序读	16k	139.81	8948
32	顺序读	16k	158.85	10166.69
64	顺序读	16k	147	9451
128	顺序读	16k	149	9542
512	顺序读	16k	153	9853
1024	顺序读	16k	151	9712.16
2048	顺序读	16k	151	9666
1	随机读	16k	5	337
16	随机读	16k	41	2668
32	随机读	16k	61	3912.03
64	随机读	16k	61	3939.21
128	随机读	16k	61	3939

因为测试磁盘io会影响生产系统，所以只测试了上面几组数据，没有对顺序读、顺序写、顺序读写、随机读、随机写、随机读写等全面测试，即使测试可能意义也不大。

因为磁盘是机械硬盘，按理应该是220，上面出现1万的情况，因为硬盘有闪存。

6 测试结论

有索引的qps在1万2左右，没索引的qps只有14，两者相差1000倍；
有索引的sql执行时间不论线程数是多少都不到一秒，而无索引的sql随着线程数的增加，其执行时间也会增加，最高到132s，相差倍数可是千倍万倍；
数据库的线程数达到128时，会使数据库性能明显下降；当增加cpu和内存时，也不能很好的解决这个问题，这可能是my.cnf和linux 内核参数配置的不合适导致，后期仔细研究这些参数，使数据库性能上一个新的台阶；
磁盘IO能力固定，只能从数据库和操作系统参数着手。

完