misc:innodb_log_file_size [Miky's Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== A more precise way to calculate InnoDB log file size for MariaDB/MySQL ======

Author: //[[michael@dmtec.com.au|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 
<code>
SELECT `variable_value` FROM information_schema.global_status WHERE `variable_name` = 'Innodb_os_log_written';
</code>

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 [[https://www.percona.com/blog/how-to-calculate-a-good-innodb-log-file-size|post]] from Baron Schwartz I originally read about it in 2008.