3 Minuten Lesezeit (535 Worte)

Less is more. MySQL Binlog Transaction Compression

Dienstag, 29. März 2022

The MySQL binary log is of particular importance for the operation of critical databases. It is the central point for DR and/or backup concepts. We have already talked about the mechanism and benefits of this "log file" in many places in this blog. With version 8.0.20, it is now possible to use compression. We will report on this topic in the following blog.

Deflate it?

But why log transactions in compressed form? In the past, we have seen many systems where the accumulated transaction logs have taken up more space than the actual data. In the best case, the log files are moved to other storage areas as part of a backup concept (physical separation from the DB server) and require the corresponding space there. Here, of course, the savings potential can be used as well.

But "slimmer" logs can also be advantageous in HA/DR concepts. The information must be sent through the network to the secondary system. But here the smaller size also brings an advantage.

The activation of compression can be done dynamically (i.e. at runtime without restarting the server). The parameter "-binlog-transaction-compression[={OFF|ON}]" can be set generally (server-wide) or session-specific.

mysql> set global binlog_transaction_compression=1;

In addition to activating compression, the degree (effort) of compression (--binlog-transaction-compression-level-zstd=#) can be set. A value from 1 to 22 (3 is the default) is available here.

mysql> set global binlog_transaction_compression_level_zstd=3

No pain…

Of course, as always in life, an advantage (less I/O, disk space, network traffic) is always bought by a disadvantage. Here we "pay" with the more intensive use of CPU. So before deciding whether and how much to "invest" in compressing binlogs, one should look very closely at the system (OS and DB). Here are a few basic questions:

How heavy is the CPU load?
Do I have an I/O problem;do I have high write rates of the binlogs?
Do I have a space problem ("archived" binlogs)?
Are the binary logs are replicated? Is there a latency/throughput problem?

… no gain

Finally, the actual effect of the compression should also be evaluated. What does the compression achieve? MySQL provides a corresponding system view for this (in the "performance_schema"):

mysql> select * from binary_log_transaction_compression_stats where compression_type = 'ZSTD'\G 
*************************** 1. row *************************** 
                            LOG_TYPE: BINARY 
                    COMPRESSION_TYPE: ZSTD 
                 TRANSACTION_COUNTER: 75 
            COMPRESSED_BYTES_COUNTER: 2811350 
          UNCOMPRESSED_BYTES_COUNTER: 6617130 
              COMPRESSION_PERCENTAGE: 58 
                FIRST_TRANSACTION_ID: ANONYMOUS 
  FIRST_TRANSACTION_COMPRESSED_BYTES: 2186 
FIRST_TRANSACTION_UNCOMPRESSED_BYTES: 4310 
         FIRST_TRANSACTION_TIMESTAMP: 2021-09-09 12:16:53.816784 
                 LAST_TRANSACTION_ID: ANONYMOUS 
   LAST_TRANSACTION_COMPRESSED_BYTES: 188 
 LAST_TRANSACTION_UNCOMPRESSED_BYTES: 212 
          LAST_TRANSACTION_TIMESTAMP: 2021-09-09 14:15:46.133141 
1 row in set (0.00 sec)

In this example, only some data was loaded using the MySQL TestDB "sakila". The resulting data of the 75 transactions could be reduced by 58%.

Conclusion: Nothing but hot air?

Certainly not. Compressing binlogs can unquestionably be helpful in some places. Before using it, of course, you should take a close look at your system to decide whether it makes sense to use this feature or not.

Do you have performance problems on your database? Then talk to us. We are happy to help.