Percona Server for MySQL Highlights – binlog_space_limit

I think it is often confusing to compare upstream MySQL and Percona Server for MySQL, and some helpful information can be found in theintroductory notes. But what does that really mean for an ordinary DBA, especially if none of the known big extra features are important in a particular use case?

In this article, I would like to start a series of short blog posts highlighting small, often less known, but potentially useful features, available in Percona Server for MySQL. Let’s start with a relatively new feature.

Limit the disk space used by binary logs problem

Historically, dealing with binary logs and the disk space used by them was quite challenging. The only thing that let you control this is the expire_logs_days variable, which can be useless in some scenarios (binlogs growing faster than can be purged) and does not solve all problems. Therefore, in order to avoid disk full and server crashes, it is often needed to create smart scripts that monitor the situation and call PURGE BINARY LOGS TO when necessary.

This isn’t very convenient and so a feature request appeared in September 2012 – https://bugs.mysql.com/bug.php?id=66733 to use the already existing logic for relay logs and apply it for binary logs as well. It has not been addressed in upstream yet.

We attempted to deal with it in Percona Server by introducing max_binlog_files variable ( Percona Server 5.5.27, October 2012). This was a good step forward, but still not as good as expected. Even though limiting the number of binary log files gives much better control in terms of disk space used by them, it has one serious downside. It does not take into account situations when there may be binary logs lot smaller or a lot bigger then the max_binlog_size specifies.

Most common situations like that may be that the server rotates to a new binary log faster due to FLUSH LOGS issued, or due to restarts. A couple of such FLUSH commands in a row may completely ruin the ability to point in time recovery by losing a large part of the history of the binary log. An opposite case is when a binary log gets bigger than maximum size due to large transactions (which are never going to be split).

Recently, a new variable in MySQL 8.0 has replaced the expire_logs_days – the binlog_expire_logs_seconds and also a period of 30 days became the new default (previously it was unlimited).  It is certainly better to have more precise control on binary logs history then “days”, for example, we can set the rotation period to 2 days 6 hours and 30 minutes or whatever best matches the backup schedule. Again, a good time limit control on binary logs may not be sufficient in all cases and won’t help much when it is hard to predict how much data will be written or changed over time. Only space-based limits can actually save us from hitting the disk space problem.

Expected solution is there

Finally, something better has arrived, the binlog_space_limit variable, introduced in Percona Server 5.7.23 on September 2018 (ported to 8.0 as well). The idea is very simple: it does the same thing as relay_log_space_limit does for the relay logs. Finally, both sides of binary logging have comparable functionality when it comes to controlling the space on the disk.

How to use it

When the binlog_space_limit variable is set to 0 (default), the feature is turned off and no limit is set apart from the one imposed by expire_logs_days . I think it is a good idea is to dedicate a separate disk partition for the binary logs (or binary and redo logs), big enough to keep as many of them as needed for a good backup and replication strategy, and set the variable to around 95% of this partition size. Remember, that even though max_binlog_size defines the maximum size of a single binary log, this is not a hard limit. This is because a single transaction is never split between multiple binlog files and has to be written to the same binary log as a whole.

Let’s say the max_binlog_size is set to 1GB (default and maximum), and the binlog_space_limit is set to 10GB, but it happens that a really big transaction changes 3GB of data. What will happen with the binlog_space_limit set is that in order to store that new 3GB in the binary log (when the total size of logs is going to hit the limit), it will remove as many old binary logs as needed first, in this case, up to three oldest ones. This way there is no risk in hitting disk full situation even if the free disk space is smaller then the new binary log addition would need. But still, I would recommend reserving at least little more than 1GB of free disk space for such partition, just in case.

Example settings for a 16GB disk partition, dedicated for binary logs only, may look like this: