MySQL基准测试小脚本：基于sysbench和tpcc-mysql

     最近入职一家新公司，成为100+服务器200+数据库的管理员之一。这几天的工作首先围绕于做MySQL数据库的基准测试，调整一些原参数配置，然后跑一下测试，最后根据测试结果看看参数修改后的效果。因为要频繁改动和测试，就写了一个Shell小脚本。测试工具基于sysbench（https://github.com/akopytov/sysbench）和tpcc-mysql(https://github.com/Percona-Lab/tpcc-mysql)。每次测试都是基于两个配置文件：原配置文件（/etc/my.cnf）和调整后的配置文件（/etc/my.cnf.opt）。

# !/bin/bash
# Author    : 蛙鳜鸡鹳狸猿
# create_ts : 2017年 12月 20日 星期三 15:45:15 CST
# program   : MySQL Benchmark# 
# 	基于MySQL基准测试工具：
# 	sysbench（https://github.com/akopytov/sysbench）
# 	tpcc-mysql(https://github.com/Percona-Lab/tpcc-mysql)
# ### header parameters ceil ###
cnf_def=/etc/my.cnf
cnf_ori=/etc/my.cnf.ori
cnf_opt=/etc/my.cnf.opt
ser_host=127.0.0.1
ser_user=benchmarker
ser_pswd=1024
ser_dbas=benchmarker
sys_tabs=1000000
sys_time=7200
sys_thre=2
sys_seed=1024
tpc_ware=11
tpc_time=3600
tpc_thre=2
tpc_warm=600
tpc_inva=60
res_dire=/result/
ori_oltp=original_oltp
ori_tpcc=original_tpcc
opt_oltp=optimize_oltp
opt_tpcc=optimize_tpcc
### header parameters floor ###### function block ceil ###
# switch file
function swit_cnf(){mv $1 $2 &> /dev/nullif [ $? == 0 ]; thenecho -e "$1 dies  o(>﹏<)o\n$2 come  \(^o^)/"						return 0						elseecho -e "\n    o(>﹏<)o  switch my.cnf failed  o(>﹏<)o"						exit 1						fi
}# init mysqld
function rest_ser(){service mysqld restart &> /dev/nullif [ $? == 0 ]; thenecho -e "\n\(^o^)/ MySQL restart \(^o^)/\n"						return 0						elseecho -e "\no(>﹏<)o MySQL die o(>﹏<)o\n"						exit 1						fi
}# create database
function crea_dbs(){# $1: MySQL host# $2: MySQL user# $3: MySQL password# $4: MySQL databasemysqladmin -h$1 -u$2 -p$3 create $4 &> /dev/nullif [ $? == 0 ]; thenecho -e "\n\(^o^)/ ${4} created \(^o^)/\n"						return 0						elseecho -e "\no(>﹏<)o ${4} uncreated o(>﹏<)o\n"						exit 1						fi
}# drop database
function drop_dbs(){# $1: MySQL host# $2: MySQL user# $3: MySQL password# $4: MySQL databasemysqladmin -h$1 -u$2 -p$3 -f drop $4 &> /dev/nullif [ $? == 0 ]; thenecho -e "\n\(^o^)/ ${4} dropped \(^o^)/\n"						return 0						elseecho -e "\no(>﹏<)o ${4} undropped o(>﹏<)o\n"						exit 1						fi
}# sysbench benchmark
function sysb_bmk(){# $1: MySQL host# $2: MySQL user# $3: MySQL password# $4: MySQL database# $5: Benchmark table size# $6: Benchmark execute time# $7: Benchmark execute threads# $8: Benchmark random seed# $9: Benchmark result writersysbench --db-driver=mysql --mysql-host=$1 --mysql-user=$2 --mysql-password=$3 \--mysql-db=$4 --table_size=$5 oltp_read_write prepare &&\sysbench --db-driver=mysql --mysql-host=$1 --mysql-user=$2 --mysql-password=$3 \--mysql-db=$4 --table_size=$5 --time=$6 --threads=$7 --rand-seed=$8 \oltp_read_write run &> $9 &&\sysbench --db-driver=mysql --mysql-host=$1 --mysql-db=$4 \--mysql-user=$2 --mysql-password=$3 --table_size=$5 oltp_read_write cleanupreturn 0
}# tpcc benchmark
function tpcc_bmk(){# $1: MySQL host# $2: MySQL user# $3: MySQL password# $4: MySQL database# $5: Benchmark warehouse size# $6: Benchmark execute time# $7: Benchmark execute threads# $8: Benchmark warm-up time# $9: Benchmark result writer# $10: Benchmark report intervaldire_scri=$(locate tpcc_load)dire_tpcc=${dire_scri%tpcc_load}cd ${dire_tpcc}mysql -h$1 -u$2 -p$3 $4 < create_table.sql &&\mysql -h$1 -u$2 -p$3 $4 < add_fkey_idx.sql &&\./tpcc_load  -h$1 -P3306 -d$4 -u$2 -p$3 -w$5 &&\./tpcc_start -h$1 -P3306 -d$4 -u$2 -p$3 -w$5 \-l$6 -c$7 -r$8 -i${10} &> $9return 0
}
### function block floor ########### benchmark block ceil ######### init
updatedb
if [ -d ${res_dire} ]; then> /dev/null
elsemkdir -p ${res_dire}
fi### ori benchmark ceil ###
#
# sysbench
#
rest_ser
crea_dbs ${ser_host} ${ser_user} ${ser_pswd} ${ser_dbas}
sysb_bmk ${ser_host} ${ser_user} ${ser_pswd} ${ser_dbas} ${sys_tabs} ${sys_time} ${sys_thre} ${sys_seed} ${res_dire}${ori_oltp}
if [ $? == 0 ]; thenecho -e "\n\(^o^)/ sysbench oltp fly high \(^o^)/\n"
elseecho -e "\no(>﹏<)o sysbench oltp fall over o(>﹏<)o\n"exit 1
fi
drop_dbs ${ser_host} ${ser_user} ${ser_pswd} ${ser_dbas}
#
# tpcc
#
rest_ser
crea_dbs ${ser_host} ${ser_user} ${ser_pswd} ${ser_dbas}
tpcc_bmk ${ser_host} ${ser_user} ${ser_pswd} ${ser_dbas} ${tpc_ware} ${tpc_time} ${tpc_thre} ${tpc_warm} ${res_dire}${ori_tpcc} ${tpc_inva}
if [ $? == 0 ]; thenecho -e "\n\(^o^)/ tpcc oltp fly high \(^o^)/\n"
elseecho -e "\no(>﹏<)o tpcc oltp fall over o(>﹏<)o\n"exit 1
fi
drop_dbs ${ser_host} ${ser_user} ${ser_pswd} ${ser_dbas}
#
### ori benchmark floor ###### switch config ceil ###
swit_cnf ${cnf_def} ${cnf_ori}
swit_cnf ${cnf_opt} ${cnf_def}
### switch config floor ###### opt benchmark ceil ###
#
# sysbench
#
rest_ser
crea_dbs ${ser_host} ${ser_user} ${ser_pswd} ${ser_dbas}
sysb_bmk ${ser_host} ${ser_user} ${ser_pswd} ${ser_dbas} ${sys_tabs} ${sys_time} ${sys_thre} ${sys_seed} ${res_dire}${opt_oltp}
if [ $? == 0 ]; thenecho -e "\n\(^o^)/ sysbench oltp fly high \(^o^)/\n"
elseecho -e "\no(>﹏<)o sysbench oltp fall over o(>﹏<)o\n"exit 1
fi
drop_dbs ${ser_host} ${ser_user} ${ser_pswd} ${ser_dbas}
#
# tpcc
#
rest_ser
crea_dbs ${ser_host} ${ser_user} ${ser_pswd} ${ser_dbas}
tpcc_bmk ${ser_host} ${ser_user} ${ser_pswd} ${ser_dbas} ${tpc_ware} ${tpc_time} ${tpc_thre} ${tpc_warm} ${res_dire}${opt_tpcc} ${tpc_inva}
if [ $? == 0 ]; thenecho -e "\n\(^o^)/ tpcc oltp fly high \(^o^)/\n"
elseecho -e "\no(>﹏<)o tpcc oltp fall over o(>﹏<)o\n"exit 1
fi
drop_dbs ${ser_host} ${ser_user} ${ser_pswd} ${ser_dbas}
#
### opt benchmark floor #### init
swit_cnf ${cnf_def} ${cnf_opt}
swit_cnf ${cnf_ori} ${cnf_def}
rest_ser######## benchmark block floor ########

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！