How to Monitor MySQL Database Performance

I’ve lost count of how many “the app is slow” tickets have turned out to be MySQL problems in disguise. Over the years, monitoring MySQL properly has saved me from firefighting at 2 AM more times than I can count. In this guide, I’ll share the exact metrics, tools, and queries I use as a working DBA to keep a MySQL instance healthy — from the fundamentals to the kind of deep internals knowledge that helps you catch problems before users ever notice.

Why Monitoring Matters More Than People Think

A database can be “up” and still be failing your application — slow queries, lock contention, and replication lag don’t necessarily show up as downtime, but they absolutely show up as a bad user experience. I treat monitoring as a first-class part of database operations, not an afterthought bolted on after an incident.

MySQL Architecture and Where Bottlenecks Happen

graph TD
    A[Client Connections] --> B[Connection/Thread Layer]
    B --> C[Query Cache - removed in 8.0]
    B --> D[Parser & Optimizer]
    D --> E[Storage Engine - InnoDB]
    E --> F[Buffer Pool]
    E --> G[Redo Log]
    E --> H[(Disk I/O)]

Every layer here can become a bottleneck:

• Connection layer — too many connections, or connections not being released, exhausts max_connections.
• Parser/optimizer — poorly written queries generate inefficient execution plans.
• Buffer pool — if it’s too small, MySQL constantly evicts hot data and re-reads from disk.
• Disk I/O — the ultimate bottleneck once everything else is tuned; slow storage caps throughput no matter how good your queries are.

Key Metrics I Watch Every Day

Enabling and Reading the Slow Query Log

This is usually the first thing I check on any unfamiliar MySQL instance.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

I set long_query_time to 1 second as a starting point, then tighten it once I’ve cleaned up the obvious offenders.

Analyzing the log with mysqldumpslow:

mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

This shows me the top 10 slowest queries sorted by total time — usually the fastest way to find what’s actually hurting performance, rather than guessing.

For a more detailed breakdown I prefer Percona Toolkit’s pt-query-digest:

pt-query-digest /var/log/mysql/slow-query.log > slow_report.txt

Sample output:

# Query 1: 0.12 QPS, 0.45x concurrency, ID 0xABC123 at byte 0
# Attribute    pct   total     min     max     avg     95%  stddev  median
# Exec time     42   4520ms    12ms   890ms   201ms   650ms   180ms   150ms

That 95% column is what I look at most — it tells me what the worst-case experience looks like for most users, not just the average.

Using EXPLAIN and EXPLAIN ANALYZE

Whenever I find a slow query, I run:

EXPLAIN SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2026-07-01'
ORDER BY o.total DESC
LIMIT 20;

Output (simplified):

+----+-------------+-------+------+---------------+---------+---------+------+------+
| id | select_type | table | type | possible_keys | key     | key_len | rows | Extra|
+----+-------------+-------+------+---------------+---------+---------+------+------+
|  1 | SIMPLE      | o     | ALL  | NULL          | NULL    | NULL    | 50000| Using filesort|
+----+-------------+-------+------+---------------+---------+---------+------+------+

type: ALL and Using filesort are red flags — a full table scan combined with an expensive sort operation. In MySQL 8.0+, I prefer EXPLAIN ANALYZE because it shows actual execution time, not just the estimated plan:

EXPLAIN ANALYZE
SELECT o.id, o.total FROM orders o WHERE o.created_at > '2026-07-01' ORDER BY o.total DESC LIMIT 20;

-> Limit: 20 row(s)  (actual time=210.4..210.5 rows=20 loops=1)
    -> Sort: o.total DESC  (actual time=210.3..210.4 rows=20 loops=1)
        -> Filter: (o.created_at > '2026-07-01')  (actual time=0.05..190.2 rows=48210 loops=1)
            -> Table scan on o  (actual time=0.03..120.1 rows=200000 loops=1)

Seeing Table scan on o scanning 200,000 rows to return 20 tells me exactly where to add an index.

Using performance_schema and sys Schema

I lean on the sys schema heavily because it wraps performance_schema internals into human-readable views.

-- Top 10 queries by total execution time
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;

-- Unused indexes taking up space and slowing writes
SELECT * FROM sys.schema_unused_indexes;

-- Which tables have the most I/O
SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10;

-- Current lock waits
SELECT * FROM sys.innodb_lock_waits;

These views have genuinely changed how fast I can diagnose issues — what used to take me digging through raw performance_schema tables now takes one query.

Monitoring InnoDB Internals

SHOW ENGINE INNODB STATUS\G

I always scan this output for:

• BUFFER POOL AND MEMORY section — hit ratio should generally stay above 99% on a well-sized instance.
• TRANSACTIONS section — long-running transactions holding locks.
• ROW OPERATIONS — insert/update/delete rates, useful for spotting sudden spikes.

Buffer pool hit ratio calculation:

SELECT
  (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 AS hit_ratio
FROM
  (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') a,
  (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests') b;

If this drops noticeably below 99%, it’s often a sign the buffer pool is too small for the working data set, and I’ll consider increasing innodb_buffer_pool_size.

External Monitoring Tools I Rely On

graph LR
    A[MySQL Server] --> B[mysqld_exporter]
    B --> C[Prometheus]
    C --> D[Grafana Dashboards]
    C --> E[Alertmanager]
    E --> F[Slack/PagerDuty Alerts]

Setting Up Alerts That Actually Matter

I’ve learned to avoid alert fatigue by only alerting on things that require action:

• Replication lag exceeding a threshold (e.g., 30 seconds) for more than 2 minutes
• Threads_connected approaching 80% of max_connections
• Buffer pool hit ratio dropping below 95%
• Disk space on the data volume below 15%
• Any query in the slow log exceeding a hard SLA threshold (e.g., 5 seconds)

Security Considerations for Monitoring

• Monitoring accounts should have read-only privileges (PROCESS, REPLICATION CLIENT, SELECT on performance_schema/sys), never write access.

CREATE USER 'monitor_user'@'10.0.0.%' IDENTIFIED BY 'MonitorPass123!';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON performance_schema.* TO 'monitor_user'@'10.0.0.%';
GRANT SELECT ON sys.* TO 'monitor_user'@'10.0.0.%';

• Restrict monitoring dashboards (Grafana, PMM) behind authentication and internal networks only — they often expose query text, which can leak sensitive data patterns.
• Rotate and store slow query logs securely, since query text may contain parameter values in some configurations.

Real-World Scenario: Catching a Regression Before It Became an Outage

On one project, our Grafana dashboard flagged a steady climb in average query latency over about 45 minutes — nothing had crashed yet, but the trend line was unmistakable. Digging into sys.statement_analysis, I found a new deployment had introduced a query missing a WHERE clause index due to a recent schema change that dropped an old column an index depended on. We caught and fixed it within the hour, well before it caused a full outage — a good example of why trend-based alerting matters as much as threshold-based alerting.

Troubleshooting Playbook

Frequently Asked Questions

How often should I review slow query logs? I review them daily on high-traffic systems, and set automated weekly summary reports (via pt-query-digest or PMM) for less critical ones.

What’s a “good” buffer pool hit ratio? Generally above 99% for OLTP workloads. Lower ratios usually mean the buffer pool is undersized relative to your working data set.

Is the query cache still useful in modern MySQL? No — the query cache was removed entirely in MySQL 8.0 due to scalability issues under concurrent writes. I rely on proper indexing and application-level caching (Redis) instead.

Should I monitor replicas the same way as the primary? Yes, and I also specifically track replication lag and Replica_SQL_Running_State, which don’t apply to a standalone primary.

Interview Questions on This Topic

1. What’s the difference between EXPLAIN and EXPLAIN ANALYZE?
2. How would you calculate the InnoDB buffer pool hit ratio, and why does it matter?
3. What does Using filesort in an EXPLAIN plan indicate, and how would you address it?
4. Why was the MySQL query cache removed in version 8.0?
5. What privileges should a dedicated monitoring user have, and why not more?

Key Takeaways

• Enable and regularly review the slow query log — it’s the fastest path to real performance wins.
• Use EXPLAIN ANALYZE for actual execution timing, not just estimated plans.
• The sys schema turns raw performance_schema data into actionable, readable insights.
• Alert on trends and thresholds that require action, not everything that moves.
• Give monitoring accounts read-only privileges — never write or admin access.

References

• MySQL 8.0 Reference Manual — Performance Schema: https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html
• MySQL 8.0 Reference Manual — The sys Schema: https://dev.mysql.com/doc/refman/8.0/en/sys-schema.html
• Percona Toolkit Documentation: https://docs.percona.com/percona-toolkit/