MySQL Debug

mysql-debug.sql

show full processlist;

SHOW ENGINE InnoDB STATUS;

SHOW OPEN TABLES WHERE In_use > 0;

SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; 

SELECT * FROM `information_schema`.`innodb_locks`;

SELECT * FROM `information_schema`.`processlist` where INFO is not null;

SELECT * FROM `information_schema`.`processlist` where INFO is not null and USER = "";

SELECT * FROM `information_schema`.`processlist` where USER = "" and HOST LIKE "";

SELECT * FROM `information_schema`.`processlist` where state LIKE "Waiting for table metadata%" and INFO not LIKE "ALTER%";

SELECT HOST FROM `information_schema`.`processlist` where state LIKE "Waiting for table metadata%" and INFO not LIKE "ALTER%";

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';


SELECT g.object_schema AS object_schema,
       g.object_name AS object_name,
       pt.thread_id AS waiting_thread_id,
       pt.processlist_id AS waiting_pid,
       sys.ps_thread_account(p.owner_thread_id) AS waiting_account,
       p.lock_type AS waiting_lock_type,
       p.lock_duration AS waiting_lock_duration,
       sys.format_statement(pt.processlist_info) AS waiting_query,
       pt.processlist_time AS waiting_query_secs,
       ps.rows_affected AS waiting_query_rows_affected,
       ps.rows_examined AS waiting_query_rows_examined,
       gt.thread_id AS blocking_thread_id,
       gt.processlist_id AS blocking_pid,
       sys.ps_thread_account(g.owner_thread_id) AS blocking_account,
       g.lock_type AS blocking_lock_type,
       g.lock_duration AS blocking_lock_duration,
       CONCAT('KILL QUERY ', gt.processlist_id) AS sql_kill_blocking_query,
       CONCAT('KILL ', gt.processlist_id) AS sql_kill_blocking_connection
  FROM performance_schema.metadata_locks g
 INNER JOIN performance_schema.metadata_locks p 
    ON g.object_type = p.object_type
   AND g.object_schema = p.object_schema
   AND g.object_name = p.object_name
   AND g.lock_status = 'GRANTED'
   AND p.lock_status = 'PENDING'
 INNER JOIN performance_schema.threads gt ON g.owner_thread_id = gt.thread_id
 INNER JOIN performance_schema.threads pt ON p.owner_thread_id = pt.thread_id
  LEFT JOIN performance_schema.events_statements_current gs ON g.owner_thread_id = gs.thread_id
  LEFT JOIN performance_schema.events_statements_current ps ON p.owner_thread_id = ps.thread_id
 WHERE g.object_type = 'TABLE';