sysbench测试mysql oltp

以前在做mysql压力测试的时候用的是jmeter,jmeter配置有点烦，最近尝试用sysbench来进行mysql的压力测试

下载最新版的sysbench

cd /usr/local/src

wget http://sourceforge.net/projects/sysbench/files/sysbench/0.4.12/sysbench-0.4.12.tar.gz/download?use_mirror=nchc&r=http%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

测试结果

[[email protected] ~]# 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 总的用时

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27181165/viewspace-1064315/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27181165/viewspace-1064315/