1-- Copyright (c) 2014, 2021, Oracle and/or its affiliates.
2--
3-- This program is free software; you can redistribute it and/or modify
4-- it under the terms of the GNU General Public License, version 2.0,
5-- as published by the Free Software Foundation.
6--
7-- This program is also distributed with certain software (including
8-- but not limited to OpenSSL) that is licensed under separate terms,
9-- as designated in a particular file or component or in included license
10-- documentation.  The authors of MySQL hereby grant you an additional
11-- permission to link the program and your derivative works with the
12-- separately licensed software that they have included with MySQL.
13--
14-- This program is distributed in the hope that it will be useful,
15-- but WITHOUT ANY WARRANTY; without even the implied warranty of
16-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
17-- GNU General Public License, version 2.0, for more details.
18--
19-- You should have received a copy of the GNU General Public License
20-- along with this program; if not, write to the Free Software
21-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
22
23--
24-- View: x$processlist
25--
26-- A detailed non-blocking processlist view to replace
27-- [INFORMATION_SCHEMA. | SHOW FULL] PROCESSLIST
28--
29-- Performs less locking than the legacy sources, whilst giving extra information.
30--
31-- mysql> select * from sys.x$processlist where conn_id is not null and command != 'daemon' and conn_id != connection_id()\G
32-- ...
33-- *************************** 2. row ***************************
34--                 thd_id: 720
35--                conn_id: 698
36--                   user: msandbox@localhost
37--                     db: test
38--                command: Query
39--                  state: alter table (read PK and internal sort)
40--                   time: 2
41--      current_statement: alter table t1 add column l int
42--      statement_latency: 2349834276374
43--               progress: 60.00
44--           lock_latency: 339707000000
45--          rows_examined: 0
46--              rows_sent: 0
47--          rows_affected: 0
48--             tmp_tables: 0
49--        tmp_disk_tables: 0
50--              full_scan: NO
51--         last_statement: NULL
52-- last_statement_latency: NULL
53--         current_memory: 10186821
54--              last_wait: wait/io/file/innodb/innodb_data_file
55--      last_wait_latency: Still Waiting
56--                 source: fil0fil.cc:5351
57--            trx_latency: NULL
58--              trx_state: NULL
59--         trx_autocommit: NULL
60--                    pid: 5559
61--           program_name: mysql
62--
63
64CREATE OR REPLACE
65  ALGORITHM = TEMPTABLE
66  DEFINER = 'mysql.sys'@'localhost'
67  SQL SECURITY INVOKER
68VIEW x$processlist (
69  thd_id,
70  conn_id,
71  user,
72  db,
73  command,
74  state,
75  time,
76  current_statement,
77  statement_latency,
78  progress,
79  lock_latency,
80  rows_examined,
81  rows_sent,
82  rows_affected,
83  tmp_tables,
84  tmp_disk_tables,
85  full_scan,
86  last_statement,
87  last_statement_latency,
88  current_memory,
89  last_wait,
90  last_wait_latency,
91  source,
92  trx_latency,
93  trx_state,
94  trx_autocommit,
95  pid,
96  program_name
97) AS
98SELECT pps.thread_id AS thd_id,
99       pps.processlist_id AS conn_id,
100       IF(pps.name IN ('thread/sql/one_connection', 'thread/thread_pool/tp_one_connection'),
101          CONCAT(pps.processlist_user, '@', pps.processlist_host),
102          REPLACE(pps.name, 'thread/', '')) user,
103       pps.processlist_db AS db,
104       pps.processlist_command AS command,
105       pps.processlist_state AS state,
106       pps.processlist_time AS time,
107       pps.processlist_info AS current_statement,
108       IF(esc.end_event_id IS NULL,
109          esc.timer_wait,
110          NULL) AS statement_latency,
111       IF(esc.end_event_id IS NULL,
112          ROUND(100 * (estc.work_completed / estc.work_estimated), 2),
113          NULL) AS progress,
114       esc.lock_time AS lock_latency,
115       esc.rows_examined AS rows_examined,
116       esc.rows_sent AS rows_sent,
117       esc.rows_affected AS rows_affected,
118       esc.created_tmp_tables AS tmp_tables,
119       esc.created_tmp_disk_tables AS tmp_disk_tables,
120       IF(esc.no_good_index_used > 0 OR esc.no_index_used > 0, 'YES', 'NO') AS full_scan,
121       IF(esc.end_event_id IS NOT NULL,
122          esc.sql_text,
123          NULL) AS last_statement,
124       IF(esc.end_event_id IS NOT NULL,
125          esc.timer_wait,
126          NULL) AS last_statement_latency,
127       mem.current_allocated AS current_memory,
128       ewc.event_name AS last_wait,
129       IF(ewc.end_event_id IS NULL AND ewc.event_name IS NOT NULL,
130          'Still Waiting',
131          ewc.timer_wait) last_wait_latency,
132       ewc.source,
133       etc.timer_wait AS trx_latency,
134       etc.state AS trx_state,
135       etc.autocommit AS trx_autocommit,
136       conattr_pid.attr_value as pid,
137       conattr_progname.attr_value as program_name
138  FROM performance_schema.threads AS pps
139  LEFT JOIN performance_schema.events_waits_current AS ewc USING (thread_id)
140  LEFT JOIN performance_schema.events_stages_current AS estc USING (thread_id)
141  LEFT JOIN performance_schema.events_statements_current AS esc USING (thread_id)
142  LEFT JOIN performance_schema.events_transactions_current AS etc USING (thread_id)
143  LEFT JOIN sys.x$memory_by_thread_by_current_bytes AS mem USING (thread_id)
144  LEFT JOIN performance_schema.session_connect_attrs AS conattr_pid
145    ON conattr_pid.processlist_id=pps.processlist_id and conattr_pid.attr_name='_pid'
146  LEFT JOIN performance_schema.session_connect_attrs AS conattr_progname
147    ON conattr_progname.processlist_id=pps.processlist_id and conattr_progname.attr_name='program_name'
148 ORDER BY pps.processlist_time DESC, last_wait_latency DESC;
149