/
processlist_top.html
97 lines (84 loc) · 7.22 KB
/
processlist_top.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
<h3>NAME</h3>
processlist_top: Listing of active processes sorted by current query runtime, desc (longest first)
<h3>TYPE</h3>
View
<h3>DESCRIPTION</h3>
<p></p>
<p><i>processlist_top</i> displays only active processes (those not in Sleep mode, hence actually performing some query); it lists longest running queries first.
</p>
<p>
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.
</p>
<p>
However, PROCESSLIST also lists down many other connections, including those sleeping for long time.
<i>processlist_top</i> provides with the short story: only active, and longest first.
This is similar to query listing as implemented in <a href="http://jeremy.zawodny.com/mysql/mytop/">mytop</a>
or <a href="http://code.google.com/p/innotop/">innotop</a>.
</p>
<p><i>processlist_top</i> does not list its own process (the process invoking the SELECT on processlist_top)
</p>
<h3>STRUCTURE</h3>
<blockquote><pre>
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 | | | |
+---------------------+---------------+------+-----+---------+-------+
</pre></blockquote>
<h3>SYNOPSIS</h3>
<p>Structure of this view is based to that of <a href="http://dev.mysql.com/doc/refman/5.1/en/processlist-table.html">INFORMATION_SCHEMA.PROCESSLIST</a> table</p>
<p>Additional columns are:
<ul>
<li>
<strong>sql_kill_query</strong>: a <strong>KILL QUERY</strong> statement for current thread.
<br/>Use with <a href="eval.html">eval()</a> to apply statement.
</li>
<li>
<strong>sql_kill_connection</strong>: a <strong>KILL</strong> statement for current thread.
<br/>Use with <a href="eval.html">eval()</a> to apply statement.
</li>
</ul>
</p>
<p>On Percona Server, this additional info is included:
<ul>
<li><strong>TIME_MS</strong>: execution time in milliseconds</li>
</ul>
</p>
<h3>EXAMPLES</h3>
<p>Show all active processes:</p>
<blockquote><pre>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 |
+----------+-------------+--------------+-----------+-------------+---------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+------------+---------------------+---------------------+
</pre></blockquote>
<p>In the above example the last three processes seem to be running for 0 seconds. However, with Percona Server's <strong>TIME_MS</strong> 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 <strong>TIME</strong>, which has a 1 second resolution.</p>
<h3>ENVIRONMENT</h3>
MySQL 5.1 or newer. Percona Server yields a different schema.
<h3>SEE ALSO</h3>
<a href="processlist_per_userhost.html">processlist_per_userhost</a>,
<a href="processlist_repl.html">processlist_repl</a>,
<a href="processlist_summary.html">processlist_summary</a>
<h3>AUTHOR</h3>
Shlomi Noach