目录
更新索引字段性能(oltp_update_index模式 )
更新非索引字段性能(oltp_update_non_index模式 )
压测前准备
机器配置:
# 我用的是虚拟机安装的MySQL,怎么玩看自己
4vCPUs | 8GB | CentOS 7.3 64bit
MySQL数据库版本:
5.7.32
创建测试库:
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工具版本:
sysbench 1.0.19
测试表准备
可以通过下面这条指令,基于sysbench指令构造测试表和测试数据:
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=12345678 --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare
参数分析
# 基于mysql的驱动去连接mysql数据库,如果是oracle、SQLserver就是用对应的数据库驱动
--db-driver=mysql
# 连续访问300秒
--time=300
# 10个线程模拟并发访问
--threads=10
# 每隔1秒输出一下压测情况
--report-interval=1
# 数据库所在机器的IP地址
--mysql-host=127.0.0.1
# 端口号:3306
--mysql-port=3306
# 用户名
--mysql-user=test_user
# 密码
--mysql-password=12345678
# 压测数据库
--mysql-db=test_db
# 模拟构造20个测试表
--tables=20
# 每个表构造100万条测试数据,测试表的名字会是类似于sbtest1,sbtest2这样
--table_size=1000000
# 执行oltp数据库的读写测试
oltp_read_write
# 禁止ps模式
--db-ps-mode=disable
# 参照这个命令的设置去构造出来我们需要的数据库里的数据
# 自动创建20个测试表,每个表里创建100万条测试数据
prepare
执行命令之后,界面输出:
Initializing worker threads...
Creating table 'sbtest7'...
Creating table 'sbtest9'...
Creating table 'sbtest5'...
Creating table 'sbtest8'...
Creating table 'sbtest3'...
Creating table 'sbtest4'...
Creating table 'sbtest1'...
Creating table 'sbtest6'...
Creating table 'sbtest2'...
Creating table 'sbtest10'...
Inserting 1000000 records into 'sbtest1'
Inserting 1000000 records into 'sbtest5'
Inserting 1000000 records into 'sbtest3'
Inserting 1000000 records into 'sbtest8'
Inserting 1000000 records into 'sbtest4'
Inserting 1000000 records into 'sbtest7'
Inserting 1000000 records into 'sbtest9'
Inserting 1000000 records into 'sbtest2'
Inserting 1000000 records into 'sbtest6'
Inserting 1000000 records into 'sbtest10'
待测试表和测试数据构造完毕之后就可以开始进行压测了~
压测结果分析
每秒输出的压测报告如下:
# 测试命令执行过程中,每秒会输出类似下面这段内容的压测报告
[ 300s ] thds: 10 tps: 8.07 qps: 177.56 (r/w/o: 121.06/40.35/16.14) lat (ms,95%): 1327.91 err/s: 0.00 reconn/s: 0.00
(每秒输出)压测报告分析:
# 第300秒输出的压测报告
[ 300s ]
# 压测线程数量:10个
thds: 10
# 每秒执行事务数量:8.07个
tps: 8.07
# 每秒执行请求数量:177.56个
qps: 177.56
# 每秒的请求数中,读请求个数/写请求个数/其他请求个数
# 每秒(177.56个)请求中,读请求:121.06个、写请求:40.35个,其他请求:16.14个
(r/w/o: 121.06/40.35/16.14)
# 95%的请求延迟在1327.91ms以下
lat (ms,95%): 1327.91
# 每秒请求数中的失败数量
err/s: 0.00
# 每秒请求数中的重连数量
reconn/s: 0.00
压测完成之后,会显示一个总压测报告,内容及分析如下:
SQL statistics:
queries performed:
read: 51814 # 在300s的压测时间内执行了5万多次读请求
write: 14804 # 压测时间内执行了1万多次写请求
other: 7402 # 压测时间内执行了7千多次其他请求
total: 74020 # 压测时间内一共执行了7万多请求
transactions: 3701 (12.32 per sec.) # 压测时间内一共执行了3701个事务,每秒执行12.32个事务
queries: 74020 (246.35 per sec.) # 压测时间内一共执行了7.4万多次请求,每秒执行246.35次请求
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
# 一共执行了300s的压测,执行了3701个事务
General statistics:
total time: 300.4640s
total number of events: 3701
Latency (ms):
min: 2.96 # 请求中延迟最小的是2.96ms
avg: 811.65 # 所有请求的平均延迟是811.65ms
max: 4516.35 # 请求中延迟最大的是4516.35ms
95th percentile: 1869.60 # 95%的请求延迟在1869.60ms以内
sum: 3003916.38
Threads fairness:
events (avg/stddev): 370.1000/9.20
execution time (avg/stddev): 300.3916/0.14
开始压测
综合TPS(oltp_read_write模式)
测试命令:
# 命令最后不是prepare,是run了,意思就是运行压测
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test --mysql-password=12345678 --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run
压测过程中,每秒输出的压测报告:
# 这里只是截取出来的部分输出内容
Running the test with following options:
Number of threads: 10
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 10 tps: 0.99 qps: 199.95 (r/w/o: 153.19/34.82/11.94) lat (ms,95%): 694.45 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 10 tps: 10.03 qps: 182.64 (r/w/o: 136.48/26.09/20.07) lat (ms,95%): 1618.78 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 10 tps: 7.99 qps: 133.76 (r/w/o: 91.83/25.95/15.97) lat (ms,95%): 1869.60 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 10 tps: 8.01 qps: 180.28 (r/w/o: 124.20/40.06/16.03) lat (ms,95%): 1836.24 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 10 tps: 11.01 qps: 201.10 (r/w/o: 145.07/34.02/22.01) lat (ms,95%): 1533.66 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 10 tps: 10.00 qps: 212.92 (r/w/o: 144.95/47.98/19.99) lat (ms,95%): 1453.01 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 10 tps: 4.99 qps: 78.79 (r/w/o: 51.86/16.95/9.97) lat (ms,95%): 1506.29 err/s: 0.00 reconn/s: 0.00
......
[ 294s ] thds: 10 tps: 0.00 qps: 150.40 (r/w/o: 116.76/33.64/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 295s ] thds: 10 tps: 9.03 qps: 174.56 (r/w/o: 122.39/34.11/18.06) lat (ms,95%): 1973.38 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 15.32 qps: 302.29 (r/w/o: 217.52/54.13/30.64) lat (ms,95%): 1149.76 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 20.01 qps: 381.15 (r/w/o: 262.10/79.03/40.02) lat (ms,95%): 1191.92 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 14.01 qps: 214.12 (r/w/o: 146.08/40.02/28.02) lat (ms,95%): 773.68 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 17.85 qps: 315.32 (r/w/o: 216.17/63.46/35.70) lat (ms,95%): 1304.21 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 8.07 qps: 177.56 (r/w/o: 121.06/40.35/16.14) lat (ms,95%): 1327.91 err/s: 0.00 reconn/s: 0.00
测试结果:
SQL statistics:
queries performed:
read: 51814
write: 14804
other: 7402
total: 74020
transactions: 3701 (12.32 per sec.)
queries: 74020 (246.35 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.4640s
total number of events: 3701
Latency (ms):
min: 2.96
avg: 811.65
max: 4516.35
95th percentile: 1869.60
sum: 3003916.38
Threads fairness:
events (avg/stddev): 370.1000/9.20
execution time (avg/stddev): 300.3916/0.14
只读性能(oltp_read_only模式 )
测试命令:
# 命令中的oltp_read_write已经变为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=test --mysql-password=12345678 --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_only --db-ps-mode=disable run
压测过程中,每秒输出的压测报告:
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 10
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 10 tps: 150.79 qps: 2499.46 (r/w/o: 2187.90/0.00/311.56) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 10 tps: 158.02 qps: 2534.27 (r/w/o: 2218.24/0.00/316.03) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 10 tps: 198.44 qps: 3149.12 (r/w/o: 2752.24/0.00/396.88) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 10 tps: 178.50 qps: 2867.05 (r/w/o: 2510.05/0.00/357.00) lat (ms,95%): 231.53 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 10 tps: 174.87 qps: 2814.85 (r/w/o: 2465.11/0.00/349.73) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 10 tps: 223.15 qps: 3548.41 (r/w/o: 3102.11/0.00/446.30) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 10 tps: 189.02 qps: 3064.34 (r/w/o: 2686.29/0.00/378.04) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 10 tps: 184.90 qps: 2951.38 (r/w/o: 2581.58/0.00/369.80) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00
......
[ 295s ] thds: 10 tps: 3381.18 qps: 54086.89 (r/w/o: 47324.53/0.00/6762.36) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 3318.25 qps: 53097.96 (r/w/o: 46461.47/0.00/6636.49) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 3346.59 qps: 53547.42 (r/w/o: 46854.25/0.00/6693.18) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 3346.55 qps: 53562.78 (r/w/o: 46869.68/0.00/6693.10) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 3366.59 qps: 53847.43 (r/w/o: 47115.25/0.00/6732.18) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 3347.53 qps: 53570.47 (r/w/o: 46875.41/0.00/6695.06) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00
测试结果:
SQL statistics:
queries performed:
read: 11724524
write: 0
other: 1674932
total: 13399456
transactions: 837466 (2791.50 per sec.)
queries: 13399456 (44664.05 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0043s
total number of events: 837466
Latency (ms):
min: 1.00
avg: 3.58
max: 1146.30
95th percentile: 4.03
sum: 2998671.04
Threads fairness:
events (avg/stddev): 83746.6000/776.59
execution time (avg/stddev): 299.8671/0.01
删除性能(oltp_delete模式 )
测试命令:
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test --mysql-password=12345678 --mysql-db=test_db --tables=20 --table_size=1000000 oltp_delete --db-ps-mode=disable run
压测过程中每秒输出压测报告:
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 10
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 10 tps: 420.52 qps: 420.52 (r/w/o: 0.00/420.52/0.00) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 10 tps: 122.08 qps: 122.08 (r/w/o: 0.00/122.08/0.00) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 10 tps: 49.18 qps: 49.18 (r/w/o: 0.00/49.18/0.00) lat (ms,95%): 909.80 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 10 tps: 270.18 qps: 270.18 (r/w/o: 0.00/268.18/2.00) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00
......
[ 294s ] thds: 10 tps: 156.95 qps: 156.95 (r/w/o: 0.00/145.25/11.70) lat (ms,95%): 475.79 err/s: 0.00 reconn/s: 0.00
[ 295s ] thds: 10 tps: 35.91 qps: 35.91 (r/w/o: 0.00/34.88/1.03) lat (ms,95%): 877.61 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 27.07 qps: 27.07 (r/w/o: 0.00/24.06/3.01) lat (ms,95%): 682.06 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 140.00 qps: 140.00 (r/w/o: 0.00/129.00/11.00) lat (ms,95%): 1109.09 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 184.75 qps: 184.75 (r/w/o: 0.00/172.83/11.92) lat (ms,95%): 102.97 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
压测结果:
SQL statistics:
queries performed:
read: 0
write: 21303
other: 900
total: 22203
transactions: 22203 (73.88 per sec.)
queries: 22203 (73.88 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.5215s
total number of events: 22203
Latency (ms):
min: 0.05
avg: 135.35
max: 2596.49
95th percentile: 846.57
sum: 3005165.33
Threads fairness:
events (avg/stddev): 2220.3000/16.70
execution time (avg/stddev): 300.5165/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=test --mysql-password=12345678 --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_index --db-ps-mode=disable run
压测过程中每秒输出压测报告:
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 10
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 10 tps: 270.62 qps: 270.62 (r/w/o: 0.00/254.88/15.75) lat (ms,95%): 101.13 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 10 tps: 232.49 qps: 232.49 (r/w/o: 0.00/212.36/20.13) lat (ms,95%): 101.13 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 10 tps: 151.27 qps: 151.27 (r/w/o: 0.00/142.20/9.08) lat (ms,95%): 746.32 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 10 tps: 271.84 qps: 271.84 (r/w/o: 0.00/251.85/19.99) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 10 tps: 5.75 qps: 5.75 (r/w/o: 0.00/5.75/0.00) lat (ms,95%): 1258.08 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 10 tps: 186.05 qps: 186.05 (r/w/o: 0.00/168.28/17.77) lat (ms,95%): 434.83 err/s: 0.00 reconn/s: 0.00
......
[ 295s ] thds: 10 tps: 21.01 qps: 21.01 (r/w/o: 0.00/20.01/1.00) lat (ms,95%): 1191.92 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 208.03 qps: 208.03 (r/w/o: 0.00/189.02/19.00) lat (ms,95%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 14.91 qps: 14.91 (r/w/o: 0.00/12.92/1.99) lat (ms,95%): 1050.76 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 12.05 qps: 12.05 (r/w/o: 0.00/10.04/2.01) lat (ms,95%): 502.20 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 22.54 qps: 22.54 (r/w/o: 0.00/21.56/0.98) lat (ms,95%): 1170.65 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 12.27 qps: 12.27 (r/w/o: 0.00/11.25/1.02) lat (ms,95%): 1213.57 err/s: 0.00 reconn/s: 0.00
压测结果:
SQL statistics:
queries performed:
read: 0
write: 12396
other: 1064
total: 13460
transactions: 13460 (44.76 per sec.)
queries: 13460 (44.76 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.6863s
total number of events: 13460
Latency (ms):
min: 0.06
avg: 223.11
max: 2541.52
95th percentile: 1013.60
sum: 3003113.54
Threads fairness:
events (avg/stddev): 1346.0000/13.31
execution time (avg/stddev): 300.3114/0.19
更新非索引字段性能(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=test --mysql-password=12345678 --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_non_index --db-ps-mode=disable run
压测过程中每秒输出压测报告:
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 10
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 10 tps: 470.88 qps: 470.88 (r/w/o: 0.00/430.07/40.82) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 10 tps: 195.01 qps: 195.01 (r/w/o: 0.00/181.01/14.00) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 10 tps: 15.97 qps: 15.97 (r/w/o: 0.00/13.98/2.00) lat (ms,95%): 861.95 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 10 tps: 101.48 qps: 101.48 (r/w/o: 0.00/91.43/10.05) lat (ms,95%): 1013.60 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 10 tps: 147.92 qps: 147.92 (r/w/o: 0.00/136.92/10.99) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00
......
[ 295s ] thds: 10 tps: 66.02 qps: 66.02 (r/w/o: 0.00/63.02/3.00) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 18.65 qps: 18.65 (r/w/o: 0.00/17.67/0.98) lat (ms,95%): 1258.08 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 175.07 qps: 175.07 (r/w/o: 0.00/165.01/10.06) lat (ms,95%): 682.06 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 47.43 qps: 47.43 (r/w/o: 0.00/45.45/1.98) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 362.57 qps: 362.57 (r/w/o: 0.00/325.69/36.87) lat (ms,95%): 86.00 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 26.96 qps: 26.96 (r/w/o: 0.00/26.96/0.00) lat (ms,95%): 1129.24 err/s: 0.00 reconn/s: 0.00
测试结果:
SQL statistics:
queries performed:
read: 0
write: 40517
other: 3565
total: 44082
transactions: 44082 (146.90 per sec.)
queries: 44082 (146.90 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0783s
total number of events: 44082
Latency (ms):
min: 0.05
avg: 68.07
max: 2797.30
95th percentile: 231.53
sum: 3000641.17
Threads fairness:
events (avg/stddev): 4408.2000/28.54
execution time (avg/stddev): 300.0641/0.01
插入性能(oltp_insert模式 )
测试命令:
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test --mysql-password=12345678 --mysql-db=test_db --tables=20 --table_size=1000000 oltp_insert --db-ps-mode=disable run
压测过程中每秒输出压测报告:
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 10
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 10 tps: 259.00 qps: 259.00 (r/w/o: 0.00/259.00/0.00) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 10 tps: 181.48 qps: 181.48 (r/w/o: 0.00/181.48/0.00) lat (ms,95%): 549.52 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 10 tps: 260.01 qps: 260.01 (r/w/o: 0.00/260.01/0.00) lat (ms,95%): 127.81 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 10 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 10 tps: 195.74 qps: 195.74 (r/w/o: 0.00/195.74/0.00) lat (ms,95%): 1129.24 err/s: 0.00 reconn/s: 0.00
......
[ 295s ] thds: 10 tps: 196.52 qps: 196.52 (r/w/o: 0.00/196.52/0.00) lat (ms,95%): 267.41 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 11.75 qps: 11.75 (r/w/o: 0.00/11.75/0.00) lat (ms,95%): 1069.86 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 20.44 qps: 20.44 (r/w/o: 0.00/20.44/0.00) lat (ms,95%): 995.51 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 297.17 qps: 297.17 (r/w/o: 0.00/297.17/0.00) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 74.98 qps: 74.98 (r/w/o: 0.00/74.98/0.00) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 11.00 qps: 11.00 (r/w/o: 0.00/11.00/0.00) lat (ms,95%): 1213.57 err/s: 0.00 reconn/s: 0.00
[ 301s ] thds: 7 tps: 5.98 qps: 5.98 (r/w/o: 0.00/5.98/0.00) lat (ms,95%): 669.89 err/s: 0.00 reconn/s: 0.00
压测结果:
SQL statistics:
queries performed:
read: 0
write: 20600
other: 0
total: 20600
transactions: 20600 (68.41 per sec.)
queries: 20600 (68.41 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 301.1423s
total number of events: 20600
Latency (ms):
min: 0.48
avg: 146.04
max: 4482.33
95th percentile: 846.57
sum: 3008453.03
Threads fairness:
events (avg/stddev): 2060.0000/10.58
execution time (avg/stddev): 300.8453/0.24
写入性能(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=test --mysql-password=12345678 --mysql-db=test_db --tables=20 --table_size=1000000 oltp_write_only --db-ps-mode=disable run
每秒输出压测报告:
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 10
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 10 tps: 301.69 qps: 1840.14 (r/w/o: 0.00/1145.87/694.27) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 10 tps: 237.18 qps: 1442.34 (r/w/o: 0.00/898.52/543.82) lat (ms,95%): 164.45 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 10 tps: 58.59 qps: 343.44 (r/w/o: 0.00/216.34/127.10) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 10 tps: 15.65 qps: 66.77 (r/w/o: 0.00/35.47/31.30) lat (ms,95%): 1352.03 err/s: 0.00 reconn/s: 0.00
......
[ 295s ] thds: 10 tps: 1.00 qps: 16.00 (r/w/o: 0.00/14.00/2.00) lat (ms,95%): 95.81 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 15.10 qps: 77.51 (r/w/o: 0.00/47.31/30.20) lat (ms,95%): 1678.14 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 19.27 qps: 115.63 (r/w/o: 0.00/73.23/42.40) lat (ms,95%): 926.33 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 20.78 qps: 125.71 (r/w/o: 0.00/79.99/45.71) lat (ms,95%): 1013.60 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 20.98 qps: 123.90 (r/w/o: 0.00/76.94/46.96) lat (ms,95%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 0.00 qps: 39.98 (r/w/o: 0.00/38.99/1.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
压测结果:
SQL statistics:
queries performed:
read: 0
write: 26215
other: 15647
total: 41862
transactions: 6977 (23.24 per sec.)
queries: 41862 (139.43 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.2274s
total number of events: 6977
Latency (ms):
min: 1.20
avg: 430.30
max: 2321.44
95th percentile: 1213.57
sum: 3002230.29
Threads fairness:
events (avg/stddev): 697.7000/5.39
execution time (avg/stddev): 300.2230/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=test --mysql-password=12345678 --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup
压测数据汇总
模式 | 读 | 写 | 其他 | 总请求 | 事物 | TPS | QPS | 最小延迟(ms) | 最大延迟(ms) | 平均延迟(ms) | 95%(ms) |
---|---|---|---|---|---|---|---|---|---|---|---|
oltp_read_write | 51814 | 14804 | 7402 | 74020 | 3701 | 12.32 | 246.35 | 2.96 | 4516.35 | 811.65 | 1869.60 |
oltp_read_only | 11724524 | 0 | 1674932 | 13399456 | 837466 | 2791.50 | 44664.05 | 1.00 | 1146.30 | 3.58 | 4.03 |
oltp_delete | 0 | 21303 | 900 | 22203 | 22203 | 73.88 | 73.88 | 0.05 | 2596.49 | 135.35 | 846.57 |
oltp_update_index | 0 | 12396 | 1064 | 13460 | 13460 | 44.76 | 44.76 | 0.06 | 2541.52 | 223.11 | 1013.60 |
oltp_update_non_index | 0 | 40517 | 3565 | 44082 | 44082 | 146.90 | 146.90 | 0.05 | 2797.30 | 68.07 | 231.53 |
oltp_insert | 0 | 20600 | 0 | 20600 | 20600 | 68.41 | 68.41 | 0.48 | 4482.33 | 146.04 | 846.57 |
oltp_write_only | 0 | 26215 | 15647 | 41862 | 6977 | 23.24 | 139.43 | 1.20 | 2321.44 | 430.30 | 1213.57 |