2023-02-03 sysbench压测工具使用-优快云博客

本文链接：https://blog.youkuaiyun.com/adofsauron/article/details/128868715

文章介绍了如何下载和编译sysbench工具，然后利用它来对MySQL数据库进行OLTP复杂模式的压力测试，包括插入数据、执行查询以及查看测试结果。在测试中，展示了每10秒的事务处理率(tps)、查询处理率(qps)和延迟情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

摘要:

sysbench压测工具使用

代码仓库:

wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"

编译:

yum install automake libtool –y

unzip -q sysbench-1.0.zip
cd sysbench-1.0

./autogen.sh
./configure
make
make install

插入数据:

需要先创建sbtest库

sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password={$pass} --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 prepare

执行查询压力测试:

压测脚本:

sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password={$pass} --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 run >> /root/tmp.log.sysbench

压测结果:

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 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 10 tps: 930.03 qps: 18611.18 (r/w/o: 13029.11/3721.02/1861.06) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 10 tps: 856.38 qps: 17132.87 (r/w/o: 11993.10/3427.01/1712.76) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 860.90 qps: 17217.80 (r/w/o: 12052.53/3443.48/1721.79) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 10 tps: 728.62 qps: 14559.86 (r/w/o: 10190.42/2912.19/1457.25) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 10 tps: 652.60 qps: 13060.47 (r/w/o: 9143.88/2611.39/1305.20) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 10 tps: 796.79 qps: 15933.01 (r/w/o: 11152.87/3186.56/1593.58) lat (ms,95%): 23.52 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 10 tps: 843.60 qps: 16875.98 (r/w/o: 11813.15/3375.62/1687.21) lat (ms,95%): 21.11 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 10 tps: 708.49 qps: 14168.43 (r/w/o: 9917.58/2833.87/1416.98) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 10 tps: 721.91 qps: 14439.98 (r/w/o: 10109.60/2886.56/1443.83) lat (ms,95%): 25.74 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 10 tps: 802.80 qps: 16054.18 (r/w/o: 11236.19/3212.40/1605.60) lat (ms,95%): 22.69 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 10 tps: 804.59 qps: 16092.12 (r/w/o: 11265.38/3217.66/1609.08) lat (ms,95%): 21.11 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 10 tps: 727.20 qps: 14544.07 (r/w/o: 10180.88/2908.79/1454.40) lat (ms,95%): 29.19 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            1320886
        write:                           377396
        other:                           188698
        total:                           1886980
    transactions:                        94349  (786.15 per sec.)
    queries:                             1886980 (15722.96 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0139s
    total number of events:              94349

Latency (ms):
         min:                                    2.72
         avg:                                   12.72
         max:                                   88.12
         95th percentile:                       23.95
         sum:                              1199803.06

Threads fairness:
    events (avg/stddev):           9434.9000/105.92
    execution time (avg/stddev):   119.9803/0.00

清理测试数据:

sysbench --test='/usr/local/share/sysbench/oltp.lua' --oltp-tables-count=1 --report-interval=10 --oltp-table-size=10000000  --mysql-user=*** --mysql-password=*** --mysql-table-engine=innodb  --rand-init=on  --mysql-host=**** --mysql-port=*** --mysql-db=*** --max-time=300 --max-requests=0   --oltp_skip_trx=on --oltp_auto_inc=off --oltp_secondary --oltp_range_size=5 --mysql_table_options='dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 2' --num-threads=200 cleanup