The use of SHOW commands in MySQL

MySQL users often wonder how to find out what their server is actually doing at any point in time usually when things start to slow down or behave strangely. You can look at operating system statistics to figure out how busy the server is, but that really doesn't reveal much. Knowing that the CPU is at 100% utilization or that there's a lot of disk I/O occurring provides a high-level picture of what is going on, but MySQL can tell far more.

Several SHOW commands provide a window into what's going on inside MySQL. They provide access to MySQL's configuration variables, ongoing statistics, and counters, as well as a description of what each client is doing.

Command SHOW VARIABLES

The easiest way to verify that configuration changes have taken effect is to ask MySQL for its current variable settings. The SHOW VARIABLES command does just that. Executing it produces quite a bit of output, which looks something like this:

mysql> SHOW VARIABLES;

+-------------------------------+-------------------+
| Variable_name                 | Value             |
+-------------------------------+-------------------+
| back_log                      | 20                |
| basedir                       | mysql             |
| binlog_cache_size             | 32768             |
| character_set                 | latin1            |
| concurrent_insert             | ON                |
| connect_timeout               | 5                 |
| datadir                       | /home/mysql/data/ | 

The output continues from there, covering over 600 variables in total. The variables are listed in alphabetical order, which is convenient for reading, but sometimes related variables aren't anywhere near each other in the output. The reason for this is because as MySQL evolves, new variables are added with more descriptive names, but the older variable names aren't changed; it would break compatibility for any program that expects them. In the rare event they do change, MySQL retains the old names as aliases for the new ones.

Many of the variables in the list may be adjusted by a set-variable entry in any of MySQL's configuration files. Some of them are compiled-in values that can't be changed. They're really constants (not variables), but they still show up in the output of SHOW VARIABLES. Still others are boolean flags.

Notice that the output of SHOW VARIABLES (and all of the SHOW commands, for that matter) looks just like the output of any SQL query. It's tabular data. MySQL returns the output in a structured format, making it easy to write tools that can summarize and act on the output of these commands. We'll put that to good use in later chapters.

Command SHOW PROCESSLIST

The other SHOW command we'll look at is SHOW PROCESSLIST. It outputs a list of what each thread is doing at the time you execute the command. Not all threads appear in the SHOW PROCESSLIST output. The thread that handles incoming network connections, for example, is never listed. It's roughly equivalent to the ps or top commands in Unix or the Task Manager in Windows.

Executing it produces a process list in tabular form:

mysql> SHOW PROCESSLIST;

+----+---------+-----------+------+-------------+------+-------+------------------+
| Id | User    | Host      | db   | Command     | Time | State | Info             |
+----+---------+-----------+------+-------------+------+-------+------------------+
| 17 | root    | localhost | wp5x | Query       | 0    | NULL  | show processlist |
+----+---------+-----------+------+-------------+------+-------+------------------+

It's common for the State and Info columns to contain more information that produces lines long enough to wrap onscreen. So it's a good idea to use the \G escape in the mysql command interpreter to produce vertical output rather than horizontal output:

mysql> SHOW PROCESSLIST \G

*************************** 1. row ***************************
 Id: 17
 User: root
 Host: localhost
 db: wp5x
Command: Query
 Time: 0
 State: NULL
 Info: show processlist

No matter which way you look at it, the same fields are included:

Slave connection: waiting for binlog update

Command SHOW STATUS

In addition to all the variable information we can query, MySQL also keeps track of many useful counters and statistics. These numbers track how often various events occur. The SHOW STATUS command produces a tabular listing of all the statistics and their names.

To confuse matters a bit, MySQL refers to these counters as variables too. In a sense, they are variables, but they're not variables you can set. They change as the server runs and handles traffic; you simply read them and reset them using the FLUSH STATUS command.

The SHOW STATUS command, though, offers a lot of insight into your server's performance.

Command SHOW INNODB STATUS

The SHOW INNODB STATUS status command provides a number of InnoDB-specific statistics. As we said earlier, InnoDB is one of MySQL's storage engines.

The output of SHOW INNODB STATUS is different from that of SHOW STATUS in that it reads more as a textual report, with section headings and such. There are different sections of the report that provide information on semaphores, transaction statistics, buffer information, transaction logs, and so forth.

SHOW INNODB STATUS is covered in greater detail along with SHOW STATUS in Appendix A. Also, note that in a future version of MySQL, this command will be replaced with a more generic SHOW ENGINE STATUS command.