5.4.4.5 Binary Log Transaction Compression

Transactions with payloads that are compressed can be rolled back like any other transaction, and they can also be filtered out on a slave by the usual filtering options. Binary log transaction compression can be applied to XA transactions.

When binary log transaction compression is enabled, the max_allowed_packet and slave_max_allowed_packet limits for the server still apply, and are measured on the compressed size of the Transaction_payload_event, plus the bytes used for the event header. Note that compressed transaction payloads are sent as a single packet, rather than each event of the transaction being sent in an individual packet, as is the case when binary log transaction compression is not in use.

For multi-threaded workers, each transaction (including its GTID event and Transaction_payload_event) is assigned to a worker thread. The worker thread decompresses the transaction payload and applies the individual events in it one by one. If an error is found applying any event within the Transaction_payload_event, the complete transaction is reported to the co-ordinator as having failed. When slave_parallel_type is set to DATABASE, all the databases affected by the transaction are mapped before the transaction is scheduled. The use of binary log transaction compression with the DATABASE policy can reduce parallelism compared to uncompressed transactions, which are mapped and scheduled for each event.

For semisynchronous replication (see Section 17.4.9, “Semisynchronous Replication”), the slave acknowledges the transaction when the complete Transaction_payload_event has been received.

When binary log checksums are enabled (which is the default), the master does not write checksums for individual events in a compressed transaction payload. Instead, a checksum is written for the complete Transaction_payload_event, and indivdual checksums are written for any events that were not compressed, such as events relating to GTIDs.

For the SHOW BINLOG EVENTS and SHOW RELAYLOG EVENTS statements, the Transaction_payload_event is first printed as a single unit, then it is unpacked and each event inside it is printed.

For operations that reference the end position of an event, such as START SLAVE with the UNTIL clause, MASTER_POS_WAIT(), and sql_slave_skip_counter, you must specify the end position of the compressed transaction payload (the Transaction_payload_event). When skipping events using sql_slave_skip_counter, a compressed transaction payload is counted as a single counter value, so all the events inside it are skipped as a unit.

MySQL Server releases that support binary log transaction compression can handle a mix of compressed and uncompressed transaction payloads.

When a master in a replication topology and its slave both have binary log transaction compression enabled, the slave receives compressed transaction payloads and writes them compressed to its relay log. It decompresses the transaction payloads to apply the transactions, and then compresses them again after applying for writing to its binary log. Any downstream slaves receive the compressed transaction payloads.

When a master in a replication topology has binary log transaction compression enabled but its slave does not, the slave receives compressed transaction payloads and writes them compressed to its relay log. It decompresses the transaction payloads to apply the transactions, and then writes them uncompressed to its own binary log, if it has one. Any downstream slaves receive the uncompressed transaction payloads.

When a master in a replication topology does not have binary log transaction compression enabled but its slave does, if the slave has a binary log, it compresses the transaction payloads after applying them, and writes the compressed transaction payloads to its binary log. Any downstream slaves receive the compressed transaction payloads.

When a MySQL server instance has no binary log, if it is at a release from MySQL 8.0.20, it can receive, handle, and display compressed transaction payloads regardless of its value for binlog_transaction_compression. Compressed transaction payloads received by such server instances are written in their compressed state to the relay log, so they benefit indirectly from compression that was carried out by other servers in the replication topology.

A slave at a release before MySQL 8.0.20 cannot replicate from a master with binary log transaction compression enabled. A slave at or above MySQL 8.0.20 can replicate from a master at an earlier release that does not support binary log transaction compression, and can carry out its own compression on transactions received from that master when writing them to its own binary log.

You can monitor the effects of binary log transaction compression using the Performance Schema table binary_log_transaction_compression_stats. The statistics include the data compression ratio for the monitored period, and you can also view the effect of compression on the last transaction on the server. You can reset the statistics by truncating the table. Statistics for binary logs and relay logs are split out so you can see the impact of compression for each log type. The MySQL server instance must have a binary log to produce these statistics.

The Performance Schema table events_stages_current shows when a transaction is in the stage of decompression or compression for its transaction payload, and displays its progress for this stage. Compression is carried out by the worker thread handling the transaction, just before the transaction is committed, provided that there are no events in the finalized capture cache that exclude the transaction from binary log transaction compression (for example, incident events). When decompression is required, it is carried out for one event from the payload at a time.

mysqlbinlog with the --verbose option includes comments stating the compressed size and the uncompressed size for compressed transaction payloads, and the compression algorithm that was used.

You can enable connection compression at the protocol level for replication connections, using the MASTER_COMPRESSION_ALGORITHMS and MASTER_ZSTD_COMPRESSION_LEVEL options of the CHANGE MASTER TO statement (or the deprecated slave_compressed_protocol system variable). If you enable binary log transaction compression in a system where connection compression is also enabled, the impact of connection compression is reduced, as there might be little opportunity to further compress the compressed transaction payloads. However, connection compression can still operate on uncompressed events and on message headers. Binary log transaction compression can be worth enabling in combination with connection compression if you need to save storage space as well as network bandwidth. For more information on connection compression for replication connections, see Section 4.2.6, “Connection Compression Control”.

For Group Replication, compression is enabled by default for messages that exceed the threshold set by the group_replication_compression_threshold system variable. You can also configure compression for messages sent for distributed recovery by the method of state transfer from a donor's binary log, using the group_replication_recovery_compression_algorithm and group_replication_recovery_zstd_compression_level system variables. If you enable binary log transaction compression in a system where these are configured, Group Replication's message compression can still operate on uncompressed events and on message headers, but its impact is reduced. For more information on message compression for Group Replication, see Section 18.6.3, “Message Compression”.