大事务

·  阅读 52

MySQL的长事务会因为事务视图太老,MVCC时中需要执行很多的回滚操作才能得到对应的数据版本,而且还会形成很大的回滚段,所以会影响性能。 那么在项目开发中,应该如何避免大事务呢?

一般可以从客户端和服务器端分别进行控制

大事务产生的原因

  • 操作的数据比较多
  • 大量的锁竞争
  • 事务中有其他非DB的耗时操作

大事务造成的影响

  • 并发情况下,数据库连接池容易被撑爆
  • 锁定太多的数据,造成大量的阻塞和锁超时
  • 执行时间长,容易造成主从延迟
  • 回滚所需要的时间比较长
  • undo log膨胀
  • 。。。

如何避免大事务

通用解法

  • 在一个事务里面, 避免一次处理太多数据
  • 在一个事务里面,尽量避免不必要的查询
  • 在一个事务里面, 避免耗时太多的操作,造成事务超时。一些非DB的操作,比如rpc调用,消息队列的操作尽量放到事务之外操作

基于mysql5.7的解法

  • 在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
  • 通过SETMAX_EXECUTION_TIME命令, 来控制每个语句查询的最长时间,避免单个语句意外查询太长时间
  • 监控 information_schema.Innodb_trx表,设置长事务阈值,超过就报警/或者kill
  • 在业务功能测试阶段要求输出所有的general_log,分析日志行为提前发现问题
  • 设置innodb_undo_tablespaces值,将undo log分离到独立的表空间。如果真的出现大事务导致回滚段过大,这样设置后清理起来更方便

客户端

  • 设定事务执行的超时时间(SET MAX_EXECUTION_TIME),可以避免意外的长事务占用过多资源
  • 事务开始到结束的时间内,避免做耗时的操作,比如网络请求等
  • 尽量把容易有冲突的SQL语句写在业务逻辑后面,减少锁占用时间

以查询执行时间超过10秒的事务为例:

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>10
复制代码

\

服务器端

  • 监控 information_schema.Innodb_trx 表,设置长事务阈值,超过就报警或者 kill
  • 删除超时的连接: pt-kill --busy-time 60 --kill
  • 在业务功能测试阶段要求输出所有的 general_log,分析日志行为提前发现问题。日志分析可以使用pt-query-digest
  • 如果使用的是 MySQL 5.6 或者更新版本,把 innodb_undo_tablespaces 设置成 2(或更大的值)。如果真的出现大事务导致回滚段过大,这样设置后清理起来更方便。但是这个选项在MySQL未来的版本会去掉,到时候回滚段会使用改进后的存储方式,比如支持压缩The innodb_undo_tablespaces option is deprecated; expect it to be removed in a future release.



作者:十毛tenmao
链接:www.jianshu.com/p/27fa5f351… 来源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

分类:
后端
标签: