Troubleshooting MySQL Issues: Daily Queries Every DBA Should Know | JusDB
Troubleshooting MySQL Issues: Daily Queries Every DBA Should Know
MySQL DBAs face a wide range of daily challenges: from identifying slow queries to resolving lock contention, replication lag, memory bottlenecks, and connection issues. While monitoring tools provide dashboards, nothing replaces a set of reliable queries that give immediate insights into database health.
At JusDB, our MySQL Consulting, Performance Tuning, and High Availability services rely on these queries daily to troubleshoot issues in production environments.
1) Slow Queries
Slow queries are the root cause of most performance complaints. Enable the slow query log to capture problematic statements:
SET PERSIST slow_query_log = ON; SET PERSIST long_query_time = 1; SET PERSIST log_output = 'FILE';
Check queries currently executing:
SHOW FULL PROCESSLIST;
Identify top queries from the Performance Schema:
SELECT DIGEST_TEXT, COUNT_STAR,
ROUND(SUM_TIMER_WAIT/1e12,2) AS total_time_s,
ROUND(AVG_TIMER_WAIT/1e9,2) AS avg_time_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
2) Locks and Blocking
Locks can cause application stalls if not monitored carefully. Use Performance Schema tables:
SELECT * FROM performance_schema.data_locks; SELECT * FROM performance_schema.data_lock_waits;
Check for deadlocks:
SHOW ENGINE INNODB STATUS\G
3) Replication Issues
Replication lag can cause read inconsistencies. Verify replication status:
SHOW REPLICA STATUS\G
Worker-level details:
SELECT channel_name, worker_id,
LAST_APPLIED_TRANSACTION,
APPLYING_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker;
4) Connection and Thread Monitoring
Too many active connections can overload MySQL:
SHOW GLOBAL STATUS LIKE 'Threads_connected'; SHOW GLOBAL STATUS LIKE 'Threads_running';
Breakdown by client host:
SELECT host, COUNT(*) AS sessions FROM information_schema.processlist GROUP BY host;
5) Memory and Buffer Pool
Check buffer pool stats:
SHOW ENGINE INNODB STATUS\G
Identify top memory consumers:
SELECT event_name, current_alloc FROM performance_schema.memory_summary_global_by_event_name ORDER BY current_alloc DESC LIMIT 10;
6) Disk Usage and Temporary Tables
Identify largest tables:
SELECT table_schema, table_name,
ROUND((data_length+index_length)/1024/1024,2) AS size_mb
FROM information_schema.tables
ORDER BY size_mb DESC
LIMIT 10;
Monitor temporary table usage:
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
7) Errors and Crash Analysis
Check server error logs along with key runtime stats:
SHOW GLOBAL STATUS LIKE 'Aborted_%'; SHOW GLOBAL STATUS LIKE 'Connections';
Inspect recent InnoDB errors:
SHOW ENGINE INNODB STATUS\G
8) Daily DBA Checklist
- ✅ Uptime check (
SHOW GLOBAL STATUS LIKE 'Uptime') - ✅ Active queries (
SHOW FULL PROCESSLIST) - ✅ Slow query digest (Performance Schema)
- ✅ Replication lag (
SHOW REPLICA STATUS) - ✅ Lock waits (
data_locks,data_lock_waits) - ✅ Buffer pool hit ratio (InnoDB stats)
- ✅ Temp tables and disk usage
- ✅ Review error log for new warnings/deadlocks
9) How JusDB Helps
JusDB provides expert DBA services for troubleshooting, optimization, and automation. Our services include:
- Performance Tuning — fix slow queries and improve indexing
- High Availability — deploy MySQL InnoDB Cluster or Orchestrator
- Migrations — upgrade from 5.7 to 8.0 with zero downtime
- Support — 24/7 incident response
- Remote DBA — proactive monitoring and daily health checks
Conclusion
These daily DBA queries provide fast insight into MySQL’s runtime health. From diagnosing slow queries and locks to monitoring replication lag and buffer pool usage, they form the foundation of effective troubleshooting. Combined with proactive monitoring and automation, they help DBAs keep systems reliable and performant.
👉 If you’re facing recurring MySQL performance issues or need round-the-clock support, contact JusDB to learn how our Database Reliability Engineers can help.
Author: JusDB Database Reliability Engineering Team