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.
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.