How to Size InnoDB Log Files for Optimal MySQL Performance

InnoDB Log File Purpose

When an InnoDB table crashes, MySQL scans the log files on restart, finds records not present in the tablespace, and performs redo operations to recover the data.

The size of the InnoDB log file is set with the innodb_log_file_size parameter.

If the value is too small, checkpoints increase, causing more disk flushes and degrading performance.

If the value is too large, the recovery process becomes slower, extending database downtime.

Therefore, choosing an appropriate log size is important.

How to Calculate an Appropriate Log Size

Idea

There is no strict definition of a “proper” size, but a common rule of thumb is to set the log size to roughly the amount of log generated in one hour.

You can measure the log size produced in one minute and extrapolate to an hour.

Calculation Method

Enable pager to filter only lines containing “sequence”. mysql> pager grep sequence; Show the current log sequence number, which represents the total number of bytes.

mysql> show engine innodb status;
Log sequence number 3836410803
1 row in set (0.00 sec)

Wait 60 seconds. mysql> select sleep(60); Show the log sequence number again.

mysql> show engine innodb status;
Log sequence number 3838334638
1 row in set (0.00 sec)

Disable the pager. mysql> nopager; Calculate the difference between the two sequence numbers and convert bytes to megabytes.

mysql> select (3838334638 - 3836410803) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 1.83471203 |
+------------+
1 row in set (0.00 sec)

This yields the amount of log generated per minute.

Other Factors Affecting Data Recovery

During recovery, besides redo, undo operations may also be required.

For example, if a transaction deletes 100,000 rows and crashes before committing, the recovery must undo the uncommitted deletes, prolonging the recovery time.

Avoid large transactions to improve recovery efficiency and reduce replication lag.