Developing Custom Monitoring Queries

In this lesson, you will learn how to design reusable, diagnostic SQL scripts that transform raw database metrics into actionable performance indicators.

While external monitoring agents are excellent for broad alerts, they often miss the specific "pain points" unique to your application’s logic. By developing custom monitoring queries, you can tap directly into the performance_schema and sys schema to extract metrics that represent your specific business load.

The "Top Heavy" Query Pattern

A common requirement for database administrators is identifying which queries are consuming the most resources over time. Rather than relying on static tools, you can write a reusable script to rank queries by their total latency:

SELECT 
    digest_text AS query_pattern,
    count_star AS execution_count,
    sum_timer_wait / 1000000000000 AS total_latency_sec,
    avg_timer_wait / 1000000000000 AS avg_latency_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY total_latency_sec DESC
LIMIT 5;

Making Queries Reusable

To turn a one-off query into a professional monitoring tool, follow these three steps:

1. Standardize Output: Ensure your query returns consistent columns so they can be piped into reporting tools or saved as CSVs for forensic analysis.
2. Filter by Time: If your server has been running for weeks, global metrics will be skewed. Use sys.x$statement_analysis or filter by last_seen timestamps to focus on recent performance regressions.
3. Encapsulation: Store these queries as VIEW objects or as scripts within your MySQL Shell configuration. This ensures that when an incident occurs, you aren't hunting for syntax; you simply run your predefined diagnostic set.

By moving beyond simple "up/down" checks and creating queries that calculate ratios—such as cache hit ratios derived from global_status or lock wait times from metadata_locks—you evolve from a reactive administrator to a proactive engineer. Treat your monitoring queries like production code: version them in a repository, add comments explaining what "healthy" thresholds look like, and keep them tuned to your schema's evolution.

Key Takeaway: Custom monitoring queries convert raw performance metadata into a tailored dashboard, allowing you to catch performance bottlenecks before they manifest as system-wide failures.