sysbench測試mysql oltp

以前在做mysql壓力測試的時候用的是jmeter,jmeter配置有點煩，最近嘗試用sysbench來進行mysql的壓力測試
下載最新版的sysbench
cd /usr/local/src
wget %3A%2F%2Fsourceforge.net%2Fprojects%2Fsysbench%2F&use_mirror=nchc
tar zxf sysbench-0.4.12.tar.gz
cd sysbench-0.4.12
找到mysql的庫檔案和標頭檔案所在的目錄
本文在/usr/local/mysql/lib和/usr/local/mysql/include下
./configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/local/mysql/include/mysql --with-mysql-libs=/usr/local/mysql/lib/mysql
配置後在sysbench-0.4.12下會生成libtool檔案，找到該檔案的ECHO="echo"行，將ECHO="echo"改成echo="echo",否則編譯的時候會出錯，提示libtool找不到檔案
make
make install
這樣sysbench安裝完成，在/usr/local/sysbench/bin下會生成sysbench檔案，為了測試方便，我做個軟連線
ln -s /usr/local/sysbench/bin/sysbench /bin/sysbench
在mysql中建立sbtest庫，並授予相關許可權
create database sbtest;
grant all privileges on sbtest.* to etnet@'local' identified by 'etnet';
flush privileges;
為測試準備資料
sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --mysql-db=sbtest --mysql-host=localhost --mysql-user=etnet --mysql-password=etnet --num-threads=128 prepare
測試
sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --mysql-db=sbtest --mysql-host=localhost --mysql-user=etnet --mysql-password=etnet --num-threads=128 run
清除資料
sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --mysql-db=sbtest --mysql-host=localhost --mysql-user=etnet --mysql-password=etnet --num-threads=128 cleanup
sysbench具體的引數可以引數sysbench --test= help
--test可選項如下
Compiled-in tests:
  fileio - File I/O test
  cpu - CPU performance test
  memory - Memory functions speed test
  threads - Threads subsystem performance test
  mutex - Mutex performance test
  oltp - OLTP test
測試結果
[root@192 ~]# sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --mysql-db=sbtest --mysql-host=localhost --mysql-user=etnet --mysql-password=etnet --num-threads=128 run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 128

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 10000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (1051.37 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (19975.96 per sec.)
    other operations:                    20000  (2102.73 per sec.)

Test execution summary:
    total time:                          9.5114s
    total number of events:              10000
    total time taken by event execution: 1210.2339
    per-request statistics:
         min:                                  2.32ms
         avg:                                121.02ms
         max:                                412.67ms
         approx.  95 percentile:             228.54ms

Threads fairness:
    events (avg/stddev):           78.1250/5.17
    execution time (avg/stddev):   9.4550/0.04
1051.37 per sec 為每秒事務量，19975.96 per sec 每秒的讀寫請求數，total time:  9.5114s 總的用時