Back to Basics - SHOW PROCESSLIST
(Page 9 of 10 )
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.
Table 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 PRIVILEGE 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 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. |
Next: SHOW STATUS >>
More MySQL Articles
More By O'Reilly Media
