参考：http://blog.csdn.net/mchdba/article/details/8970056
http://hugnew.com/sysbench%e4%bd%bf%e7%94%a8%e8%af%a6%e8%a7%a3/
sysbench是一个模块化的、跨平台、多线程基准测试工具,主要用于评估测试各种不同系统、参数下的数据库负载情况。关于这个项目的详细介绍请看：http://sysbench.sourceforge.net
它主要包括以下几种方式的测试:
(1).cpu性能
(2).磁盘io性能
(3).调度程序性能
(4).内存分配及传输速度
(5).POSIX线程性能
(6).数据库性能(OLTP基准测试)
目前sysbench主要支持 MySQL,pgsql,oracle 这3种数据库。
一、安装
(1). 在 http://sourceforge.net/projects/sysbench 下载源码包。
接下来，按照以下步骤安装：
http://pan.baidu.com/s/1kTmhvYv
(2).安装依赖环境：
yum -y install gcc automake libtool mysql mysql-libs mysql-devel mysql-shared
(3).解压安装sysbench
tar -xf sysbench-0.4.12.tar.gz cd sysbench-0.4.12 ./autogen.sh ./configure make && make install
如果想要让 sysbench 支持 pgsql/oracle 的话，就需要在编译的时候加上参数：--with-pgsql 或者 --with-oracle，这2个参数默认是关闭的，只有MySQL是默认支持的。
# 如果你的mysql是自己通过源码编译安装的，需要指定mysql的函数库位置和头文件位置
./configure –with-mysql-includes=/usr/local/mysql/include –with-mysql-libs=/usr/local/mysql/lib
报错及解决方式：
(1).make 时报错：
../libtool: line 838: X--tag=CC: command not found
../libtool: line 871: libtool: ignoring unknown tag : command not found
../libtool: line 838: X--mode=link: command not found
../libtool: line 1004: *** Warning: inferring the mode of operation is deprecated.: command not found
../libtool: line 1005: *** Future versions of Libtool will require --mode=MODE be specified.: command not found
../libtool: line 2231: X-g: command not found
../libtool: line 2231: X-O2: command not found
# 这个问题，一般只要先运行一次autogen.sh，然后再configure，make就可以了；
#如果还是不能解决，是因为sysbench自带的libtool 工具版本太旧。 安装一个新版本的libtool，然后，复制libtool覆盖sysbench下的libtool，然后直接make[千万不要再./configure，否则libtool又被恢复成老版的libtool]
# 如：
yum -y install libtool
cp /usr/bin/libtool ~/sysbench-0.4.10/
（2）安装完毕后，如果在运行时出现下面的错误提示：
sysbench: error while loading shared libraries: libmysqlclient_r.so.18: cannot open shared object file: No such file or directory
使用下面的命令查看libmysqlclient_r.so.18是否存在
# find / -name "libmysqlclient_r.so.18" -print
/usr/local/lib/mysql/libmysqlclient_r.so.18
结果显示是存在的，那么我们需要做个链接
# ln -s /usr/local/mysql/libmysqlclient_r.so.18 /usr/lib64/
或者 # export LD_LIBRARY_PATH=/usr/local/mysql/lib
如果没有，可以自己下载 devel 或者 share 包来安装。
二：性能测试
测试环境：
Centos6.5-x64 mysql版本：mysql-5.5.33-linux2.6-x86_64.tar.gz
Cpu：2核 内存：4GB
1、cpu性能测试
主要是进行素数的加法运算，下面例子中，指定了最大的素数为 20000，采用1核cpu进行测试，这里自己可以根据机器cpu的性能来适当调整数值。主要看total time所花费的时间.
# sysbench --test=cpu --cpu-max-prime=20000 run Doing CPU performance benchmark Threads started! Maximum prime number checked in CPU test: 20000 Test execution summary: total time: 29.6030s total number of events: 10000 total time taken by event execution: 29.6000 per-request statistics: min: 2.91ms avg: 2.96ms max: 11.35ms approx. 95 percentile: 3.17ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 29.6000/0.00
这里指定了最大的素数为 20000，采用2核cpu进行测试
# grep "processor" /proc/cpuinfo | wc -l ：获取cpu的线程数 # sysbench --test=cpu --num-threads=`grep "processor" /proc/cpuinfo | wc -l` --cpu-max-prime=20000 run Doing CPU performance benchmark Threads started! Maximum prime number checked in CPU test: 20000 Test execution summary: total time: 14.9528s total number of events: 10000 total time taken by event execution: 29.8994 per-request statistics: min: 2.91ms avg: 2.99ms max: 14.43ms approx. 95 percentile: 3.01ms Threads fairness: events (avg/stddev): 5000.0000/35.00 execution time (avg/stddev): 14.9497/0.00
2、线程测试
# sysbench --test=threads --num-threads=64 --thread-yields=20000 --thread-locks=2 run Running the test with following options: Number of threads: 64 Doing thread subsystem performance test Thread yields per test: 20000 Locks used: 2 Threads started! Done. Test execution summary: total time: 150.6140s total number of events: 10000 total time taken by event execution: 9626.9717 per-request statistics: min: 220.82ms avg: 962.70ms max: 1834.04ms approx. 95 percentile: 1407.51ms Threads fairness: events (avg/stddev): 156.2500/2.78 execution time (avg/stddev): 150.4214/0.17 # sysbench --test=threads --num-threads=32 --thread-yields=20000 --thread-locks=2 run Running the test with following options: Number of threads: 32 Doing thread subsystem performance test Thread yields per test: 20000 Locks used: 2 Threads started! Test execution summary: total time: 157.1462s total number of events: 10000 total time taken by event execution: 5025.4688 per-request statistics: min: 30.86ms avg: 502.55ms max: 1061.74ms approx. 95 percentile: 768.63ms Threads fairness: events (avg/stddev): 312.5000/6.02 execution time (avg/stddev): 157.0459/0.08
# sysbench --test=threads --num-threads=64 --thread-yields=20000 --thread-locks=2 run Running the test with following options: Number of threads: 64 Doing thread subsystem performance test Thread yields per test: 20000 Locks used: 2 Threads started! Test execution summary: total time: 150.6140s total number of events: 10000 total time taken by event execution: 9626.9717 per-request statistics: min: 220.82ms avg: 962.70ms max: 1834.04ms approx. 95 percentile: 1407.51ms Threads fairness: events (avg/stddev): 156.2500/2.78 execution time (avg/stddev): 150.4214/0.17 # sysbench --test=threads --num-threads=64 --thread-yields=20000 --thread-locks=1 run Running the test with following options: Number of threads: 64 Doing thread subsystem performance test Thread yields per test: 20000 Locks used: 1 Threads started! Test execution summary: total time: 77.3589s total number of events: 10000 total time taken by event execution: 4944.6555 per-request statistics: min: 146.11ms avg: 494.47ms max: 975.27ms approx. 95 percentile: 628.58ms Threads fairness: events (avg/stddev): 156.2500/3.16 execution time (avg/stddev): 77.2602/0.08
# sysbench --test=threads --num-threads=64 --thread-yields=20000 --thread-locks=2 run Running the test with following options: Number of threads: 64 Doing thread subsystem performance test Thread yields per test: 20000 Locks used: 2 Threads started! Test execution summary: total time: 150.6140s total number of events: 10000 total time taken by event execution: 9626.9717 per-request statistics: min: 220.82ms avg: 962.70ms max: 1834.04ms approx. 95 percentile: 1407.51ms Threads fairness: events (avg/stddev): 156.2500/2.78 execution time (avg/stddev): 150.4214/0.17 # sysbench --test=threads --num-threads=64 --thread-yields=10000 --thread-locks=2 run Running the test with following options: Number of threads: 64 Doing thread subsystem performance test Thread yields per test: 10000 Locks used: 2 Threads started! Test execution summary: total time: 74.0936s total number of events: 10000 total time taken by event execution: 4735.5944 per-request statistics: min: 2.78ms avg: 473.56ms max: 1280.24ms approx. 95 percentile: 839.09ms Threads fairness: events (avg/stddev): 156.2500/4.90 execution time (avg/stddev): 73.9937/0.06
3、磁盘io测试[主要看每秒请求数(request)和总体的吞吐量(total)，这两个参数对评估磁盘性能有帮助]
初识准备 # sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw prepare 运行 # sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw run Running the test with following options: Number of threads: 16 Extra file open flags: 0 128 files, 24Mb each 3Gb total file size Block size 16Kb Number of random requests for random IO: 10000 Read/Write ratio for combined random IO test: 1.50 Periodic FSYNC enabled, calling fsync() each 100 requests. Calling fsync() at the end of test, Enabled. Using synchronous I/O mode Doing random r/w test Threads started! Operations performed: 6005 Read, 3995 Write, 12800 Other = 22800 Total Read 93.828Mb Written 62.422Mb Total transferred 156.25Mb (150.64Mb/sec) 9641.17 Requests/sec executed Test execution summary: total time: 1.0372s total number of events: 10000 total time taken by event execution: 10.8996 per-request statistics: min: 0.00ms avg: 1.09ms max: 96.86ms approx. 95 percentile: 5.89ms Threads fairness: events (avg/stddev): 625.0000/129.64 execution time (avg/stddev): 0.6812/0.07 清空测试数据 # sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw cleanup 运行 # sysbench --test=fileio --num-threads=16 --file-total-size=9G --file-test-mode=rndrw run Running the test with following options: Number of threads: 16 Extra file open flags: 0 128 files, 72Mb each 9Gb total file size Block size 16Kb Number of random requests for random IO: 10000 Read/Write ratio for combined random IO test: 1.50 Periodic FSYNC enabled, calling fsync() each 100 requests. Calling fsync() at the end of test, Enabled. Using synchronous I/O mode Doing random r/w test Threads started! Operations performed: 6006 Read, 3994 Write, 12800 Other = 22800 Total Read 93.844Mb Written 62.406Mb Total transferred 156.25Mb (48.733Mb/sec) 3118.94 Requests/sec executed Test execution summary: total time: 3.2062s total number of events: 10000 total time taken by event execution: 49.1649 per-request statistics: min: 0.00ms avg: 4.92ms max: 157.97ms approx. 95 percentile: 23.80ms Threads fairness: events (avg/stddev): 625.0000/40.08 execution time (avg/stddev): 3.0728/0.02
# seqwr 顺序写 / seqrewr 连续改写 / seqrd 连续读 / rndrd 随机读取 / rndwr 随机写 / rndrw 结合随机读/写
上述参数指定了最大创建16个线程，创建的文件总大小为3G，文件读写模式为随机读。
4、内存测试
# sysbench --test=memory --num-threads=512 --memory-block-size=262144 --memory-total-size=32G run
上述参数指定了本次测试是在内存中传输 32G 的数据量，最大创建16个线程,每个 block 大小为256K(256*1024=262144)。
OLTP官方测试范例：
5. MySQL数据库测试 select
5.1 准备数据
首先需要创建默认的test数据库,或者使用–mysql-db指定一个已经存在的数据库生成测试数据，--mysql-table-engine 可以指定为 innodb 等 MySQL 支持的表存储引擎类型。表大小为500000条记录,其他参数主要是指定登录方式。
--oltp-test-mode=STRING test type to use {simple,complex,nontrx,sp} [complex]
--oltp-nontrx-mode=STRING mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]# time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=500000 --oltp-table-name=t1 --oltp-nontrx-mode=select --mysql-socket=/tmp/mysql.sock prepare Creating table 't1'... Creating 500000 records in table 't1'... real 0m4.549s user 0m0.086s sys 0m0.008s
报错如下：
FATAL: no database driver specified
FATAL: failed to initialize database driver!
test库没有建立，去create database
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
5.2 执行测试
# time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=500000 --oltp-table-name=t1 --oltp-nontrx-mode=select --mysql-socket=/tmp/mysql.sock run WARNING: Preparing of "BEGIN" is unsupported, using emulation Running the test with following options: Number of threads: 1 Maximum number of requests for OLTP test is limited to 10000 Threads started! OLTP test statistics: queries performed: read: 140000 write: 50000 other: 20000 total: 210000 transactions: 10000 (416.94 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 190000 (7921.78 per sec.) other operations: 20000 (833.87 per sec.) Test execution summary: total time: 23.9845s total number of events: 10000 total time taken by event execution: 23.9371 per-request statistics: min: 2.09ms avg: 2.39ms max: 24.35ms approx. 95 percentile: 2.58ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 23.9371/0.00 real 0m24.005s user 0m1.836s sys 0m1.230s
5.3执行清楚测试数据
# time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=500000 --oltp-table-name=t1 --mysql-socket=/tmp/mysql.sock cleanup Dropping table 't1'... Done. real 0m0.109s user 0m0.002s sys 0m0.002s
6. MySQL数据库测试 insert
6.1首先需要创建默认的test数据库,或者使用–mysql-db指定一个已经存在的数据库生成测试数据，--mysql-table-engine 可以指定为 innodb 等 MySQL 支持的表存储引擎类型。表大小为500000条记录,其他参数主要是指定登录方式。
# time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=500000 --oltp-table-name=t1 --oltp-nontrx-mode=select --mysql-socket=/tmp/mysql.sock prepare Creating table 't1'... Creating 500000 records in table 't1'... real 0m4.549s user 0m0.086s sys 0m0.008s
6.2 执行测试 --oltp-nontrx-mode=insert
# time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=500000 --oltp-table-name=t1 --oltp-nontrx-mode=insert --mysql-socket=/tmp/mysql.sock run WARNING: Preparing of "BEGIN" is unsupported, using emulation Running the test with following options: Number of threads: 1 Maximum number of requests for OLTP test is limited to 10000 Threads started! OLTP test statistics: queries performed: read: 140000 write: 50000 other: 20000 total: 210000 transactions: 10000 (416.94 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 190000 (7921.78 per sec.) other operations: 20000 (833.87 per sec.) Test execution summary: total time: 23.9845s total number of events: 10000 total time taken by event execution: 23.9371 per-request statistics: min: 2.09ms avg: 2.39ms max: 24.35ms approx. 95 percentile: 2.58ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 23.9371/0.00 real 0m24.005s user 0m1.836s sys 0m1.230s
6.3 --oltp-nontrx-mode=update_key 修改测试 带index
# time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=500000 --oltp-table-name=t1 --oltp-nontrx-mode=update_key --mysql-socket=/tmp/mysql.sock run Number of threads: 1 Maximum number of requests for OLTP test is limited to 10000 OLTP test statistics: queries performed: read: 140000 write: 50000 other: 20000 total: 210000 transactions: 10000 (421.26 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 190000 (8003.96 per sec.) other operations: 20000 (842.52 per sec.) Test execution summary: total time: 23.7382s total number of events: 10000 total time taken by event execution: 23.6919 per-request statistics: min: 2.09ms avg: 2.37ms max: 34.80ms approx. 95 percentile: 2.53ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 23.6919/0.00 real 0m23.755s user 0m1.874s sys 0m1.214s
6.4 --oltp-nontrx-mode=update_nokey 修改测试 No Index
# time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=500000 --oltp-table-name=t1 --oltp-nontrx-mode=update_nokey --mysql-socket=/tmp/mysql.sock run Number of threads: 1 Maximum number of requests for OLTP test is limited to 10000 Threads started! OLTP test statistics: queries performed: read: 140000 write: 50000 other: 20000 total: 210000 transactions: 10000 (418.89 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 190000 (7958.97 per sec.) other operations: 20000 (837.79 per sec.) Test execution summary: total time: 23.8724s total number of events: 10000 total time taken by event execution: 23.8262 per-request statistics: min: 2.09ms avg: 2.38ms max: 27.83ms approx. 95 percentile: 2.57ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 23.8262/0.00 real 0m23.889s user 0m1.846s sys 0m1.209s
6.5 --oltp-nontrx-mode=delete 删除测试
# time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=500000 --oltp-table-name=t1 --oltp-nontrx-mode=delete --mysql-socket=/tmp/mysql.sock run Number of threads: 1 Maximum number of requests for OLTP test is limited to 1000 OLTP test statistics: queries performed: read: 140000 write: 50000 other: 20000 total: 210000 transactions: 10000 (423.12 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 190000 (8039.21 per sec.) other operations: 20000 (846.23 per sec.) Test execution summary: total time: 23.6342s total number of events: 10000 total time taken by event execution: 23.5881 per-request statistics: min: 2.07ms avg: 2.36ms max: 22.51ms approx. 95 percentile: 2.54ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 23.5881/0.00 real 0m23.651s user 0m1.816s sys 0m1.238s
6.6 执行清楚测试数据
# time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=500000 --oltp-table-name=t1 --mysql-socket=/tmp/mysql.sock cleanup Dropping table 't1'... Done. real 0m0.109s user 0m0.002s sys 0m0.002s
6.7 总结
--oltp-nontrx-mode=STRING mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]
--max-requests=N limit for total number of requests [10000]
如果这里不指定--max-requests，默认为10000，就是执行1W次操作就结束了，如果要做压力测试的话，就需要手动设置此值。
--num-threads=N number of threads to use [1] (clients to access mysql db)
这里指有多少个mysql clents来访问mysql服务器，用show full processlist就可以看到大概的记录数。
7.1 接下来重新进行压力测试，设置2000 request测试，录入测试数据 2000000条记录。
# time sysbench --test=oltp --oltp-test-mode=nontrx --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --num-threads=50 --max-requests=2000 --oltp-nontrx-mode=insert --mysql-db=test --oltp-table-size=2000000 --oltp-table-name=t1 --mysql-socket=/tmp/mysql.sock prepare Creating table 't1'... Creating 2000000 records in table 't1'... real 0m18.239s user 0m0.347s sys 0m0.022s
7.2 开始测试纯写
# time sysbench --test=oltp --oltp-test-mode=nontrx --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --num-threads=50 --max-requests=2000 --oltp-nontrx-mode=insert --mysql-db=test --oltp-table-size=2000000 --oltp-table-name=t1 --mysql-socket=/tmp/mysql.sock run Number of threads: 50 Maximum number of requests for OLTP test is limited to 2000 OLTP test statistics: queries performed: read: 0 write: 2000 other: 0 total: 2000 transactions: 2000 (9594.57 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 2000 (9594.57 per sec.) other operations: 0 (0.00 per sec.) Test execution summary: total time: 0.2085s total number of events: 2000 total time taken by event execution: 9.9711 per-request statistics: min: 0.45ms avg: 4.99ms max: 27.85ms approx. 95 percentile: 13.54ms Threads fairness: events (avg/stddev): 40.0000/3.82 execution time (avg/stddev): 0.1994/0.01 real 0m0.755s user 0m0.034s sys 0m0.023s
7.3 测试 混合读写
# time sysbench --test=oltp --oltp-test-mode=complex --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --num-threads=50 --max-requests=2000 --oltp-nontrx-mode=select --mysql-db=test --oltp-table-size=2000000 --oltp-table-name=t1 --mysql-socket=/tmp/mysql.sock run Number of threads: 50 Maximum number of requests for OLTP test is limited to 2000 Threads started! OLTP test statistics: queries performed: read: 6314798 write: 2255285 other: 453057 total: 9023140 transactions: 2000 (3.45 per sec.) deadlocks: 449057 (773.54 per sec.) read/write requests: 8570083 (14762.77 per sec.) other operations: 453057 (780.43 per sec.) Test execution summary: total time: 580.5199s total number of events: 2000 total time taken by event execution: 28844.6283 per-request statistics: min: 1.64ms avg: 14422.31ms max: 580514.07ms approx. 95 percentile: 22369.87ms Threads fairness: events (avg/stddev): 40.0000/28.00 execution time (avg/stddev): 576.8926/3.86 real 9m41.072s user 1m3.521s sys 0m53.930s
avg: 14422.31ms
看到这里，leader说这个数值偏高了，在game领域，最好<10ms，并且他猜测我是local测试的，local500个线程也许会对db server有一些压力的，建议我remote测试访问测试下看.
8.1 重新进行远程访问压力测试，设置2000 request测试，录入测试数据 2000000条记录。
# time sysbench --test=oltp --oltp-test-mode=nontrx --mysql-table-engine=innodb --mysql-host=192.168.100.11 --mysql-user=root --mysql-password=123456 --db-driver=mysql --num-threads=50 --max-requests=2000 --oltp-nontrx-mode=insert --mysql-db=test --oltp-table-size=2000000 --oltp-table-name=t1 --mysql-socket=/tmp/mysql.sock prepare
8.2 远程进行测试纯写
# time sysbench --test=oltp --oltp-test-mode=nontrx --mysql-table-engine=innodb --mysql-host=192.168.100.11 --mysql-user=root --mysql-password=123456 --db-driver=mysql --num-threads=50 --max-requests=2000 --oltp-nontrx-mode=insert --mysql-db=test --oltp-table-size=2000000 --oltp-table-name=t1 --mysql-socket=/tmp/mysql.sock run
8.3 远程进行混合读写测试
# time sysbench --test=oltp --oltp-test-mode=complex --mysql-table-engine=innodb --mysql-host=192.168.100.11 --mysql-user=root --mysql-password=123456 --db-driver=mysql --num-threads=50 --max-requests=2000 --oltp-nontrx-mode=select --mysql-db=test --oltp-table-size=2000000 --oltp-table-name=t1 --mysql-socket=/tmp/mysql.sock run
