Calculate Log file size

- Needs log file to be big enough to let InnoDB optimize its I/O, but not so big that recovery takes a long time.
Formula-1: Using sequence number of 'show engine innodb status'
mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> show engine innodb status\G
Log sequence number 8759936991603
1 row in set (0.02 sec)
mysql> select sleep(60);
1 row in set (1 min 0.01 sec)
mysql> show engine innodb status\G
Log sequence number 8759947542478
1 row in set (0.01 sec)
mysql> exit
Bye
=> Notice the log sequence number. That’s the total number of bytes written to the transaction log. So, now you can see how many MB have been written to the log in one minute.
=> As a rough rule of thumb, you can make the log big enough that it can hold at most an hour or so of logs. That’s generally plenty of data for InnoDB to work with; an hour’s worth is more than enough so that it can reorder the writes to use sequential I/O during the flushing and checkpointing process.
mysql> select (8759947542478-8759936991603)/1024/1024 "MB per minute";
+---------------+
| MB per minute |
+---------------+
| 10.06209850 |
+---------------+
1 row in set (0.00 sec)
Thus, approx 600 MB of data been written in 1 hour. Since there are two log files by default, divide that in half, and now you can set.
innodb_log_file_size=384M
Formula-2: Using global status 'Innodb_os_log_written'
mysql> SHOW GLOBAL STATUS like 'Innodb_os_log_written';
+-----------------------+---------------+
| Variable_name | Value |
+-----------------------+---------------+
| Innodb_os_log_written | 2869002693632 |
+-----------------------+---------------+
1 row in set (0.00 sec)
mysql> select sleep(60);
+-----------+
| sleep(60) |
+-----------+
| 0 |
+-----------+
1 row in set (59.99 sec)
mysql> SHOW GLOBAL STATUS like 'Innodb_os_log_written';
+-----------------------+---------------+
| Variable_name | Value |
+-----------------------+---------------+
| Innodb_os_log_written | 2869020893696 |
+-----------------------+---------------+
1 row in set (0.01 sec)
mysql> select (2869020893696-2869002693632)/1024/1024 "MB per minute";
+---------------+
| MB per minute |
+---------------+
| 17.35693359 |
+---------------+
1 row in set (0.00 sec)
Thus, approx 1041MB of data been written in 1 hour. Since there are two log files by default, divide that in half, and now you can set.
innodb_log_file_size=512M
References:
https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/