1、压测准备
1.1、测试机器
| 机器 | 配置 |
|---|---|
| Mysql机器 | 4C8G |
| sysbench压力机 | 2C4G |
1.2、建立测试库（mysql所在机器）
create database test_db
character set utf8mb4 collate utf8mb4_bin;
1.3、下载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
```
1.4、准备测试表（压力机）
```
```
sysbench
//基于mysql的驱动去连接mysql数据库
--db-driver=mysql
//连续访问300秒
--time=300
//10个线程模拟并发访问
--threads=10
//每隔1秒输出一下压测情况
--report-interval=1
//mysql地址
--mysql-host=172.25.215.83
//端口号：3306
--mysql-port=3306
//测试用户
--mysql-user=root
//测试密码
--mysql-password=root123456
//测试数据库
--mysql-db=test_db
//模拟新建20个表
--tables=20
//100万条数据 执行oltp数据库的读写测试
--table_size=1000000 oltp_read_write
//参照这个命令的设置去构造出来我们需要的数据库里的数据
//自动创建20个测试表，每个表里创建100万条测试数据
--db-ps-mode=disable prepare
```
```
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=8.148.5.197--mysql-port=3306 --mysql-user=root --mysql-password=root123456 --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare
执行命令之后
Creating table 'sbtest6'...
Creating table 'sbtest8'...
Creating table 'sbtest1'...
Creating table 'sbtest4'...
Creating table 'sbtest7'...
Creating table 'sbtest9'...
Creating table 'sbtest10'...
Creating table 'sbtest3'...
Inserting 1000000 records into 'sbtest1'
Inserting 1000000 records into 'sbtest4'
Inserting 1000000 records into 'sbtest9'
Inserting 1000000 records into 'sbtest8'
Inserting 1000000 records into 'sbtest5'
Inserting 1000000 records into 'sbtest2'
Inserting 1000000 records into 'sbtest7'
Inserting 1000000 records into 'sbtest3'
Inserting 1000000 records into 'sbtest6'
Inserting 1000000 records into 'sbtest10'
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest10'...
1.5、测试指标
thds: 10 tps: 312.00 qps: 6242.09 (r/w/o: 4367.06/1251.02/624.01) 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: 1143282
write: 326652
other: 163326
total: 1633260
transactions: 81663 (272.20 per sec.)
queries: 1633260 (5444.04 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0075s
total number of events: 81663
Latency (ms):
min: 3.63
avg: 36.74
max: 618.79
95th percentile: 86.00
sum: 2999933.50
Threads fairness:
events (avg/stddev): 8166.3000/231.11
execution time (avg/stddev): 299.9934/0.00
- 300s内执行了114万+读请求，32万+写请求，16万+其他请求，总共执行了163万+请求。
- 总共执行事务8万+，每秒272次事务
- 总共执行163万+次请求，每秒5444次请求
- 总用时：300s，81663次事务
- 延迟最小 3.63ms
- 平均请求 36.74ms
- 延迟最大 618.79ms
- 95% 的请求延迟在86ms
2、开始测试
2.1、综合TPS，oltp_read_write
a、测试命令
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
b、测试数据
[ 290s ] thds: 10 tps: 297.01 qps: 6047.11 (r/w/o: 4242.08/1205.02/600.01) lat (ms,95%): 82.96 err/s: 0.00 reconn/s: 0.00
[ 291s ] thds: 10 tps: 301.00 qps: 6035.03 (r/w/o: 4218.02/1215.01/602.00) lat (ms,95%): 86.00 err/s: 0.00 reconn/s: 0.00
[ 292s ] thds: 10 tps: 308.00 qps: 6159.98 (r/w/o: 4311.99/1232.00/616.00) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 293s ] thds: 10 tps: 323.99 qps: 6347.72 (r/w/o: 4440.81/1258.94/647.97) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
[ 294s ] thds: 10 tps: 294.99 qps: 6031.82 (r/w/o: 4224.87/1216.96/589.98) lat (ms,95%): 92.42 err/s: 0.00 reconn/s: 0.00
[ 295s ] thds: 10 tps: 304.00 qps: 6071.96 (r/w/o: 4251.97/1211.99/608.00) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 296.00 qps: 5928.00 (r/w/o: 4148.00/1188.00/592.00) lat (ms,95%): 81.48 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 290.00 qps: 5786.98 (r/w/o: 4059.98/1147.00/580.00) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 306.03 qps: 6112.57 (r/w/o: 4279.40/1221.11/612.06) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 299.99 qps: 6012.84 (r/w/o: 4204.89/1207.97/599.98) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 312.00 qps: 6242.09 (r/w/o: 4367.06/1251.02/624.01) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
c、测试结果
SQL statistics:
queries performed:
read: 1143282
write: 326652
other: 163326
total: 1633260
transactions: 81663 (272.20 per sec.)
queries: 1633260 (5444.04 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0075s
total number of events: 81663
Latency (ms):
min: 3.63
avg: 36.74
max: 618.79
95th percentile: 86.00
sum: 2999933.50
Threads fairness:
events (avg/stddev): 8166.3000/231.11
execution time (avg/stddev): 299.9934/0.00
2.2、只读性能，oltp_read_only
a、测试命令
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
b、测试数据
[ 290s ] thds: 10 tps: 1966.02 qps: 31458.34 (r/w/o: 27526.30/0.00/3932.04) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 291s ] thds: 10 tps: 1966.63 qps: 31440.01 (r/w/o: 27510.76/0.00/3929.25) lat (ms,95%): 8.90 err/s: 0.00 reconn/s: 0.00
[ 292s ] thds: 10 tps: 1967.91 qps: 31461.62 (r/w/o: 27521.79/0.00/3939.83) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 293s ] thds: 10 tps: 1956.07 qps: 31369.19 (r/w/o: 27457.04/0.00/3912.15) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 294s ] thds: 10 tps: 1964.44 qps: 31400.00 (r/w/o: 27472.13/0.00/3927.87) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 295s ] thds: 10 tps: 1963.82 qps: 31416.07 (r/w/o: 27489.44/0.00/3926.63) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 1962.71 qps: 31394.29 (r/w/o: 27467.88/0.00/3926.41) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 1966.02 qps: 31453.37 (r/w/o: 27522.32/0.00/3931.05) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 1961.98 qps: 31412.72 (r/w/o: 27486.76/0.00/3925.97) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 1969.04 qps: 31513.72 (r/w/o: 27575.63/0.00/3938.09) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 1961.92 qps: 31385.70 (r/w/o: 27461.86/0.00/3923.84) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
c、测试结果
SQL statistics:
queries performed:
read: 8253882
write: 0
other: 1179126
total: 9433008
transactions: 589563 (1965.17 per sec.)
queries: 9433008 (31442.68 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0050s
total number of events: 589563
Latency (ms):
min: 2.18
avg: 5.09
max: 18.85
95th percentile: 8.58
sum: 2999429.43
Threads fairness:
events (avg/stddev): 58956.3000/6692.40
execution time (avg/stddev): 299.9429/0.01
2.3、删除性能，oltp_delete
a、测试命令
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
b、测试数据
[ 290s ] thds: 10 tps: 2591.97 qps: 2591.97 (r/w/o: 0.00/795.99/1795.98) lat (ms,95%): 29.72 err/s: 0.00 reconn/s: 0.00
[ 291s ] thds: 10 tps: 2609.99 qps: 2609.99 (r/w/o: 0.00/807.00/1802.99) lat (ms,95%): 29.19 err/s: 0.00 reconn/s: 0.00
[ 292s ] thds: 10 tps: 2743.97 qps: 2743.97 (r/w/o: 0.00/817.99/1925.98) lat (ms,95%): 20.00 err/s: 0.00 reconn/s: 0.00
[ 293s ] thds: 10 tps: 2406.05 qps: 2406.05 (r/w/o: 0.00/679.01/1727.03) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00
[ 294s ] thds: 10 tps: 2559.95 qps: 2559.95 (r/w/o: 0.00/767.98/1791.96) lat (ms,95%): 29.72 err/s: 0.00 reconn/s: 0.00
[ 295s ] thds: 10 tps: 2891.06 qps: 2891.06 (r/w/o: 0.00/819.02/2072.04) lat (ms,95%): 20.00 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 2522.95 qps: 2522.95 (r/w/o: 0.00/786.98/1735.96) lat (ms,95%): 22.28 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 2460.01 qps: 2460.01 (r/w/o: 0.00/746.00/1714.00) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 2396.06 qps: 2396.06 (r/w/o: 0.00/722.02/1674.04) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 2866.90 qps: 2866.90 (r/w/o: 0.00/805.97/2060.93) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 2291.91 qps: 2291.91 (r/w/o: 0.00/704.97/1586.94) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00
c、测试结果
SQL statistics:
queries performed:
read: 0
write: 259449
other: 249120
total: 508569
transactions: 508569 (1695.21 per sec.)
queries: 508569 (1695.21 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0027s
total number of events: 508569
Latency (ms):
min: 0.06
avg: 5.90
max: 130.16
95th percentile: 39.65
sum: 2999830.79
Threads fairness:
events (avg/stddev): 50856.9000/239.96
execution time (avg/stddev): 299.9831/0.00
2.4、更新索引字段性能，oltp_update_index
#### a、测试命令
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
b、测试数据
[ 290s ] thds: 10 tps: 2922.00 qps: 2922.00 (r/w/o: 0.00/895.00/2027.00) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
[ 291s ] thds: 10 tps: 2911.01 qps: 2911.01 (r/w/o: 0.00/873.00/2038.01) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00
[ 292s ] thds: 10 tps: 3068.99 qps: 3068.99 (r/w/o: 0.00/895.00/2173.99) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00
[ 293s ] thds: 10 tps: 3138.02 qps: 3138.02 (r/w/o: 0.00/906.01/2232.02) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00
[ 294s ] thds: 10 tps: 2986.00 qps: 2986.00 (r/w/o: 0.00/879.00/2107.00) lat (ms,95%): 21.89 err/s: 0.00 reconn/s: 0.00
[ 295s ] thds: 10 tps: 2973.99 qps: 2973.99 (r/w/o: 0.00/867.00/2106.99) lat (ms,95%): 28.16 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 3086.98 qps: 3086.98 (r/w/o: 0.00/907.99/2178.98) lat (ms,95%): 21.89 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 3053.01 qps: 3053.01 (r/w/o: 0.00/873.00/2180.01) lat (ms,95%): 20.00 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 2984.98 qps: 2984.98 (r/w/o: 0.00/853.00/2131.99) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 3071.02 qps: 3071.02 (r/w/o: 0.00/882.01/2189.02) lat (ms,95%): 20.00 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 2941.99 qps: 2941.99 (r/w/o: 0.00/878.00/2063.99) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00
c、测试结果
SQL statistics:
queries performed:
read: 0
write: 257855
other: 623379
total: 881234
transactions: 881234 (2937.17 per sec.)
queries: 881234 (2937.17 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0267s
total number of events: 881234
Latency (ms):
min: 0.07
avg: 3.40
max: 149.61
95th percentile: 22.28
sum: 2999957.92
Threads fairness:
events (avg/stddev): 88123.4000/529.86
execution time (avg/stddev): 299.9958/0.00
2.5、更新非索引字段性能，oltp_update_non_index
#### a、测试命令
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
b、测试数据
[ 290s ] thds: 10 tps: 4194.96 qps: 4194.96 (r/w/o: 0.00/1235.99/2958.97) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
[ 291s ] thds: 10 tps: 5558.03 qps: 5558.03 (r/w/o: 0.00/1679.01/3879.02) lat (ms,95%): 6.09 err/s: 0.00 reconn/s: 0.00
[ 292s ] thds: 10 tps: 4171.03 qps: 4171.03 (r/w/o: 0.00/1202.01/2969.02) lat (ms,95%): 9.73 err/s: 0.00 reconn/s: 0.00
[ 293s ] thds: 10 tps: 5229.97 qps: 5229.97 (r/w/o: 0.00/1521.99/3707.98) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 294s ] thds: 10 tps: 5088.01 qps: 5088.01 (r/w/o: 0.00/1525.00/3563.01) lat (ms,95%): 3.75 err/s: 0.00 reconn/s: 0.00
[ 295s ] thds: 10 tps: 4833.02 qps: 4833.02 (r/w/o: 0.00/1425.01/3408.02) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 5624.96 qps: 5624.96 (r/w/o: 0.00/1629.99/3994.97) lat (ms,95%): 6.09 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 4179.02 qps: 4179.02 (r/w/o: 0.00/1239.00/2940.01) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 5453.97 qps: 5453.97 (r/w/o: 0.00/1592.99/3860.98) lat (ms,95%): 6.09 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 4380.00 qps: 4380.00 (r/w/o: 0.00/1302.00/3078.00) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 4915.02 qps: 4915.02 (r/w/o: 0.00/1457.00/3458.01) lat (ms,95%): 6.67 err/s: 0.00 reconn/s: 0.00
c、测试结果
SQL statistics:
queries performed:
read: 0
write: 442745
other: 1068126
total: 1510871
transactions: 1510871 (5035.66 per sec.)
queries: 1510871 (5035.66 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0330s
total number of events: 1510871
Latency (ms):
min: 0.07
avg: 1.99
max: 88.04
95th percentile: 7.30
sum: 2999810.53
Threads fairness:
events (avg/stddev): 151087.1000/581.46
execution time (avg/stddev): 299.9811/0.01
2.6、插入性能，oltp_insert
a、测试命令
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
b、测试数据
[ 290s ] thds: 10 tps: 3606.98 qps: 3606.98 (r/w/o: 0.00/3606.98/0.00) lat (ms,95%): 7.30 err/s: 0.00 reconn/s: 0.00
[ 291s ] thds: 10 tps: 3600.04 qps: 3600.04 (r/w/o: 0.00/3600.04/0.00) lat (ms,95%): 7.43 err/s: 0.00 reconn/s: 0.00
[ 292s ] thds: 10 tps: 3437.99 qps: 3437.99 (r/w/o: 0.00/3437.99/0.00) lat (ms,95%): 7.56 err/s: 0.00 reconn/s: 0.00
[ 293s ] thds: 10 tps: 3433.98 qps: 3433.98 (r/w/o: 0.00/3433.98/0.00) lat (ms,95%): 7.17 err/s: 0.00 reconn/s: 0.00
[ 294s ] thds: 10 tps: 3391.02 qps: 3391.02 (r/w/o: 0.00/3391.02/0.00) lat (ms,95%): 4.82 err/s: 0.00 reconn/s: 0.00
[ 295s ] thds: 10 tps: 3411.99 qps: 3411.99 (r/w/o: 0.00/3411.99/0.00) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 3384.99 qps: 3384.99 (r/w/o: 0.00/3384.99/0.00) lat (ms,95%): 4.74 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 3354.97 qps: 3354.97 (r/w/o: 0.00/3354.97/0.00) lat (ms,95%): 4.74 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 3266.05 qps: 3266.05 (r/w/o: 0.00/3266.05/0.00) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 3425.73 qps: 3425.73 (r/w/o: 0.00/3425.73/0.00) lat (ms,95%): 5.18 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 3171.71 qps: 3171.71 (r/w/o: 0.00/3171.71/0.00) lat (ms,95%): 6.21 err/s: 0.00 reconn/s: 0.00
c、测试结果
SQL statistics:
queries performed:
read: 0
write: 683330
other: 0
total: 683330
transactions: 683330 (2277.73 per sec.)
queries: 683330 (2277.73 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0030s
total number of events: 683330
Latency (ms):
min: 0.47
avg: 4.39
max: 112.97
95th percentile: 16.71
sum: 2998860.08
Threads fairness:
events (avg/stddev): 68333.0000/43.82
execution time (avg/stddev): 299.8860/0.00
2.7、写入性能，oltp_write_only
a、测试命令
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
b、测试数据
[ 290s ] thds: 10 tps: 764.00 qps: 4580.99 (r/w/o: 0.00/2620.99/1959.99) lat (ms,95%): 50.11 err/s: 0.00 reconn/s: 0.00
[ 291s ] thds: 10 tps: 775.96 qps: 4655.75 (r/w/o: 0.00/2677.86/1977.89) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 292s ] thds: 10 tps: 700.98 qps: 4207.90 (r/w/o: 0.00/2407.94/1799.96) lat (ms,95%): 57.87 err/s: 0.00 reconn/s: 0.00
[ 293s ] thds: 10 tps: 727.00 qps: 4367.02 (r/w/o: 0.00/2498.01/1869.01) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00
[ 294s ] thds: 10 tps: 824.00 qps: 4936.02 (r/w/o: 0.00/2821.01/2115.01) lat (ms,95%): 50.11 err/s: 0.00 reconn/s: 0.00
[ 295s ] thds: 10 tps: 731.00 qps: 4385.97 (r/w/o: 0.00/2537.98/1847.99) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00
[ 296s ] thds: 10 tps: 840.00 qps: 5042.01 (r/w/o: 0.00/2877.01/2165.01) lat (ms,95%): 50.11 err/s: 0.00 reconn/s: 0.00
[ 297s ] thds: 10 tps: 779.00 qps: 4672.98 (r/w/o: 0.00/2711.99/1960.99) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00
[ 298s ] thds: 10 tps: 798.00 qps: 4793.03 (r/w/o: 0.00/2767.02/2026.01) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 299s ] thds: 10 tps: 765.00 qps: 4585.02 (r/w/o: 0.00/2644.01/1941.01) lat (ms,95%): 49.21 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 10 tps: 751.00 qps: 4512.98 (r/w/o: 0.00/2593.99/1918.99) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
c、测试结果
SQL statistics:
queries performed:
read: 0
write: 792245
other: 872455
total: 1664700
transactions: 277446 (924.70 per sec.)
queries: 1664700 (5548.27 per sec.)
ignored errors: 6 (0.02 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0383s
total number of events: 277446
Latency (ms):
min: 0.84
avg: 10.81
max: 219.88
95th percentile: 47.47
sum: 3000115.52
Threads fairness:
events (avg/stddev): 27744.6000/43.85
execution time (avg/stddev): 300.0116/0.00
2.8、测试完成-CleanUp
a、测试命令
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
b、测试数据
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'...
3、测试记录汇总
| condition | read | write | other | total | transactions | tps | qps | min | avg | max | 95th |
|---|---|---|---|---|---|---|---|---|---|---|---|
| oltp_read_write | 1143282 | 326652 | 163326 | 1633260 | 81663 | 272.20 | 5444.04 | 3.63ms | 36.74ms | 618.79ms | 86.00ms |
| oltp_read_only | 8253882 | 0 | 1179126 | 9433008 | 589563 | 1965.17 | 31442.68 | 2.18ms | 5.09ms | 18.85ms | 8.58ms |
| oltp_delete | 0 | 259449 | 249120 | 508569 | 508569 | 1695.21 | 1695.21 | 0.06ms | 5.90ms | 130.16ms | 39.65ms |
| oltp_update_index | 0 | 257855 | 623379 | 881234 | 881234 | 2937.17 | 2937.17 | 0.07ms | 3.40ms | 149.61ms | 22.28ms |
| oltp_update_non_index | 0 | 442745 | 1068126 | 1510871 | 1510871 | 5035.66 | 5035.66 | 0.07ms | 1.99ms | 88.04ms | 7.30ms |
| oltp_insert | 0 | 683330 | 0 | 683330 | 683330 | 2277.73 | 2277.73 | 0.47ms | 4.39ms | 112.97ms | 16.71ms |
| oltp_write_only | 0 | 792245 | 872455 | 872455 | 277446 | 924.70 | 5548.27 | 0.84ms | 10.81ms | 219.88ms | 47.47ms |
