sysbench压测自装MySQL数据库

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

执行命令之后

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

2.1.1、测试命令

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

2.1.2、测试数据

 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

2.1.3、测试结果

SQL statistics:
    queries performed:
        read:                            1143282
        write:                           326652
        other:                           163326
        total:                           1633260
    transactions:                        81663