status
Published
date
Aug 16, 2021
slug
summary
category
技术分享
tags
数据库
测试

0 前言

Sysbench只能安装在Linux/Unix系统上,不支持Windows
本次采用的是腾讯云1c 2g的云服务器搭建的MySQL

1 安装

我以ubuntu 16.04为例
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
sudo apt -y install sysbench
找到sysbench的安装目录whereis sysbench
ubuntu@k8s-master:~$ whereis sysbench
sysbench: /usr/bin/sysbench /usr/share/sysbench
/usr/bin/sysbench为脚本的执行地址,/usr/share/sysbench为安装目录

2 使用

2.1 切换目录

cd /usr/share/sysbench

2.2 准备测试库

创建用于测试的数据库sbtest
mysql -uroot -p123456
create database sbtest;
exit;

2.3 准备测试数据

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

2.4 执行测试

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

2.5 查看执行结果

cat ~/mysysbench.log
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: 387.38 qps: 7764.64 (r/w/o: 5436.55/1552.33/775.76) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 10 tps: 443.90 qps: 8870.96 (r/w/o: 6210.04/1773.11/887.81) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 461.80 qps: 9244.63 (r/w/o: 6470.55/1850.49/923.59) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 10 tps: 448.81 qps: 8969.61 (r/w/o: 6280.38/1791.62/897.61) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 10 tps: 460.49 qps: 9207.35 (r/w/o: 6444.40/1841.97/920.99) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 10 tps: 372.01 qps: 7445.82 (r/w/o: 5211.18/1490.62/744.01) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 10 tps: 431.09 qps: 8620.49 (r/w/o: 6035.32/1722.98/862.19) lat (ms,95%): 49.21 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 10 tps: 445.70 qps: 8917.14 (r/w/o: 6241.92/1783.81/891.40) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 10 tps: 466.40 qps: 9320.58 (r/w/o: 6523.95/1863.82/932.81) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 10 tps: 448.30 qps: 8972.20 (r/w/o: 6281.90/1793.70/896.60) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 10 tps: 467.20 qps: 9340.77 (r/w/o: 6538.08/1868.29/934.40) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 10 tps: 401.90 qps: 8034.82 (r/w/o: 5623.51/1607.50/803.80) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            733068 write:                           209448
        other:                           104724
        total:                           1047240
    transactions:                        52362  (436.14 per sec.)
    queries:                             1047240 (8722.89 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0551s
    total number of events:              52362

Latency (ms):
         min:                                    4.01
         avg:                                   22.92
         max:                                  300.45 95th percentile:                       45.79
         sum:                              1200154.68

Threads fairness:
    events (avg/stddev):           5236.2000/11.34
    execution time (avg/stddev):   120.0155/0.01

结果分析

2.6 删除测试数据

sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123456 cleanup

参考

  1. Scriptable database and system performance benchmark
  1. 详解MySQL基准测试和sysbench工具
  1. FATAL: Worker threads failed to initialize within 30 seconds!
  1. sysbench压测使用问题记录
  1. 用sysbench轻松定制自己的测试场景

  • Cusdis
  • GitTalk