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
|
-- 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
--
-- mysql> select * from processlist where conn_id is not null\G
-- *************************** 1. row ***************************
-- thd_id: 23
-- conn_id: 4
-- user: msandbox@localhost
-- db: test
-- command: Query
-- state: Sending data
-- time: 4
-- current_statement: select count(*) from t1
-- statement_latency: 4.56 s
-- lock_latency: 108.00 us
-- rows_examined: 0
-- rows_sent: 0
-- rows_affected: 0
-- tmp_tables: 0
-- tmp_disk_tables: 0
-- full_scan: YES
-- last_statement: NULL
-- last_statement_latency: NULL
-- last_wait: wait/io/table/sql/handler
-- last_wait_latency: Still Waiting
-- source: handler.cc:2688
--
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'mariadb.sys'@'localhost'
SQL SECURITY INVOKER
VIEW processlist (
thd_id,
conn_id,
user,
db,
command,
state,
time,
current_statement,
statement_latency,
lock_latency,
rows_examined,
rows_sent,
rows_affected,
tmp_tables,
tmp_disk_tables,
full_scan,
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,
IF(esc.end_event_id IS NULL,
format_pico_time(esc.timer_wait),
NULL) AS statement_latency,
format_pico_time(esc.lock_time) AS lock_latency,
esc.rows_examined AS rows_examined,
esc.rows_sent AS rows_sent,
esc.rows_affected AS 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,
IF(esc.end_event_id IS NOT NULL,
sys.format_statement(esc.sql_text),
NULL) AS last_statement,
IF(esc.end_event_id IS NOT NULL,
format_pico_time(esc.timer_wait),
NULL) AS last_statement_latency,
ewc.event_name AS last_wait,
IF(ewc.end_event_id IS NULL AND ewc.event_name IS NOT NULL,
'Still Waiting',
format_pico_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)
ORDER BY pps.processlist_time DESC, last_wait_latency DESC;
|
