processlist_top

NAME

processlist_top: Listing of active processes sorted by current query runtime, desc (longest first)

TYPE

View

DESCRIPTION

processlist_top displays only active processes (those not in Sleep mode, hence actually performing some query); it lists longest running queries first.

When looking at running processes, we are many times interested in those queries making trouble. We may look at PROCESSLIST when server seems to react slowly. We may be looking for queries acquiring locks, blocking other queries, or for extremely long queries which are wasting system resources. A good heuristic would be to look for queries running the longest.

However, PROCESSLIST also lists down many other connections, including those sleeping for long time. processlist_top provides with the short story: only active, and longest first. This is similar to query listing as implemented in mytop or innotop.

processlist_top does not list its own process (the process invoking the SELECT on processlist_top)

STRUCTURE

mysql> DESC common_schema.processlist_top;
+---------------------+---------------+------+-----+---------+-------+
| Field               | Type          | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+-------+
| ID                  | bigint(4)     | NO   |     | 0       |       |
| USER                | varchar(16)   | NO   |     |         |       |
| HOST                | varchar(64)   | NO   |     |         |       |
| DB                  | varchar(64)   | YES  |     | NULL    |       |
| COMMAND             | varchar(16)   | NO   |     |         |       |
| TIME                | int(7)        | NO   |     | 0       |       |
| STATE               | varchar(64)   | YES  |     | NULL    |       |
| INFO                | longtext      | YES  |     | NULL    |       |
| sql_kill_query      | varbinary(31) | NO   |     |         |       |
| sql_kill_connection | varbinary(25) | NO   |     |         |       |
+---------------------+---------------+------+-----+---------+-------+

SYNOPSIS

Structure of this view is based to that of INFORMATION_SCHEMA.PROCESSLIST table

Additional columns are:

  • sql_kill_query: a KILL QUERY statement for current thread.
    Use with eval() to apply statement.
  • sql_kill_connection: a KILL statement for current thread.
    Use with eval() to apply statement.

On Percona Server, this additional info is included:

  • TIME_MS: execution time in milliseconds

EXAMPLES

Show all active processes:

mysql> SELECT * FROM common_schema.processlist_top;
+----------+-------------+--------------+-----------+-------------+---------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+------------+---------------------+---------------------+
| ID       | USER        | HOST         | DB        | COMMAND     | TIME    | STATE                                                            | INFO                                                                                                                  | TIME_MS    | sql_kill_query      | sql_kill_connection |
+----------+-------------+--------------+-----------+-------------+---------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+------------+---------------------+---------------------+
|  3598334 | system user |              | NULL      | Connect     | 4281883 | Waiting for master to send event                                 | NULL                                                                                                                  | 4281883102 | KILL QUERY 3598334  | KILL 3598334        |
|  3598469 | replica     | sql01:51157  | NULL      | Binlog Dump | 4281878 | Has sent all binlog to slave; waiting for binlog to be updated   | NULL                                                                                                                  | 4281877707 | KILL QUERY 3598469  | KILL 3598469        |
| 31066726 | replica     | sql02:48924  | NULL      | Binlog Dump | 1041758 | Has sent all binlog to slave; waiting for binlog to be updated   | NULL                                                                                                                  | 1041758134 | KILL QUERY 31066726 | KILL 31066726       |
|  3598335 | system user |              | NULL      | Connect     |  195747 | Has read all relay log; waiting for the slave I/O thread to upda | NULL                                                                                                                  |          0 | KILL QUERY 3598335  | KILL 3598335        |
| 39946702 | store       | app03:46795  | datastore | Query       |       0 | Writing to net                                                   | SELECT * FROM store_location                                                                                          |         27 | KILL QUERY 39946702 | KILL 39946702       |
| 39946693 | store       | app05:51090  | datastore | Query       |       0 | Writing to net                                                   | SELECT store.store_id, store_location.zip_code FROM store JOIN store_location USING (store_id) WHERE store_class = 5  |         54 | KILL QUERY 39946693 | KILL 39946693       |
| 39946692 | store       | sql01:47849  | datastore | Query       |       0 | Writing to net                                                   | SELECT store.store_id, store_location.zip_code FROM store JOIN store_location USING (store_id) WHERE store_class = 34 |        350 | KILL QUERY 39946692 | KILL 39946692       |
+----------+-------------+--------------+-----------+-------------+---------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+------------+---------------------+---------------------+

In the above example the last three processes seem to be running for 0 seconds. However, with Percona Server's TIME_MS we see the sub-second runtime for each process. As it turns out, these three processes are not strictly order from oldest to newest. This is because we order them based on TIME, which has a 1 second resolution.

ENVIRONMENT

MySQL 5.1 or newer. Percona Server yields a different schema.

SEE ALSO

processlist_per_userhost, processlist_repl, processlist_summary

AUTHOR

Shlomi Noach
 
common_schema documentation