sysbench简单使用介绍

安装

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

tar zxf sysbench-0.4.8.tar.gzcd 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 runsysbench 0.4.12:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 100Doing OLTP test.Running mixed OLTP testUsing Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)Using "BEGIN" for starting transactionsUsing auto_inc on the id columnThreads 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.87msThreads 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 preparesysbench 0.4.12:  multi-threaded system evaluation benchmarkFATAL: no database driver specifiedFATAL: 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 preparesysbench 0.4.12:  multi-threaded system evaluation benchmarkCreating table 'sbtest'...Creating 1000000 records in table 'sbtest'...