MYSQL长事务排查

查看造成等待的事务执行SQL

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

SELECT   tmp.waiting_thread '等待线程 ID',   tmp.waiting_trx_id '等待事务 ID',   tmp.wating_trx_state 等待事务状态,   tmp.waiting_query 等待语句,   case     when (tmp.waiting_time - 28800) > 0 then (tmp.waiting_time - 28800)     else tmp.waiting_time   end as 等待时间,   p.HOST 阻塞连接,   tmp.blocking_thread '阻塞线程 ID',   tmp.blocking_trx_id '阻塞事务 ID',   tmp.blocking_trx_state 阻塞事务状态,   case     when (tmp.blocking_exe_time - 28800) > 0 then (tmp.blocking_exe_time - 28800)     else tmp.blocking_exe_time   end as 阻塞事务运行时间,   IFNULL( blocking_query, c.SQL_Text ) 阻塞语句,   c.current_schema 阻塞表 FROM   (   SELECT     r.trx_state wating_trx_state,     r.trx_id waiting_trx_id,     r.trx_mysql_thread_Id waiting_thread,     r.trx_query waiting_query,     b.trx_state blocking_trx_state,     b.trx_id blocking_trx_id,     b.trx_mysql_thread_id blocking_thread,     b.trx_query blocking_query,     timestampdiff( SECOND, r.trx_wait_started, CURRENT_TIMESTAMP ()) as waiting_time,     timestampdiff( SECOND, r.TRX_STARTED, CURRENT_TIMESTAMP ()) as blocking_exe_time   FROM     information_schema.innodb_lock_waits w     INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id     INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id   ) tmp   LEFT JOIN information_schema.PROCESSLIST p ON tmp.blocking_thread = p.id   LEFT JOIN PERFORMANCE_SCHEMA.threads t ON t.PROCESSLIST_ID = p.id   LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current c ON t.thread_id = c.THREAD_ID

　　

　　

查看长事务的连接信息

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SELECT   t.thread_id 线程ID,   t.processlist_id 会话ID,   t.processlist_user 用户,   t.processlist_host 用户地址,   t.processlist_db 数据库,   p.command 会话状态,   e.STATE 事务状态,   e.timer_wait 事务持续时长,   p.current_statement 执行语句 FROM   PERFORMANCE_SCHEMA.events_transactions_current  e   left JOIN PERFORMANCE_SCHEMA.threads t on t.thread_id = e.thread_id   LEFT JOIN sys.PROCESSLIST p ON p.thd_id = t.thread_id WHERE   t.type = "FOREGROUND"   and e.STATE = "ACTIVE" ORDER BY   e.timer_wait DESC;

　　

　查看事务相关信息

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SELECT   b.id 会话 ID,   b.USER 用户名,   b.HOST 连接地址,   b.db 数据库,   a.TRX_STATE 事务状态,   a.trx_tables_locked 表锁数量,   a.trx_rows_locked 行锁数量,   d.SQL_TEXT 已执行语句,   a.TRX_QUERY 正在执行语句, CASE           WHEN (( UNIX_TIMESTAMP( now()) - UNIX_TIMESTAMP( a.trx_started )) - 28800 ) > 0 THEN     (( UNIX_TIMESTAMP( now()) - UNIX_TIMESTAMP( a.trx_started )) - 28800 ) ELSE ((         UNIX_TIMESTAMP(         now()) - UNIX_TIMESTAMP( a.trx_started )))   END AS 事务持续时间 FROM   information_schema.innodb_trx a   INNER JOIN information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id #AND b.command = 'Sleep'   INNER JOIN PERFORMANCE_SCHEMA.threads t ON b.id = t.PROCESSLIST_ID   INNER JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = t.THREAD_ID

　　　　

　

查看历史事务语句

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SELECT   ps.id 'PROCESS ID',   ps.USER,   ps.HOST,   esh.EVENT_ID,   trx.trx_started,   esh.event_name 'EVENT NAME',   esh.sql_text 'SQL',   ps.TIME FROM   PERFORMANCE_SCHEMA.events_statements_history esh   JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id   JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id   LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id WHERE   trx.trx_id IS NOT NULL   AND ps.USER != 'SYSTEM_USER' ORDER BY   esh.EVENT_ID