概述

偷个懒，分享一个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 << EOFUsage: $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=1COUNT=1LOOP=0PAGESIZE=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 "+-------|-------|-------+\n" printf "|%6s\t|%6s\t|%6s |\n" "qc" "ic" "ibp" printf "+-------|-------|-------+\n" fi printf "|%6.2f\t|%6.2f\t|%6.2f |\n" $RESULT1 $RESULT2 $RESULT3 COUNT=$((COUNT-1)) PRINTHEAD=$((($PRINTHEAD + 1) % $PAGESIZE)) sleep $INTERVALdone

输出：

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