4

I read recently about a setting in Percona Server called max_binlog_files which restricts the total number of binlog files. This is exactly what I need. My binlog_expire_logs_seconds is currently set to 3 days, which works great 99% of the time. However, I'm currently engaged in project which runs a massive amount of queries and the binlogs grew to consume over 70GB in just a few hours. In this case, I really need to limit the number of files.

Is there any native setting with MySQL to achieve this? What is the best way to restrict total disk space consumed by binlogs?

1
  • Big UPDATEs or DELETEs can cause lots of binlog entries. Perhaps we should discuss alternatives. What were your commands? Here are some tips to avoid binlog-bloat when doing Big deletes . (And help the deletes run faster.)
    – Rick James
    Nov 7, 2023 at 22:09

2 Answers 2

3

No, the upstream MySQL implementation does not have a feature equivalent to max_binlog_files. So you can't really prevent a short-term surge in binary log growth.

Well, the option log_bin=OFF would definitely prevent unlimited binary log growth, but that's the only solution provided by stock MySQL Server.

You could implement a scheduled job outside of MySQL (e.g. a cron job) to periodically check SHOW BINARY LOGS and if they are using more space than you want, then PURGE BINARY LOGS to reduce the space.

The risk is that your aggressive purging might delete some logs that are still needed for replication, point-in-time recovery, or CDC tools, if you use those features.

At my last job, we occasionally had binary logs on a given MySQL grow to 500GB. The developers were not aware of the impact of doing huge bulk data loads, and they would do so anytime they felt like it, without notifying the DBA team. We were using Percona Server so we set a max_binlog_files=500 so they wouldn't use more than 500GB, but we weren't at liberty to be more restrictive.

Ultimately, we just had to make sure there was enough storage space free on each database server for these kinds of surges.

8
  • Thanks, I appreciate the input. I'm not generally concerned about replication or point in time recovery in this case so a cron job it is! I appreciate your feedback. Oct 23, 2023 at 16:18
  • Why do you have binary logs enabled at all, if you don't use them? Oct 23, 2023 at 16:30
  • Aren't they used for transactions...? Oct 23, 2023 at 16:31
  • 1
    It's starting to sound like log_bin=OFF is your best solution! :-) Oct 23, 2023 at 16:40
  • 1
    An aside, you can use SQL_LOG_BIN=0 to turn off binlogging for a session in MySQL; tell your developers to do that before they load huge amounts of test data.
    – j4nd3r53n
    Oct 24, 2023 at 8:57
0

In light of the helpful answer by @BillKarwin, I opted to have cron run a simple bash script at 15 minute intervals. I'm not very good with bash so there might be a more succinct way, but for now this seems to be getting the job done:

#!/bin/bash
MAX_BINLOG_FILES=50
PURGE_TO_LOG=$(mysql -e "SHOW BINARY LOGS" | tac | sed "${MAX_BINLOG_FILES}q;d" | awk '{print $1}')
if [ ! -z "$PURGE_TO_LOG" ] 
then
    mysql -e "PURGE BINARY LOGS TO '$PURGE_TO_LOG'"
fi
4
  • Keep in mind that not every binary log has the maximum size. They can start new log files when the server restarts, or when someone runs FLUSH LOGS. Some of the binlog files may be small. To make this more bulletproof, you should really sum up the sizes of the respective files, and purge only if the sum is greater than what you want. Oct 23, 2023 at 16:21
  • And I guess you're okay with the risk that your replicas are lagging behind, and you might inadvertently break replication by deleting a binlog file that is still needed. Oct 23, 2023 at 16:22
  • Regarding your first point, yes I am aware. I considered doing a filesize sum, but in this case since I'm purging back to 50 logs I suspect that will be fine since I normally only see the last couple of binlogs with smaller size. Is it ever the case that there are more than 2 or 3 under max size? In a more active production situation I agree, checking sum of sizes would be best. Oct 23, 2023 at 16:36
  • Sure, I've seen cases where a server was restarted repeatedly in a short time, and all the binlog files were small. Oct 23, 2023 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.