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”