MYSQL壓力測試工具sysbench安裝測試詳解

sysbench是一款開源的多線程性能測試工具，可以執行CPU/內存/線程/IO/數據庫等方面的性能測試。數據庫目前支持MySQL/Oracle/PostgreSQL，下面我們來看看它的安裝與測試。

如果評測一台mysql數據庫的壓力，可以使用sysbench來測試，

具體的操作出下，先安裝sysbench工具，安裝操作如下：

安裝環境

CentOS release 5.4 (Final)
MySQL 5.1.40 MySQL_HOME=/usr/local/mysql/
Sysbench 0.4.12

安裝步驟：

1. 去http://sourceforge.net/projects/sysbench/下載最新版本的sysbench 0.4.12

2. 解壓縮sysbench-0.4.12.tar.gz

tar --zxvf sysbench-0.4.12.tar.gz

3. 進入解壓縮包 sysbench-0.4.12,並執行腳本autogen.sh

cd sysbench-0.4.12
./autogen.sh

4．關鍵的三部曲：

configure&make&make install

首先是./configure命令，sysbench默認是支持MySQL的benchmarking的，如果不加任何選項則要求保證MySQL的安裝路徑都是默認的標准路徑，headfile位於/usr/include目錄下，libraries位於/usr/lib/目錄下。因為我的MySQL是源碼編譯安裝的，安裝路徑是放在/usr/local/mysql下，所以這裡要添加相應的選項命令：

./configure --prefix=/usr/local/mysql --with-mysql=/usr/local/mysql --with-mysql-includes=/usr/local/mysql/include/mysql/ --with-mysql-libs=/usr/local/mysql/lib/mysql/

注意：經反復測試這裡在編譯時要將路徑寫到最後的include/mysql,lib/mysql

--with-mysql-includes=/usr/local/mysql/include/mysql/
--with-mysql-libs=/usr/local/mysql/lib/mysql/

因為網上好多資料都沒有提到這一層，在編譯時總是編譯不過去，這裡浪費了好多精力。

make
make install

安裝就可以了，下面再來看測試

sysbench數據庫的測試案例：
第一步：准備數據庫，准備在mysql數據庫創建1000000數據量，存儲引擎是innodb。
bin/sysbench --test=oltp \           --測試類型數據庫oltp
--mysql-host=127.0.0.1 \             --mysql主機
--mysql-port=3421 \                  --mysql端口
--mysql-user=root \                  --mysql登陸用戶
--mysql-password=$password \         --mysql密碼
--mysql-db=test \                    --mysql測試數據庫名
--mysql-socket=/var/mysql.sock \     --socket位置
--oltp-table-name=test \             --mysql測試表名
--mysql-table-engine=innodb \        --mysql表使用存儲引擎
--oltp-table-size=1000000 prepare    --表數據量
第二步：開始測試，
bin/sysbench --test=oltp \
--mysql-host=127.0.0.1 \             --mysql主機
--mysql-port=3421 \                  --mysql端口
--mysql-user=root \                  --mysql登陸用戶
--mysql-password=$password \         --mysql密碼
--mysql-db=test \                    --mysql測試數據庫名
--mysql-socket=/var/mysql.sock \     --socket位置
--oltp-table-name=test \             --mysql測試表名
--mysql-table-engine=innodb \        --mysql表使用存儲引擎
--max-requests=100000            \   --測試請求次數
--max-time=600                 \     --測試最長時間
--num-threads=100 run                --並發線程數
測試結果：
sysbench 0.4.12:  multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 100
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 100000
Threads started!
Done.
OLTP test statistics:
    queries performed:
        read:                            1401582
        write:                           500563
        other:                           200225
        total:                           2102370
    transactions:                        100112 (3446.37 per sec.)
    deadlocks:                           1      (0.03 per sec.)
    read/write requests:                 1902145 (65481.66 per sec.)
    other operations:                    200225 (6892.78 per sec.)
Test execution summary:
    total time:                          29.0485s
    total number of events:              100112
    total time taken by event execution: 2898.7554
    per-request statistics:
         min:                                  3.52ms
         avg:                                 28.96ms
         max:                                187.11ms
         approx.  95 percentile:              46.05ms
Threads fairness:
    events (avg/stddev):           1001.1200/10.80
    execution time (avg/stddev):   28.9876/0.03
第三步：清理測試數據。
bin/sysbench --test=oltp \
--mysql-host=127.0.0.1 \             --mysql主機
--mysql-port=3421 \                  --mysql端口
--mysql-user=root \                  --mysql登陸用戶
--mysql-password=$password \         --mysql密碼
--mysql-db=test \                    --mysql測試數據庫名
--mysql-socket=/var/mysql.sock \     --socket位置
--oltp-table-name=test \             --mysql測試表名
--mysql-table-engine=innodb \        --mysql表使用存儲引擎
cleanup
在版本0.5中支持自定義語句，可以參考下面的鏈接。
http://dba.stackexchange.com/questions/39221/stress-test-mysql-with-queries-captured-with-general-log-in-mysql
3. 測試案例，表屬性compress對insert速度的影響。
第一步准備：
創建表結構
bin/sysbench --test=oltp \           --測試類型數據庫oltp
--mysql-host=127.0.0.1 \             --mysql主機
--mysql-port=3421 \                  --mysql端口
--mysql-user=root \                  --mysql登陸用戶
--mysql-password=$password \         --mysql密碼
--mysql-db=test \                    --mysql測試數據庫名
--mysql-socket=/var/mysql.sock \     --socket位置
--oltp-table-name=sbtest \             --mysql測試表名
--mysql-table-engine=innodb \        --mysql表使用存儲引擎
--oltp-table-size=1 prepare    --表數據量
將表名修改為uncompressed
rename table sbtest to uncompressed;
再次創建表sbtest，並將表名修改為compressed
rename table sbtest to compressed;
將表改為compressed
alter table compressed ROW_FORMAT=compressed;
第二步：向表uncompressed表中添加記錄
bin/sysbench --test=oltp           \
--mysql-host=127.0.0.1 \             --mysql主機
--mysql-port=3421 \                  --mysql端口
--mysql-user=root \                  --mysql登陸用戶
--mysql-password=$password \         --mysql密碼
--mysql-db=test \                    --mysql測試數據庫名
--mysql-socket=/var/mysql.sock \     --socket位置
--oltp-table-name=uncompressed \             --mysql測試表名
--mysql-table-engine=innodb \        --mysql表使用存儲引擎
--oltp-nontrx-mode=insert      \
--oltp-test-mode=nontrx        \
--max-requests=1000000            \
--max-time=600                 \
--num-threads=100 run
測試結果：
OLTP test statistics:
    queries performed:
        read:                            0
        write:                           1001110
        other:                           0
        total:                           1001110
    transactions:                        1001110 (13617.75 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1001110 (13617.75 per sec.)
    other operations:                    0      (0.00 per sec.)
Test execution summary:
    total time:                          73.5151s
    total number of events:              1001110
    total time taken by event execution: 7346.0036
    per-request statistics:
         min:                                  0.10ms
         avg:                                  7.34ms
         max:                                239.05ms
         approx.  95 percentile:              22.00ms
Threads fairness:
    events (avg/stddev):           10011.1000/86.80
    execution time (avg/stddev):   73.4600/0.00
說明：總共消耗了73s。每秒達到13617個事務.
第三步：向表compressed表中添加記錄
bin/sysbench --test=oltp           \
--mysql-host=127.0.0.1 \             --mysql主機
--mysql-port=3421 \                  --mysql端口
--mysql-user=root \                  --mysql登陸用戶
--mysql-password=$password \         --mysql密碼
--mysql-db=test \                    --mysql測試數據庫名
--mysql-socket=/var/mysql.sock \     --socket位置
--oltp-table-name=compressed \             --mysql測試表名
--mysql-table-engine=innodb \        --mysql表使用存儲引擎
--oltp-nontrx-mode=insert      \
--oltp-test-mode=nontrx        \
--max-requests=1000000            \
--max-time=600                 \
--num-threads=100 run
測試結果：
OLTP test statistics:
    queries performed:
        read:                            0
        write:                           1000515
        other:                           0
        total:                           1000515
    transactions:                        1000515 (2313.85 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1000515 (2313.85 per sec.)
    other operations:                    0      (0.00 per sec.)
Test execution summary:
    total time:                          432.4036s
    total number of events:              1000515
    total time taken by event execution: 43229.6698
    per-request statistics:
         min:                                  0.10ms
         avg:                                 43.21ms
         max:                                730.32ms
         approx.  95 percentile:             133.46ms
Threads fairness:
    events (avg/stddev):           10005.1500/109.30
    execution time (avg/stddev):   432.2967/0.01
說明：總共消耗了432s，每秒處理2313事務數。和uncompressed的速度相差6倍。
4. 測試案例，表屬性compress對復合操作速度的影響。
第一步：准備數據，和第3項一樣准備。
第二步：針對compressed表進行復合操作測試。
bin/sysbench --test=oltp           \
--mysql-host=127.0.0.1 \             --mysql主機
--mysql-port=3421 \                  --mysql端口
--mysql-user=root \                  --mysql登陸用戶
--mysql-password=$password \         --mysql密碼
--mysql-db=test \                    --mysql測試數據庫名
--mysql-socket=/var/mysql.sock \     --socket位置
--oltp-table-name=compressed \             --mysql測試表名
--mysql-table-engine=innodb \        --mysql表使用存儲引擎
--max-requests=1000000            \
--max-time=600                 \
--num-threads=100 run
OLTP test statistics:
    queries performed:
        read:                            14012236
        write:                           5004356
        other:                           2001743
        total:                           21018335
    transactions:                        1000869 (3145.37 per sec.)
    deadlocks:                           5      (0.02 per sec.)
    read/write requests:                 19016592 (59762.20 per sec.)
    other operations:                    2001743 (6290.75 per sec.)
Test execution summary:
    total time:                          318.2043s
    total number of events:              1000869
    total time taken by event execution: 31800.5571
    per-request statistics:
         min:                                  3.19ms
         avg:                                 31.77ms
         max:                                344.54ms
         approx.  95 percentile:              53.37ms
Threads fairness:
    events (avg/stddev):           10008.6900/32.50
    execution time (avg/stddev):   318.0056/0.01
說明：共消耗時間318s，每秒3145個事務。
第三步：針對uncompressed表進行復合操作測試。
bin/sysbench --test=oltp           \
--mysql-host=127.0.0.1 \             --mysql主機
--mysql-port=3421 \                  --mysql端口
--mysql-user=root \                  --mysql登陸用戶
--mysql-password=$password \         --mysql密碼
--mysql-db=test \                    --mysql測試數據庫名
--mysql-socket=/var/mysql.sock \     --socket位置
--oltp-table-name=uncompressed \             --mysql測試表名
--mysql-table-engine=innodb \        --mysql表使用存儲引擎
--oltp-nontrx-mode=insert      \
--oltp-test-mode=nontrx        \
--max-requests=1000000            \
--max-time=600                 \
--num-threads=100 run
OLTP test statistics:
    queries performed:
        read:                            14013370
        write:                           5004769
        other:                           2001908
        total:                           21020047
    transactions:                        1000953 (3389.22 per sec.)
    deadlocks:                           2      (0.01 per sec.)
    read/write requests:                 19018139 (64395.20 per sec.)
    other operations:                    2001908 (6778.44 per sec.)
Test execution summary:
    total time:                          295.3347s
    total number of events:              1000953
    total time taken by event execution: 29512.3204
    per-request statistics:
         min:                                  3.37ms
         avg:                                 29.48ms
         max:                                157.93ms
         approx.  95 percentile:              48.19ms
Threads fairness:
    events (avg/stddev):           10009.5300/30.59
    execution time (avg/stddev):   295.1232/0.02

說明：共消耗時間295s，每秒3389個事務。在復合操作中，compressed的表會快一些。