Bug #114709 Binlog compression for the replica applier requires a replica thread restart
Description:
When binlog_transaction_compression is turned ON globally on the replica, transactions executed by the replica applier thread are not written to the binary logs with a compressed transaction payload unless the replica thread is restarted.
How to repeat:
1. execute the below statement to turn on binlog transaction compression on both source and replica.
set global binlog_transaction_compression=ON
2. Now execute a DML on the source.
create database testdb;
use testdb;
create table t1(col1 int primary key auto_increment, col2 int);
insert into t1 values(1,1);
3. Now check the contents of the binarylogs on both the source and the replica.
i.We see a compressed transaction payload(Transaction_payload) on the source:
| binarylog.014830 | 1023 | Gtid | 1479660350 | 1102 | SET @@SESSION.GTID_NEXT= 'd0a01eee-f3af-11ee-8e4f-0626efa814db:679936417' |
| binarylog.014830 | 1102 | Transaction_payload | 1479660350 | 1278 | compression='ZSTD', decompressed_size=187 bytes |
| binarylog.014830 | 1278 | Query | 1479660350 | 1278 | BEGIN |
| binarylog.014830 | 1278 | Table_map | 1479660350 | 1278 | table_id: 118 (testdb.t1) |
| binarylog.014830 | 1278 | Write_rows | 1479660350 | 1278 | table_id: 118 flags: STMT_END_F |
| binarylog.014830 | 1278 | Xid | 1479660350 | 1278 | COMMIT /* xid=73642149235 */ |
+----------------------------+------+---------------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.00 sec)
ii. However, we don't see a compressed transaction payload on the replica:
| binarylog.031837 | 940 | Gtid | 1479660350 | 1026 | SET @@SESSION.GTID_NEXT= 'd0a01eee-f3af-11ee-8e4f-0626efa814db:679936417' |
| binarylog.031837 | 1026 | Query | 1479660350 | 1098 | BEGIN |
| binarylog.031837 | 1098 | Table_map | 1479660350 | 1149 | table_id: 117 (testdb.t1) |
| binarylog.031837 | 1149 | Write_rows | 1479660350 | 1193 | table_id: 117 flags: STMT_END_F |
| binarylog.031837 | 1193 | Xid | 1479660350 | 1224 | COMMIT /* xid=7646873191 */ |
+----------------------------+------+---------------------+------------+-------------+---------------------------------------------------------------------------------------------------+
4. We also confirm this by querying performance_schema.binary_log_transaction_compression_stats.
On the source:
mysql> select * from performance_schema.binary_log_transaction_compression_stats where COMPRESSION_PERCENTAGE>0 \G
*************************** 1. row ***************************
LOG_TYPE: BINARY
COMPRESSION_TYPE: ZSTD
TRANSACTION_COUNTER: 679932574
COMPRESSED_BYTES_COUNTER: 1352483300449
UNCOMPRESSED_BYTES_COUNTER: 3621960328351
COMPRESSION_PERCENTAGE: 63
FIRST_TRANSACTION_ID: d0a01eee-f3af-11ee-8e4f-0626efa814db:40
FIRST_TRANSACTION_COMPRESSED_BYTES: 232063
FIRST_TRANSACTION_UNCOMPRESSED_BYTES: 321840
FIRST_TRANSACTION_TIMESTAMP: 2024-04-06 03:25:40.946997
LAST_TRANSACTION_ID: d0a01eee-f3af-11ee-8e4f-0626efa814db:679936417
LAST_TRANSACTION_COMPRESSED_BYTES: 143
LAST_TRANSACTION_UNCOMPRESSED_BYTES: 187
LAST_TRANSACTION_TIMESTAMP: 2024-04-19 22:58:36.895799
On the replica the above query won't return any rows.
5. Now restart the replica thread and re-run the insert on the source.
On the Replica:
stop replica;start replica;
On the Source:
insert into t1 values(2,2);
6. We see that the transaction payload is compressed on both the source and the replica.
On the Source:
|0a01eee-f3af-11ee-8e4f-0626efa814db:1-679936418 |
| binarylog.014833 | 197 | Gtid | 1479660350 | 276 | SET @@SESSION.GTID_NEXT= 'd0a01eee-f3af-11ee-8e4f-0626efa814db:679936419' |
| binarylog.014833 | 276 | Transaction_payload | 1479660350 | 453 | compression='ZSTD', decompressed_size=187 bytes |
| binarylog.014833 | 453 | Query | 1479660350 | 453 | BEGIN |
| binarylog.014833 | 453 | Table_map | 1479660350 | 453 | table_id: 118 (testdb.t1) |
| binarylog.014833 | 453 | Write_rows | 1479660350 | 453 | table_id: 118 flags: STMT_END_F |
| binarylog.014833 | 453 | Xid | 1479660350 | 453 | COMMIT /* xid=73642152167 */ |
+----------------------------+-----+---------------------+------------+-------------+---------------------------------------------------------------------------+
On the Replica:
| binarylog.031840 | 197 | Gtid | 1479660350 | 283 | SET @@SESSION.GTID_NEXT= 'd0a01eee-f3af-11ee-8e4f-0626efa814db:679936419' |
| binarylog.031840 | 283 | Transaction_payload | 1479660350 | 451 | compression='ZSTD', decompressed_size=182 bytes |
| binarylog.031840 | 451 | Query | 1479660350 | 451 | BEGIN |
| binarylog.031840 | 451 | Table_map | 1479660350 | 451 | table_id: 117 (testdb.t1) |
| binarylog.031840 | 451 | Write_rows | 1479660350 | 451 | table_id: 117 flags: STMT_END_F |
| binarylog.031840 | 451 | Xid | 1479660350 | 451 | COMMIT /* xid=7646876143 */ |
+----------------------------+-----+---------------------+------------+-------------+---------------------------------------------------------------------------+
8 rows in set (0.00 sec)
Suggested fix:
It would be ideal if binlog transaction compression for the replica applier threads can dynamically reflect on the replica without a replica restart.
However, if this is something you consider by design, the below documentations should be updated to reflect this behavior, similar to the documentation for replica_parallel_workers.
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_...
https://dev.mysql.com/doc/refman/8.0/en/binary-log-transaction-compression.html
This would also apply to binlog_transaction_compression_level_zstd.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: When binlog_transaction_compression is turned ON globally on the replica, transactions executed by the replica applier thread are not written to the binary logs with a compressed transaction payload unless the replica thread is restarted. How to repeat: 1. execute the below statement to turn on binlog transaction compression on both source and replica. set global binlog_transaction_compression=ON 2. Now execute a DML on the source. create database testdb; use testdb; create table t1(col1 int primary key auto_increment, col2 int); insert into t1 values(1,1); 3. Now check the contents of the binarylogs on both the source and the replica. i.We see a compressed transaction payload(Transaction_payload) on the source: | binarylog.014830 | 1023 | Gtid | 1479660350 | 1102 | SET @@SESSION.GTID_NEXT= 'd0a01eee-f3af-11ee-8e4f-0626efa814db:679936417' | | binarylog.014830 | 1102 | Transaction_payload | 1479660350 | 1278 | compression='ZSTD', decompressed_size=187 bytes | | binarylog.014830 | 1278 | Query | 1479660350 | 1278 | BEGIN | | binarylog.014830 | 1278 | Table_map | 1479660350 | 1278 | table_id: 118 (testdb.t1) | | binarylog.014830 | 1278 | Write_rows | 1479660350 | 1278 | table_id: 118 flags: STMT_END_F | | binarylog.014830 | 1278 | Xid | 1479660350 | 1278 | COMMIT /* xid=73642149235 */ | +----------------------------+------+---------------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------+ 16 rows in set (0.00 sec) ii. However, we don't see a compressed transaction payload on the replica: | binarylog.031837 | 940 | Gtid | 1479660350 | 1026 | SET @@SESSION.GTID_NEXT= 'd0a01eee-f3af-11ee-8e4f-0626efa814db:679936417' | | binarylog.031837 | 1026 | Query | 1479660350 | 1098 | BEGIN | | binarylog.031837 | 1098 | Table_map | 1479660350 | 1149 | table_id: 117 (testdb.t1) | | binarylog.031837 | 1149 | Write_rows | 1479660350 | 1193 | table_id: 117 flags: STMT_END_F | | binarylog.031837 | 1193 | Xid | 1479660350 | 1224 | COMMIT /* xid=7646873191 */ | +----------------------------+------+---------------------+------------+-------------+---------------------------------------------------------------------------------------------------+ 4. We also confirm this by querying performance_schema.binary_log_transaction_compression_stats. On the source: mysql> select * from performance_schema.binary_log_transaction_compression_stats where COMPRESSION_PERCENTAGE>0 \G *************************** 1. row *************************** LOG_TYPE: BINARY COMPRESSION_TYPE: ZSTD TRANSACTION_COUNTER: 679932574 COMPRESSED_BYTES_COUNTER: 1352483300449 UNCOMPRESSED_BYTES_COUNTER: 3621960328351 COMPRESSION_PERCENTAGE: 63 FIRST_TRANSACTION_ID: d0a01eee-f3af-11ee-8e4f-0626efa814db:40 FIRST_TRANSACTION_COMPRESSED_BYTES: 232063 FIRST_TRANSACTION_UNCOMPRESSED_BYTES: 321840 FIRST_TRANSACTION_TIMESTAMP: 2024-04-06 03:25:40.946997 LAST_TRANSACTION_ID: d0a01eee-f3af-11ee-8e4f-0626efa814db:679936417 LAST_TRANSACTION_COMPRESSED_BYTES: 143 LAST_TRANSACTION_UNCOMPRESSED_BYTES: 187 LAST_TRANSACTION_TIMESTAMP: 2024-04-19 22:58:36.895799 On the replica the above query won't return any rows. 5. Now restart the replica thread and re-run the insert on the source. On the Replica: stop replica;start replica; On the Source: insert into t1 values(2,2); 6. We see that the transaction payload is compressed on both the source and the replica. On the Source: |0a01eee-f3af-11ee-8e4f-0626efa814db:1-679936418 | | binarylog.014833 | 197 | Gtid | 1479660350 | 276 | SET @@SESSION.GTID_NEXT= 'd0a01eee-f3af-11ee-8e4f-0626efa814db:679936419' | | binarylog.014833 | 276 | Transaction_payload | 1479660350 | 453 | compression='ZSTD', decompressed_size=187 bytes | | binarylog.014833 | 453 | Query | 1479660350 | 453 | BEGIN | | binarylog.014833 | 453 | Table_map | 1479660350 | 453 | table_id: 118 (testdb.t1) | | binarylog.014833 | 453 | Write_rows | 1479660350 | 453 | table_id: 118 flags: STMT_END_F | | binarylog.014833 | 453 | Xid | 1479660350 | 453 | COMMIT /* xid=73642152167 */ | +----------------------------+-----+---------------------+------------+-------------+---------------------------------------------------------------------------+ On the Replica: | binarylog.031840 | 197 | Gtid | 1479660350 | 283 | SET @@SESSION.GTID_NEXT= 'd0a01eee-f3af-11ee-8e4f-0626efa814db:679936419' | | binarylog.031840 | 283 | Transaction_payload | 1479660350 | 451 | compression='ZSTD', decompressed_size=182 bytes | | binarylog.031840 | 451 | Query | 1479660350 | 451 | BEGIN | | binarylog.031840 | 451 | Table_map | 1479660350 | 451 | table_id: 117 (testdb.t1) | | binarylog.031840 | 451 | Write_rows | 1479660350 | 451 | table_id: 117 flags: STMT_END_F | | binarylog.031840 | 451 | Xid | 1479660350 | 451 | COMMIT /* xid=7646876143 */ | +----------------------------+-----+---------------------+------------+-------------+---------------------------------------------------------------------------+ 8 rows in set (0.00 sec) Suggested fix: It would be ideal if binlog transaction compression for the replica applier threads can dynamically reflect on the replica without a replica restart. However, if this is something you consider by design, the below documentations should be updated to reflect this behavior, similar to the documentation for replica_parallel_workers. https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_... https://dev.mysql.com/doc/refman/8.0/en/binary-log-transaction-compression.html This would also apply to binlog_transaction_compression_level_zstd.