参考: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