基于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

接下來看腳本

#!/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' [email protected] < /tmp/slow_query_inc
fi
fi      

然後在crontab裡設定每2小時運作該腳本，有新的慢查詢出現的話，就會收到郵件啦，如下圖：

另有一點：sql監控這個環節最好從開發環境做起，将慢sql扼殺在萌芽階段^_^