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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
-- Copyright (c) 2014, 2015, Oracle and/or its affiliates. All rights reserved.
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; version 2 of the License.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
--
-- View: processlist
--
-- A detailed non-blocking processlist view to replace
-- [INFORMATION_SCHEMA. | SHOW FULL] PROCESSLIST
--
-- Performs less locking than the legacy sources, whilst giving extra information.
--
-- mysql> select * from processlist where conn_id is not null\G
-- ...
-- *************************** 8. row ***************************
-- thd_id: 31
-- conn_id: 12
-- user: root@localhost
-- db: information_schema
-- command: Query
-- state: Sending data
-- time: 0
-- current_statement: select * from processlist limit 5
-- lock_latency: 684.00 us
-- rows_examined: 0
-- rows_sent: 0
-- rows_affected: 0
-- tmp_tables: 2
-- tmp_disk_tables: 0
-- full_scan: YES
-- current_memory: 1.29 MiB
-- last_statement: NULL
-- last_statement_latency: NULL
-- last_wait: wait/synch/mutex/sql/THD::LOCK_query_plan
-- last_wait_latency: 260.13 ns
-- source: sql_optimizer.cc:1075
--
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW processlist (
thd_id,
conn_id,
user,
db,
command,
state,
time,
current_statement,
lock_latency,
rows_examined,
rows_sent,
rows_affected,
tmp_tables,
tmp_disk_tables,
full_scan,
current_memory,
last_statement,
last_statement_latency,
last_wait,
last_wait_latency,
source
) AS
SELECT pps.thread_id AS thd_id,
pps.processlist_id AS conn_id,
IF(pps.name = 'thread/sql/one_connection',
CONCAT(pps.processlist_user, '@', pps.processlist_host),
REPLACE(pps.name, 'thread/', '')) user,
pps.processlist_db AS db,
pps.processlist_command AS command,
pps.processlist_state AS state,
pps.processlist_time AS time,
sys.format_statement(pps.processlist_info) AS current_statement,
sys.format_time(esc.lock_time) AS lock_latency,
esc.rows_examined,
esc.rows_sent,
esc.rows_affected,
esc.created_tmp_tables AS tmp_tables,
esc.created_tmp_disk_tables AS tmp_disk_tables,
IF(esc.no_good_index_used > 0 OR esc.no_index_used > 0,
'YES', 'NO') AS full_scan,
sys.format_bytes(mem.current_allocated) AS current_memory,
IF(esc.timer_wait IS NOT NULL,
sys.format_statement(esc.sql_text),
NULL) AS last_statement,
IF(esc.timer_wait IS NOT NULL,
sys.format_time(esc.timer_wait),
NULL) AS last_statement_latency,
ewc.event_name AS last_wait,
IF(ewc.timer_wait IS NULL AND ewc.event_name IS NOT NULL,
'Still Waiting',
sys.format_time(ewc.timer_wait)) last_wait_latency,
ewc.source
FROM performance_schema.threads AS pps
LEFT JOIN performance_schema.events_waits_current AS ewc USING (thread_id)
LEFT JOIN performance_schema.events_statements_current AS esc USING (thread_id)
LEFT JOIN sys.x$memory_by_thread_by_current_bytes AS mem USING (thread_id)
ORDER BY pps.processlist_time DESC, last_wait_latency DESC;
|
