Monitoring MySQL performance in production environments means establishing continuous observation of your database’s query execution times, resource utilization, and connection patterns to catch bottlenecks before they degrade user experience. The most effective approach combines real-time metrics from the database server itself with application-level query logging and slow query tracking. For example, a WordPress site handling an unexpected traffic spike might see query response times climb from 50ms to 500ms—but only if you’re actively monitoring slow queries and server metrics will you catch this degradation before site visitors experience timeout errors.
Production monitoring differs significantly from development environments because you’re watching live user traffic with real stakes. In a development database, performance issues might go unnoticed because query volumes are low and data sets are small. In production, the same poorly indexed query that takes 100ms with test data might take 5 seconds with a million real rows, creating a cascading problem as connection pools exhaust waiting for results.
Table of Contents
- What Database Metrics Should You Track in Production?
- Setting Up MySQL Slow Query Logging and Analysis
- Using MySQL Performance Schema and System Variables
- Setting Up Real-Time Monitoring with Tools Like Percona Monitoring and Management
- Diagnosing Common Production Performance Issues
- Leveraging Query Optimization Techniques Based on Monitoring Data
- Integrating Database Monitoring with Application Performance Monitoring
- Conclusion
What Database Metrics Should You Track in Production?
The fundamental metrics to monitor are query execution time, slow query logs, active connections, and CPU/memory utilization on the database server. Query execution time tells you which operations are taking longer than expected—most database systems consider anything over 1-2 seconds a problem in production. The mysql slow query log captures queries exceeding a configurable threshold (usually 1-2 seconds) and dumps them to a file, letting you identify repeat offenders without monitoring every single query.
Active connections reveal whether your application is properly closing database connections or leaking them. A WordPress site might typically run 5-10 concurrent connections during normal traffic; if you suddenly see 50 idle connections, you have a connection pool leak that will eventually exhaust your database’s max_connections limit. CPU and memory utilization on the database server directly impact query response times—when MySQL can’t keep indexes in memory, it resorts to disk reads, which are thousands of times slower than RAM access.
Setting Up MySQL Slow Query Logging and Analysis
Enable slow query logging by setting `slow_query_log = 1` and `long_query_time = 2` in your MySQL configuration file (adjusting the threshold based on your application’s performance targets). The slow query log creates a plain-text file containing the actual slow queries, how many rows they examined, and how many they returned—this information tells you whether the query was inefficient or simply working with large datasets. A warning: the slow query log has filesystem overhead, so set a reasonable threshold rather than capturing every query under 1ms.
tools like Percona Toolkit’s `pt-query-digest` parse and summarize your slow query log, grouping identical queries together and showing you execution counts, total time, and average lock time. This reveals the true impact: a query that runs 10,000 times per day at 100ms each is costing you 1000 seconds of database time daily, while a query running once daily might be acceptable even at 5 seconds. Many hosting providers like WP Engine and Kinsta include slow query analysis in their dashboards, eliminating the need to manage these tools yourself if you’re on managed hosting.
Using MySQL Performance Schema and System Variables
MySQL’s Performance Schema is a built-in tool that collects fine-grained performance metrics without requiring external software installation. When enabled with `performance_schema = ON`, it tracks table I/O, index usage, wait events, and prepared statement performance—data that reveals which tables are most heavily accessed and which indexes aren’t being used. For a drupal site, Performance Schema might show that the `node` table is the bottleneck, prompting you to focus optimization efforts there rather than guessing.
Enable specific Performance Schema instruments relevant to your monitoring goals: `events_statements_current` shows queries currently running, `events_waits_summary` reveals lock contention, and `table_io_waits_summary_by_table` shows which tables are causing slowdowns. A limitation to understand: Performance Schema consumes CPU and memory to collect these metrics, so enable only the specific instruments you need rather than everything available. On a high-traffic site, enabling all Performance Schema instruments could itself become a performance problem.
Setting Up Real-Time Monitoring with Tools Like Percona Monitoring and Management
Percona Monitoring and Management (PMM) is an open-source monitoring platform that installs an agent on your database server, continuously collects metrics into a Prometheus time-series database, and visualizes them in Grafana dashboards. Unlike the slow query log, which is passive and post-hoc, PMM gives you real-time visibility into what’s happening right now—you can watch connection spikes, lock contention, and replication lag as they occur. For a busy WordPress multisite, this means catching a runaway plugin query in real-time rather than discovering it after your site is already slow.
The tradeoff with PMM and similar tools is operational complexity. You’re running additional services that themselves need monitoring, patching, and disk space to store metrics. A smaller team might be better served by cloud-based monitoring services like Datadog or New Relic, which handle infrastructure and long-term retention but cost money per monitored host. Many WordPress and Drupal agencies standardize on Datadog APM, which integrates application performance tracing with database monitoring in a single pane of glass.
Diagnosing Common Production Performance Issues
The most common production issue is missing indexes on frequently searched columns. You’ll identify this in slow query logs when a query examines 1 million rows but returns only 100—the WHERE clause isn’t using an index, forcing a table scan. Adding a single `CREATE INDEX idx_post_status ON wp_posts(post_status)` on a WordPress site can reduce that query from 5 seconds to 50ms. However, adding too many indexes creates a different problem: every INSERT and UPDATE becomes slower because MySQL must update all those indexes, eventually making writes your bottleneck instead of reads.
Another common issue is connection pool exhaustion, where your application spawns connection after connection without closing them. WordPress sites sometimes hit this when a plugin repeatedly creates new PDO connections instead of reusing the global `$wpdb` object. Lock contention is a third issue—when many concurrent queries try to modify the same table, they queue up waiting for locks, creating artificial delays. This typically surfaces in slow query logs as queries with high `Lock_time` values, signaling that the query itself was fast but spent significant time waiting to acquire a lock.
Leveraging Query Optimization Techniques Based on Monitoring Data
Once monitoring identifies slow queries, the next step is optimization through EXPLAIN analysis, which shows MySQL’s execution plan: how many rows it examines, whether indexes are used, and join order. Running `EXPLAIN SELECT * FROM wp_posts WHERE post_status = ‘publish’ AND post_date > ‘2024-01-01’` reveals that MySQL will scan the entire posts table (inefficient) rather than using an index (efficient). Adding the right index transforms this query from a table scan to an index seek.
A practical example: a client’s Drupal site had a custom module running `SELECT * FROM cache WHERE expires < UNIX_TIMESTAMP()` every page load to clear expired cache entries. Monitoring revealed this query was taking 2-3 seconds on their 50-million-row cache table. The solution wasn't an index—it was codebase change to use Drupal's built-in cache invalidation rather than manual cleanup. Sometimes optimization requires rethinking the query entirely rather than just adding an index.
Integrating Database Monitoring with Application Performance Monitoring
Database monitoring provides the server-side view, but application performance monitoring (APM) shows the client-side impact. An APM tool like New Relic or Datadog connects database metrics with web request traces, showing that a particular WordPress page load is slow because a specific database query is slow. This connection prevents false optimization efforts—your database metrics might show high CPU, but APM reveals that most of that CPU is being wasted on a query that only 0.1% of users trigger, so optimizing other paths would have greater impact.
Looking forward, modern observability stacks increasingly use distributed tracing, where individual requests are traced from browser through application server to database and back. This visibility, once reserved for enterprise teams, is becoming standard with managed Drupal hosting and WordPress platforms. The convergence means developers can spend less time setting up multiple monitoring tools and more time acting on the insights they generate.
Conclusion
Effective MySQL performance monitoring in production requires layering multiple approaches: slow query logs for historical analysis, Performance Schema for granular metrics, and real-time monitoring tools for immediate visibility into what’s happening now. The common thread is moving from reactive (discovering problems after users complain) to proactive (identifying bottlenecks before they degrade user experience).
Start with slow query logging enabled on all production databases, set up a weekly review cadence to analyze those logs, and add real-time monitoring only if you have the operational capacity to respond quickly to alerts. For WordPress and Drupal sites, many managed hosting providers include monitoring built-in—using their dashboards before building custom infrastructure is almost always the better choice for small to mid-sized teams.
