专栏首页SEian.G学习记录MySQL优化 InnoDB 事务管理

MySQL优化 InnoDB 事务管理

今天我们来聊聊如何优化InnoDB事务管理?

要优化InnoDB事务管理,请在事务功能的性能开销与服务器的工作负载之间找到理想的平衡。例如,如果一个应用程序每秒提交数千次,则可能会遇到性能问题;如果仅每2-3小时提交一次,则可能会遇到不同的性能问题。

  • 默认的MySQL设置AUTOCOMMIT=1 可能会对繁忙的数据库服务器造成性能限制。在可行的情况下,通过发出SET AUTOCOMMIT=0或START TRANSACTION声明,将多个相关的数据更改操作包装到单个事务中 ,然后在进行所有更改后再添加一个 COMMIT语句。
  • InnoDB如果该事务对数据库进行了修改,则必须在每次事务提交时将日志刷新到磁盘。在每次更改之后都进行一次提交(与默认的自动提交设置一样)时,存储设备的I / O吞吐量将限制每秒可能进行的操作的数量。
  • 对于仅包含一条SELECT语句的事务,打开AUTOCOMMIT有助于 InnoDB识别只读事务并对其进行优化。
  • 避免在插入,更新或删除大量行之后执行回滚。如果大事务减慢了服务器性能,则回滚它会使问题变得更糟,执行时间可能是原始数据更改操作的几倍。终止数据库进程无济于事,因为回滚会在服务器启动时再次开始。
  • 为了最大程度地减少发生此问题的可能性,请执行以下操作:
  • 增加缓冲池的大小, 以便可以缓存所有数据更改更改,而不是立即将它们写入磁盘。
  • 进行设置, innodb_change_buffering=all 以便除了插入操作外还缓冲更新和删除操作。
  • 考虑COMMIT在大数据更改操作期间定期发布语句,可能将单个删除或更新分解为对较少行数进行操作的多个语句。

要消除发生的回滚,请增加缓冲池,以使回滚成为CPU约束并快速运行,或者终止服务器并重新启动 innodb_force_recovery=3。

默认设置预计不会出现此问题,该默认设置 innodb_change_buffering=all允许将更新和删除操作缓存在内存中,从而使它们首先可以更快地执行,并且在需要时可以更快地回滚。确保在处理具有许多插入,更新或删除操作的长期事务的服务器上使用此参数设置。

  • 如果可以承受因意外退出而导致的一些最新提交事务的丢失,可以将innodb_flush_log_at_trx_commit 参数设置 为0。InnoDB尽管不能保证刷新,但还是尝试每秒刷新一次日志。
  • 修改或删除行时,不会立即删除行和关联的 撤消日志,甚至不会在事务提交后立即删除。保留旧数据,直到更早或同时开始的事务完成为止,以便那些事务可以访问已修改或已删除行的先前状态。因此,长时间运行的事务可以防止InnoDB清除由其他事务更改的数据。
  • 如果在长时间运行的事务中修改或删除行,则使用READ COMMITTED和 REPEATABLE READ隔离级别的其他事务 必须读取旧的数据,才能做更多工作来重建较旧的数据。
  • 当长时间运行的事务修改表时,来自其他事务的对该表的查询不会使用覆盖索引技术。通常可以从二级索引检索所有结果列,而从表数据中查找适当值的查询。如果发现二级索引页面的索引 PAGE_MAX_TRX_ID太新,或者二级索引中的记录被删除标记,则 InnoDB可能需要使用聚集索引来查找记录。

官方文档链接:

https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-transaction-management.html

本文分享自微信公众号 - DBA的辛酸事儿(dbabitter),作者:SEian.G

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-07-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • mysql-innodb-事务

    InnoDB存储引擎层产生,物理日志,记录的是对页的修改,innodb1.2版本后,最大512GB

    温安适
  • MySQL 体系架构简介

    松哥原创的 Spring Boot 视频教程已经杀青,感兴趣的小伙伴戳这里-->Spring Boot+Vue+微人事视频教程

    江南一点雨
  • MySql - InnoDB - 事务 , Php版

    (出处:https://cloud.tencent.com/developer/user/1148436/activities) 1,前序 由于要重构APP(...

    林冠宏-指尖下的幽灵
  • MySQL InnoDB 锁和事务

    A(Automicity)原子性,要么成功要么失败 C(consistency)一致性 I(isolation)隔离性 D(durability)持久性

    yingzi_code
  • 超详细的mysql数据库参数优化,都总结在这里了

    最近在对各个系统的mysql做一些参数上的优化,也开了慢查询,准备后面针对特定sql再进一步优化。下面主要介绍一下一些优化的参数。

    lyb-geek
  • 【我在拉勾训练营学技术】Mysql 架构原理

    mysql 数据库作为现在互联网企业首选的数据库,我们程序员就应该对它多一些了解,我在拉勾训练营学到第五阶段啦,了解了mysql 整体架构,记录下来。

    程序员爱酸奶
  • MySQL DBA基本面试题总结--Java学习网

    MyISAM:默认的MySQL插件式存储引擎,它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

    用户1289394
  • MGR最优化配置推荐

    本次内容整理参考了3306π社区广州站《MGR Best Practice》万里DB CTO娄帅分享和一些个人的理解认识。本次总结内容如下:

    wubx
  • MySQL 8.0 版本功能变更介绍

    作者介绍:朱强,腾讯云数据库高级工程师,主要负责腾讯云数据库MySQL的开发和运营,曾就职于华为和网宿,在存储、文件系统开发有丰富经验。

    腾讯云数据库 TencentDB
  • MySQL高可用架构选型,言简意赅全是要点

    MySQL发展至今,在高可用性方面不断前进,从最初的异步复制、半同步复制、群组复制,演进到现在的InnoDB Cluster和InnoDB Replica Se...

    PHP开发工程师
  • MySQL innoDB的事务隔离

    疑问: 那读提交和可重复读有什么区别吗? 是的,我也有这个疑问,读提交和可重复读不都是在提交后对其他事务可见。确实是这样 但是读提交在另一个事务提交后再去读取...

    居士
  • MySQL DBA亲授MySQL InnoDB事务ACID实现原理

    隔离性的实现原理就是锁,因而隔离性也可以称为并发控制、锁等。事务的隔离性要求每个读写事务的对象对其他事务的操作对象能互相分离。

    数据和云
  • 美团面试官:讲清楚MySQL结构体系,立马发offer

    继续和大家分享,我去上海美团面试遇到的技术问题,当时,回答的也是马马虎虎的,不能说不好,也不能说好,反正就是没有给面试官一种爽的感觉。

    田维常
  • MySQL数据库架构——高可用演进

    MySQL发展至今,在高可用性方面不断前进,从最初的异步复制、半同步复制、群组复制,演进到现在的InnoDB Cluster和InnoDB Replica Se...

    MySQLSE
  • MySQL架构分析

    编程之心
  • MySQL Database Service with Analytics Engine

    昨天12月2日,MySQL团队放了一个大招——MySQL Database Service with Analytics Engine。这是个什么东西?先看看官...

    MySQLSE
  • 【Mysql-InnoDB 系列】InnoDB 架构

    封面图片来自:mysql官方文档,8.0版本,InnoDB Architecture。

    程序员架构进阶
  • 【分布式事务】面试官问我:MySQL中的XA事务崩溃了如何恢复??

    作者个人研发的在高并发场景下,提供的简单、稳定、可扩展的延迟消息队列框架,具有精准的定时任务和延迟队列处理功能。自开源半年多以来,已成功为十几家中小型企业提供了...

    冰河
  • 【分布式事务】面试官问我:如何恢复MySQL中的XA事务?

    作者个人研发的在高并发场景下,提供的简单、稳定、可扩展的延迟消息队列框架,具有精准的定时任务和延迟队列处理功能。自开源半年多以来,已成功为十几家中小型企业提供了...

    冰河

扫码关注云+社区

领取腾讯云代金券