Adjusting redo log capacity to the right size

MySQL & MariaDB
1

After using the percona grafana-dashboards I understand I need to adjust my innodb_redo_log_capacity by a lot.
But I have a lot of DBs and I need to set this once for all (Dose not important why). They are all big but some write 7GB per hour and some 1.5GB per hour.
I read that the rule of thumb is innodb_redo_log_capacity should equal 1 hour of writing.
What is best to do? make it 1.5G, average (like 4GB) or 7GB?

2

Hello @Idan_Ahal and welcome to Percona Community,

You say to have “lot of DBs” where “some write 7GB per hour and some 1.5GB per hour”. The recommendation usually are on overall traffic for InnoDB and not per database. If both DB applications are writing at once, it might be 8.5GB per hour!
Example: consider the log file usage chart where we see the spikes going around 5.5G. I think it should be OK to set up the capacity to 4 / 5G.

image
image1410×526 32 KB

Note that redo log size is also related to the checkpointing process (check references). If you’re not really pressured by the checkpointing process and having few spikes higher than capacity, it should not be an issue.
TL;DR If disk capacity is non-issue, I do not see any harm if provisioning 7G worth capacity. That said, we have well written guides for the same configuration. Please go ahead and follow them.

  1. https://www.percona.com/blog/how-to-calculate-a-good-mysql-redo-log-size-in-mysql-8/
  2. The relationship between Innodb Log checkpointing and dirty Buffer pool pages

Thanks,
K

3

Thank you,
If I have a mysql instance that writes 5mb per hour and I’ll configure innodb_redo_log_capacity to 5 or 7GB, can it be a problem?

4

Performance wise mostly no. It will grab more disk space but that’s it.
Thanks,
K