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