MySQL Troubleshooting

Information-Gathering Tools

Information directs all troubleshooting. It is very important to know what is happening in the server process. I have discussed ways to get this information throughout this book, but here I will add some missing details about the tools discussed.

Information Schema

INFORMATION_SCHEMA is a schema that provides information about database metadata. All SHOW queries are now mapped to SELECT statements from INFORMATION_SCHEMA tables. You can query INFORMATION_SCHEMA tables like any other table; this is their great advantage over other tools. The only problem is that INFORMATION_SCHEMA tables are not optimized to work fast, so queries on them can be slow, especially on tables that contain information about many objects.

I won’t describe each and every table here, because the MySQL Reference Manual contains a great deal of detail about their structure (see http://dev.mysql.com/doc/refman/5.6/en/information-schema.html). Instead, I’ll show a few queries to demonstrate the sort of useful information you can get from the INFORMATION_SCHEMA. You’ll still need the user manual for details. I put the link to the 5.6 MySQL Reference Manual here because I mention a few tables introduced in this version.

To get an idea of what can be done with INFORMATION_SCHEMA, let’s start by extracting an overview of how many tables in each storage engine are in current use. I’m excluding the mysql database from the list because all its tables always use the MyISAM storage engine.

mysql> ...