文档

调整RDS MySQL的innodb_flush_log_at_trx_commit和sync_binlog参数

更新时间:

innodb_flush_log_at_trx_commit和sync_binlog参数分别控制了MySQL中Redo log日志的记录方式和Binlog的更新策略。根据业务需求,您可通过调整参数取值进行实例调优,以在数据库性能和数据安全性之间取得平衡。

背景

优化innodb_flush_log_at_trx_commit和sync_binlog参数的配置可以提高数据库的性能和数据安全性,参数作用如下:

  • innodb_flush_log_at_trx_commit:控制MySQL实例在事务提交后,是否立即将日志缓冲数据更新至log file文件中,用于进行数据持久化。

  • sync_binlog:控制MySQL实例在事务提交后,何时将Binlog数据持久化至磁盘。

注意事项

  • 请了解每个参数的含义和作用后再修改参数项,以免产生非预期的结果,详情请参见MySQL官方文档innodb_flush_log_at_trx_commitsync_binlog

  • 请根据实际情况调整参数,例如业务压力、业务安全性要求、实例规格和配置等。

  • 建议在业务低峰期进行修改尝试,持续观察查询性能的变化。

  • 修改前请明确业务安全性要求,虽然不将innodb_flush_log_at_trx_commit和sync_binlog参数均设置为1,比将二者均设置为1,能够带来性能上的提升,但是也会带来数据丢失的风险,请谨慎修改。

  • 需要在合理的范围内进行参数的配置:

    • 将sync_binlog参数设置为0时,能获得更好的性能,但是binlog rotate会阻塞更长的时间,导致更明显的抖动,所以不建议设置为0。

    • 将innodb_flush_log_at_trx_commit参数设置为0时,事务提交时不会主动将Redo Buffer中的日志写入文件中,因此每次实例进程重启都可能导致InnoDB内部数据丢失,所以不建议设置为0。

参数取值含义及调整原则

innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit是MySQL InnoDB存储引擎独有的参数,用于控制InnoDB的Redo log日志记录方式。通过调优该参数,可以提升数据库的性能和数据安全性。该参数的取值范围为0、1、2,不同的值代表MySQL数据库的Redo log不同的刷盘的方式:

  • 当innodb_flush_log_at_trx_commit=1时,InnoDB将在每次事务提交时将log buffer的数据更新到文件系统os buffer中,并调用文件系统的flush操作将数据缓存更新至磁盘中。此种方式下,数据库完全遵守ACID特性,安全性较高。

  • 当innodb_flush_log_at_trx_commit=2时,InnoDB将在每次事务提交时将log buffer中的数据更新到文件系统缓存中,每秒钟将文件系统缓存中的数据更新到磁盘一次,该操作由操作系统调度。因为DDL变更或其他InnoDB内部原因会导致更新磁盘的操作独立于innodb_flush_log_at_trx_commit参数设置,不能完全保证每秒更新磁盘一次,没有被更新到磁盘中的事务可能会因宕机而丢失。

  • 当innodb_flush_log_at_trx_commit=0时,InnoDB会每秒钟将log buffer中的数据更新到磁盘中。因为DDL变更或其他InnoDB内部原因会导致更新磁盘的操作独立于innodb_flush_log_at_trx_commit参数设置,并不能完全保证每秒将数据更新到磁盘一次。因此,在实例崩溃恢复场景中,可能会出现丢失1秒钟的事务。

需要注意的是,当innodb_flush_log_at_trx_commit设置为0或2时,并不能完全保证每秒将数据更新到磁盘一次,但也有可能更频繁地更新数据到磁盘。因此,在实际应用中,需要根据性能和数据安全性等方面的需求来设置。

sync_binlog

sync_binlog是MySQL Binlog日志的重要参数,用于控制Binlog的更新策略,通过对该参数的调优,可以提升数据库的性能和数据安全性:

  • 当sync_binlog=1时,MySQL会在每次事务提交后,将Log Buffer中的数据更新到磁盘上,此时MySQL安全性较高,但是IO消耗也较高。

  • 当sync_binlog=0时,MySQL会在每次事务提交后将binlog_cache中的数据更新至文件系统缓冲区,但不会进行持久化,而是依赖操作系统来调度数据刷入磁盘。

  • 当sync_binlog=N时,MySQL会在每N组事务提交后将数据更新到磁盘中。通过这种方式,可以在一定程度上平衡MySQL的性能和数据的安全性。如果N设置得比较大,可以提高系统的性能,但会降低数据的安全性。

综上所述,innodb_flush_log_at_trx_commit和sync_binlog参数需要根据具体的需求来设置:

  • 在对数据安全性要求较高的场景下,建议将这两个参数设置为1。

  • 在对实例性能要求较高的场景下,建议将这两个参数设置为0或者将innodb_flush_log_at_trx_commit设置为0,sync_binlog设置为N,以提高系统的性能,但需要注意可能会增加数据丢失的风险。

参数设置步骤

请参见设置实例参数

参数设置示例

下文通过几个典型的场景对上述两个参数的配置进行说明:

  • 场景一:只读实例延迟

    RDS MySQL提供了只读实例功能,用户可以通过创建只读实例来分担主实例上的读压力。在创建只读实例时,用户需要重新选择只读实例的参数模板(不会直接继承主实例的参数模板)。如果创建只读实例时选择了“默认参数模板”(sync_binlog=1,innodb_flush_log_at_trx_commit=1),当主实例上的写压力比较大时,只读节点上可能出现binlog event应用不及时,导致出现只读节点延迟。 由于只读实例只会承担用户的读请求,并且不需要对只读实例进行备份,所以建议在只读实例上,直接将sync_binlo设置为1000,innodb_flush_log_at_trx_commit设置为2,保证只读实例上的binlog event应用效率,避免出现只读节点延迟。

    说明

    导致只读节点出现延迟的原因很多,将sync_binlog设置为1000,innodb_flush_log_at_trx_commit设置为2,不能保证一定没有延迟,需要根据具体情况进行分析。

  • 场景二:DTS数据迁移速度慢

    DTS的数据迁移功能支持用户在RDS MySQL之间、RDS MySQL与自建MySQL之间进行数据的迁移与同步。在数据迁移过程中,由于数据的写入量大,可能出现目标实例性能瓶颈,导致迁移速度慢。 在此种场景中,目标实例数据写入慢的原因可能是目标实例使用了“默认参数模板”(sync_binlog=1,innodb_flush_log_at_trx_commit=1),在数据写入的过程中,需要保证binlog和redo log的落盘,从而影响了数据写入的效率。为了提升目标实例的写入效率,可以将目标实例上的sync_binlog设置为1000, innodb_flush_log_at_trx_commit设置为2,加速数据迁移速度。

  • 场景三:热点更新性能差

    对于交易或者支付类的业务,为了保证数据安全,通常会将sync_binlog和innodb_flush_log_at_trx_commit均设置为1,保证binlog和redo log的实时落盘,但是这种配置会使大促场景、秒杀场景出现大量的并发写入和热点数据更新,导致系统性能变差。 对于大促场景、秒杀场景,可以临时将sync_binlog设置为1000,innodb_flush_log_at_trx_commit设置为2,保证事务的快速提交,提升系统的并发能力。当促销结束后,再恢复到原来的配置。

相关文档

您可以通过参数诊断功能,获取实例参数优化方案。

  • 本页导读 (1)