12 Essential MySQL Debugging Queries for Database Diagnostics

When working with MySQL databases, it’s crucial to have a set of tools and queries at your disposal to diagnose issues, monitor performance, and ensure everything is running smoothly. This article presents 12 essential SQL queries that can help you debug and optimize your MySQL databases effectively.

1. Check Table Size #

This query displays the size of each table along with its indexes in your database. It’s useful for assessing disk space usage.

SELECT 
    table_name AS "Table", 
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" 
FROM 
    information_schema.tables 
WHERE 
    table_schema = "database_name"
ORDER BY 
    (data_length + index_length) DESC;

Replace database_name with the name of your database.

2. View Table Partitions Information #

If your tables are partitioned, this query shows the size of each partition, helping you analyze disk space usage for each part.

SELECT 
    table_name, 
    partition_name, 
    CONCAT(ROUND(SUM(data_length + index_length) / 1024 / 1024, 2), ' MB') AS partition_size 
FROM 
    information_schema.partitions 
WHERE 
    table_schema = 'database_name'
GROUP BY 
    table_name, partition_name;

Replace database_name with the name of your database.

3. View Currently Running Queries #

Displays a list of all active queries in the database. Useful for tracking long-running or stuck queries.

SHOW FULL PROCESSLIST;

4. Search for Running Queries by Substring #

Helps you quickly find active queries containing a specific substring, which is handy when debugging problematic queries.

SELECT * FROM information_schema.processlist 
WHERE info LIKE '%part_of_query%';

Replace %part_of_query% with the substring you want to search for.

5. View Unique Errors from Error Log #

At the operating system level, this command extracts unique errors from the MySQL error log to quickly identify recurring issues.

cat /var/log/mysql/error.log | grep ERROR | sort | uniq

Note: Replace /var/log/mysql/error.log with the actual path to your MySQL error log file.

6. View Current Table Locks #

Shows information about current locks, which is useful for identifying issues with concurrent access to tables.

SELECT * FROM information_schema.innodb_locks;

7. Check Table Fragmentation (Analyze Need for Optimization) #

Displays the status of a table, including fragmentation information. Helps determine if you need to optimize the table using commands like OPTIMIZE TABLE.

SHOW TABLE STATUS WHERE Name = 'table_name';

Replace table_name with the name of your table.

8. Analyze Index Usage #

Shows information about the indexes of a table, which is important for verifying correct index usage to optimize queries.

SHOW INDEX FROM table_name;

Replace table_name with the name of your table.

9. Check InnoDB System Status #

Outputs information about the state of InnoDB, including locks, buffers, and current transactions. Useful for deep analysis of the database state.

SHOW ENGINE INNODB STATUS;

10. View Connection Errors #

Displays the number of failed connection attempts to the server, which is helpful for debugging network issues or authentication settings.

SHOW STATUS LIKE 'Aborted_connects';

11. Check Replication Status #

Shows the current status of replication, including the slave’s position and any replication errors. This is the primary query for monitoring replication health.

SHOW SLAVE STATUS\G;

This query provides valuable information, including:

• Slave_IO_State — Current state of the IO thread.
• Master_Log_File — The master’s log file being read.
• Seconds_Behind_Master — Seconds the slave is behind the master (an important synchronization indicator).

12. Debug Replication Issues #

If replication isn’t working, you can enable the general log for detailed information.

SET GLOBAL general_log = 'ON';

This command turns on the general query log, allowing you to track all SQL statements, including those related to replication, in the general log file.

After debugging, remember to turn off the general log to prevent unnecessary performance overhead:

SET GLOBAL general_log = 'OFF';

By utilizing these queries, you can effectively diagnose and troubleshoot a wide range of issues in your MySQL databases. Whether you’re dealing with performance bottlenecks, replication problems, or just monitoring the health of your database, these tools are essential for any database administrator or developer working with MySQL.

Note: Always ensure you have the necessary permissions to run these queries and commands. Some of them may require administrative privileges.