05：Sysbench压测-innodb_deadlock_detect参数对性能的影响
目录
sysbench压测-innodb_deadlock_detect参数对性能的影响
一、OLTP测试前准备
- 基本信息：
- 主机信息
| CPU | 内存 | 硬盘 | 系统版本 | MySQL版本 | sysbench版本 |
|---|---|---|---|---|---|
| Intel(R) Core(TM) i7-7500U CPU @ 2.70GHz *2 | 2G | 虚拟机硬盘 | CentOS release 6.9 (Final) | 5.7.18 | 1.1.0-76 |
- sysbench 目录（/software/sysbench）
- 脚本（mysql_oltp_sysbench.sh）
- 创建dbtest库
# line V1.2
# mail:
# data: 2017-10-26
# file_name: mysql_oltp_sysbench.sh
# 修改符合公司环境
#通过sysbench测试mysql相关性能，并将关键数据存储于‘dbtest.sysbenc_test’表中
#----------自定义部分----------
#定义记录测试结果的mysql连接相关参数，本例我在测试机上记录测试结果
m_user='root'
m_passwd='iforgot'
m_port='3306'
m_host='localhost'
#测试结果存储于哪个库
m_db='dbtest'
#测试结果存储于哪个表
m_table='sysbench_test'
#sysbench lua脚本目录
lua_dir=/software/sysbench/tests/include/oltp_legacy
#sysbench 参数
SYSBENCH_PARAMETER="--mysql-table-engine=innodb --oltp-table-size=5000000 --oltp-tables-count=16 --oltp-test-mode=complex --rand-type=uniform --rand-init=on --report-interval=10 --max-time=1500 "
#画图维度（关注的三个指标，也就是会画三张图）
target="request_per_second transactions_per_second 95_pct_time"
#定义错误日志文件
log=/tmp/mysql_oltp.log
#定义分析结果文件
data=/tmp/mysql_oltp.dat
#定义测试线程
threds_num='8 24 48 64 96 128 160 196 256'
#每种场景的测试次数，分析时取平均值
times=3
#----------自定义部分结束----------
# sysbenc cleanup and perpare
#sysbench $lua_dir/parallel_prepare.lua --mysql-user=$6 --mysql-password=$7 --mysql-port=$5 --mysql-host=$4 $SYSBENCH_PARAMETER --num-threads=8 cleanup
#sysbench $lua_dir/parallel_prepare.lua --mysql-user=$6 --mysql-password=$7 --mysql-port=$5 --mysql-host=$4 $SYSBENCH_PARAMETER --num-threads=8 prepare
#测试函数
sb_test() {
if [ "$3" == "read-only" ];then read_only='on';else read_only='off';fi #根据脚本参数确定是否read-only
#创建记录测试信息的表
echo -e "\n---------------\n创建测测试结果表$m_db.$m_table\n---------------"
return=$(mysql -u$m_user -p$m_passwd -P$m_port -h$m_host <<EOF 2>&1
CREATE TABLE IF NOT EXISTS $m_db.$m_table (
scenario varchar(30) NOT NULL DEFAULT '' COMMENT '测试场景',
server_name varchar(15) NOT NULL COMMENT '被测DB name',
test_type varchar(15) NOT NULL COMMENT 'read-only,read-write,insert等',
sb_threads int(11) NOT NULL DEFAULT '0' COMMENT 'sysbench 测试线程',
server_load decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '以当前线程测试完后立刻记录一分钟负载值',
request_read int(11) NOT NULL DEFAULT '0',
request_write int(11) NOT NULL DEFAULT '0',
transactions_per_second decimal(12,2) NOT NULL DEFAULT '0.00',
request_per_second decimal(12,2) NOT NULL DEFAULT '0.00',
95_pct_time decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '单位毫秒'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
EOF
)
if [ $? -ne 0 ];then
echo $return|sed 's/[Ww]arning:.*password on the command.*insecure\.//'
#echo "create table $m_db.$m_table failed"
exit -1
fi
#开始测试,每种条件测$times次,分析时取平均值
echo -e "\n---------------\n场景:$2 模式:$3\n---------------"
for i in `seq $times`;do
for sb_threds in $threds_num;do #按照指定的sysbench线程测试
printf " %-10s %s\n" $sb_threds线程 第$i次运行...
#result 作为每次最小测试单元的结果，根据sysbench测试结果各参数的出现顺序
#以request_read、request_write、request_total、request_per_second、total_time、95_pct_time为顺序插入表中
#下条命令中，egerp之后的操作是为了对sysbench的输出做筛选和格式化，以便插入数据库
#sysbench 参数
#--oltp_table_count=1：指定测试过程中表的个数，0.5新增，0.4整个测试过程只有一个表。
#--oltp-table-size=：指定表的大小，如果指定1000，那么它会往表里初始化1000条数据
#--rand-init=on：是否随机初始化数据，如果不随机化那么初始好的数据每行内容除了主键不同外其他完全相同。
#--num-threads=：测试过程中并发线程数，看测试要求来定并发压力。
#--otlp-read-only=off：知否只读测试
#--report-interval=10：每隔多久打印一次统计信息，单位秒，0.5新增
#--rand-type=special：数据分布模式，special表示存在热点数据，uniform表示非热点数据模式，还有其他几个选项。
#--rand-spec-pct=5：这个与上面那个选项相关，热点数据的百分比，我们公司的一个应用测试出来是4.9%的热点数据。
#--mysql-table-engine=$type：表的存储引擎类型，innodb/myisam/tokudb/这些都可以。
#--max-time=8000：这个命令跑多长时间，单位秒，与之相反的是指定请求数--max-requests
sysbench $lua_dir/oltp.lua --mysql-user=$6 --mysql-password=$7 --mysql-port=$5 --mysql-host=$4 \
--num-threads=$sb_threds $SYSBENCH_PARAMETER --oltp-skip-trx=on --oltp-read-only=$read_only run &> $log
if [ $? -ne 0 ];then
echo -e "\nSysbench error! For more information see $log"
exit -1
fi
result=$(cat $log | egrep "read:|write:|transactions:|queries:|95th\ percentile:" | sed -r -e "s/[0-9]+ \(//g" -e "s/\ per sec\.\)//g" -e "s/m?s$//g"| awk '{printf("%s ",$NF)}' | sed "s/\ /,/g" | sed "s/,$//g" | sed "s/(//g")
#测试完成后立刻记录系统一分钟负载值，可近似认为测试过程中proxy的负载抽样
load=`uptime|awk -F: '{print $NF}'| awk -F , '{print $1}'`
if [ -s $load ];then
load=0.00
fi
#本次测试结果写入数据库
return=$(mysql -u$m_user -p$m_passwd -P$m_port -h$m_host $m_db <<EOF 2>&1
INSERT INTO $m_table (scenario,server_name,test_type,sb_threads,server_load,request_read,
request_write,transactions_per_second,request_per_second,95_pct_time)
VALUES ('$2','$4','$3','$sb_threds','$load',$result);
EOF
)
# echo "INSERT INTO $m_table (scenario,server_name,test_type,sb_threads,server_load,request_read,request_write,transactions_per_second,request_per_second,95_pct_time) VALUES ('$2','$4','$3','$sb_threds','$load',$result)"
# exit 1
if [ $? -ne 0 ];then
echo -e "\n----------$sb_threds线程测试，第$i次插入数据库时失败----------"
#错误输出中排除mysql安全提示
echo $return|sed 's/[Ww]arning:.*password on the command.*insecure\.//'
#echo "INSERT VALUES ('$2','$4','$3',$sb_threds,$load,$result)"
exit -2
fi
sleep 60 #让库歇一会，也让一分钟负载能够恢复到测试前的值
done
done
}
#结果分析函数
sb_analyse() {
#2>&1|grep部分为避免安全提示,使用该技巧就无法获取SQL执行的返回码了
mysql -u$m_user -p$m_passwd -h$m_host -P$m_port <<EOF 2>&1|grep -v 'password on the command line'
SELECT
scenario,
server_name,
test_type,
sb_threads,
convert(avg(server_load),decimal(12,2)) as server_load,
convert(avg(request_read),decimal(12,0)) as request_read,
convert(avg(request_write),decimal(12,0)) as request_write,
convert(avg(transactions_per_second),decimal(12,2)) as transactions_per_second,
convert(avg(request_per_second),decimal(12,2)) as request_per_second,
convert(avg(95_pct_time),decimal(12,2)) as 95_pct_time
FROM $m_db.$m_table group by scenario,server_name,test_type,sb_threads
EOF
}
#画图函数
sb_chart() {
sb_analyse >$data
for chart_type in $target;do
col_num=0 #该行及下面这个for循环用于取得三个指标在数据中的列号
for col_name in `cat $data |awk 'NR<2 {print}'`;do
let col_num++
if [ $col_name == $chart_type ];then break;fi
done
if [ $chart_type == "transactions_per_second" ];then #根据图表特点为不同的chart_type设置gunplot不同的key position
key_pos="bottom right"
unit=""
elif [ $chart_type == "request_per_second" ];then #根据图表特点为不同的chart_type设置gunplot不同的key position
key_pos="bottom right"
unit=""
elif [ $chart_type == "95_pct_time" ];then
key_pos="top left"
unit="(ms)"
fi
plot_cmd="set term png size 800,600;set output '/tmp/$chart_type.png';set title '$chart_type $unit';set grid;set key $key_pos;plot "
if [ $# -eq 0 ];then
#对分析结果中所有场景进行画图
for scenario in `mysql -u$m_user -p$m_passwd -h$m_host -P