sysbench测试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 总的用时