概述
偷个懒,分享一个mysql实时监控内存命中率的脚本,先看下其内存结构(假设使用innodb引擎)
![498282bcac861d0482247996de746ebd.png](https://img-blog.csdnimg.cn/img_convert/498282bcac861d0482247996de746ebd.png)
计算公式
查询缓存命中率 = (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'"
![9d27361e660e948f47b6666c0f586f90.png](https://img-blog.csdnimg.cn/img_convert/9d27361e660e948f47b6666c0f586f90.png)
监控脚本
#!/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 "+-------|-------|-------+" 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 $INTERVALdone
输出:
![50228547bfe3b6e34204b42f5ac8eb57.png](https://img-blog.csdnimg.cn/img_convert/50228547bfe3b6e34204b42f5ac8eb57.png)
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下!
![e7a2234b48ad80b50fa3664f9791fccf.png](https://img-blog.csdnimg.cn/img_convert/e7a2234b48ad80b50fa3664f9791fccf.png)