SHOW PROCESSLIST - MySQL

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.* It’s roughly equivalent to the ps or top commands in Unix or the Task Manager in Windows.

(* Not all threads appear in the SHOW PROCESSLIST output. The thread that handles incoming network connec tions, 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 pro duces 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: NUL L
Command: Quer y
Time: 0
State: NUL L
Info: show processlis t

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

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

Time

The number of seconds that the process has been running the current com mand. 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 charac ters are displayed in the output of SHOW PROCESSLIST . To get the full SQL, use SHOW FULL PROCESSLIST . 

If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today! Visit the O'Reilly Network http://www.oreillynet.com for more online content.