processlist_top
NAME
processlist_top: Listing of active processes sorted by current query runtime, desc (longest first)TYPE
ViewDESCRIPTION
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 | | +---------+-------------+------+-----+---------+-------+
SYNOPSIS
Structure of this view is identical to that of INFORMATION_SCHEMA.PROCESSLIST table
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 | +----------+-------------+--------------+-----------+-------------+---------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+------------+ | 3598334 | system user | | NULL | Connect | 4281883 | Waiting for master to send event | NULL | 4281883102 | | 3598469 | replica | sql01:51157 | NULL | Binlog Dump | 4281878 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | 4281877707 | | 31066726 | replica | sql02:48924 | NULL | Binlog Dump | 1041758 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | 1041758134 | | 3598335 | system user | | NULL | Connect | 195747 | Has read all relay log; waiting for the slave I/O thread to upda | NULL | 0 | | 39946702 | store | app03:46795 | datastore | Query | 0 | Writing to net | SELECT * FROM store_location | 27 | | 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 | | 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 | +----------+-------------+--------------+-----------+-------------+---------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+------------+
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.