(mysql.info) show-processlist
Info Catalog
(mysql.info) show-procedure-status
(mysql.info) show
(mysql.info) show-status
13.5.4.19 `SHOW PROCESSLIST' Syntax
...................................
SHOW [FULL] PROCESSLIST
`SHOW PROCESSLIST' shows you which threads are running. You can also
get this information using the `mysqladmin processlist' command. If you
have the `SUPER' privilege, you can see all threads. Otherwise, you can
see only your own threads (that is, threads associated with the MySQL
account that you are using). See kill. If you do not use the
`FULL' keyword, only the first 100 characters of each statement are
shown in the `Info' field.
This statement is very useful if you get the `too many connections'
error message and want to find out what is going on. MySQL reserves one
extra connection to be used by accounts that have the `SUPER'
privilege, to ensure that administrators should always be able to
connect and check the system (assuming that you are not giving this
privilege to all your users).
The output of `SHOW PROCESSLIST' may look like this:
mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1030455
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1004
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 3112
User: replikator
Host: artemis:2204
db: NULL
Command: Binlog Dump
Time: 2144
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 4. row ***************************
Id: 3113
User: replikator
Host: iconnect2:45781
db: NULL
Command: Binlog Dump
Time: 2086
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 5. row ***************************
Id: 3123
User: stefan
Host: localhost
db: apollon
Command: Query
Time: 0
State: NULL
Info: SHOW FULL PROCESSLIST
5 rows in set (0.00 sec)
The columns have the following meaning:
* `Id'
The connection identifier.
* `User'
The MySQL user who issued the statement. If this is `system user',
it refers to a non-client thread spawned by the server to handle
tasks internally. This could be the I/O or SQL thread used on
replication slaves or a delayed-row handler. For `system user',
there is no host specified in the `Host' column.
* `Host'
The hostname of the client issuing the statement (except for
`system user' where there is no host).
`SHOW PROCESSLIST' reports the hostname for TCP/IP connections in
`HOST_NAME:CLIENT_PORT' format to make it easier to determine
which client is doing what.
* `db'
The default database, if one is selected, otherwise `NULL'.
* `Command'
The value of that column corresponds to the `COM_XXX' commands of
the client/server protocol. See server-status-variables
The `Command' value may be one of the following: `Binlog Dump',
`Change user', `Close stmt', `Connect', `Connect Out', `Create DB',
`Debug', `Delayed insert', `Drop DB', `Error', `Execute', `Fetch',
`Field List', `Init DB', `Kill', `Long Data', `Ping', `Prepare',
`Processlist', `Query', `Quit', `Refresh', `Register Slave', `Reset
stmt', `Set option', `Shutdown', `Sleep', `Statistics', `Table
Dump', `Time'
* `Time'
The time in seconds between the start of the statement or command
and now.
* `State'
An action, event, or state, which can be one of the following:
`After create', `Analyzing', `Changing master', `Checking master
version', `Checking table', `Connecting to master', `Copying to
group table', `Copying to tmp table', `Creating delayed handler',
`Creating index', `Creating sort index', `Creating table from
master dump', `Creating tmp table', `Execution of init_command',
`FULLTEXT initialization', `Finished reading one binlog; switching
to next binlog', `Flushing tables', `Killed', `Killing slave',
`Locked', `Making temp file ', `Opening master dump table',
`Opening table', `Opening tables', `Processing request', `Purging
old relay logs', `Queueing master event to the relay log',
`Reading event from the relay log', `Reading from net', `Reading
master dump table data', `Rebuilding the index on master dump
table', `Reconnecting after a failed binlog dump request',
`Reconnecting after a failed master event read', `Registering
slave on master', `Removing duplicates', `Reopen tables', `Repair
by sorting', `Repair done', `Repair with keycache', `Requesting
binlog dump', `Rolling back', `Saving state', `Searching rows for
update', `Sending binlog event to slave', `Sending data', `Sorting
for group', `Sorting for order', `Sorting index', `Sorting
result', `System lock', `Table lock', `Thread initialized',
`Updating', `User lock', `Waiting for INSERT', `Waiting for master
to send event', `Waiting for master update', `Waiting for slave
mutex on exit', `Waiting for table', `Waiting for tables', `Waiting
for the next event in relay log', `Waiting on cond', `Waiting to
finalize termination', `Waiting to reconnect after a failed binlog
dump request', `Waiting to reconnect after a failed master event
read', `Writing to net', `allocating local table', `cleaning up',
`closing tables', `converting HEAP to MyISAM', `copy to tmp table',
`creating table', `deleting from main table', `deleting from
reference tables', `discard_or_import_tablespace', `end', `freeing
items', `got handler lock', `got old table', `info', `init',
`insert', `logging slow query', `login', `preparing', `purging old
relay logs', `query end', `removing tmp table', `rename', `rename
result table', `reschedule', `setup', `starting slave',
`statistics', `storing row into queue', `update', `updating',
`updating main table', `updating reference tables', `upgrading
lock', `waiting for delay_list', `waiting for handler insert',
`waiting for handler lock', `waiting for handler open'
The most common `State' values are described in the rest of this
section. Most of the other `State' values are useful only for
finding bugs in the server. See also
replication-implementation-details, for additional information
about process states for replication servers.
For the `SHOW PROCESSLIST' statement, the value of `State' is
`NULL'.
* `Info'
The statement that the thread is executing, or `NULL' if it is not
executing any statement.
Some `State' values commonly seen in the output from `SHOW PROCESSLIST':
* `Checking table'
The thread is performing a table check operation.
* `Closing tables'
Means that the thread is flushing the changed table data to disk
and closing the used tables. This should be a fast operation. If
not, you should verify that you do not have a full disk and that
the disk is not in very heavy use.
* `Connect Out'
A replication slave is connecting to its master.
* `Copying to tmp table'
The server is copying to a temporary table in memory.
* `Copying to tmp table on disk'
The server is copying to a temporary table on disk. The temporary
result set was larger than `tmp_table_size' and the thread is
changing the temporary table from in-memory to disk-based format
to save memory.
* `Creating tmp table'
The thread is creating a temporary table to hold a part of the
result for the query.
* `deleting from main table'
The server is executing the first part of a multiple-table delete.
It is deleting only from the first table, and saving fields and
offsets to be used for deleting from the other (reference) tables.
* `deleting from reference tables'
The server is executing the second part of a multiple-table delete
and deleting the matched rows from the other tables.
* `Flushing tables'
The thread is executing `FLUSH TABLES' and is waiting for all
threads to close their tables.
* `FULLTEXT initialization'
The server is preparing to perform a natural-language full-text
search.
* `Killed'
Someone has sent a `KILL' statement to the thread and it should
abort next time it checks the kill flag. The flag is checked in
each major loop in MySQL, but in some cases it might still take a
short time for the thread to die. If the thread is locked by some
other thread, the kill takes effect as soon as the other thread
releases its lock.
* `Locked'
The query is locked by another query.
* `Sending data'
The thread is processing rows for a `SELECT' statement and also is
sending data to the client.
* `Sorting for group'
The thread is doing a sort to satisfy a `GROUP BY'.
* `Sorting for order'
The thread is doing a sort to satisfy a `ORDER BY'.
* `Opening tables'
The thread is trying to open a table. This is should be very fast
procedure, unless something prevents opening. For example, an
`ALTER TABLE' or a `LOCK TABLE' statement can prevent opening a
table until the statement is finished.
* `Reading from net'
The server is reading a packet from the network.
* `Removing duplicates'
The query was using `SELECT DISTINCT' in such a way that MySQL
could not optimize away the distinct operation at an early stage.
Because of this, MySQL requires an extra stage to remove all
duplicated rows before sending the result to the client.
* `Reopen table'
The thread got a lock for the table, but noticed after getting the
lock that the underlying table structure changed. It has freed the
lock, closed the table, and is trying to reopen it.
* `Repair by sorting'
The repair code is using a sort to create indexes.
* `Repair with keycache'
The repair code is using creating keys one by one through the key
cache. This is much slower than `Repair by sorting'.
* `Searching rows for update'
The thread is doing a first phase to find all matching rows before
updating them. This has to be done if the `UPDATE' is changing the
index that is used to find the involved rows.
* `Sleeping'
The thread is waiting for the client to send a new statement to it.
* `statistics'
The server is calculating statistics to develop a query execution
plan.
* `System lock'
The thread is waiting to get an external system lock for the
table. If you are not using multiple `mysqld' servers that are
accessing the same tables, you can disable system locks with the
-skip-external-locking option.
* `Upgrading lock'
The `INSERT DELAYED' handler is trying to get a lock for the table
to insert rows.
* `Updating'
The thread is searching for rows to update and is updating them.
* `updating main table'
The server is executing the first part of a multiple-table update.
It is updating only the first table, and saving fields and offsets
to be used for updating the other (reference) tables.
* `updating reference tables'
The server is executing the second part of a multiple-table update
and updating the matched rows from the other tables.
* `User Lock'
The thread is waiting on a `GET_LOCK()'.
* `Waiting for tables'
The thread got a notification that the underlying structure for a
table has changed and it needs to reopen the table to get the new
structure. However, to reopen the table, it must wait until all
other threads have closed the table in question.
This notification takes place if another thread has used `FLUSH
TABLES' or one of the following statements on the table in
question: `FLUSH TABLES TBL_NAME', `ALTER TABLE', `RENAME TABLE',
`REPAIR TABLE', `ANALYZE TABLE', or `OPTIMIZE TABLE'.
* `waiting for handler insert'
The `INSERT DELAYED' handler has processed all pending inserts and
is waiting for new ones.
* `Writing to net'
The server is writing a packet to the network.
Most states correspond to very quick operations. If a thread stays in
any of these states for many seconds, there might be a problem that
needs to be investigated.
Info Catalog
(mysql.info) show-procedure-status
(mysql.info) show
(mysql.info) show-status
automatically generated byinfo2html