Bug #114709 Binlog compression for the replica applier requires a replica thread restart

Submitted: 19 Apr 2024 23:29 Modified: 6 May 2024 11:49
Reporter: Mershad Irani Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0+ OS:Any
Assigned to: CPU Architecture:Any

[19 Apr 2024 23:29] Mershad Irani
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.
[30 Apr 2024 9:15] MySQL Verification Team
Hello Mershad,

Thank you for the report and feedback.

regards,
Umesh
[6 May 2024 11:49] Jon Stephens
Fixed in the indicated places in the Manual, versions 8.0+ (mysqldoc rev 78515).

Thanks!

Closed.

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.