Real-Time Slow Query Monitoring Architecture for MySQL

Slow query monitoring is crucial in MySQL operations to diagnose performance fluctuations and slow business queries. When the number of clusters and instances grows, collecting and storing slow queries becomes difficult and real‑time alerts are hard to achieve.

Common Approaches

1. Slow Log Collection

Typically a scheduled task runs pt-query-digest to collect each instance's slow log into a MySQL table; because it is triggered periodically, it is not real‑time.

2. Slow Log Statistics

Queries can be run against the MySQL table using host, port, user, fingerprint, time range, etc., to generate statistics.

3. Slow Log Alerting

Alerts are sent to DBAs and developers after querying the MySQL table, but since the data is stored in full and queried in batches, alerts cannot be real‑time.

Real‑Time Slow Query Monitoring Architecture

The solution introduces an agent that continuously tails MySQL slow‑log files. Each slow‑log segment is pushed as a list item into Redis. One agent can monitor all MySQL instances on a host, aggregating dispersed logs into a single Redis store.

A consumer service pops the assembled entries from Redis, parses host, port, database, and user, matches them to the responsible DBA or developer, and pushes the slow‑query information to the appropriate personnel in real‑time. The service also stores the logs as files keyed by host‑port for later analysis.

Collector Implementation

The collector treats a slow‑log entry as a block that starts with the line # Time and ends before the next # Time. Additional MySQL instance metadata is appended before pushing the block to Redis, resulting in a data structure similar to the diagram shown.

Consumer Implementation

The consumer performs two tasks: (1) it writes the popped messages to files named by ip:port for subsequent pt-query-digest analysis; (2) it looks up the cluster owner and DBA for the ip:port and sends the slow‑query details via SMS or email.

Frontend Display

The UI lists centralized slow‑log files, shows log size aggregated by cluster and instance for a selected time window, and provides an “Analyze” button that runs pt-query-digest on the file, presenting the result.

Real‑time slow SQL queries are displayed by continuously popping data from Redis, allowing rolling updates and optional push notifications to subscribers.

Upcoming Event

An announcement for the D+Talks 2021 technical conference on October 24, where the database operations platform will be presented.