MySQL之路—————sysbench测试（一）

#先安装下编译包

yum install libtool

#下载源码包要通过bzr获取,比较麻烦

#bzr branch lp:~vadim-tk/sysbench/sysbench-db-scripts

文件放到/tpm下

unzip sysben..tab

./autogen.sh

cp /usr/bin/libtool ./

./configure –prefix=/tmp/sysbench-0.4.12 \

–with-mysql \

–with-mysql-includes=/u01/mysql/include/ \

–with-mysql-libs=/u01/mysql/lib ;

make -j 8

sudo make install

sudo cp -r sysbench/tests/ /tmp/sysbench-0.4.12/bin/

cd /tmp/sysbench-0.4.12/bin/

/u01/mysql/bin/mysql -uroot -e “CREATE DATABASE sbtest”

#导数据 耗时较长

./sysbench –test=tests/db/oltp.lua –max-time=7200 –oltp-dist-type=uniform. –max-requests=0 \

–mysql-user=root –mysql-table-engine=innodb –oltp-table-size=5000000 –oltp-tables-count=48 \

–oltp-range-size=40 –oltp-point-selects=10 –oltp-simple-ranges=1 –oltp-sum-ranges=1 \

–oltp-order-ranges=1 –oltp-distinct-ranges=1 –oltp-index-updates=1 –oltp-non-index-updates=1 \

–num-threads=200 –mysql-socket=/u01/mysql/run/mysql.sock \

–oltp-read-only=on perpare

#开始压测 混合读写

./sysbench –test=tests/db/oltp.lua –max-time=7200 –oltp-dist-type=uniform. –max-requests=0 \

–mysql-user=root –mysql-table-engine=innodb –oltp-table-size=5000000 –oltp-tables-count=48 \

–oltp-range-size=40 –oltp-point-selects=10 –oltp-simple-ranges=1 –oltp-sum-ranges=1 \

–oltp-order-ranges=1 –oltp-distinct-ranges=1 –oltp-index-updates=1 –oltp-non-index-updates=1 \

–num-threads=200 –mysql-socket=/u01/mysql/run/mysql.sock \

run

#开始压测 只读

./sysbench –test=tests/db/oltp.lua –max-time=7200 –oltp-dist-type=uniform. –max-requests=0 \

–mysql-user=root –mysql-table-engine=innodb –oltp-table-size=5000000 –oltp-tables-count=48 \

–oltp-range-size=40 –oltp-point-selects=10 –oltp-simple-ranges=1 –oltp-sum-ranges=1 \

–oltp-order-ranges=1 –oltp-distinct-ranges=1 –oltp-index-updates=1 –oltp-non-index-updates=1 \

–num-threads=200 –mysql-socket=/u01/mysql/run/mysql.sock \

–oltp-read-only=on run

#挂载NAS

sudo /etc/rc.d/init.d/rpcbind start

sudo mount -o intr,soft,retry=1,retrans=1 10.232.31.203:/vol/mysqlbackup01 /bak

df -h

#数据备份

sudo ./mydumper -u root -S /u01/mysql/run/mysql.sock -B sbtest -o /bak/uic/sysbench -t 6

#数据导入

mysql -uroot -e “create database sbtest;”

sudo ./myloader -u root -S /u01/mysql/run/mysql.sock -B sbtest -d /bak/uic/sysbench -t 6

run

prepare

watch -n 5 mysql -uroot -e “show engine innodb status\G” |grep “trx0trx.c line 1772″

 

这个工具相对比较简单,是用了percona的lua文件后,支持多表多线程压测;

对于参数对比,瓶颈定位还是非常有效的!~~

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28792027/viewspace-758236/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28792027/viewspace-758236/