mysql buffer 命中率_MySQL监控InnoDB Buffer Pool命中率

参加炼数成金的MySQL培训,这是其中的一个作业题.

原来做过类似的监控

http://blog.itpub.net/29254281/viewspace-1222337/

首先,使用脚本进行数据采集

vim /tmp/probe.sh

#!/bin/bash

#采集间隔时间,单位s

asleep=10

user=root

password=root

probe_file=probe.log

while [ 1 ] ; do

a=$(pt-mysql-summary --user $user --password $password --sleep

$asleep | grep -i

"Innodb_buffer_pool_read_requests\|Innodb_buffer_pool_reads" | awk

'{if(length($3)==0) print 0 ;else print $3}'| tr -t '\n' ' ');

Innodb_buffer_pool_read_requests=$(echo $a | awk '{print $1}')

Innodb_buffer_pool_reads=$(echo $a | awk '{print $2}')

Innodb_buffer_read_hit_ratio=$(echo "scale=2; ((1 -

$Innodb_buffer_pool_reads/$Innodb_buffer_pool_read_requests) * 100)" |

bc)

echo $(date "+%Y-%m-%d_%H:%M:%S") $Innodb_buffer_read_hit_ratio >> $probe_file

done;

然后准备绘制图形的脚本

vim probe.gp

set terminal png size 3000,1000 font '/usr/share/fonts/wqy-zenhei/wqy-zenhei.ttc,14';

set yrange [0:100]

set size ratio 0.9 ;

set output '/tmp/probe.png' ;

set xdata time ;

set grid;

set timefmt '%Y-%m-%d_%H:%M:%S' ;

set xlabel "时间"

set ylabel "命中率"

plot '/tmp/probe.log' using 1:2 with line title 'InnoDB Buffer Pool命中率'

每次需要查看InnoDB Buffer Pool命中率的时候,执行命令

然后查看绘制的图形

http://blog.itpub.net/29254281/viewspace-1278514/