MySQL Performance Monitoring: Key Metrics & Tools

MySQL performance monitoring is the practice of tracking database metrics like query latency, throughput, connection usage, and resource consumption to identify slowdowns and prevent outages. You monitor by collecting metrics from MySQL itself (status variables, performance_schema) and from the operating system, then alerting when values cross thresholds. This article explains the core metrics, the main monitoring approaches, and how to turn raw data into fixes.

What MySQL Monitoring Actually Tracks

MySQL exposes hundreds of status variables (starting with Com_, Innodb_, Threads_, Bytes_) plus the performance_schema and sys schema, which give per-query and per-wait-event detail. The operating system contributes CPU, memory, disk I/O, and network stats. Effective monitoring combines both layers because a slow query can be caused by the database logic or by a disk that is saturated.

The most commonly tracked groups are:

Throughput: Queries per second (QPS), transactions per second, rows read/written.
Latency: Average and 95th/99th percentile query execution time, connection time.
Connections: Current connections, max connections, aborted connections, thread cache hits.
InnoDB internals: Buffer pool hit ratio, redo log writes, row lock waits, deadlocks.
Replication: Seconds behind master, relay log position, slave SQL thread state.
OS resources: CPU usage, memory pressure (swap), disk latency and queue depth, network throughput.

For example, Threads_connected rising steadily while Threads_running stays low often indicates connection pooling issues, not a database bottleneck. Innodb_buffer_pool_reads (reads from disk) versus Innodb_buffer_pool_read_requests (reads from memory) gives the buffer pool hit ratio; a ratio below 95% usually means the pool is too small or the working set does not fit.

How MySQL Monitoring Works: The Data Sources

MySQL offers three main data collection mechanisms, each with different overhead and detail levels.

Status variables and global counters: Use SHOW GLOBAL STATUS or query information_schema.GLOBAL_STATUS. These are cumulative counters (e.g., Queries total) that you sample over time to compute rates. Overhead is minimal, but you get no per-query detail.
performance_schema: This is an engine that collects wait events, statement summaries, and file I/O data. It is enabled by default in MySQL 5.7+. It provides fine-grained data like which table has the most lock waits, but it consumes memory and CPU; you can tune which instruments are enabled.
sys schema: A set of views that make performance_schema readable, e.g., sys.statement_analysis shows the top queries by total latency. It is installed by default and is the easiest starting point.

External tools (Prometheus + mysqld_exporter, Percona Monitoring and Management, Datadog, New Relic) typically connect as a MySQL user with PROCESS and REPLICATION CLIENT privileges and scrape these sources periodically (every 10–60 seconds). They store time-series data, draw graphs, and fire alerts.

For one-off diagnostics, the built-in SHOW ENGINE INNODB STATUS output shows the latest deadlock, current transactions, and buffer pool stats. EXPLAIN on a query shows the execution plan, which reveals missing indexes or full table scans.

Choosing a Monitoring Approach

Your choice depends on team size, budget, and whether you run on-premises or in the cloud. Here are the common paths:

Built-in tools only: Use performance_schema and sys for ad-hoc checks. Good for a single instance or for learning, but no historical graphs or proactive alerts.
Open-source stack: Prometheus with mysqld_exporter plus Grafana dashboards. Free, widely used, and gives you a full time-series database. Requires setup and maintenance.
Commercial SaaS agents: Datadog, New Relic, Percona Monitoring and Management (PMM is free but self-hosted). They offer automated dashboards, alerting, and query explain plans, but cost money per host.
Cloud provider tools: AWS RDS Performance Insights, Google Cloud SQL’s Query Insights, Azure Database for MySQL’s built-in monitoring. These are integrated with the managed service and require no extra agent.

For a single small application, start with SHOW GLOBAL STATUS and the sys schema. For a production system with multiple replicas, invest in a time-series tool that can alert on trends, not just thresholds.

Common Problems and How to Spot Them

Most MySQL performance issues fall into a few repeatable categories. Here is what to look for:

Slow queries: Check sys.statement_analysis for queries with high total latency. Look for missing indexes (full scans), inefficient joins, or queries that fetch too many rows. Use EXPLAIN to see the plan.
Connection spikes: Threads_connected near max_connections causes “Too many connections” errors. Check if the app leaks connections or if a connection pool is misconfigured.
Buffer pool thrashing: Low hit ratio (< 95%) means disk reads are happening. Increase innodb_buffer_pool_size (up to 70–80% of RAM for a dedicated DB) or optimize queries to touch fewer pages.
Lock contention: Innodb_row_lock_current_waits and Innodb_row_lock_time_avg indicate row locks. Look for long transactions that hold locks, or queries that update many rows.
Replication lag: Seconds_Behind_Master growing means the replica cannot keep up. Check for long-running queries on the replica, insufficient CPU, or disk I/O.
Disk I/O saturation: OS metrics like iowait or disk latency above 20–30 ms indicate the storage is the bottleneck. Consider faster disks or moving to SSD.

A classic example: a query that joins a 10-million-row table without an index will show high Handler_read_rnd_next (random reads) and high CPU. Adding an index can reduce execution time from seconds to milliseconds.

Key Metrics to Watch First

If you only monitor five things, make them these:

Query latency (p95) – the experience your users actually feel.
Queries per second – the workload volume; sudden changes indicate traffic shifts or a runaway query.
Connection usage – percentage of max_connections used; a spike here is often the first sign of a problem.
Buffer pool hit ratio – memory efficiency; below 95% means disk reads are slowing you down.
Replication lag – if you use replicas, this tells you how stale your reads are.

Also watch CPU and memory at the OS level, because a MySQL server that is swapping will show high latency even with perfect queries.

Monitoring Costs and Overhead

Monitoring itself consumes resources. The performance_schema can use 10–20% extra memory and CPU if all instruments are enabled. In practice, you can disable unused instruments (e.g., wait/io/file/innodb if you don’t need file-level detail) to reduce overhead. External agents like mysqld_exporter are lightweight (a few MB of RAM) but the time-series database (Prometheus) can grow quickly; retention policies help.

Commercial tools charge per host per month, which can be significant for a fleet of replicas. Cloud-managed tools like RDS Performance Insights include a free tier (7 days of history) and paid longer retention.

Alternatives and Complementary Tools

Full monitoring is not the only way to catch problems. Alternatives that complement or replace it:

Slow query log: Enable slow_query_log with a threshold (e.g., 2 seconds) to capture the worst queries. Analyze with mysqldumpslow or pt-query-digest.
pt-query-digest (Percona Toolkit): Summarizes slow query logs and general logs to show the top query patterns.
EXPLAIN: Manual query analysis for a single problematic query.
MySQL Enterprise Monitor: Commercial Oracle tool with query analysis and advisory rules.
ProxySQL or MaxScale: If you need query routing and caching, these can also log query performance.

The slow query log is often the first step before investing in full monitoring, because it directly shows the queries that need attention.

Frequently Asked Questions

What is the simplest way to start monitoring MySQL?

Enable the slow query log and run SELECT * FROM sys.statement_analysis LIMIT 10; to see the top time-consuming queries. For historical graphs, install Prometheus and mysqld_exporter; it takes about 30 minutes and gives you a solid dashboard.

How often should I collect MySQL metrics?

Every 15–60 seconds is typical for status variables. For performance_schema, you can query it on demand. Collecting more often than every 5 seconds adds overhead and rarely helps because most issues develop over minutes.

What does “buffer pool hit ratio” mean and why is it important?

It is the percentage of data requests served from memory instead of disk. A ratio below 95% means many disk reads, which are 10–100x slower than memory. Increase innodb_buffer_pool_size or optimize queries to improve it.

Why is my MySQL slow even though CPU is low?

Low CPU with high latency often points to disk I/O waits (check iowait), row lock waits, or network latency. Use SHOW ENGINE INNODB STATUS to see current waits, and check sys.innodb_lock_waits for blocking transactions.

Can monitoring itself cause performance problems?

Yes, if you enable every performance_schema instrument or query it too frequently. Keep only the instruments you need, and use a dedicated monitoring user with limited privileges. The overhead is usually under 5% when configured sensibly.

What is the difference between QPS and latency?

QPS (queries per second) is throughput – how many queries the server handles per second. Latency is the time one query takes. A server can have high QPS but terrible latency if queries are simple; or low QPS with high latency if queries are complex. Monitor both to understand the full picture.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →