SQLize Online / PHPize Online  /  SQLtest Online

DROP VIEW IF EXISTS rep_processlist ; CREATE OR REPLACE VIEW rep_processlist AS WITH par AS (SELECT '\n;\n' br, 1 / 1024 / 1024 b_to_mb, 1000000000000 to_pico_sec) , proc AS (SELECT * FROM performance_schema.processlist) , th AS (SELECT * FROM performance_schema.threads) , uv AS (SELECT thread_id, variable_name var, CAST(variable_value AS CHAR) val FROM performance_schema.user_variables_by_thread) /*, sv AS ( SELECT variable_name var, variable_value val FROM performance_schema.session_variables sv )*/ SELECT /*+ SET_VAR(group_concat_max_len = 9999999)*/ p.time tq -- time query / time trx /*CONCAT_WS('/', p.time , NULLIF(TIMESTAMPDIFF(SECOND, trx.trx_started, CONVERT_TZ(NOW() , (SELECT val FROM sv WHERE var = 'time_zone') , (SELECT val FROM sv WHERE var = 'log_timestamps') )), 0))*/ , CONCAT(p.info, par.br) sql_text , CONCAT(CONCAT_WS(': ' , CONCAT('!!!!', NULLIF(tr.isolation_level, 'READ COMMITTED'), '!!!!') , CONCAT('!!!!', IF(p.command = 'Sleep' AND tr.state = 'ACTIVE', 'SLEEP, NOT COMMITED', NULL), '!!!!') , trx.trx_operation_state, trx.trx_state, p.command, p.state , (SELECT GROUP_CONCAT(ROUND(work_completed / work_estimated * 100, 1) , '% ', IF(work_completed > 0 AND work_completed != work_estimated , CONCAT('(+', sys.format_time((p.time / (work_completed / work_estimated) - p.time) * par.to_pico_sec), ') ') , CONCAT('(', work_estimated, ')') ) , '\n', EVENT_NAME SEPARATOR ', ' ) percent FROM performance_schema.events_stages_current t WHERE tr.nesting_event_id IN (t.nesting_event_id, t.event_id) )) , '(', p.user,')' , IFNULL(CONCAT(' / ', vars.app), '') ) state -- mem/locked , CONCAT_WS('/', ROUND(t.controlled_memory * par.b_to_mb, 1), NULLIF(ROUND(trx.trx_lock_memory_bytes * par.b_to_mb, 1), 0)) mem , t.thread_id th, p.id pid , l.b, l.w -- , SUM(l.b_cn) OVER () bl_cn , CONCAT_WS(',', trx.trx_tables_in_use, trx.trx_tables_locked, trx.trx_lock_structs, trx.trx_rows_locked) lo -- !! locks: table use, t. locked, object locked, rows , ml.ml , es.* , NULLIF(trx.trx_rows_modified, 0) rm -- row modified , vars.vars -- , CONCAT('KILL QUERY ', p.ID, par.br) kill_sql , CONCAT('CALL mysql.rds_kill_query(', p.ID, ') -- rds_kill', par.br) kill_sql , CONCAT('EXPLAIN FOR CONNECTION ', p.ID, par.br) expl_sql FROM par JOIN proc p JOIN th t ON t.processlist_id = p.id LEFT JOIN performance_schema.events_transactions_current tr ON tr.thread_id = t.thread_id LEFT JOIN LATERAL ( SELECT CONCAT('B:', NULLIF(COUNT(DISTINCT blocking), 0), ': ', GROUP_CONCAT(DISTINCT blocking)) b , CONCAT('W:', NULLIF(COUNT(DISTINCT waiting), 0), ':', GROUP_CONCAT(DISTINCT waiting)) w , COUNT(DISTINCT blocking) b_cn , COUNT(DISTINCT waiting) w_cn FROM ( SELECT IF(t.thread_id = l.blocking_thread_id, w.processlist_id, NULL) blocking , IF(t.thread_id = l.requesting_thread_id, b.processlist_id, NULL) waiting FROM performance_schema.data_lock_waits l LEFT JOIN th w ON l.requesting_thread_id = w.thread_id LEFT JOIN th b ON l.blocking_thread_id = b.thread_id WHERE t.thread_id IN (l.blocking_thread_id, l.requesting_thread_id) UNION ALL SELECT IF(p.id = l.blocking_pid, l.waiting_pid, NULL) blocking , IF(p.id = l.waiting_pid, l.blocking_pid, NULL) waiting FROM sys.schema_table_lock_waits l WHERE p.id IN (l.blocking_pid, l.waiting_pid) AND l.blocking_pid != l.waiting_pid ) l ) l ON TRUE LEFT JOIN LATERAL ( SELECT SUM(es.rows_affected) ra, SUM(es.rows_examined) re FROM performance_schema.events_statements_current es WHERE es.thread_id = t.thread_id ) es ON TRUE LEFT JOIN LATERAL ( SELECT CONCAT('SET ', GROUP_CONCAT(CASE WHEN v.var != 'APP.info' THEN CONCAT('@', v.var, '=\'', v.val, '\'') END ORDER BY CHARACTER_LENGTH(v.val)), par.br) vars , REPLACE(REPLACE(GROUP_CONCAT(CASE WHEN v.var = 'APP.info' THEN v.val END), 'App\\Http\\Controllers\\Api\\', ''), 'App\\Services\\', '') app FROM uv v WHERE t.thread_id = v.thread_id AND v.val IS NOT NULL ) vars ON TRUE LEFT JOIN LATERAL ( SELECT trx.* FROM information_schema.innodb_trx trx WHERE trx.trx_mysql_thread_id = p.id ) trx ON TRUE LEFT JOIN LATERAL ( WITH d AS ( SELECT ml.* , COUNT(*) OVER w cn , COUNT(*) OVER w2 cn_o FROM performance_schema.metadata_locks ml WHERE ml.owner_thread_id = t.thread_id WINDOW w AS (PARTITION BY ml.owner_thread_id) , w2 AS (PARTITION BY ml.owner_thread_id, ml.object_name) ) , d2 AS ( SELECT *, IF(DENSE_RANK() OVER w > 3, 'other', object_name) object_name_calc FROM d WINDOW w AS (ORDER BY cn_o DESC) ) , d3 AS ( SELECT *, COUNT(*) OVER w cn_o_2 FROM d2 d WINDOW w AS (PARTITION BY owner_thread_id, object_name) ) SELECT CONCAT_WS('|', cn, GROUP_CONCAT(DISTINCT cn_o_2, ':', object_name ORDER BY cn_o DESC SEPARATOR ',')) ml FROM d3 GROUP BY cn ) ml ON TRUE WHERE p.user != 'rdsrepladmin' -- $ END $ HAVING sql_text IS NOT NULL OR b OR w OR IFNULL(ml.ml, '') != '' ORDER BY p.state != 'Sleep', IF(b_cn > 10, -b_cn, 0), IF(b_cn > 10, -w_cn, 0), time DESC ; TABLE rep_processlist ;