mysql性能检查脚本-部分(二)
SQL_USER} -p${MYSQL_PASS} -e"${qps_01}" |grep -v Variable_name \
|cut -f 2 >${qps_re}
qps_02=`cat ${qps_re}`
qps_03=`cat /proc/uptime |awk '{print $1}'`
qps_04=`awk 'BEGIN{print '${qps_02}' / '${qps_03}'}'` #shell默认不支持浮点运算
cechon "4. current mysql server QPS is: ${qps_04} " red
echo " "
rm -rf ${qps_re}
#5.TPS(每秒事务量)
tps_01="show status where Variable_name in('Com_commit'); "
tps_02="show status where Variable_name in('Com_rollback'); "
tps_re01="tpsre01.`date +%Y%m%d%H%M%S`.txt"
tps_re02="tpsre02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_01}" |grep -v Variable_name \
|cut -f 2 >${tps_re01}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${tps_02}" |grep -v Variable_name \
|cut -f 2 >${tps_re02}
tps_03=`cat ${tps_re01}`
tps_04=`cat ${tps_re02}`
tps_sum=`awk 'BEGIN{print '${tps_03}' + '${tps_04}'}'` #shell默认不支持浮点运算
tps_uptime=`cat /proc/uptime |awk '{print $1}'`
tps_avg=`awk 'BEGIN{print '${tps_sum}' / '${tps_uptime}'}'` #shell默认不支持浮点运算
cechon "5. current mysql server TPS is: ${tps_avg} " red
echo " "
rm -rf ${tps_re01}
rm -rf ${tps_re02}
#6.key Buffer 命中率
#key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%
kbrd_01="show status like 'Key_reads'; "
kbrd_02="show status like 'Key_read_requests'; "
kbrd_re01="kbrd01.`date +%Y%m%d%H%M%S`.txt"
kbrd_re02="kbrd02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${kbrd_01}" |grep -v Variable_name \
|cut -f 2 >${kbrd_re01}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${kbrd_02}" |grep -v Variable_name \
|cut -f 2 >${kbrd_re02}
kbrd_03=`cat ${kbrd_re01}`
kbrd_04=`cat ${kbrd_re02}`
if [ "${kbrd_03}" -eq 0 ];then
cechon "6.1 there is no any value!" green
echo " "
else
kbrd_05=`awk 'BEGIN{print '${kbrd_03}' / '${kbrd_04}'}'` #shell默认不支持浮点运算
kbrd_06=`awk 'BEGIN{print '1-${kbrd_05}'}'` #shell默认不支持浮点运算
key_buffer_read_hits=`awk 'BEGIN{print '${kbrd_06}' * 100}'`
cechon "6.1 current mysql key_buffer_read_hits is: ${key_buffer_read_hits} " red
echo " "
fi
rm -rf ${kbrd_re01}
rm -rf ${kbrd_re02}
#key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%
kbwd_01="show status like 'Key_writes'; "
kbwd_02="show status like 'Key_write_requests'; "
kbwd_re01="kbwd01.`date +%Y%m%d%H%M%S`.txt"
kbwd_re02="kbwd02.`date +%Y%m%d%H%M%S`.txt"
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${kbwd_01}" |grep -v Variable_name \
|cut -f 2 >
${kbwd_re01}
mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e"${kbwd_02}" |grep -v Variable_name \
|cut -f 2 >${kbwd_re02}
kbwd_03=`cat ${kbwd_re01}`
kbwd_04=`cat ${kbwd_re02}`
if [ "${kbwd_03}" -eq 0 ] ;then
cechon "6.2 there is no any value!" green
echo " "
else
kbwd_05=`awk 'BEGIN{print '${kbwd_03}' / '${kbwd_04}'}'` #shell默认不支持浮点运算
kbwd_06=`awk 'BEGIN{print '1-${kbwd_05}'}'` #shell默认不支持浮点运算
key_buffer_write_hits=`awk 'BEGIN{print '${kbwd_06}' * 100}'`
cechon "6.2 current mysql key_buffer_write_hits is: ${key_buffer_write_hits} " red
echo " "
fi
rm -rf ${kbwd_re01}
rm -rf ${kbwd_re02}
#7.InnoDB Buffer命中率
#Innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%
innob_01="show s