PolarDB:Enable optimized binlog writing for large transactions

Background

After binary logging is enabled, the binlog data of a transaction is written into the binlog file when the transaction is committed. The following figure shows the process. When a transaction is executed, the generated binlogs are temporarily stored in the binlog cache of the transaction. If multiple transactions are committed at the same time, the transactions queue up to write their binlog data from the binlog cache to the binlog file. If one of the transactions such as Trx2 has a large binlog size, such as 1 GB, the transaction requires a longer period to write its binlog data to the binlog file. Consequently, other transactions in the queue are forced to wait, resulting in delays in their binlog writing and transaction commit. This latency can temporarily make the system unwritable and lead to issues such as a surge in slow SQL queries. As a result, applications may repeatedly retry failed operations, which causes a surge of new connections and further increases the system load.

To address this issue, PolarDB for MySQL provides the optimized binlog writing feature for large transactions. After you enable the feature, the binlog writing process of a large transaction no longer impedes the commit process of other transactions. This optimization prevents temporary write unavailability in the system, which could otherwise lead to issues such as a surge in slow SQL write requests or a rapid increase in connections.

Supported versions

Your cluster must run PolarDB for MySQL Standard Edition 8.0.1 whose revision version is 8.0.1.1.42 or later.

Limits

After you enable optimized binlog writing for large transactions, binlogs generated by a large transaction are written into a dedicated binlog file. The following figure shows that the header of the dedicated binlog file contains events such as FORMAT DESCRIPTION EVENT and PREVIOUS GTIDS LOG EVENT. A special placeholder event called IGNORABLE LOG EVENT is also contained in the header. Downstream systems automatically ignore IGNORABLE LOG EVENT.

The following limits apply due to the special structure of the binlog file:

• Downstream replication nodes cannot use database concurrency-based multi-threaded replication but can use logical clock concurrency-based multi-threaded replication. You must not specify slave_parallel_workers>0 and slave_parallel_type='DATABASE' in combination. However, you can specify slave_parallel_workers> 0 and slave_parallel_type= 'logical_clock' in combination.

• You cannot use checksum for binlog files of large transactions.

Usage

Set the loose_enable_large_trx_optimization parameter to ON to enable optimized binlog writing for large transactions. Use the loose_binlog_large_trx_threshold_up parameter to define the threshold binlog size that triggers optimized binlog writing for large transactions.

The following table describes the parameters.

Performance comparison

The following figure shows the differences between the time required for a cluster that uses the PSL5 storage type to commit large transactions before and after optimized binlog writing for large transactions is enabled.

The figure shows a substantial reduction in the time required to commit large transactions after optimized binlog writing for large transactions is enabled. Consequently, issues such as heavy I/O load and prolonged write locks caused by large transaction commits are eliminated.