1.3 The SHOW Commands

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.

1.3.1 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 120 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.^[3]

^[3] 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 not 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.

1.3.2 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.^[4] It's roughly equivalent to the ps or top commands in Unix or the Task Manager in Windows.

^[4] Not all threads appear in the SHOW PROCESSLIST output. The thread that handles incoming network connections, for example, is never listed.

Executing it produces a process list in tabular form:

mysql> SHOW PROCESSLIST;



+----+---------+-----------+------+-------------+------+-------+------------------+

| Id | User    | Host      | db   | Command     | Time | State | Info             |

+----+---------+-----------+------+-------------+------+-------+------------------+

| 17 | jzawodn | localhost | NULL | 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: jzawodn

   Host: localhost

     db: NULL

Command: Query

   Time: 0

  State: NULL

   Info: show processlist

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

Id

The number that uniquely identifies this process. Since MySQL is a multi-threaded server, it really identifies the thread (or connection) and is unrelated to process IDs the operating system may use. As the operating system does with processes, MySQL starts numbering the threads at 1 and gives each new thread an ID one higher than the previous thread.

User

The name of the MySQL user connected to this thread.

Host

The name of the host or IP address from which the user is connected.

db

The database currently selected. This may be NULL if the user didn't specify a database.

Command

This shows the command state (from MySQL's internal point of view) that the thread is currently in. Table 1-1 lists each command with a description of when you are likely to see it. The commands roughly correspond to various function calls in MySQL's C API. Many commands represent very short-lived actions. Two of those that don't, Sleep and Query, appear frequently in day-to- day usage.

Table 1-1. Commands in SHOW PROCESSLIST output

Command	Meaning
Binlog Dump	The slave thread is reading queries from the master's binary log.
Change user	The client is logging in as a different user.
Connect	A new client is connecting.
Connect Out	The slave thread is connecting to the master to read queries from its binary log.
Create DB	A new database is being created.
Debug	The thread is producing debugging output. This is very uncommon.
Delayed_insert	The thread is processing delayed inserts.
Drop DB	A database is being dropped.
Field List	The client has requested a list of fields in a table.
Init DB	The thread is changing to a different database, typically as the result of a USE command.
Kill	The thread is executing a KILL command.
Ping	The client is pinging the server to see if it's still connected.
Processlist	The client is running SHOW PROCESSLIST.
Query	The thread is currently executing a typical SQL query: SELECT, INSERT, UPDATE, DELETE. This is the most common state other than Sleep.
Quit	The thread is being terminated as part of the server shutdown process.
Refresh	The thread is issuing the FLUSH PRIVILEGES command.
Register Slave	A slave has connected and is registering itself with the master.
Shutdown	The server is being shut down.
Sleep	The thread is idle. No query is being run.
Statistics	Table and index statistics are being gathered for the query optimizer.

Time

The number of seconds that the process has been running the current command. A process with a Time of 90 and Command of Sleep has been idle for a minute and a half.

State

Additional human-readable information about the state of this thread. Here's an example:

Slave connection: waiting for binlog update

This appears on the master server when a slave is actively replicating from it.

Info

This is the actual SQL currently being executed, if any. Only the first 100 characters are displayed in the output of SHOW PROCESSLIST. To get the full SQL, use SHOW FULL PROCESSLIST.

1.3.3 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. It's covered in much greater depth in Appendix A.

1.3.4 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; look for more on storage engines in Chapter 2.

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.