基於 pt-query-digest 的慢查詢日誌自動巡檢與郵件告警腳本實戰

基於 pt-query-digest 的慢查詢日誌自動巡檢與郵件告警腳本實戰

工具介紹

Percona Toolkit 被譽為 MySQL DBA 的「瑞士軍刀」，其 pt-query-digest 比官方 mysqldumpslow 強大得多：支援靈活過濾、按庫/表聚合、歷史趨勢對比、生成報告等。

核心思路

1. 利用 --review 與 --history 將慢查詢指紋存入資料庫（percona_schema.query_review、query_history）。
2. 定時任務每 15 分鐘掃描一次，只推送「首次出現」的新慢查詢（first_seen = last_seen AND first_seen > 15分鐘前），避免重複告警。
3. 郵件正文包含：樣本 SQL、95% 分位執行時間、返回/掃描行數，便於快速定位。

部署腳本 slow_query_monitor.sh

#!/bin/bash
# 依賴：percona-toolkit、mailx、mysql client
# Crontab: */15 * * * * /path/to/slow_query_monitor.sh

DATE_AGO=$(date -d '15 min ago' '+%Y-%m-%d %H:%M:%S')
MYSQL_CONN="mysql -uuser -ppwd -h127.0.0.1"
SLOW_LOG="/mysqldata/dev-env-slow.log"
SCHEMA="test"
TMP_FILE="/tmp/slow_query_inc"

# 1. 寫入 review & history 表（無報告模式）
pt-query-digest --history u=user,p=pwd,D=$SCHEMA $SLOW_LOG --no-report
pt-query-digest --review  u=user,p=pwd,D=$SCHEMA $SLOW_LOG --no-report

# 2. 清空上次結果
> $TMP_FILE

# 3. 找出 15 分鐘內首次出現的 checksum
for cks in $($MYSQL_CONN -Nse "SELECT checksum FROM percona_schema.query_review WHERE first_seen=last_seen AND first_seen>'$DATE_AGO'"); do
    $MYSQL_CONN -e "
        SELECT 
            sample AS Statement,
            ts_max AS Time,
            query_time_pct_95 AS Query_Time,
            Rows_sent_pct_95 AS Rows_Sent,
            Rows_examined_pct_95 AS Rows_Examined
        FROM percona_schema.query_history
        WHERE checksum='$cks'\G
    " 2>/dev/null | sed 's/^[ \t]*//g' | awk -F: '{print $1": "$2}' >> $TMP_FILE
done

# 4. 有新增慢查詢才發郵件
if [ -s "$TMP_FILE" ]; then
    mail -s "【慢查詢告警】Dev 環境新增慢 SQL - $(date '+%H:%M')" dba@company.com < $TMP_FILE
fi

關鍵參數解析

進階優化建議

• 生產環境只開從庫慢查詢：避免主庫寫入性能抖動。
• 接入監控平台：將結果推送到 Prometheus/Grafana 或 ELK，實現圖表化與歷史追溯。
• 分級告警：結合 Rows_examined/Rows_sent > 100 或 Query_Time > 5s 觸發 P1/P2 不同級別。
• 開發環境左移：CI/CD 流水線集成 pt-query-digest，合併請求階段攔截慢 SQL，扼殺於萌芽。

效果展示

腳本輕量、零依賴框架，幾分鐘即可落地，讓慢查詢無處遁形。