The Complete Production SOP: How to Deploy and Run Percona pt-query-digest Slow Query Profiling in Autonomous AI Agents

---
name: "mysql-percona-toolkit-pt-query-digest"
description: "Analyze MySQL slow query logs to detect query anti-patterns, missing indexes, and lock stalls."
metadata:
  author: "Percona"
  version: "1.0.0"
  runtime_compatibility:
    - "codex"
    - "claude-code"
    - "cursor"
    - "windsurf"
triggers:
  - "analyze mysql slow query log"
  - "run pt-query-digest"
  - "identify query bottlenecks mysql"
permissions:
  fs_read: true
  fs_write: "workspace"
  shell_exec: true
  net_access: false
---

# Percona pt-query-digest Slow Query Profiling SOP

1. Ensure pt-query-digest binary is available in runtime PATH.
2. Execute pt-query-digest on target slow log with --limit=10 to isolate top resource consumers.
3. Extract Rank 1 to 3 query fingerprints and compute Rows_examined / Rows_sent ratios.
4. Run EXPLAIN FORMAT=JSON for fingerprint matches before proposing DDL index changes.
5. Never ingest raw .log files exceeding 50KB into context.

#!/usr/bin/env bash
set -euo pipefail

if ! command -v pt-query-digest >/dev/null 2>&1; then
  echo "ERROR: pt-query-digest is not installed in runtime PATH." >&2
  exit 1
fi

pt-query-digest --version
echo "Percona Toolkit ready for agent invocation."

import sys
import json
import subprocess

def check_explain(query_file):
    with open(query_file, 'r') as f:
        query = f.read().strip()
    cmd = ["mysql", "--batch", "--raw", "-e", f"EXPLAIN FORMAT=JSON {query}"]
    res = subprocess.run(cmd, capture_output=True, text=True, check=True)
    plan = json.loads(res.stdout)
    print(f"Verified EXPLAIN tree successfully: {json.dumps(plan, indent=2)}")

if __name__ == '__main__':
    check_explain("./workspace/top_query.sql")