[RFC] Update innodb_log_file_size/innodb_redo_log_capacity recommendation

[ravage84]

The recommendation for adjusting innodb_log_file_size (innodb_redo_log_capacity nowadays) was introduced in bbc04d8#diff-126d6c210bc4ad821cc428b8034b177003fc7171ac178088fdf6ce91cb787867R4674.

The reasoning in the related PR #258 (comment) for this recommendation goes back to a recommendation from phpMyAdmin linking to a blog post from 2007:

Especially on a system with a lot of writes to InnoDB tables you should set innodb_log_file_size to 25% of innodb_buffer_pool_size. However the bigger this value, the longer the recovery time will be when database crashes, so this value should not be set much higher than 256 MiB. Please note however that you cannot simply change the value of this variable. You need to shutdown the server, remove the InnoDB log files, set the new value in my.cnf, start the server, then check the error logs if everything went fine. See also this blog entry

[Formatting & emphasis by myself]

Percona, a specialised provider for MySQL performance monitoring & optimisation, has an article (series) on the matter which disagrees with the percentage part of that rule.
Their take on optimising the setting is based on usage observation over time (one hour), not a general rule of thumb like the one from phpMyAdmin.

Also, since day the percentage rule of thumb was written initially, the typical database size has increased enormously. Percentage rules often don't scale well with exponential growth.

The documentation of MySQL does not state any recommendation (or I haven't found any):

https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_redo_log_capacity
https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-logging.html
https://dev.mysql.com/doc/refman/8.4/en/innodb-redo-log.html

For dedicated MySQL servers, there is an automatic value calculation, though:

Redo log capacity is configured according to the amount of logical processors available on the server. The formula is (number of available logical processors / 2) GB, with a maximum dynamic default value of 16 GB.

https://dev.mysql.com/doc/refman/8.4/en/innodb-dedicated-server.html

Because of all that, I think the recommendation for innodb_redo_log_capacity needs to be updated.

Also, it could simply be removed (for now) if no other good, applicable rule can be found.

---

Side note: If the recommendation was removed in total, quite a few related, open issues could be closed.

https://github.com/major/MySQLTuner-perl/issues?q=is%3Aissue+is%3Aopen+innodb_log_file_size
https://github.com/major/MySQLTuner-perl/issues?q=is%3Aissue+is%3Aopen+innodb_redo_log_capacity

[gordan-bobic]

I would like to second the motion for removing this recommendation entirely. IMNSHO it is impossible to make a meaningful recommendation on this setting based on a spot check. Assessing a reasonable value for this requires continuous monitoring over a prolonged period of time (days/weeks) and considering high resolution data over that entire period.

[ravage84]

@gordan-bobic agreed.

Here we: #827