MYSQL压力测试工具sysbench安装测试详解

MYSQL压力测试工具sysbench安装测试详解sysbench是一款开源的多线程性能测试工具,可以执行CPU/内存/线程/IO/数据库等方面的性能测试,数据库目前支持MySQL/Oracle/PostgreSQL,下面我们来看看它的安装与测试.如果评测一台mysql数据库的压力,可以使用sysbench来测试,具体的操作出下,先安装sysbench工具,安装操作如下.安装环境CentOS release 5.4 (Final),MySQL 5.1.40 MySQL_HOME=/usr/local/mysql/,Sysbench 0.4.12安装步骤1.去/projects/sysbench/下载最新版本的sysbench 0.4.122.解压缩sysbench-0.4.12.tar.gztar --zxvf sysbench-0.4.12.tar.gz3.进入解压缩包sysbench-0.4.12,并执行脚本autogen.shcd sysbench-0.4.12./autogen.sh4.关键的三部曲:configure&make&make install首先是./configure命令，sysbench默认是支持MySQL的benchmarking的,如果不加任何选项则要求保证MySQL的安装路径都是默认的标准路径,headfile位于/usr/include目录下,libraries 位于/usr/lib/目录下,因为我的MySQL是源码编译安装的,安装路径是放在/usr/local/mysql下,所以这里要添加相应的选项命令:./configure --prefix=/usr/local/mysql --with-mysql=/usr/local/mysql --with-mysql-includes=/usr/local/mysql/include/mysql/--with-mysql-libs=/usr/local/mysql/lib/mysql/注意:经反复测试这里在编译时要将路径写到最后的include/mysql,lib/mysql--with-mysql-includes=/usr/local/mysql/include/mysql/--with-mysql-libs=/usr/local/mysql/lib/mysql/因为网上好多资料都没有提到这一层,在编译时总是编译不过去,这里浪费了好多精力. makemake install安装就可以了,下面再来看测试.sysbench数据库的测试案例:第一步:准备数据库,准备在mysql数据库创建1000000数据量,存储引擎是innodb.bin/sysbench --test=oltp \ --测试类型数据库oltp--mysql-host=127.0.0.1 \ --mysql主机--mysql-port=3421 \ --mysql端口--mysql-user=root \ --mysql登陆用户--mysql-password=$password \ --mysql密码--mysql-db=test \ --mysql测试数据库名--mysql-socket=/var/mysql.sock \ --socket位置--oltp-table-name=test \ --mysql测试表名--mysql-table-engine=innodb \ --mysql表使用存储引擎--oltp-table-size=1000000 prepare --表数据量第二步:开始测试.bin/sysbench --test=oltp \--mysql-host=127.0.0.1 \ --mysql主机--mysql-port=3421 \ --mysql端口--mysql-user=root \ --mysql登陆用户--mysql-password=$password \ --mysql密码--mysql-db=test \ --mysql测试数据库名--mysql-socket=/var/mysql.sock \ --socket位置--oltp-table-name=test \ --mysql测试表名--mysql-table-engine=innodb \ --mysql表使用存储引擎--max-requests=100000 \ --测试请求次数--max-time=600 \ --测试最长时间--num-threads=100 run --并发线程数//测试结果：sysbench 0.4.12: multi-threaded system evaluation benchmarkNo DB drivers specified, using mysqlRunning the test with following options:Number of threads: 100Doing OLTP test.Running mixed OLTP testUsing Special ibution (12 iterations, 1 pct of values are returned in 75 pct cases)Using "BEGIN" for starting transactionsUsing auto_inc on the id columnMaximum number of requests for OLTP test is limited to 100000Threads started!Done.OLTP test statistics:queries performed:read: 1401582write: 500563other: 200225total: 2102370transactions: 100112 (3446.37 per sec.)deadlocks: 1 (0.03 per sec.)read/write requests: 1902145 (65481.66 per sec.)other operations: 200225 (6892.78 per sec.)Test execution summary:total time: 29.0485stotal number of events: 100112total time taken by event execution: 2898.7554per-request statistics:min: 3.52msavg: 28.96msmax: 187.11msapprox. 95 percentile: 46.05msThreads fairness:events (avg/stddev): 1001.1200/10.80execution time (avg/stddev): 28.9876/0.03第三步:清理测试数据.bin/sysbench --test=oltp \--mysql-host=127.0.0.1 \ --mysql主机--mysql-port=3421 \ --mysql端口--mysql-user=root \ --mysql登陆用户--mysql-password=$password \ --mysql密码--mysql-db=test \ --mysql测试数据库名--mysql-socket=/var/mysql.sock \ --socket位置--oltp-table-name=test \ --mysql测试表名--mysql-table-engine=innodb \ --mysql表使用存储引擎cleanup在版本0.5中支持自定义语句,可以参考下面的链接./questions/39221/stress-test-mysql-with-queries-captured-with-general-log-in-mysql3.测试案例,表属性compress对insert速度的影响.第一步准备:创建表结构:bin/sysbench --test=oltp \ --测试类型数据库oltp--mysql-host=127.0.0.1 \ --mysql主机--mysql-port=3421 \ --mysql端口--mysql-user=root \ --mysql登陆用户--mysql-password=$password \ --mysql密码--mysql-db=test \ --mysql测试数据库名--mysql-socket=/var/mysql.sock \ --socket位置--oltp-table-name=sbtest \ --mysql测试表名--mysql-table-engine=innodb \ --mysql表使用存储引擎--oltp-table-size=1 prepare --表数据量将表名修改为uncompressedrename table sbtest to uncompressed;再次创建表sbtest,并将表名修改为compressedrename table sbtest to compressed;将表改为compressedalter table compressed ROW_FORMAT=compressed;第二步:向表uncompressed表中添加记录bin/sysbench --test=oltp \--mysql-host=127.0.0.1 \ --mysql主机--mysql-port=3421 \ --mysql端口--mysql-user=root \ --mysql登陆用户--mysql-password=$password \ --mysql密码--mysql-db=test \ --mysql测试数据库名ket=/var/mysql.sock \ --socket位置--oltp-table-name=uncompressed \ --mysql测试表名--mysql-table-engine=innodb \ --mysql表使用存储引擎--oltp-nontrx-mode=insert \--oltp-test-mode=nontrx \--max-requests=1000000 \--max-time=600 \--num-threads=100 run--测试结果：OLTP test statistics:queries performed:read: 0write: 1001110other: 0total: 1001110transactions: 1001110 (13617.75 per sec.) deadlocks: 0 (0.00 per sec.)read/write requests: 1001110 (13617.75 per sec.) other operations: 0 (0.00 per sec.)Test execution summary:total time: 73.5151stotal number of events: 1001110total time taken by event execution: 7346.0036per-request statistics:min: 0.10msavg: 7.34msmax: 239.05msapprox. 95 percentile: 22.00msThreads fairness:events (avg/stddev): 10011.1000/86.80execution time (avg/stddev): 73.4600/0.00说明:总共消耗了73s,每秒达到13617个事务.第三步:向表compressed表中添加记录:bin/sysbench --test=oltp \--mysql-host=127.0.0.1 \ --mysql主机--mysql-port=3421 \ --mysql端口--mysql-user=root \ --mysql登陆用户--mysql-password=$password \ --mysql密码--mysql-db=test \ --mysql测试数据库名--mysql-socket=/var/mysql.sock \ --socket位置--oltp-table-name=compressed \ --mysql测试表名--mysql-table-engine=innodb \ --mysql表使用存储引擎--oltp-nontrx-mode=insert \--oltp-test-mode=nontrx \--max-requests=1000000 \--max-time=600 \--num-threads=100 run--测试结果：OLTP test statistics:queries performed:read: 0write: 1000515other: 0total: 1000515transactions: 1000515 (2313.85 per sec.)deadlocks: 0 (0.00 per sec.)read/write requests: 1000515 (2313.85 per sec.)other operations: 0 (0.00 per sec.)Test execution summary:total time: 432.4036stotal number of events: 1000515total time taken by event execution: 43229.6698per-request statistics:min: 0.10msavg: 43.21msmax: 730.32msapprox. 95 percentile: 133.46msThreads fairness:events (avg/stddev): 10005.1500/109.30execution time (avg/stddev): 432.2967/0.01说明:总共消耗了432s,每秒处理2313事务数,和uncompressed的速度相差6倍.4.测试案例,表属性compress对复合操作速度的影响.第一步:准备数据,和第3项一样准备.第二步:针对compressed表进行复合操作测试.bin/sysbench --test=oltp \--mysql-host=127.0.0.1 \ --mysql主机--mysql-port=3421 \ --mysql端口--mysql-user=root \ --mysql登陆用户--mysql-password=$password \ --mysql密码--mysql-db=test \ --mysql测试数据库名--mysql-socket=/var/mysql.sock \ --socket位置--oltp-table-name=compressed \ --mysql测试表名--mysql-table-engine=innodb \ --mysql表使用存储引擎--max-requests=1000000 \--max-time=600 \--num-threads=100 runOLTP test statistics:queries performed:read: 14012236write: 5004356other: 2001743total: 21018335transactions: 1000869 (3145.37 per sec.) deadlocks: 5 (0.02 per sec.)read/write requests: 19016592 (59762.20 per sec.) other operations: 2001743 (6290.75 per sec.) Test execution summary:total time: 318.2043stotal number of events: 1000869total time taken by event execution: 31800.5571per-request statistics:min: 3.19msavg: 31.77msmax: 344.54msapprox. 95 percentile: 53.37msThreads fairness:events (avg/stddev): 10008.6900/32.50execution time (avg/stddev): 318.0056/0.01说明:共消耗时间318s,每秒3145个事务.第三步:针对uncompressed表进行复合操作测试.bin/sysbench --test=oltp \--mysql-host=127.0.0.1 \ --mysql主机--mysql-port=3421 \ --mysql端口--mysql-user=root \ --mysql登陆用户--mysql-password=$password \ --mysql密码--mysql-db=test \ --mysql测试数据库名--mysql-socket=/var/mysql.sock \ --socket位置--oltp-table-name=uncompressed \ --mysql测试表名--mysql-table-engine=innodb \ --mysql表使用存储引擎--oltp-nontrx-mode=insert \--oltp-test-mode=nontrx \--max-requests=1000000 \--max-time=600 \--num-threads=100 runOLTP test statistics:queries performed:read: 14013370write: 5004769other: 2001908total: 21020047transactions: 1000953 (3389.22 per sec.) deadlocks: 2 (0.01 per sec.)read/write requests: 19018139 (64395.20 per sec.)other operations: 2001908 (6778.44 per sec.)Test execution summary:total time: 295.3347stotal number of events: 1000953total time taken by event execution: 29512.3204per-request statistics:min: 3.37msavg: 29.48msmax: 157.93msapprox. 95 percentile: 48.19msThreads fairness:events (avg/stddev): 10009.5300/30.59execution time (avg/stddev): 295.1232/0.02说明:共消耗时间295s,每秒3389个事务,在复合操作中,compressed的表会快一些.。