Open qeepcologne opened 1 year ago
I have the same issue. I was about to open a ticket about this but since this one already exists, I'll just add my voice to it. In case it matters for debugging, I am using Ubuntu 22.04.3 LTS and MySQL 8.0.34.
btw why is the recommendaton 25% of the buffer pool size? https://blogs.oracle.com/mysql/post/dynamic-innodb-redo-log-in-mysql-80 says at least 1 hour during peak. If you have large buffer pool (e.g. in our case 200G, mysql tuner recommend crazy large size (50G), default is only 100M, calculated value based on the written amount of data is ~500M).
my opinion is to remove this remcommendation and replace it based on warning (there is warning in log if the value is too low:
2023-09-17T00:02:09.388631Z 0 [Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
Agreed. Lowering the redo log capacity decreased my RAM usage by a lot. And it seems we have less IO overall
We ran into this, too, after upgrading to MySQL 8.0 on Ubuntu 20.04 this week.
An undersized Redo Log Capacity is problematic and lead to performance issues. However, it’s not recommended to oversize the Redo Log either. Redo Log files consume disk space and increases the recovery time in case of a restart (innodb_fast_shutdown=1) or a sudden crash. And it also slows down shutdown when innodb_fast_shutdown=0.
https://blogs.oracle.com/mysql/post/dynamic-innodb-redo-log-in-mysql-80
This was a small DB of 347 MB (384 MB InnoDB buffer pool size), which would place the "desired" redo log capacity at just under 100 MB, which was configured, anyway.
In our case, we need to increase the InnoDB buffer pool size from 128 MB, which placed the "desired" redo log capacity at just 32 MB at first.
A few thoughts:
my opinion is to remove this remcommendation and replace it based on warning (there is warning in log if the value is too low:
2023-09-17T00:02:09.388631Z 0 [Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
Something like this makes much more sense, IMHO.
To confuse the issue even more check out what mysql does to the innodb_redo_log_capacity if you enable innodb_dedicated_server (automatically configures most of the important settings for you)
https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html
Spoiler. In our case with a server that has 128GB of memory it sets the innodb_redo_log_capacity to 56.25% of the server memory.
Even though it sounds crazy mysqltuner should probably follow Oracle/Mysql's table if innodb_dedicated_server is enabled.
Even though it sounds crazy mysqltuner should probably follow Oracle/Mysql's table if innodb_dedicated_server is enabled.
Given that innodb_dedicated_server sets all these values automatically, I would think the better approach for the case where innodb_dedicated_server is enabled, would be to simply advise that the innodb_redo_log_capacity setting be removed if it is manually defined, to allow MySQL to set its own defaults?
Same here.
MySQLTuner 2.5.3
MySQL 8.0.37-0ubuntu0.22.04.3
[!!] Ratio InnoDB redo log capacity / InnoDB Buffer pool size (2.34375%): 1.0G / 4.0G should be equal to 25%
example output: innodb_redo_log_capacity should be (=512M) if possible, so InnoDB Redo log Capacity equals 25% of buffer pool size.
config:
INNODB
innodb_redo_log_capacity = 512M
checked in mysql: mysql> show global variables like "innodb_redo_log_capacity"; +--------------------------+-----------+ | Variable_name | Value | +--------------------------+-----------+ | innodb_redo_log_capacity | 536870912 | +--------------------------+-----------+