MySQL 定位长事务(Identify Long Transactions)

发布时间:2024年01月24日

在MySQL的运行中,经常会遇到一些长事务。长事务意味着长时间持有系统资源,这在OLAP系统中很常见,但在OLTP系统中,长事务意味着争用、并发降低,等待。长事务伴随的典型现象就是经常听到开发人员说"xxx表被锁住了…"

目录

一、长事务的成因

二、查找长事务


一、长事务的成因

长事务表面上来看都是运行时间过长。但其背地里的成因却可能不同,我认为长事务的成因可以分为以下3类:

  1. 表、索引设计不合理,存在慢SQL
  2. 事务设计不合理,耦合度过高
  3. 事务未正常结束,例如忘记事务提交或事务执行出错后没有后续处理。

第一类:表、索引设计不合理,这种就是常见的慢SQL导致事务执行时间过长,有些慢SQL在数据量低的时候可能无法发现,当生产数据逐渐增多,慢SQL的问题会越来越严重,最终导致长事务。这类长事务的解决方式是优化SQL(可以通过慢查询日志抓取慢SQL)。

第二类:事务设计不合理是将大量的逻辑处理塞到一个事务中,导致事务过于臃肿。这种问题需要从业务层面分析,看是否可以将过大事务拆分成多个独立事务,降低耦合,对于OLTP系统,大部分都应该是短小的事务。

第三类:事务未正常结束,这种可能是忘记提交,或者事务处理中出错,但用户没有后续处理。当事务某条语句出错时,其仍然处于活跃状态,已成功执行语句的锁会继续持有。某些人可能会直接杀死客户端连接,但对数据库来说,并没有收到显式结束事务的命令,它保持事务是活跃状态,一直等待用户的命令,直到互动超时(interactive_timeout 默认28800秒,即会话8小时没活动,关闭会话)。

以上三类长事务中,第一二类属于性能优化问题,事务通常可以正常结束。危害最大的是第三类,这种被遗忘的事务会长时间占用系统资源(默认8小时),是不可接受的。在事务执行出现问题时,需要显式的rollback或commit来结束该事务,如果客户端已经杀死连接,无法控制事务,那么只能从服务端杀死该会话。

二、查找长事务

MySQL已提供了相关性能视图帮助我们查询活跃事务信息,通过performance_schema.events_transactions_current可以查询所有当前事务的event,配合其他视图即可定位长事务及其会话信息,主要用到的视图如下:

  • performance_schema.events_transactions_current 查询事务的线程ID,状态,持续时间等信息
  • performance_schema.threads 查询线程类型,用户,IP地址等信息(MySQL中一个线程对应一个用户会话)
  • sys.processlist 查询线程当前的状态,执行的SQL等信息

各个视图的关键字段,即要查询的关键信息解释如下:

performance_schema.events_transactions_current

  • thread_id, event_id 事务线程ID,事件ID,这是一个联合主键,唯一定位一行记录
  • state 事务的状态,有ACTIVE, COMMITTED 或ROLLED BACK三种状态,找长事务需要关注的是ACTIVE状态
  • timer_start, timer_end, timer_wait 事务起始,结束(未结束则是当前)及持续时长,单位是皮秒(十亿分之一秒),我们要关注的是timer_wait
  • isolation_level 事务的隔离级别

注:如果是MySQL8.0.16之后的版本,可以直接用format_pico_time()函数将timer_wait转换成易读的格式。

performance_schema.threads

  • thread_id 线程ID
  • type 线程类型,分为BACKGROUND(后台线程)和FOREGROUND(用户线程),我们要关注的是用户线程
  • processlist_id 用户会话ID,只有用户线程才有
  • processlist_user 会话用户名,只有用户线程才有
  • processlist_host 会话主机地址(IP),只有用户线程才有
  • processlist_db 会话当前操作的数据库

sys.processlist

  • thd_id 线程ID
  • conn_id 会话ID
  • user 用户信息,user@host格式
  • db 用户操作数据库
  • command 当前会话状态
  • time 线程处于当前状态的时长
  • current_statement 当前执行SQL

了解了上面3个视图提供的信息含义,我们可以很容易的找出当前哪些事务执行时间过长,及这些事务当前在做什么:

select
t.thread_id 线程ID,
t.processlist_id 会话ID,
t.processlist_user 用户,
t.processlist_host 用户地址,
t.processlist_db 数据库,
p.command 会话状态,
e.state 事务状态,
format_pico_time(e.timer_wait) 事务持续时长,
p.current_statement 执行SQL
from performance_schema.events_transactions_current e
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;

  • 这里提前开了2个会话,通过begin手动开启事务,一个会话执行select sleep(10000),另一个执行了一条普通的insert into语句。
  • 第一个会话模拟了大事务/慢SQL的状态,会话的状态是Query,且执行SQL有内容,表示事务在运行中
  • 第二个会话模拟了事务未正常结束的状态,会话的状态是Sleep,执行SQL为NULL,表示事务处于空闲状态,这类事务需要重点关注
  • 第三条记录是这个查询本身

定位到长事务后,分析长事务属于哪一类,决定是否需要优化事务或人工介入。例如上面第二个事务,如果判断会话异常,可以通过杀死会话ID来结束该会话(事务);

kill 451;

文章来源:https://blog.csdn.net/frostlulu/article/details/135827492
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。