2023年7月3日发(作者：)

MySQL：数据库压⼒测试报告⽂章⽬录1 压⼒测试环境1.1 服务器配置类别OSDISKMySQLSysbench名称虚拟机 CentOS release 6.5 (Final)765GBv5.6.27v0.52 性能需求1. 测试innodb buffer pool设置为24G和44G这两种情形下的性能差距；2. 测试操作系统cpu个数在8和12这两种情形下的性能差距；测试操作系统cpu个数在8和12这两种情形下的性能差距；3. 测试表加索引和不加索引时数据库性能差距；测试表加索引和不加索引时数据库性能差距；4. 测试硬盘的随机读、随机写、随机读写、顺序写、顺序读、顺序读写等所有模式的iops、吞吐量。测试硬盘的随机读、随机写、随机读写、顺序写、顺序读、顺序读写等所有模式的iops、吞吐量。3 准备⼯作利⽤现在⽣产MySQL备库搭建压⼒测试环境，在测试时，停掉备库的slave复制：说明：本次测试，只测试在不同情况下的select查询，⽤来测试的sql如下：SELECT pad FROM 1 where k = ‘xxxxxx; 表结构为：数据量为100万⾏：4 专业术语1. QPS：Queries Per Second意思是“每秒查询率”，是⼀台服务器每秒能够相应的查询次数，是对⼀个特定的查询服务器在规定时间内所处理流量多少的衡量标准。2. cpu负载：通过top命令的load average获取1分钟内的平均值，它代表了任务队列的平均长度。cpu负载：通过top命令的loadaverage获取1分钟内的平均值，它代表了任务队列的平均长度。5 测试过程执⾏下⾯的命令准备测试数据：sysbench --test=/usr/local/src/sysbench-0.5/sysbench/tests/db/ --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/#选⽤的测试脚本(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/ --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负载，每组数据重复测试三次后取平均值，具体数据对⽐如下表所⽰：上表对应的QPS折线图如下所⽰：从QPS折线图可以看出，当sysbench的并发测试线程数⼩于128时，有索引的QPS在1万2左右。 这主要是因为当sysbench并发线程少时，数据库性能没有得到充分的发挥。当sysbench的并发测试线程到128时，此时MySQL的性能就得到了充分的发挥，有索引的QPS达到了1万5左右。如果继续增加并发测试线程数，有索引的QPS稍有下降，但是还在1万3左右，还是不错的。这时，我们再观察⽆索引的情况,⽆论并发测试线程数是多少，⽆索引的QPS都是11，也就是，⽆索引时数据库每秒只能处理11个select查询，这对⾼并发的业务简直不可接受。这说明了索引对数据库的性能影响是多么巨⼤。上表对应的每条SQL执⾏时间折线图如下所⽰：从每条SQL执⾏时间的折线图来看，⽆索引的sql执⾏时间随着并发测试线程数的增加⽽增加。也就是说，本来单条sql执⾏时间是1s，但是线程数越多，其执⾏时间越长，如上图，当线程数128时，其执⾏时间已经由1s升到10.379s了。这时，我们再观察有索引的每条sql执⾏时间，不论线程数是多少，其执⾏时间都不会超过1s，可见有索引和⽆索引的性能差距太⼤了。上表对应的cpu负载折线图如下所⽰：从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，其他配置都不变，再进⾏压⼒测试，看有什么变化。上表对应的QPS折线图如下所⽰：从上图可以看到，当线程数为512时，有索引的qps开始骤降；但⽆索引的qps不论线程数是多少，都是14。上表对应的每条SQL执⾏时间折线图如下所⽰：从上图可以看到，随着线程数的增加，⽆索引的每条sql执⾏时间在增加；⽽有索引的每条sql平均执⾏时间不到1s。上表对应的cpu负载折线图如下所⽰：从上图可以看到，随着线程数的增加，cpu负载也在增加，但是当线程数为256时，有索引的cpu负载要⽐⽆索引的⾼，这个暂时没法解释。5.3 当buffer pool =44G和cpu= 12时的压⼒测试情况下⾯我们把innodb buffer pool由24G升⾄44G，其他配置都不变，再进⾏压⼒测试，看有什么变化。上表对应的QPS折线图如下所⽰：从上图可以看到，当线程数为512时，有索引的qps开始骤降；但⽆索引的qps不论线程数是多少，都是14。上表对应的每条SQL执⾏时间折线图如下所⽰：从上图可以看到，随着线程数的增加，⽆索引的每条sql平均执⾏时间在增加；⽽有索引的每条sql平均执⾏时间不到1s。上表对应的cpu负载折线图如下所⽰：从上图可以看到，随着线程数的增加，cpu负载也在增加，但是当线程数为256时，有索引的cpu负载要⽐⽆索引的⾼，这种现象暂时没法解释。5.4 磁盘ioio测试脚本：[root@Mysql03 test]# cat #!/bin/shset -uset -xset -efor 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 done sysbench --test=fileio --file-total-size=$size cleanup done donedone得到如下数据：因为测试磁盘io会影响⽣产系统，所以只测试了上⾯⼏组数据，没有对顺序读、顺序写、顺序读写、随机读、随机写、随机读写等全⾯测试，即使测试可能意义也不⼤。因为磁盘是机械硬盘，按理应该是220，上⾯出现1万的情况，因为硬盘有闪存。6 测试结论有索引的qps在1万2左右，没索引的qps只有14，两者相差1000倍；有索引的sql执⾏时间不论线程数是多少都不到⼀秒，⽽⽆索引的sql随着线程数的增加，其执⾏时间也会增加，最⾼到132s，相差倍数可是千倍万倍；数据库的线程数达到128时，会使数据库性能明显下降；当增加cpu和内存时，也不能很好的解决这个问题，这可能是和linux 内核参数配置的不合适导致，后期仔细研究这些参数，使数据库性能上⼀个新的台阶；磁盘IO能⼒固定，只能从数据库和操作系统参数着⼿。完