The innodb_flush_log_at_trx_commit and sync_binlog parameters are used to manage the methods used to record redo logs and the update policy of binary logs of an ApsaraDB RDS for MySQL instance. You can modify the parameters to balance database performance and data security.
Usage notes
Before you modify the parameters, we recommend that you familiarize yourself with the meaning and functionality of the parameters to prevent unexpected results, such as data loss or performance degradation. 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 specifications and 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 determine your security requirements. If your workloads require high security, we recommend that you set the parameters to 1. If your workloads require high performance, we recommend that you set the
innodb_flush_log_at_trx_commitparameter to 2 and thesync_binlogparameter to 1000. However, the settings may cause data loss.You must appropriately configure the parameters.
We recommend that you do not use the following settings:
sync_binlog=0: This setting may cause jitters in binary log rotation.innodb_flush_log_at_trx_commit=0: If you restart your RDS instance, data loss may occur.
Parameter values and modification rules
innodb_flush_log_at_trx_commit
This parameter is specific to the InnoDB storage engine and is used to specify how the InnoDB storage engine records redo logs. Valid values: 0, 1, and 2.
0: The InnoDB storage engine writes data in the log buffer to the disk every second. When a transaction is committed, InnoDB does not proactively flush disks.
Advantages: The optimal performance can be delivered.
Risks: If instance downtime occurs, up to 1 second of transaction data may be lost.
1 (default value): The InnoDB storage engine writes data in the log buffer to the disk each time a transaction is committed and then flushes the disk.
Advantages: This setting is fully compliant with atomicity, consistency, isolation, and durability (ACID) and provides the highest security.
Disadvantages: The performance overheads are high, especially in high-concurrency scenarios.
2: The InnoDB storage engine writes data in the log buffer to the file system buffer each time a transaction is committed. Then, the operating system schedules and flushes the data to the disk once every second.
Advantages: This setting provides higher performance than the value of 1 and higher data security than the value of 0.
Risks: If instance downtime occurs, transaction data within the most recent 1 second may be lost.
sync_binlog
This parameter is an important parameter for MySQL binary logs. This parameter is used to specify the update policy of binary logs. Valid values: 0, 1, and N. The value N specifies a positive integer.
0: After a transaction is committed, MySQL writes binary logs to the file system buffer and the operating system schedules and flushes the data to the disk.
Advantages: The optimal performance can be delivered.
Risks: If instance downtime occurs, the binary logs that are not flushed to the disk may be lost.
1 (default value): After a transaction is committed, MySQL immediately writes binary logs to the disk.
Advantages: This setting provides the highest data security.
Disadvantages: The performance overheads are high.
N: After every N transactions are committed, MySQL writes binary logs to the disk.
Advantages: The setting is a compromise between performance and data security.
Risks: If instance downtime occurs, the binary logs of up to N transactions may be lost.
Optimization rules
If your workloads require high security, we recommend that you set the
innodb_flush_log_at_trx_commitandsync_binlogparameters to 1. This ensures that data can be written to the disk in real time.If your workloads require high performance, we recommend that you set the
innodb_flush_log_at_trx_commitparameter to 2 and thesync_binlogparameter to 1000. This improves performance but may cause data loss.We recommend that you do not use the following settings:
innodb_flush_log_at_trx_commit=0: If you restart your RDS instance, a large amount of data may be lost.sync_binlog=0: The binary log rotation may be blocked for a long period of time, which affects performance and stability.
Procedure for configuring parameters
For more information, see Modify the parameters of an ApsaraDB RDS for MySQL instance.
Examples of parameter configurations
The following section describes how to configure the preceding 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. If you select the default parameter template that uses the
sync_binlog=1andinnodb_flush_log_at_trx_commit=1settings for a read-only RDS instance, the binary logs may not be immediately applied to the read-only RDS instance when the primary RDS instance needs to process a large number of write requests.Optimization suggestions:
To ensure that the binary logs are immediately applied to the read-only RDS instance, we recommend that you set the
sync_binlogparameter to 1000 and theinnodb_flush_log_at_trx_commitparameter to 2 for your read-only RDS instance.Note: The settings do not fully eliminate latency.
Scenario 2: DTS-based data migration is slow.
If you use the data migration feature of DTS to migrate data, binary and redo logs are frequently written to the disk and performance bottlenecks may occur on the required instance.
Optimization suggestions:
To accelerate data writes, we recommend that you set the
sync_binlogparameter to 1000 and theinnodb_flush_log_at_trx_commitparameter to 2.After data migration is complete, you can restore the settings to the default settings based on your business requirements.
Scenario 3: The instance performance is poor due to hotspot updates.
If you set both the
sync_binlogandinnodb_flush_log_at_trx_commitparameters to 1 and a large amount of data is concurrently written in scenarios such as flash sales and big promotions, the performance may be degraded.Optimization suggestions:
To improve concurrency, we recommend that you set the
sync_binlogparameter to 1000 and theinnodb_flush_log_at_trx_commitparameter to 2.After the activities end, you can restore the settings to the default settings to ensure data security.
References
You can use the parameter diagnostics feature to obtain parameter optimization solutions.