sysbench简单使用介绍

安装

1.可以下载源码编译安装
在 http://sourceforge.net/projects/sysbench 下载源码包
接下来，按照以下步骤安装：

tar zxf sysbench-0.4.8.tar.gz
cd sysbench-0.4.8
./configure 
make && make install

以上方法适用于 MySQL 安装在标准默认目录下的情况，如果 MySQL 并不是安装在标准目录下的话，那么就需要自己指定 MySQL 的路径了。比如我的 MySQL 喜欢自己安装在 /usr/local/mysql 下，则按照以下方法编译：

./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib && make && make install

当然了，用上面的参数编译的话，就要确保你的 MySQL lib目录下有对应的 so 文件，如果没有，可以自己下载 devel 或者 share 包来安装。
另外，如果想要让 sysbench 支持 pgsql/oracle 的话，就需要在编译的时候加上参数
–with-pgsql
或者
–with-oracle
这2个参数默认是关闭的，只有 MySQL 是默认支持的。

2.偷懒的方法，用rpm安装
下载rpm包
ftp://ftp.pbone.net/mirror/download.fedora.redhat.com/pub/fedora/epel/6/x86_64/sysbench-0.4.12-5.el6.x86_64.rpm

上传到服务器，然后yum localinstall sysbench-0.4.12-5.el6.x86_64.rpm

环境准备

1.创建mysql用户，赋予权限。
grant all privileges on . to ‘lijingkuan’@’%’ identified by ‘123456’;
2.创建数据库
create database sbtest;
3.创建sysbench测试数据
sysbench –test=oltp –mysql-table-engine=innodb –oltp-table-size=1000000 –mysql-socket=/data/mysql/mysql.sock –mysql-user=lijingkuan –mysql-host=localhost –mysql-password=123456 –db-driver=mysql prepare

测试

[root@localhost ~]# sysbench --test=oltp --oltp-table-size=1000000 --oltp-test-mode=complex 
--oltp-read-only=off --num-threads=100 --max-time=180 --max-requests=0 --mysql-db=sbtest 
--mysql-user=lijingkuan --mysql-password=123456 --mysql-socket=/data/mysql/mysql.sock 
--db-driver=mysql run

sysbench 0.4.12:  multi-threaded system evaluation benchmark

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
Threads started!
Time limit exceeded, exiting...
(last message repeated 99 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            768250
        write:                           274375
        other:                           109750
        total:                           1152375
    transactions:                        54875  (304.47 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1042625 (5784.87 per sec.)
    other operations:                    109750 (608.93 per sec.)

Test execution summary:
    total time:                          180.2331s
    total number of events:              54875
    total time taken by event execution: 18001.2119
    per-request statistics:
         min:                                 17.83ms
         avg:                                328.04ms
         max:                               1638.71ms
         approx.  95 percentile:             577.87ms

Threads fairness:
    events (avg/stddev):           548.7500/14.78
    execution time (avg/stddev):   180.0121/0.03

QA

1.运行prepare命令出错

[root@localhost ~]# sysbench --test=oltp --mysql-table-engine=innodb 
--oltp-table-size=1000000 --mysql-socket=/data/mysql/mysql.sock 
--mysql-user=lijingkuan --mysql-host=localhost 
--mysql-password=123456 prepare
sysbench 0.4.12:  multi-threaded system evaluation benchmark

FATAL: no database driver specified
FATAL: failed to initialize database driver!

解决方案：
After some research, found that with Sysbench 0.4.x, we have to use option –db-driver to make it work. So I have used it and it worked.

[root@localhost ~]# sysbench --test=oltp --mysql-table-engine=innodb 
--oltp-table-size=1000000 --mysql-socket=/data/mysql/mysql.sock 
--mysql-user=lijingkuan --mysql-host=localhost --mysql-password=123456 --db-driver=mysql prepare
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Creating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...