Overview
In a database, binlog is a log file used to record database operations, including addition, deletion, and modification. The binlog files increase over time, occupying a large disk space, so they should be regularly purged. However, binlog purging may conflict with normal binlog rotation and write transactions. Particularly when there are many binlog files, performance fluctuation is easily caused by binlog purging. Through analysis on the scenarios of purging binlog files, the Tencent Cloud kernel team has optimized the binlog purging performance.
The optimization addresses the LOCK_index conflict between binlog purging and binlog rotation, preventing failure to obtain the lock and continue writing binlogs in binlog rotation.
Currently, when an index lock is held by binlog purging, if binlog rotation is required after the binlog size reaches 256 MB but the lock cannot be obtained, binlog writing will stop until the index lock is obtained. The main optimization for this issue is that if the index lock cannot be obtained, binlog rotation is not performed and binlogs continue to be written into the current binlog file. Attempts will be made constantly to obtain the index lock and binlog rotation will be performed only after the index lock is obtained.
The optimization reduces the volume of information read from binlog files to be purged, thereby reducing locks.
The binlog purging process requires reading the GTID information from all binlogs to be purged and then writing it into the mysql.gtid executed table. Therefore, the exclusive lock of GTID is held during the entire purging period, resulting in that normal transactions cannot obtain the GTID lock. The main optimization for this issue is that GTID information is read only from the last binlog file in the binlog list, reducing the holding time of the GTID lock and decreasing its impact on normal transactions.
The maximum size of a single binlog file has been increased to 10 GB, significantly reducing the number of times of binlog rotation.
The default binlog size is 256 MB. Under a load of 5,000 QPS, binlog rotation is performed approximately every 1.4s, each taking 10-20 ms. Currently, the max_binlog_size can be increased from 256 MB to 10 GB (open-source MySQL supports up to 1 GB). Under the same load, rotation is now performed every 56s, reducing the frequency of obtaining the index lock. The max_binlog_size parameter is set to 1 GB in the current policy and can be further adjusted based on business load requirements.
Supported Versions
Kernel version 3.1.12 or later for TDSQL-C for MySQL 8.0.
Kernel version 2.1.12 or later for TDSQL-C for MySQL 5.7.
Use Cases
This feature is applicable to scenarios where binlog purging may cause lock contention and block binlog writing in case of many binlog files.
Use Instructions
This feature is used with parameters. Set txsql_binlog_rotate_try_lock_index and txsql_binlog_rotate_try_lock_log to ON, and set txsql_binlog_purge_check_file_count to 10 to use this feature. Detailed parameter descriptions are as follows:
|
txsql_binlog_rotate_try_lock_index | Global | bool | ON | ON/OFF | When it is set to ON, if the lock of the index file cannot be obtained in binlog rotation, the current rotation will be abandoned. |
txsql_binlog_rotate_try_lock_log | Global | bool | ON | ON/OFF | When it is set to ON, if the lock of the binlog file cannot be obtained in binlog rotation, the current rotation will be abandoned. |
txsql_binlog_purge_check_file_count | Global | ulonglong | 10 | 0-UINT64_MAX | The binlog purging process requires obtaining the binlog file name from the binlog index file. If there are many binlog files, this operation is time-consuming. Normally, only one binlog file name is required. This parameter restricts the number of file names obtained. 0 indicates no limit. It is generally set to 10. |