Background information

You can optimize the configurations of the innodb_flush_log_at_trx_commit and sync_binlog parameters to improve instance performance and data security. The following list describes the parameters:

• innodb_flush_log_at_trx_commit: specifies whether to immediately flush data in the log buffer of the RDS instance to the log file after transactions on the RDS instance are committed.

• sync_binlog: specifies the time to flush binary log data to the disk after transactions on the RDS instance are committed.

Usage notes

• Before you modify the parameters, we recommend that you understand the meaning and functionality of the parameters. For more information, see innodb_flush_log_at_trx_commit and sync_binlog.

• You can modify the parameters based on your business requirements, such as workloads, security requirements, and instance configurations.

• We recommend that you modify the parameters during off-peak hours and continuously observe changes in query performance.

• Before you modify the parameters, you must understand your security requirements. If you set one of the innodb_flush_log_at_trx_commit and sync_binlog parameters to 1 rather than both parameters to 1, instance performance is improved but data loss may occur. Proceed with caution.

• You must appropriately configure the parameters.

  • If you set the sync_binlog parameter to 0, instance performance is improved. However, the binary log file of your RDS instance is rotated for a long period of time, which causes significant jitters. We recommend that you do not set the parameter to 0.

  • If you set the innodb_flush_log_at_trx_commit parameter to 0, logs in the redo buffer of your RDS instance are not actively written to the log file when the transactions on the RDS instance are committed. In this case, the internal data of the InnoDB storage engine may be lost each time an instance process is restarted. We recommend that you do not set the parameter to 0.

Parameter values and modification rules

innodb_flush_log_at_trx_commit

The innodb_flush_log_at_trx_commit parameter is specific to the InnoDB storage engine. This parameter is used to specify how the InnoDB storage engine records redo logs. You can optimize the configuration of this parameter to improve instance performance and data security. You can set this parameter to 0, 1, or 2 to specify how the InnoDB storage engine flushes redo logs to the disk.

• If you set the innodb_flush_log_at_trx_commit parameter to 1, the InnoDB storage engine writes data in the log buffer to the file system buffer each time a transaction is committed and then flushes the data in the file system buffer to the disk. This configuration is required for full compliance with atomicity, consistency, isolation, and durability (ACID) and provides high security for your RDS instance.

• If you set the innodb_flush_log_at_trx_commit parameter to 2, the InnoDB storage engine writes data in the log buffer to the file system buffer each time a transaction is committed and then flushes the data in the file system buffer to the disk once every second. The flush operation is scheduled by the operating system. DDL changes and other internal InnoDB activities cause data to be flushed independently of the innodb_flush_log_at_trx_commit setting. In this case, the data may not be flushed once every second, and transactions that are not flushed to the disk may be lost due to downtime.

• If you set the innodb_flush_log_at_trx_commit parameter to 0, InnoDB flushes data in the log buffer to the disk every second. DDL changes and other internal InnoDB activities cause data to be flushed independently of the innodb_flush_log_at_trx_commit setting. In this case, the data may not be flushed once every second. During a system breakdown, up to 1 second of transactions may be lost.

The values 0 and 2 cannot ensure that the data is flushed to the disk once every second. In some cases, data may be flushed to the disk more frequently than once every second. You must configure this parameter based on your business requirements, including performance and security requirements.

sync_binlog

sync_binlog is an important parameter for MySQL binary logs. This parameter is used to specify the flush policy of binary logs. You can optimize the configuration of this parameter to improve instance performance and data security.

• If you set the sync_binlog parameter to 1, MySQL flushes data in the log buffer to the disk each time a transaction is committed. This setting ensures high security but causes high I/O consumption.

• If you set the sync_binlog parameter to 0, MySQL writes data in the binary log cache to the file system buffer each time a transaction is committed and the file system schedules and manages the data to be flushed to the disk.

• If you set the sync_binlog parameter to N, MySQL flushes data to the disk after every N transactions are committed. This configuration helps balance instance performance and data security. If you set N to a large value, instance performance is improved, but data security is compromised.

You must configure the parameters based on your business requirements:

• If you require high data security, we recommend that you set both the parameters to 1.

• If you require high instance performance, we recommend that you set both the parameters to 0 or set the innodb_flush_log_at_trx_commit parameter to 0 and the sync_binlog parameter to N. The configurations can improve performance but may cause data loss.

Procedure for parameter settings

For more information, see Modify the parameters of an ApsaraDB RDS for MySQL instance.

Examples of parameter configurations

This section describes how to configure the preceding two parameters in the following scenarios:

• Scenario 1: Latency occurs on your read-only RDS instance.

  ApsaraDB RDS for MySQL allows you to create read-only RDS instances to offload read requests from the primary RDS instance. When you create a read-only RDS instance, you must select a parameter template for the read-only RDS instance because the read-only RDS instance does not inherit the parameter template of the primary RDS instance. If you select the default parameter template in which the values of the sync_binlog and innodb_flush_log_at_trx_commit parameters are 1, the binary log events may not be applied on the read-only RDS instance at the earliest opportunity when the primary RDS instance needs to process a large number of write requests. As a result, latency occurs on your read-only RDS instance. Read-only RDS instances process only read requests. Therefore, you do not need to back up read-only RDS instances. We recommend that you set the sync_binlo parameter to 1000 and the innodb_flush_log_at_trx_commit parameter to 2 for your read-only RDS instance. This ensures that the events in the binary log are applied on the read-only RDS instance at the earliest opportunity and prevents latency on the read-only RDS instance.

  Note

  Latency occurs on a read-only RDS instance due to various causes. Although you set the sync_binlog parameter to 1000 and the innodb_flush_log_at_trx_commit parameter to 2 for the read-only RDS instance, latency may occur on the read-only RDS instance. You must identify the causes based on your business scenarios.

• Scenario 2: DTS-based data migration is slow.

  The data migration feature of Data Transmission Service (DTS) allows you to migrate and synchronize data between RDS instances or between an RDS instance and a self-managed MySQL instance. If you use the data migration feature of DTS to migrate data and a large amount of data needs to be migrated, performance bottlenecks may occur on the required instance. As a result, the migration is slow. The possible cause of the slow data migration is that the required instance uses the default parameter template in which the values of the sync_binlog parameter and the innodb_flush_log_at_trx_commit parameters are 1. However, binary logs and redo logs must be flushed to the disk during data migration, which affects the efficiency of data migration. To accelerate data migration, you can set the sync_binlog parameter to 1000 and the innodb_flush_log_at_trx_commit parameter to 2 for the required instance.

• Scenario 3: The instance performance is poor due to hotspot updates.

  For transactional or payment services, you can set both the sync_binlog and innodb_flush_log_at_trx_commit parameters to 1 to ensure data security and ensure that the binary logs and redo logs are flushed to the disk in real time. However, the configurations cause a large number of concurrent writes and hot data updates in scenarios such as flash sales and big promotions, resulting in poor system performance. In flash sales and big promotions scenarios, you can temporarily set the sync_binlog parameter to 1000 and the innodb_flush_log_at_trx_commit parameter to 2 to accelerate transaction commitment and improve the concurrency of the system. After the sales or promotions end, restore the configurations of the sync_binlog and innodb_flush_log_at_trx_commit parameters.