Data Management

Specialized status commands in MySQL

Obtaining status information from MySQL goes beyond the SHOW STATUS command. We describe a number of the other useful commands to help you understand the performance of your database.

While the SHOW STATUS command in MySQL generates a lot of useful statistics for understanding database performance, there are a number of more specialized commands you should also be familiar with. These give you specific status information that could help you debug any performance issues.

SHOW INNODB STATUS

This command reveals performance statistics for the InnoDB table handler. InnoDB tables are the most sophisticated table types available in MySQL, and include support for large table files, crash recovery, and transactions (COMMIT and ROLLBACK commands). If you're using InnoDB tables (and you should) for your applications, then review the data provided by the SHOW INNODB STATUS command to ensure that all the values are within optimal limits.

SHOW TABLE STATUS

This command reveals information about all the tables in a particular database, including the table type, size, number of rows, number and size of indexes, and available space. This is useful if you need to check on the properties of a particular table but don't really want to run a SELECT COUNT(*) type of command.

SHOW ERRORS, SHOW WARNINGS

These two commands list all the errors and warnings generated by the last command executed. Similarly, the SHOW LOGS command tells you which log files (if any) are in use at the current time.

SHOW SLAVE STATUS, SHOW MASTER STATUS

If you're using MySQL replication, the SHOW SLAVE STATUS and SHOW MASTER STATUS reveal the status of the two agents involved in the replication process.

Remember to look in the MySQL manual for detailed explanations of every variable, and for tips on how to optimize your server for maximum performance.


Read more about performance statistics in MySQL

"Understanding real-time performance statistics in MySQL"

"How to use the SHOW STATUS command in MySQL"

"Understanding the performance statistics from SHOW STATUS"

"How to examine and kill MySQL client processes"


Editor's Picks