基于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，如需转载请自行联系原作者