sysbench压测自装MySQL数据库

42 阅读 0 评论 28 点赞

我是靠谱客的博主激昂路灯，最近开发中收集的这篇文章主要介绍sysbench压测自装MySQL数据库，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

压测准备
测试机器
2vCPUs | 4GB | s6.large.2 CentOS 7.6 64bit

建立测试库
create database test_db
character set utf8mb4 collate utf8mb4_bin;
下载sysbench
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
sysbench --version
sysbench 1.0.19
准备测试表
sysbench
//基于mysql的驱动去连接mysql数据库
–db-driver=mysql
//连续访问300秒
–time=300
//10个线程模拟并发访问
–threads=10
//每隔1秒输出一下压测情况
–report-interval=1
//本机
–mysql-host=127.0.0.1
//端口号：3306
–mysql-port=3306
//测试用户
–mysql-user=root
//测试密码
–mysql-password=*******
//测试数据库
–mysql-db=test_db
//模拟新建20个表
–tables=20
//100万条数据执行oltp数据库的读写测试
–table_size=1000000 oltp_read_write
//参照这个命令的设置去构造出来我们需要的数据库里的数据
//自动创建20个测试表，每个表里创建100万条测试数据
–db-ps-mode=disable prepare
执行命令之后：

Initializing worker threads…

Creating table ‘sbtest9’…
Creating table ‘sbtest1’…
Creating table ‘sbtest4’…
Creating table ‘sbtest2’…
Creating table ‘sbtest5’…
Creating table ‘sbtest7’…
Creating table ‘sbtest6’…
Creating table ‘sbtest3’…
Creating table ‘sbtest8’…
Creating table ‘sbtest10’…
Inserting 1000000 records into ‘sbtest9’
Inserting 1000000 records into ‘sbtest4’
Inserting 1000000 records into ‘sbtest5’
Inserting 1000000 records into ‘sbtest8’
Inserting 1000000 records into ‘sbtest6’
Inserting 1000000 records into ‘sbtest2’
Inserting 1000000 records into ‘sbtest7’
Inserting 1000000 records into ‘sbtest1’
Inserting 1000000 records into ‘sbtest10’
Inserting 1000000 records into ‘sbtest3’
测试指标
thds: 10 tps: 489.10 qps: 9735.09 (r/w/o: 6817.46/1939.42/978.21) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
thds 压测线程数
tps 每秒事务数
qps 每秒请求数
(r/w/o) 每秒的请求数中读请求个数/写请求个数/其他请求个数
lat(ms,95%) 95% 的请求延迟都在多少以下
err/s 错误数
reconn/s 重连数
SQL statistics:
queries performed:
read: 1785882
write: 510252
other: 255126
total: 2551260
transactions: 127563 (425.09 per sec.)
queries: 2551260 (8501.77 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 300.0839s
total number of events: 127563

Latency (ms):
min: 4.24
avg: 23.52
max: 1087.40
95th percentile: 69.29
sum: 2999819.79

Threads fairness:
events (avg/stddev): 12756.3000/52.74
execution time (avg/stddev): 299.9820/0.00
300s内执行了178万读请求，50万写请求，25万其他请求，总共执行了255万请求。
总共执行事务12万，每秒425次事务
总共执行255万次请求，每秒8500次请求
总用时：300s，127563次事务
延迟最小 4.24ms
平均请求 23.52ms
延迟最大 1087ms
95% 的请求延迟在69ms
开始测试
综合TPS，oltp_read_write
测试命令
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=’******’ --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run

测试数据
[ 261s ] thds: 10 tps: 489.10 qps: 9735.09 (r/w/o: 6817.46/1939.42/978.21) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 262s ] thds: 10 tps: 398.03 qps: 8034.62 (r/w/o: 5613.43/1625.12/796.06) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
[ 263s ] thds: 10 tps: 325.93 qps: 6459.53 (r/w/o: 4532.97/1275.71/650.85) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 264s ] thds: 10 tps: 499.17 qps: 9961.43 (r/w/o: 6975.40/1986.68/999.34) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 265s ] thds: 10 tps: 431.96 qps: 8644.16 (r/w/o: 6038.42/1741.83/863.92) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00
[ 266s ] thds: 10 tps: 433.91 qps: 8723.24 (r/w/o: 6108.77/1747.65/866.83) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
[ 267s ] thds: 10 tps: 450.07 qps: 8997.40 (r/w/o: 6302.98/1793.28/901.14) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00
测试结果
SQL statistics:
queries performed:
read: 1785882
write: 510252
other: 255126
total: 2551260
transactions: 127563 (425.09 per sec.)
queries: 2551260 (8501.77 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 300.0839s
total number of events: 127563

Latency (ms):
min: 4.24
avg: 23.52
max: 1087.40
95th percentile: 69.29
sum: 2999819.79

Threads fairness:
events (avg/stddev): 12756.3000/52.74
execution time (avg/stddev): 299.9820/0.00

只读性能，oltp_read_only
测试命令
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=’******’ --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_only --db-ps-mode=disable run

测试数据
[ 199s ] thds: 10 tps: 867.92 qps: 13910.67 (r/w/o: 12174.84/0.00/1735.83) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 10 tps: 907.19 qps: 14500.97 (r/w/o: 12686.60/0.00/1814.37) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
[ 201s ] thds: 10 tps: 913.33 qps: 14610.29 (r/w/o: 12783.63/0.00/1826.66) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
[ 202s ] thds: 10 tps: 904.80 qps: 14486.87 (r/w/o: 12677.26/0.00/1809.61) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 203s ] thds: 10 tps: 895.75 qps: 14329.00 (r/w/o: 12537.50/0.00/1791.50) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 204s ] thds: 10 tps: 920.16 qps: 14715.57 (r/w/o: 12877.25/0.00/1838.32) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 205s ] thds: 10 tps: 913.00 qps: 14635.93 (r/w/o: 12807.94/0.00/1827.99) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 206s ] thds: 10 tps: 908.77 qps: 14509.38 (r/w/o: 12692.83/0.00/1816.55) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
测试结果
SQL statistics:
queries performed:
read: 3682476
write: 0
other: 526068
total: 4208544
transactions: 263034 (876.75 per sec.)
queries: 4208544 (14027.94 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 300.0102s
total number of events: 263034

Latency (ms):
min: 1.66
avg: 11.40
max: 183.17
95th percentile: 15.27
sum: 2999435.93

Threads fairness:
events (avg/stddev): 26303.4000/298.92
execution time (avg/stddev): 299.9436/0.00
删除性能，oltp_delete
测试命令
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=’******’ --mysql-db=test_db --tables=20 --table_size=1000000 oltp_delete --db-ps-mode=disable run

测试数据
[ 183s ] thds: 10 tps: 3732.66 qps: 3732.66 (r/w/o: 0.00/1143.90/2588.76) lat (ms,95%): 7.56 err/s: 0.00 reconn/s: 0.00
[ 184s ] thds: 10 tps: 3741.75 qps: 3741.75 (r/w/o: 0.00/1129.92/2611.82) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
[ 185s ] thds: 10 tps: 4053.34 qps: 4053.34 (r/w/o: 0.00/1261.11/2792.23) lat (ms,95%): 7.70 err/s: 0.00 reconn/s: 0.00
[ 186s ] thds: 10 tps: 3831.98 qps: 3831.98 (r/w/o: 0.00/1133.99/2697.98) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 187s ] thds: 10 tps: 3996.16 qps: 3996.16 (r/w/o: 0.00/1188.05/2808.11) lat (ms,95%): 7.56 err/s: 0.00 reconn/s: 0.00
[ 188s ] thds: 10 tps: 4178.94 qps: 4178.94 (r/w/o: 0.00/1249.98/2928.95) lat (ms,95%): 7.17 err/s: 0.00 reconn/s: 0.00
[ 189s ] thds: 10 tps: 3654.09 qps: 3654.09 (r/w/o: 0.00/1054.03/2600.06) lat (ms,95%): 8.90 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 10 tps: 3700.97 qps: 3700.97 (r/w/o: 0.00/1120.99/2579.98) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
测试结果
SQL statistics:
queries performed:
read: 0
write: 366172
other: 552067
total: 918239
transactions: 918239 (3060.73 per sec.)
queries: 918239 (3060.73 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 300.0048s
total number of events: 918239

Latency (ms):
min: 0.04
avg: 3.27
max: 586.35
95th percentile: 9.39
sum: 2999188.03

Threads fairness:
events (avg/stddev): 91823.9000/400.61
execution time (avg/stddev): 299.9188/0.00
更新索引字段性能，oltp_update_index
测试命令
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=’******’ --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_index --db-ps-mode=disable run

测试数据
[ 222s ] thds: 10 tps: 4390.82 qps: 4390.82 (r/w/o: 0.00/968.18/3422.64) lat (ms,95%): 7.56 err/s: 0.00 reconn/s: 0.00
[ 223s ] thds: 10 tps: 4451.01 qps: 4451.01 (r/w/o: 0.00/1029.23/3421.78) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 224s ] thds: 10 tps: 4508.21 qps: 4508.21 (r/w/o: 0.00/1062.05/3446.16) lat (ms,95%): 7.56 err/s: 0.00 reconn/s: 0.00
[ 225s ] thds: 10 tps: 3429.97 qps: 3429.97 (r/w/o: 0.00/811.76/2618.21) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 226s ] thds: 10 tps: 4667.34 qps: 4667.34 (r/w/o: 0.00/1093.31/3574.03) lat (ms,95%): 7.30 err/s: 0.00 reconn/s: 0.00
[ 227s ] thds: 10 tps: 4238.59 qps: 4238.59 (r/w/o: 0.00/990.90/3247.68) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00
[ 228s ] thds: 10 tps: 4300.49 qps: 4300.49 (r/w/o: 0.00/1092.87/3207.62) lat (ms,95%): 7.43 err/s: 0.00 reconn/s: 0.00
[ 229s ] thds: 10 tps: 4447.64 qps: 4447.64 (r/w/o: 0.00/1079.15/3368.48) lat (ms,95%): 7.70 err/s: 0.00 reconn/s: 0.00
测试结果
SQL statistics:
queries performed:
read: 0
write: 295467
other: 936875
total: 1232342
transactions: 1232342 (4107.59 per sec.)
queries: 1232342 (4107.59 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 300.0140s
total number of events: 1232342

Latency (ms):
min: 0.04
avg: 2.43
max: 1148.12
95th percentile: 7.70
sum: 2998912.79

Threads fairness:
events (avg/stddev): 123234.2000/607.91
execution time (avg/stddev): 299.8913/0.00
更新非索引字段性能，oltp_update_non_index
测试命令
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=’******’ --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_non_index --db-ps-mode=disable run

测试数据
[ 215s ] thds: 10 tps: 4941.69 qps: 4941.69 (r/w/o: 0.00/1194.92/3746.76) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00
[ 216s ] thds: 10 tps: 4107.03 qps: 4107.03 (r/w/o: 0.00/992.77/3114.26) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 217s ] thds: 10 tps: 4584.08 qps: 4585.08 (r/w/o: 0.00/1044.25/3540.83) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 218s ] thds: 10 tps: 4947.58 qps: 4946.58 (r/w/o: 0.00/1200.14/3746.44) lat (ms,95%): 6.21 err/s: 0.00 reconn/s: 0.00
[ 219s ] thds: 10 tps: 5302.95 qps: 5302.95 (r/w/o: 0.00/1248.99/4053.96) lat (ms,95%): 6.09 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 10 tps: 4318.10 qps: 4318.10 (r/w/o: 0.00/1063.02/3255.08) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00
[ 221s ] thds: 10 tps: 5652.77 qps: 5652.77 (r/w/o: 0.00/1363.94/4288.83) lat (ms,95%): 7.30 err/s: 0.00 reconn/s: 0.00
[ 222s ] thds: 10 tps: 4128.77 qps: 4128.77 (r/w/o: 0.00/973.95/3154.83) lat (ms,95%): 9.56 err/s: 0.00 reconn/s: 0.00
测试结果
SQL statistics:
queries performed:
read: 0
write: 322361
other: 1025120
total: 1347481
transactions: 1347481 (4491.48 per sec.)
queries: 1347481 (4491.48 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 300.0065s
total number of events: 1347481

Latency (ms):
min: 0.05
avg: 2.23
max: 784.62
95th percentile: 7.04
sum: 2998814.89

Threads fairness:
events (avg/stddev): 134748.1000/644.68
execution time (avg/stddev): 299.8815/0.00
插入性能，oltp_insert
测试命令
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=’******’ --mysql-db=test_db --tables=20 --table_size=1000000 oltp_insert --db-ps-mode=disable run

测试数据
[ 230s ] thds: 10 tps: 1655.99 qps: 1655.99 (r/w/o: 0.00/1655.99/0.00) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 231s ] thds: 10 tps: 1745.25 qps: 1745.25 (r/w/o: 0.00/1745.25/0.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 232s ] thds: 10 tps: 1669.10 qps: 1669.10 (r/w/o: 0.00/1669.10/0.00) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 233s ] thds: 10 tps: 1676.79 qps: 1676.79 (r/w/o: 0.00/1676.79/0.00) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 234s ] thds: 10 tps: 1721.36 qps: 1721.36 (r/w/o: 0.00/1721.36/0.00) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
[ 235s ] thds: 10 tps: 1639.59 qps: 1639.59 (r/w/o: 0.00/1639.59/0.00) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 236s ] thds: 10 tps: 1800.01 qps: 1800.01 (r/w/o: 0.00/1800.01/0.00) lat (ms,95%): 9.91 err/s: 0.00 reconn/s: 0.00
[ 237s ] thds: 10 tps: 1674.80 qps: 1674.80 (r/w/o: 0.00/1674.80/0.00) lat (ms,95%): 9.91 err/s: 0.00 reconn/s: 0.00
测试结果
SQL statistics:
queries performed:
read: 0
write: 527182
other: 0
total: 527182
transactions: 527182 (1757.22 per sec.)
queries: 527182 (1757.22 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 300.0081s
total number of events: 527182

Latency (ms):
min: 1.68
avg: 5.69
max: 525.61
95th percentile: 10.65
sum: 2998239.06

Threads fairness:
events (avg/stddev): 52718.2000/60.72
execution time (avg/stddev): 299.8239/0.00
写入性能，oltp_write_only
测试命令
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=’******’ --mysql-db=test_db --tables=20 --table_size=1000000 oltp_write_only --db-ps-mode=disable run

测试数据
[ 86s ] thds: 10 tps: 1112.14 qps: 6636.85 (r/w/o: 0.00/2819.36/3817.49) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00
[ 87s ] thds: 10 tps: 1122.80 qps: 6736.82 (r/w/o: 0.00/2850.50/3886.32) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00
[ 88s ] thds: 10 tps: 1226.66 qps: 7369.96 (r/w/o: 0.00/3021.16/4348.79) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00
[ 89s ] thds: 10 tps: 645.13 qps: 3896.80 (r/w/o: 0.00/1665.34/2231.46) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 10 tps: 1062.22 qps: 6373.30 (r/w/o: 0.00/2721.55/3651.74) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 91s ] thds: 10 tps: 984.95 qps: 5870.71 (r/w/o: 0.00/2502.88/3367.84) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 92s ] thds: 10 tps: 1144.76 qps: 6870.55 (r/w/o: 0.00/2963.37/3907.17) lat (ms,95%): 22.69 err/s: 0.00 reconn/s: 0.00
[ 93s ] thds: 10 tps: 1018.26 qps: 6146.56 (r/w/o: 0.00/2684.68/3461.88) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
测试结果
SQL statistics:
queries performed:
read: 0
write: 803503
other: 926111
total: 1729614
transactions: 288269 (960.86 per sec.)
queries: 1729614 (5765.13 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 300.0115s
total number of events: 288269

Latency (ms):
min: 1.95
avg: 10.41
max: 413.10
95th percentile: 44.17
sum: 2999460.47

Threads fairness:
events (avg/stddev): 28826.9000/33.90
execution time (avg/stddev): 299.9460/0.00
测试完成-CleanUp
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=’**’ --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup

Dropping table ‘sbtest1’…
Dropping table ‘sbtest2’…
Dropping table ‘sbtest3’…
Dropping table ‘sbtest4’…
Dropping table ‘sbtest5’…
Dropping table ‘sbtest6’…
Dropping table ‘sbtest7’…
Dropping table ‘sbtest8’…
Dropping table ‘sbtest9’…
Dropping table ‘sbtest10’…
Dropping table ‘sbtest11’…
Dropping table ‘sbtest12’…
Dropping table ‘sbtest13’…
Dropping table ‘sbtest14’…
Dropping table ‘sbtest15’…
Dropping table ‘sbtest16’…
Dropping table ‘sbtest17’…
Dropping table ‘sbtest18’…
Dropping table ‘sbtest19’…
Dropping table ‘sbtest20’…
测试汇总
condition read write other total transactions tps qps min avg max 95th
oltp_read_write 1785882 510252 255126 2551260 127563 425.09 8501.77 4.24ms 23.52ms 1087.40ms 69.29ms
oltp_read_only 3682476 0 526068 4208544 263034 876.75 14027.94 1.66ms 11.40ms 183.17ms 15.27ms
oltp_delete 0 366172 552067 918239 918239 3060.73 3060.73 0.04ms 3.27ms 586.35ms 9.39ms
oltp_update_index 0 295467 936875 1232342 1232342 4107.59 4107.59 0.04ms 2.43ms 1148.12ms 7.70ms
oltp_update_non_index 0 322361 1025120 1347481 1347481 4491.48 4491.48 0.05ms 2.23ms 784.62ms 7.04ms
oltp_insert 0 527182 0 527182 527182 1757.22 1757.22 1.68ms 5.69ms 525.61ms 10.65ms
oltp_write_only 0 803503 926111 1729614 288269 960.86 5765.13 1.95ms 10.41ms 431.10ms 44.17ms
龙华大道1号http://www.kinghill.cn/LongHuaDaDao1Hao/index.html