背景

优化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_commit、 sync_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，保证事务的快速提交，提升系统的并发能力。当促销结束后，再恢复到原来的配置。