MySQL 8.0长事务查看

2023-08-10 09:32 abce 阅读(64) 评论(0) 编辑收藏举报

查看长时间运行的事务：

SELECT thr.processlist_id AS mysql_thread_id,
       concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
       Command,
       FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
       current_statement as `latest_statement`
  FROM performance_schema.events_transactions_current trx
  INNER JOIN performance_schema.threads thr USING (thread_id)
  LEFT JOIN sys.processlist p ON p.thd_id=thread_id
 WHERE thr.processlist_id IS NOT NULL
   AND PROCESSLIST_USER IS NOT NULL
   AND trx.state = 'ACTIVE'
 GROUP BY thread_id, timer_wait 
 ORDER BY TIMER_WAIT DESC LIMIT 10;
  
+-----------------+--------------------+---------+--------------+-------------------------------------------------------------------+
| mysql_thread_id | User               | Command | trx_duration | latest_statement                                                  |
+-----------------+--------------------+---------+--------------+-------------------------------------------------------------------+
|         3062872 | root@192.168.11.23 | Sleep   | 5.13 s       | NULL                                                              |
|         3062771 | root@localhost     | Query   | 309.02 us    | SELECT thr.processlist_id AS m ... ER BY TIMER_WAIT DESC LIMIT 10 |
+-----------------+--------------------+---------+--------------+-------------------------------------------------------------------+
2 rows in set (0.05 sec)

从查看结果可以看到，有个活跃事务运行了5.13s，而且latest_statement的结果是NULL，目前什么也看不到。sleep状态的会话通常可能是引起问题的会话。他们可能是被遗忘的非活跃会话，持续存在很长时间。默认的超时设置是8小时(interactive_timeout参数设定)。

如果开启了相应的instrumentation，也可以通过performance_schema_events_statements_history_size来查看事务中执行过的语句：
开启以下两个instrumentation：

UPDATE performance_schema.setup_consumers 
       SET enabled = 'yes'
  WHERE name LIKE 'events_statements_history_long'
     OR name LIKE 'events_transactions_history_long';

根据上面查询结果中的mysql_thread_id的值进行查看：

SELECT DATE_SUB(now(), INTERVAL (
         SELECT variable_value 
           FROM performance_schema.global_status 
           WHERE variable_name='UPTIME')-TIMER_START*10e-13 second) start_time,
       SQL_TEXT 
  FROM performance_schema.events_statements_history  
 WHERE nesting_event_id=(
               SELECT EVENT_ID 
                 FROM performance_schema.events_transactions_current t   
                 LEFT JOIN sys.processlist p ON p.thd_id=t.thread_id  
                 WHERE conn_id=<VALUE OF mysql_thread_id COLUMN>) 
 ORDER BY event_id;