How to Build Effective MySQL Performance Management and Analysis

MySQL Performance Management Needs and Current Situation

Enterprises migrating to MySQL face challenges: few DBAs must manage many instances, need simple performance‑improvement methods, and require continuous stability.

MySQL provides limited post‑mortem data; performance information is mainly real‑time, making root‑cause tracing difficult when overseeing many databases.

Limitations of Traditional MySQL Performance Management

Typical tools rely on real‑time execution analysis and slow‑query logs. Percona Toolkit can generate slow‑query reports but cannot correlate them with the overall database state at the same period. When a failure occurs, only logs and monitoring metrics are available, which are hard to associate precisely with offending SQL statements.

Design Goals for an Optimized MySQL Performance Management Tool

Lightweight collection – use a privileged database account only, no agents, to avoid additional load.

Incremental recording – store state changes and statement information to enable rich time‑range visualisation and historical back‑trace.

Experience accumulation – define metric‑statement correlations so expert knowledge becomes reusable analysis logic.

Core Metrics and Load Formula

The analysis framework starts from a “performance entry” layer that selects a small set of core metrics:

QPS – raw query volume; does not reflect response time or load.

AS/PS (Average Statements per Second) – incorporates execution time, providing better correlation with system load.

QRTi (Query Response Time index) – scores queries based on response‑time thresholds, enabling quick identification of slow‑query risk.

Load can be approximated as Load = QPS × avg_Latency × time. Relying solely on QPS may miss slow queries that are not captured within the sampling interval. AS/PS counts statement execution time, aligning database‑level load with OS‑level load for more accurate root‑cause analysis.

Metric Comparison and Correlation

Typical comparison scenarios:

Within a single database: compare Com_delete vs. Innodb_rows_deleted to assess delete impact.

Across databases: compare InnoDB buffer‑pool hit rates.

Across time periods: evaluate the effect of query optimisations.

Metrics can be linked to statements, e.g., high Bytes_sent often correlates with statements returning large result sets; temporary‑table creation rates can be traced to specific statements; high rows_sent values can be associated with particular SQL.

Historical Time‑Period Execution Backtrace

Collect metric deltas over fixed intervals (30 s or 1 min). Special handling is required for periodic statements and for cases where a database restart causes negative deltas. A statement index table with unique IDs enables fast lookup of statement occurrences across databases while reducing storage overhead.

Enriching Statement Information

Performance data is sourced from performance_schema, providing dimensions such as execution count, latency, and I/O. Although statements are stored as templates, extracting concrete examples for specific time windows allows deeper plan analysis.

Extended Capabilities

Beyond raw performance, the framework can incorporate:

SQL syntax checks and configuration audits.

Detection of schema issues (missing indexes, fragmentation, foreign‑key or primary‑key problems).

Holistic database‑operation management that turns manual, experience‑driven troubleshooting into an automated, data‑driven process.