基于pt-query-digest的慢查询日志持续追踪

POOPE

　　percona-toolkit被称为MySQL DBA的“瑞士***”，其强大性不言而喻。而其中的慢查询分析工具“pt-query-digest”相比官方的“mysqldumpslow”提供了很多额外的属性，例如灵活的过滤器，基于库和表的分析排序等等。
　　今天分享一例基于“pt-query-digest”和邮件自动追踪MySQL慢查询日志的小脚本，同时避免对同类型的sql语句重复提示。
　　直接上脚本(只要安装了percona-toolkit，该脚本可以说是傻瓜式的，当然还是看看官方文档深入理解一下最好啦)
　　首先，我们在test库（工具默认会创建percona_schema库并在其下建表，这里我们在脚本里指定test库）下创建query_history表：
　　这里我没让工具自动建表是因为我想在query_history表里记录‘客户host’和‘当前database’，默认表里没有这两项记录，其次，工具自动建表有好多基本用不上的列

CREATE TABLE `test`.`query_history` (   `checksum` bigint(20) unsigned NOT NULL,   `sample` text NOT NULL,   `host_min` varchar(50) not null DEFAULT '',   `db_min` varchar(30) not null DEFAULT '',   `ts_min` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `ts_max` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `ts_cnt` float DEFAULT NULL,   `Query_time_sum` float DEFAULT NULL,   `Query_time_min` float DEFAULT NULL,   `Query_time_max` float DEFAULT NULL,   `Query_time_pct_95` float DEFAULT NULL,   `Query_time_stddev` float DEFAULT NULL,   `Query_time_median` float DEFAULT NULL,   `Lock_time_sum` float DEFAULT NULL,   `Lock_time_min` float DEFAULT NULL,   `Lock_time_max` float DEFAULT NULL,   `Lock_time_pct_95` float DEFAULT NULL,   `Lock_time_stddev` float DEFAULT NULL,   `Lock_time_median` float DEFAULT NULL,   `Rows_sent_sum` float DEFAULT NULL,   `Rows_sent_min` float DEFAULT NULL,   `Rows_sent_max` float DEFAULT NULL,   `Rows_sent_pct_95` float DEFAULT NULL,   `Rows_sent_stddev` float DEFAULT NULL,   `Rows_sent_median` float DEFAULT NULL,   `Rows_examined_sum` float DEFAULT NULL,   `Rows_examined_min` float DEFAULT NULL,   `Rows_examined_max` float DEFAULT NULL,   `Rows_examined_pct_95` float DEFAULT NULL,   `Rows_examined_stddev` float DEFAULT NULL,   `Rows_examined_median` float DEFAULT NULL,    PRIMARY KEY (`checksum`,`ts_min`,`ts_max`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

　　接下来看脚本

#!/bin/sh
date_ago=`date +%Y-%m-%d\ %H:%M:%S --date='2 hours ago'`  #获取2小时之前的时间点，因为我的crontab里面每2小时执行一遍该脚本  
alias mysql_con='mysql -uuser -ppwd'
datebase=test
#以下两行操作会在test库里创建query_review表，同时将分析结果存储到两张表里
pt-query-digest --history u=user,p=pwd,D=$datebase /mysqldata/dev-env-slow.log --no-report
pt-query-digest --review u=user,p=pwd,D=$datebase /mysqldata/dev-env-slow.log --no-report
rm -f /tmp/slow_query_inc 2> /dev/null  #清空存放上次结果的文件
#从query_review表查询出'first_seen'时间在2小时以内的'checksum',并组织成  checksum1,checksum2的形式，以便后面放在sql语句的in条件里
#where条件保证只查询第一次出现的慢查询信息，避免重复提醒
check_sum=`mysql_con -N -s -e "SELECT checksum FROM percona_schema.query_review where first_seen > '$date_ago'" 2>/dev/null|awk '{printf("%s,",$0)}'|sed 's/,$//'`
#根据上面查出的checksum值从query_history表查出慢查询相关信息
if [ ! -z $check_sum ];then
mysql_con -e "
SELECT
    checksum,
  sample AS Statment,
  host_min AS Host,
  db_min AS DB,
  ts_max AS Time,
  ts_cnt as Counts,
  query_time_pct_95 AS Query_Time,
  Rows_sent_pct_95 AS Rows_Sent,
  Rows_examined_pct_95 AS Rows_Examined
FROM
  $datebase.query_history
WHERE
  checksum in ($check_sum)                 
  AND 
  ts_max in (select max(ts_max) from $datebase.query_history where checksum in ($check_sum) group by checksum)\G
" 2> /dev/null >> /tmp/slow_query_inc
if [ -s /tmp/slow_query_inc ];then  #/tmp/slow_query_inc文件有内容才发邮件
  mail -s 'slow_log in last 2 hours----from Dev' k@test.com < /tmp/slow_query_inc
fi
fi

　　然后在crontab里设置每2小时运行该脚本，有新的慢查询出现的话，就会收到邮件啦，如下图：

　　另有一点：sql监控这个环节最好从开发环境做起，将慢sql扼杀在萌芽阶段^_^
　　

　　续：以上虽然能发邮件提醒了，但是都在邮件里终究还是略显零碎，而且不易跟踪管理。我们项目管理采用的是redmine，于是翻看redmine的API文档，最终实现了将开发环境、生产环境的慢查询都自动记录在redmine里，方便追踪统计。
　　这部分只是想跟大家分享这个思路，大家所采用的项目管理软件不尽相同，所以我这部分的实现过程就不详细展开了。大致就是在上面脚本的基础上再加修改，按照redmine API的规范生成相应的xml文件，然后通过 curl调用redmine接口即可。