MySQL Consider increasing innodb_redo_log_capacity
Overview
I started getting the following warnings in the log from MySQL:
2023-03-18T05:12:59.566053Z 0 [Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
Redo logs are stored transactions that are used to recover data in the event MySQL crashes. This does not explain why it crashed but it’s important to size it correctly to make sure it’s as performant as possible and prevent data loss.
Too small of redo log size, it will constantly have to write to disk to ensure it does not fill up. Too big and it will take a long time to start up and recover after a crash.
It turns out there was a change from 8.0.30 changes the way it handles redo logs.
Best Setting
It’s dependent on your setup and I’m not an expert so some helpful articles on what is the optimimun size can be found here and here.
You can check the current status of the redo by running the following command logged in MySQL:
SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_resize_status';
Updating Redo Size
Log into MySQL and run the following command to increase the size by 200MB:
set global innodb_redo_log_capacity=200*1024*1024;
To go up a unit, add an extra 1024 multiplier. For example 2GB:
set global innodb_redo_log_capacity=2*1024*1024*1024;
15.6.5 Redo Log
Dynamic InnoDB Redo Log in MySQL 8.0
How to calculate a good InnoDB log file size
