概述

偷个懒，分享一个mysql实时监控内存命中率的脚本，先看下其内存结构（假设使用innodb引擎）

计算公式

查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

# mysql -e "show status like 'Qcache_%s'" | grep -E 'hits|inserts'

索引命中率 = (Key_read_requests - Key_reads) / Key_read_requests * 100%

# mysql -e "show status like 'Key_read%s'"

缓冲池命中率 = (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%

# mysql -e "show status like 'Innodb_buffer_pool_read%s'"

监控脚本

#!/bin/sh
# Get the key value of input arguments format like '--args=value'.
function get_key_value()
{
 echo "$1" | sed 's/^-[cif]=[0-9]*$//' 
}
function usage()
{
cat << EOF
Usage: $0 [options]
 -? Show this help message.
 -i interval The delay between updates in seconds. Default to 1.
 -c count the number of updates. Default to 1.
EOF
}
function parse_options()
{
while [ $# -gt 0 ]; do
 case "$1" in
 -c)
 shift
 COUNT="$1"
 ;;
 -i)
 shift
 INTERVAL="$1"
 RETVAL=$?
 ;;
 *)
 usage
 exit 1
 ;;
 esac
 
 shift
 
done
}
INTERVAL=1
COUNT=1
LOOP=0
PAGESIZE=20 # 每页显示的行数
PRINTHEAD=0 # 打印页眉辅助变量
parse_options "$@"
[ $COUNT -eq 0 ] && LOOP=1
#
# COUNT为0时无限循环(LOOP=1)
# COUNT大于0时循环COUNT次(LOOP=0)
#
while [ $LOOP -eq 1 -o $COUNT -gt 0 ]; do
 # for query cache
 VAR=$(mysql -e "show status like 'Qcache_%s'" | awk '/hits/{print $2} /inserts/{print $2}')
 TOTAL=$(echo $VAR | awk '{print $1}')
 MISS=$(echo $VAR | awk '{print $2}')
 if [ $TOTAL -eq 0 ]; then
 RESULT1=0
 else
 RESULT1=$(echo "scale=2; 100 * ($TOTAL - $MISS) / $TOTAL" | bc)
 fi
 
 # for index cache
 VAR=$(mysql -e "show status like 'Key_read%s'" | awk '/Key_reads/{print $2} /Key_read_requests/{print $2}')
 TOTAL=$(echo $VAR | awk '{print $1}')
 MISS=$(echo $VAR | awk '{print $2}')
 if [ $TOTAL -eq 0 ]; then
 RESULT2=0
 else
 RESULT2=$(echo "scale=2; 100 * ($TOTAL - $MISS) / $TOTAL" | bc)
 fi
 
 # for InnoDB buffer pool
 VAR=$(mysql -e "show status like 'Innodb_buffer_pool_read%s'" | awk '/Innodb_buffer_pool_reads/{print $2} /Innodb_buffer_pool_read_requests/{print $2}')
 TOTAL=$(echo $VAR | awk '{print $1}')
 MISS=$(echo $VAR | awk '{print $2}')
 if [ $TOTAL -eq 0 ]; then
 RESULT3=0
 else
 RESULT3=$(echo "scale=2; 100 * ($TOTAL - $MISS) / $TOTAL" | bc)
 fi
 
 if [ $PRINTHEAD -eq 0 ]; then
 printf "+-------|-------|-------+
"
 printf "|%6s	|%6s	|%6s |
" "qc" "ic" "ibp"
 printf "+-------|-------|-------+
"
 fi
 printf "|%6.2f	|%6.2f	|%6.2f |
" $RESULT1 $RESULT2 $RESULT3
 COUNT=$((COUNT-1))
 PRINTHEAD=$((($PRINTHEAD + 1) % $PAGESIZE))
 sleep $INTERVAL
done

输出：

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下！