MySQL 8.0：如何找到长事务（译文）

姚远OracleACE

发布于 2023-04-06 18:37:27

1.4K00

代码可运行

文章被收录于专栏：oracleaceoracleace

运行总次数：0

代码可运行

内容摘要：让我们看看如何在MySQL 8.0中轻松找到那些可能成为DBA噩梦的正在运行的长事务。

关于译者，姚远：

Oracle ACE（Oracle和MySQL数据库方向）
华为云MVP
《MySQL 8.0运维与优化》的作者
中国唯一一个Oracle高可用大师
拥有包括 Oracle 10g和12c OCM在内的20+数据库相关认证
曾任IBM公司数据库部门经理
现在一家第三方公司任首席数据库专家，服务2万+客户。

原文网址：https://lefred.be/content/diskspace-problem-when-modifying-a-large-innodb-table-in-mysql-and-how-to-solve-it/

作者：Frédéric Descamps（https://lefred.be/）

最近，有人问我如何在MySQL中找到长时间运行的事务。我已经开发了一个MySQL Shell插件，可以让你找到按时间排序的当前事务，这个插件还可以让您获取所需事务的详细信息。请参见check.getRunningStatements()(https://github.com/lefred/mysqlshell-plugins/wiki/check#getrunningstatements)。让我们看看如何轻松找到那些可能成为DBA噩梦的长事务(参见MySQL历史列表长度文章https://lefred.be/content/a-graph-a-day-keeps-the-doctor-away-mysql-history-list-length/)。

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;

我们可以看到，目前我们有一个运行时间超过43分钟的活动事务，但似乎什么也没做。休眠会话实际上是导致最多问题的会话，因为它们可能是已经被遗忘的交互式会话，并且默认情况下将保持很长很长时间(8小时，interactive_timeout)。

如果使用下面的命令激活了计量，还可以查询出该事务中执行的语句(默认限制为10条，performance_schema_events_statements_history_size ):

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

现在激活了计量，我们可以使用以下语句查看所有新事务的历史记录:

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;

让我们试一下：