Table of Contents
A more precise way to calculate InnoDB log file size for MariaDB/MySQL
Author: Michael Ibanes - 17 Feb 2023
Pick the time when your server is usually the busiest and …
1 - Run the following query to retrieve the current value of 'Innodb_os_log_written'
Also take note of the time
SELECT `variable_value` FROM information_schema.global_status WHERE `variable_name` = 'Innodb_os_log_written';
Which will return a value in bytes, for example
1641146999
2 - Run that same query one hour later
Which will return a greater value in bytes than an hour earlier, for example
1766976119
3 - Calculate the number of MB written to the transaction log in one hour
(1766976119 - 1641146999) / 1024 / 1024 = 120MB
As it is a low amount in this example, you can easily double it to be on the safe side. 256M would be appropriate. For larger amounts over 512M don't just double it, simply add a little extra for good measure. From experience, it is rare to require more than 3G (except in some special cases), even with a very large 'innodb_buffer_pool_size'.
Additional Information
I have been calculating it this way for a long time and it has never failed me. For more details, refer to the post from Baron Schwartz I originally read about it in 2008.