MySQL Bugs: #71697: Make expire_logs_days accept decimal values (more flexible retention)

Bug #71697	Make expire_logs_days accept decimal values (more flexible retention)
Submitted:	13 Feb 2014 7:25	Modified:	24 May 2017 6:36
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Utilities: Binlog Events	Severity:	S4 (Feature request)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	binlog, retention, windmill

Description:
expire_logs_days takes an integer number of days to keep/retain binlog files.

Under some circumstances on servers which write a lot of binlogs this is too coarse as a server may write 500+ GB for example in a single day, so space may be an issue.  I may not have enough space to store 2 days of binlogs safely but may have enough for 1.5, and 1 day is rather "tight".

Currently the configuration for this setting must be an integer number of days that is not flexible enough.

How to repeat:
See above.

Suggested fix:
In order to avoid overcomplicating any changes to the current setup, if you allow decimal values (e.g. 0.5, 0.1 etc) then you could follow the same procedure for determining when to expire the binlogs but you could then keep periods which are not a whole number of days and thus allow retention of say 1 hour ( 0.0417 ).

In the end most people if they wanted this functionality would simply use values like 0.1, 0.25, 0.5 etc.
and that would be fine.

These values may look a bit funny and it may be nicer to have something so you can define SET GLOBAL expire_logs_days = "1h" which is obviously more readable, or values like "1d". That would match similar behaviour to defining KB, MB and GB values in other places, but is not desperately important.

So no change in behaviour on when the process happens is needed, just the values that can be used which define this retention period is wanted.

also: http://bugs.mysql.com/bug.php?id=68236

Thanks for the report. Verified as described.

The internal representation of expire_logs_days is unsigned long, and changing this to a floating point value would allow computations to be done in the same way as before, so it is a straightforward change.

This issue is addressed in MySQL 8.0.1 by the addition of the binlog_expire_logs_days system variable.

See BUG#85828.

I do not understand why you implement this FR with a different variable and then try to deprecate this one.

All you do with that is make life for people that use these settings harder as they have to use one setting for 5.7 and earlier and a different one for 8.0 and later.  If you manager servers of both versions then this makes configuration management harder.

The suggestion I made was to use the same variable and make it backwards compatible (integer value behaviour would not change) but decimal usage would give more flexibility.  And additionally if you used a "period suffix" then you could make it clearer the intent that was needed.

The solution implemented in 8.0 is not really very user friendly.

Thank you for your feedback, Simon!

We chose to use seconds because:

- We want to standardize on using seconds for measuring time.
  (I believe expire_logs_days is the only place that uses days).
- It becomes easy to format/parse in HH:MM:SS format, using
  SEC_TO_TIME/TIME_TO_SEC, or standard library calls in most
  languages.

Notice that you can still use expire_logs_days, and even in combination with binlog_expire_logs_seconds, e.g. like:

  SET GLOBAL expire_logs_days = 2,
             binlog_expire_logs_seconds = TIME_TO_SEC('12:00:00')

to set 2.5 days expiration. You can also use:

  SET GLOBAL binlog_expire_logs_seconds = TIME_TO_SEC('2 12:00:00')

as an alternative to set 2.5 days expiration.

Expire_logs_days still exists and has not been deprecated. (There is a *proposal* to change the name in order to have more consistent identifier prefixes. That would involve a deprecation procedure with the old name existing as an alias for the new one during one or more major versions.)

I sincerely hope this feature will make it easy for you to set your binlog expiration time with sub-day precision!

Thanks for the effort you are putting into this. That is appreciated.  Moving to support a decimal/floating point expire_logs_days would have solved more situations, and if you want to move to seconds I would have supported a format such as "12h", "2d8h", "36s" etc as that's much more usable than people using seconds. Why do you allow innodb_buffer_pool_size = "800M", or "80G" ? Simply because that is a much more intuitive measure (and a quick read) than 838860800 or 85899345920. If you don't pay attention the 2 values look the same and you have to count the digits to be sure.

So the ease of use of understanding the value means a lot to those who have different servers with different configurations and need to manage this.   Had you started with the _seconds setting that that would have been fine. History is hard to explain to people but you're going to force people who manage 5.7 and 8.0 (not to talk about AWS, MariaDB etc) where have to check the version of MySQL they are using when generating the configuration settings and also when making changes to the server. That's a larger cost, which may not be for most people but bites those of us who manage multiple versions and different "forks" of MySQL.

I guess it is done now but in the future please don't only consider a single MySQL version when changing settings. Think about those who manage more and try to keep things as simple as possible where that is possible.