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:

… 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"):

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.