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:

 

 

 

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. 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.

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.

 

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.

 

SHOW COMMAND