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

Working with JusDB on MySQL Operations

These diagnostic queries are the starting point — interpreting what they tell you and knowing which lever to pull next is where experience matters. We provide on-call MySQL DBA support and performance troubleshooting as part of our managed database SRE service. Reach out if you need MySQL expertise on demand.

Related reading: MySQL Performance Tuning | MySQL Timeout Variables | MySQL Group Replication