High Performance MySQL [Electronic resources] نسخه متنی

1.3 The SHOW Commands

MySQL users often wonder how to find
out what their server is actually doing at any point in
timeusually 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.

Change user

Connect

Connect Out

Create DB

Debug

Delayed_insert

Drop DB

Field List

Init DB

Kill

Ping

Processlist

Query

Quit

Refresh

Register Slave

Shutdown

Sleep

Statistics

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.