How to monitor MySQL performance?

Monitoring MySQL performance is crucial for ensuring your database runs efficiently and can handle load without bottlenecks. Here’s a structured approach:

---

1. Use MySQL’s Built-in Tools

a. SHOW STATUS

Provides server status variables to monitor performance metrics.

SHOW GLOBAL STATUS;
SHOW SESSION STATUS;

Important metrics:

• Threads_connected → Number of active connections.
• Queries → Total number of queries executed.
• Slow_queries → Number of slow queries.
• Innodb_buffer_pool_reads → Reads from disk vs memory.
• Connections → Total connection attempts.

---

b. SHOW VARIABLES

Shows server configuration variables that affect performance.

SHOW VARIABLES LIKE '%buffer%';
SHOW VARIABLES LIKE '%cache%';

Check for:

• innodb_buffer_pool_size
• query_cache_size
• max_connections

---

c. EXPLAIN / EXPLAIN ANALYZE

Used to analyze query execution plans to detect inefficient queries.

EXPLAIN SELECT * FROM orders WHERE customer_id = 101;

---

d. Slow Query Log

Enable to identify queries taking longer than a threshold.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- seconds

---

2. Use Performance Schema

Performance Schema collects detailed runtime metrics.

SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

It can track:

• Query execution times
• Table I/O
• Index usage
• Wait events

---

3. Use MySQL Workbench / GUI Tools

• MySQL Workbench Performance Dashboard → Monitors key metrics like:
  • CPU usage
  • Connections
  • InnoDB buffer pool usage
  • Slow queries
• phpMyAdmin → Simple server stats and query monitoring.
• Third-party tools: Percona Monitoring & Management (PMM), Datadog, Zabbix, New Relic.

---

4. Monitor System Resources

MySQL performance is affected by the server itself. Monitor:

• CPU & RAM usage (top, htop)
• Disk I/O (iostat, vmstat)
• Network latency

---

5. Analyze Query Performance

• Use EXPLAIN or EXPLAIN ANALYZE to find slow queries.
• Optimize queries with:
  • Proper indexes
  • Avoiding SELECT *
  • Reducing joins/subqueries
  • Using LIMIT where possible

---

6. Track Key Metrics Over Time

Track trends to proactively detect issues:

• Queries per second (QPS)
• Slow queries count
• InnoDB buffer pool hit rate
• Connection utilization

---

✅ Summary

1. Enable slow query logs.
2. Monitor Performance Schema.
3. Use SHOW STATUS and SHOW VARIABLES.
4. Use GUI dashboards (MySQL Workbench, PMM).
5. Track system resource usage.
6. Optimize slow queries and indexes.