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

  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

接下来看脚本

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

#!/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接口即可。

     本文转自kai404 51CTO博客，原文链接：http://blog.51cto.com/kaifly/1745011，如需转载请自行联系原作者