processlist_per_userhost

NAME

processlist_per_userhost: State of processes per user/host: connected, executing, average execution time

TYPE

View

DESCRIPTION

processlist_per_userhost lists connected processes grouped by user & host combination. It provides with aggregated data per such entry.

This view makes it easier to detect particular users who are causing for longer running queries, or particular hosts from where heavy weight queries are executed.

STRUCTURE

mysql> DESC common_schema.processlist_per_userhost;
+--------------------------+---------------+------+-----+---------+-------+
| Field                    | Type          | Null | Key | Default | Extra |
+--------------------------+---------------+------+-----+---------+-------+
| user                     | varchar(16)   | NO   |     |         |       |
| host                     | varchar(64)   | YES  |     | NULL    |       |
| count_processes          | bigint(21)    | NO   |     | 0       |       |
| active_processes         | decimal(23,0) | YES  |     | NULL    |       |
| median_active_time       | decimal(10,2) | YES  |     | NULL    |       |
| median_95pct_active_time | decimal(10,2) | YES  |     | NULL    |       |
| max_active_time          | bigint(20)    | YES  |     | NULL    |       |
| average_active_time      | decimal(14,4) | YES  |     | NULL    |       |
+--------------------------+---------------+------+-----+---------+-------+

SYNOPSIS

Columns of this view:

  • user: login name of running process
  • host: connection host of origin. This excludes the connection port
  • count_processes: number of connections for current user/host
  • active_processes: number of connections executing queries for current user/host
  • median_active_time: longest run time for an active process of this user/host
  • median_95pct_active_time: run time at 95% point (95% processes run at this time or less) for active processes of this user/host
  • max_active_time: longest run time for an active process of this user/host
  • average_active_time: average time of currently executing queries for current user/host (excludes sleeping processes)

Processes, threads & connections have mixed terminology, but usually mean the same thing in the MySQL world. Read MySQL terminology: processes, threads & connections for more on this.

EXAMPLES

mysql> SELECT * FROM common_schema.processlist_per_userhost;
+------------+-----------+-----------------+------------------+--------------------+--------------------------+-----------------+---------------------+
| user       | host      | count_processes | active_processes | median_active_time | median_95pct_active_time | max_active_time | average_active_time |
+------------+-----------+-----------------+------------------+--------------------+--------------------------+-----------------+---------------------+
| web_user   | apps01    |               9 |                4 |               0.00 |                     2.00 |               2 |              0.5000 |
| web_user   | apps05    |               5 |                0 |               NULL |                     NULL |            NULL |                NULL |
| web_user   | apps04    |              11 |                4 |               0.00 |                     0.00 |               0 |              0.0000 |
| web_user   | sql00     |               1 |                0 |               NULL |                     NULL |            NULL |                NULL |
| web_user   | sql01     |               1 |                0 |               NULL |                     NULL |            NULL |                NULL |
| web_user   | sql02     |               3 |                1 |               0.00 |                     0.00 |               0 |              0.0000 |
| web_user   | apps08    |              17 |               15 |               0.00 |                     1.00 |               2 |              0.4667 |
| web_user   | apps03    |               2 |                0 |               NULL |                     NULL |            NULL |                NULL |
| web_user   | apps06    |              12 |                5 |               2.00 |                     2.00 |               2 |              1.4000 |
| web_user   | apps07    |               9 |                3 |               0.00 |                     2.00 |               2 |              0.6667 |
| monitor    | localhost |               1 |                0 |               NULL |                     NULL |            NULL |                NULL |
| monitor    | sql00     |               1 |                1 |               0.00 |                     0.00 |               0 |              0.0000 |
| monitor    | sql01     |               1 |                1 |               0.00 |                     0.00 |               0 |              0.0000 |
| monitor    | sql02     |               1 |                1 |               0.00 |                     0.00 |               0 |              0.0000 |
| openarkkit | sql02     |               8 |                8 |               0.00 |                     3.00 |               3 |              0.8750 |
| replicator | sql00     |               1 |                1 |           41571.00 |                 41571.00 |           41571 |          41571.0000 |
| replicator | sql02     |               1 |                1 |           41571.00 |                 41571.00 |           41571 |          41571.0000 |
+------------+-----------+-----------------+------------------+--------------------+--------------------------+-----------------+---------------------+

ENVIRONMENT

MySQL 5.1 or newer.

SEE ALSO

processlist_repl, processlist_states, processlist_summary, processlist_top

AUTHOR

Shlomi Noach
 
common_schema documentation