High Performance MySQL, 3rd Edition

Appendix B. MySQL Server Status

You can answer many questions about a MySQL server by inspecting its status. MySQL exposes information about server internals in several ways. The newest is the PERFORMANCE_SCHEMA database in MySQL 5.5, but the standard INFORMATION_SCHEMA database has existed since MySQL 5.0, and there are a series of SHOW commands that have existed practically forever. Some information you can get via SHOW commands isn’t found in the INFORMATION_SCHEMA tables.

The challenges for you are determining what is relevant to your problem, how to get the information you need, and how to interpret it. Although MySQL lets you see a lot of information about what’s going on inside the server, it’s not always easy to use that information. Understanding it requires patience, experience, and ready access to the MySQL manual. Good tools are helpful, too.

This appendix is mostly reference material, but you will also find some information on the functioning of server internals, especially in the sections on InnoDB.

System Variables

MySQL exposes many system variables through the SHOW VARIABLES SQL command, as variables you can use in expressions, or with mysqladmin variables at the command line. From MySQL 5.1, you can also access them through tables in the INFORMATION_SCHEMA database.

These variables represent a variety of configuration information, such as the server’s default storage engine (storage_engine), the available time zones, the connection’s collation, and startup parameters. ...