2023面试高手

这个屌丝很懒，什么也没留下！

热门标签

【压测初体验】sysbench压测MySQL数据库_sysbench oltp_read_only

作者：2023面试高手 | 2024-02-16 10:33:12

踩

sysbench oltp_read_only

综合TPS（oltp_read_write模式）

只读性能（oltp_read_only模式）

删除性能（oltp_delete模式）

更新索引字段性能（oltp_update_index模式）

更新非索引字段性能（oltp_update_non_index模式）

插入性能（oltp_insert模式）

写入性能（oltp_write_only模式）

压测结束 - cleanup

压测数据汇总

压测前准备

机器配置：


# 我用的是虚拟机安装的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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/2023面试高手/article/detail/92323

【压测初体验】sysbench压测MySQL数据库_sysbench oltp_read_only

压测前准备

测试表准备

压测结果分析

开始压测

综合TPS（oltp_read_write模式）

只读性能（oltp_read_only模式 ）

删除性能（oltp_delete模式 ）

更新索引字段性能（oltp_update_index模式 ）

更新非索引字段性能（oltp_update_non_index模式 ）

插入性能（oltp_insert模式 ）

写入性能（oltp_write_only模式 ）

压测结束 - cleanup

压测数据汇总

只读性能（oltp_read_only模式）

删除性能（oltp_delete模式）

更新索引字段性能（oltp_update_index模式）

更新非索引字段性能（oltp_update_non_index模式）

插入性能（oltp_insert模式）

写入性能（oltp_write_only模式）