使用 sysbench 壓力測試 mysql

sysbench 是一個壓測工具

除了對系統，也可以針對資料庫 mysql

# 簡單安裝

yum install sysbench

# 壓測前

要先建立資料庫名 sbtest

CREATE DATABASE sbtest;

# 產生資料

sysbench 可以協助寫入大量亂數資料

sysbench /usr/share/sysbench/oltp_read_write.lua \
    --db-driver=mysql \
    --mysql-host=127.0.0.1 \
    --mysql-port=3306 \
    --mysql-user=root \
    --mysql-password=password \
    --mysql-db=sbtest \
    --tables=2 \
    --table-size=10000 \
    --threads=1 \
    --events=0 \
    --time=60 \
    --rand-type=uniform \
    prepare

--tables=2          # 資料表的數量 ， 會建立 TABLE sbtest1 與 sbtest2
--table-size=10000  # 幾筆資料數量

# 開始進行壓測

sysbench /usr/share/sysbench/oltp_read_write.lua \
    --db-driver=mysql \
    --mysql-host=127.0.0.1 \
    --mysql-port=3306 \
    --mysql-user=root \
    --mysql-password=password \
    --mysql-db=sbtest \
    --tables=2 \
    --table-size=10000 \
    --threads=1 \
    --events=0 \
    --time=60 \
    --rand-type=uniform \
    run

結果

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            5656
        write:                           1022
        other:                           1402
        total:                           8080
    transactions:                        404    (6.73 per sec.)
    queries:                             8080   (134.53 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0575s
    total number of events:              404

Latency (ms):
         min:                                    8.85
         avg:                                  148.65
         max:                                  501.86
         95th percentile:                      257.95
         sum:                                60053.43

Threads fairness:
    events (avg/stddev):           404.0000/0.00
    execution time (avg/stddev):   60.0534/0.00

# 清除測試資料

只是把資料表刪除，資料庫還在

sysbench /usr/share/sysbench/oltp_read_write.lua \
    --db-driver=mysql \
    --mysql-host=127.0.0.1 \
    --mysql-port=3306 \
    --mysql-user=root \
    --mysql-password=password \
    --mysql-db=sbtest \
    --tables=2 \
    --table-size=10000 \
    --threads=1 \
    --events=0 \
    --time=60 \
    --rand-type=uniform \
    cleanup