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
;
Telegram: DB developers channel