搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发
Lambda在线 > IT那活儿 > mysql commit 引起的性能问题

mysql commit 引起的性能问题

IT那活儿 2020-08-01


概述



对于支持事务的关系型数据库来说,事务的完成需要执行commit命令,用于保存该事务操作的相关日志、标记该事务已完成,确保数据的一致性。通常情况下,commit命令执行很快,但也会存在commit是性能瓶颈,影响整体数据库性能的情况发生。




案例分析



慢日志分析

对慢日志分析可见,commit操作平均耗时近1分钟是非正常现象。


MySQL commit机制

mysql数据库为了保证binlog、redolog中事务的一致性,事务采用了两阶段提交(2pc)机制:

  • prepare阶段

innodb write/sync事务redo、undolog,binlog不做任何操作。


  • commit阶段

mysql层write/syncbinlog,innodb写 commit 至redolog。

从这个机制可以看出,commit操作包括binlog的落地动作。这就延长了commit动作的时间,也成了一个性能瓶颈,为了优化这一性能问题,mysql5.6推出了组提交机制。


binlog组提交的基本思想是,引入队列机制保证innodb commit顺序与binlog落盘顺序一致,并将事务分组,组内的binlog刷盘动作交给一个事务进行,实现组提交目的。


binlog提交将提交分为了3个阶段,FLUSH阶段,SYNC阶段和COMMIT阶段。基本流程如下:


FLUSH 阶段

  1. 持有Lock_log mutex [leader持有,follower等待]

  2. 获取队列中的一组binlog(队列中的所有事务)

  3. 将binlog buffer到I/O cache

  4. 通知dump线程dump binlog


SYNC阶段

  1. 释放Lock_log mutex,持有Lock_sync mutex[leader持有,follower等待]

  2. 将一组binlog 落盘(sync动作,最耗时,假设sync_binlog为1)


COMMIT阶段

  1. 释放Lock_sync mutex,持有Lock_commit mutex[leader持有,follower等待]

  2. 遍历队列中的事务,逐一进行innodb commit

  3. 释放Lock_commit mutex

  4. 唤醒队列中等待的线程


组提交就是每次sync一组binlog,从而提升效率,而一组binlog的数量则由以下两个参数决定:


  • binlog_group_commit_sync_delay=N:在等待N μs后,开始事务刷盘

  • binlog_group_commit_sync_no_delay_count=N:如果队列中的事务数达到N个,就忽视binlog_group_commit_sync_delay的设置




数据库参数分析



参数名

参数值

sync_binlog

1

binlog_group_commit_sync_delay

1

binlog_group_commit_sync_no_delay_count

1000


以上参数为故障发生时数据库的参数设置,可以看binlog_group_commit_sync_delay设置为1微妙,即1微妙内sync一组binlog,这对于高并发的dml操作而言对IO压力相对比较大;sync_binlog参数在组提交机制下其意义也发生了变化,官方文档如下:


当sync_binlog设置为0或1时,binlog_group_commit_sync_delay时间后sync一组binlog;当sync_binlog设置N(N>1)时binlog_group_commit_sync_delay时间后syncN组binlog。


所以整体上看,故障发生时有大量的dml操作,且因为数据库的参数设置1微妙sync一组binlog,对IO造成了很大压力,再加上同时有大量的select查询,此刻IO已经达到极限,最终造成commit提交阻塞,事务不能及时释放资源,其它dml操作因不能及时获取事务锁、io等资源而延长执行时间。




总结


由于数据库设置为1微妙sync一组binlog,在大并发dml操作时对IO造成很大压力,造成commit阻塞和其它dml操作执行延长。由于binlog的sync操作代价相对较高,因此可以增加每组binlog的数量、每次syncbinlog组的数量,减轻对IO的压力。


设置sync_binlog为1,则commit操作需要等待binlog_group_commit_sync_delay时间才能完成,阻塞了commit操作,设置太大的值(如:1000),则一次syncbinlog量比较大,很容易造成IO波动,如果存大大事务,则IO波动会更大。


binlog_group_commit_sync_delay设置太小,则每组中binlog数量较小,起不到组提交带来的性能优化,设置太大则阻塞了commit操作很长时间且binlog事务太大。


binlog_group_commit_sync_delay最好设置为10的倍数,不然会引起如下bug:

版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《mysql commit 引起的性能问题》的版权归原作者「IT那活儿」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注IT那活儿微信公众号

IT那活儿微信公众号:justdoit2019syy

IT那活儿

手机扫描上方二维码即可关注IT那活儿微信公众号

IT那活儿最新文章

精品公众号随机推荐