12-09
12-09
12-09
12-09
12-09
12-09
12-09
12-09
12-09
12-09
12-09
12-09
ADADADADAD
mysql数据库 时间：2024-12-03 12:11:52
作者：文/会员上传
12-09
12-09
12-09
12-09
12-09
12-09
12-09
12-09
12-09
12-09
12-09
12-09
MySQL数据库压力测试报告
1 压力测试环境
1.1 服务器配置
类别 名称 OS 虚拟机 CentOS release 6.5 (Final) DISK 765GB MySQLl v5.6.27 Sysbench
以下为本文的正文内容，内容仅供参考！本站为公益性网站，复制本文以及下载DOC文档全部免费。
MySQL数据库压力测试报告
类别
名称
OS
虚拟机 CentOS release 6.5 (Final)
DISK
765GB
MySQLl
v5.6.27
Sysbench
v0.5
测试innodb buffer pool设置为24G和44G这两种情形下的性能差距；
测试操作系统cpu个数在8和12这两种情形下的性能差距；
测试表加索引和不加索引时数据库性能差距；
测试硬盘的随机读、随机写、随机读写、顺序写、顺序读、顺序读写等所有模式的iops、吞吐量。
利用现在生产MySQL备库搭建压力测试环境，在测试时，停掉备库的slave复制：
说明：本次测试，只测试在不同情况下的select查询，用来测试的sql如下：
SELECT pad FROM test.sbtest1 where k = ‘xxxxxx;
表结构为：
数据量为100万行：
QPS：Queries Per Second意思是“每秒查询率”，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。
cpu负载：通过top命令的load average获取1分钟内的平均值，它代表了任务队列的平均长度。
执行下面的命令准备测试数据：
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清理数据
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折线图如下所示：
从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，其他配置都不变，再进行压力测试，看有什么变化。
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折线图如下所示：
从上图可以看到，当线程数为512时，有索引的qps开始骤降；但无索引的qps不论线程数是多少，都是14。
上表对应的每条SQL执行时间折线图如下所示：
从上图可以看到，随着线程数的增加，无索引的每条sql执行时间在增加；而有索引的每条sql平均执行时间不到1s。
上表对应的cpu负载折线图如下所示：
从上图可以看到，随着线程数的增加，cpu负载也在增加，但是当线程数为256时，有索引的cpu负载要比无索引的高，这个暂时没法解释。
下面我们把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折线图如下所示：
从上图可以看到，当线程数为512时，有索引的qps开始骤降；但无索引的qps不论线程数是多少，都是14。
上表对应的每条SQL执行时间折线图如下所示：
从上图可以看到，随着线程数的增加，无索引的每条sql平均执行时间在增加；而有索引的每条sql平均执行时间不到1s。
上表对应的cpu负载折线图如下所示：
从上图可以看到，随着线程数的增加，cpu负载也在增加，但是当线程数为256时，有索引的cpu负载要比无索引的高，这种现象暂时没法解释。
io测试脚本：
[root@Mysql03test]# cat iotest.sh
#!/bin/sh
set -u
set -x
set -e
for size in 2G ;do
formode 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=$blksizerun | tee -a sysbench-size-$size-mode-$mode-threads-$threads-blksz-$blksize2>&1
done
done
sysbench --test=fileio --file-total-size=$size cleanup
done
done
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万的情况，因为硬盘有闪存。
有索引的qps在1万2左右，没索引的qps只有14，两者相差1000倍；
有索引的sql执行时间不论线程数是多少都不到一秒，而无索引的sql随着线程数的增加，其执行时间也会增加，最高到132s，相差倍数可是千倍万倍；
数据库的线程数达到128时，会使数据库性能明显下降；当增加cpu和内存时，也不能很好的解决这个问题，这可能是my.cnf和linux 内核参数配置的不合适导致，后期仔细研究这些参数，使数据库性能上一个新的台阶；
磁盘IO能力固定，只能从数据库和操作系统参数着手。
完
11-20
11-19
11-20
11-20
11-20
11-19
11-20
11-20
11-19
11-20
11-19
11-19
11-19
11-19
11-19
11-19