MySQL Bugs: #113214: Documentation on innodb_redo_log_capacity is either wrong or not clear.

Bug #113214	Documentation on innodb_redo_log_capacity is either wrong or not clear.
Submitted:	25 Nov 2023 0:34	Modified:	29 Mar 2024 22:14
Reporter:	Agustín G	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In the online manual for innodb_redo_log_capacity (https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_redo_log_capa...) we can read the following:

"[...] otherwise, these settings are used to compute the innodb_redo_log_capacity setting (innodb_log_files_in_group * innodb_log_file_size = innodb_redo_log_capacity)."

This means that if we have, for instance, innodb_log_file_size defined in the my.cnf file, then innodb_redo_log_capacity will be calculated using it and innodb_log_files_in_group. However, one would expect the value for innodb_redo_log_capacity to be the same when querying the database for variables (and not the default, as it currently is).

How to repeat:
Set any value for log file size in the my.cnf file, for instance:

[mysqld]
...
innodb_log_file_size = 209715200

Restart the server, and check the variables:

shell> mysql -e "select @@innodb_log_file_size, @@innodb_log_files_in_group, @@innodb_redo_log_capacity;"
+------------------------+-----------------------------+----------------------------+
| @@innodb_log_file_size | @@innodb_log_files_in_group | @@innodb_redo_log_capacity |
+------------------------+-----------------------------+----------------------------+
|              209715200 |                           2 |                  104857600 |
+------------------------+-----------------------------+----------------------------+

So, we can see that the following applies:

1) innodb_log_file_size * innodb_log_files_in_group = 209715200 * 2 = 419430400

2) innodb_redo_log_capacity = 104857600

From the documentation, we expect the following to happen:

innodb_log_file_size * innodb_log_files_in_group = innodb_redo_log_capacity

which in reality doesn't:

419430400 =/= 104857600

However, if we check the redo log files, we see that 419430400 is correctly being used, because each file is 419430400/32 = 13107200

shell> ll -r data/'#innodb_redo' | head -n3
total 400000
-rw-r-----. 1 agustin percona 13107200 Nov 25 00:21 #ib_redo4839_tmp
-rw-r-----. 1 agustin percona 13107200 Nov 25 00:21 #ib_redo4838_tmp

Suggested fix:
Either change the documentation to mention that if any of innodb_log_files_in_group or innodb_log_file_size are used, then innodb_redo_log_capacity is not set (and should be ignored) or, even better in my opinion, actually set innodb_redo_log_capacity to what it should be given the mentioned formula (which, again, is indeed correctly used).

The second fix is better because innodb_redo_log_capacity is dynamic and can change even if innodb_log_file_size or innodb_log_files_in_group are indeed used in the my.cnf file. After the server starts, we can simply issue SET innodb_redo_log_capacity, and the redo files will change accordingly. In this way, we can make sure that innodb_redo_log_capacity is the source of truth at all times, and we can avoid misunderstandings.

Thanks!

Hi Mr. Agustin,

Thank you for your bug report.

We have managed to repeat the behaviour and we agree that this is a documentation bug.

Documentation has to be updated for 8.0 and higher versions.

Verified as reported.

Hi, 
It also applies to version 8.3.0.

The problem is that when the redo log space calculation happens - the old variable is, but innodb_redo_log_capacity is not specified in the config, the redo log space is still properly sized, but the variable status confusingly shows the default 100M:

mysql > select @@version,@@innodb_redo_log_capacity/1024/1024,@@innodb_log_file_size/1024/1024,@@innodb_log_files_in_group;
+-----------+--------------------------------------+----------------------------------+-----------------------------+
| @@version | @@innodb_redo_log_capacity/1024/1024 | @@innodb_log_file_size/1024/1024 | @@innodb_log_files_in_group |
+-----------+--------------------------------------+----------------------------------+-----------------------------+
| 8.3.0     |                         100.00000000 |                     200.00000000 |                           2 |
+-----------+--------------------------------------+----------------------------------+-----------------------------+
1 row in set (0.00 sec)

$ du -hs data/#innodb_redo/
401M	data/#innodb_redo/

Regardless of how the variables are used, the innodb_redo_log_capacity variable should reflect the real size.

Hi Mr. Malkowski,

We concur with your conclusions.

Posted by developer:
 
Please confirm if the following is by design and true:

---
(1) If innodb_redo_log_capacity is set: 

(A) It is used; innodb_log_file_size and innodb_log_files_in_group are ignored. 

(2) If innodb_redo_log_capacity is not set: 

(A) and if innodb_log_file_size and/or innodb_log_files_in_group are set, then innodb_redo_log_capacity does not report the InnoDB redo log file capacity as it remains the default value. A calculator is required to get the true InnoDB redo log file capacity :)

(B) and if neither innodb_log_file_size or innodb_log_files_in_group are set, then innodb_redo_log_capacity is used.
---

If the above is true, then that means this quoted documentation is incorrect:

"[...] otherwise, these settings are used to compute the innodb_redo_log_capacity setting (innodb_log_files_in_group * innodb_log_file_size = innodb_redo_log_capacity)"

Meaning, innodb_redo_log_capacity is not dynamic (calculated), and that the documentation probably means that the InnoDB redo log file capacity (not the innodb_redo_log_capacity setting itself) equals innodb_log_files_in_group * innodb_log_file_size in that scenario. 

Once the above is confirmed then the documentation will be updated.

Thank you, Philip.

Hi Mr. Malkowski,

Please reply to the above questions, since your answers are essential in order to proceed.

Hi MySQL Verification Team,

I must admit I find it quite amusing that you ask me how it should work by design :) Maybe it would make more sense to ask the people who actually designed and those who implemented the new redo log solution?
I can only complain if the behavior is confusing or different from the documented, but I guess it's not a community member's role to tell you how things were designed in the first place?
On the other hand, I appreciate you are listening to the community voice! :)

Having said that, the following is my observation of how actually things work (in v. 8.3.0).

* None of the redo log-related settings are mentioned in the configuration file:

mysql > select round(@@innodb_redo_log_capacity/1024/1024),round(@@ /1024/1024),@@innodb_log_files_in_group;
+---------------------------------------------+-----------------------------------------+-----------------------------+
| round(@@innodb_redo_log_capacity/1024/1024) | round(@@innodb_log_file_size/1024/1024) | @@innodb_log_files_in_group |
+---------------------------------------------+-----------------------------------------+-----------------------------+
|                                         100 |                                      48 |                           2 |
+---------------------------------------------+-----------------------------------------+-----------------------------+
1 row in set (0.00 sec)

101M	data/#innodb_redo/

This means both settings took their documented defaults, but innodb_log_file_size is not applicable. In this case, I would prefer to see the returned value of innodb_log_file_size as 50M or, even better, as NULL.

* Only innodb_log_file_size is specified in config (innodb_log_file_size=200M):

mysql  > select round(@@innodb_redo_log_capacity/1024/1024),round(@@innodb_log_file_size/1024/1024),@@innodb_log_files_in_group;
+---------------------------------------------+-----------------------------------------+-----------------------------+
| round(@@innodb_redo_log_capacity/1024/1024) | round(@@innodb_log_file_size/1024/1024) | @@innodb_log_files_in_group |
+---------------------------------------------+-----------------------------------------+-----------------------------+
|                                         100 |                                     200 |                           2 |
+---------------------------------------------+-----------------------------------------+-----------------------------+
1 row in set (0.00 sec)

391M	data/#innodb_redo/

2024-03-28T21:36:25.292773Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=419430400. Please use innodb_redo_log_capacity instead.

So, a nice explainer is printed to the error log, but despite the redo log space being set to ~400M, the variable shows 100M, which is highly confusing, will break monitoring systems, etc.

* Only innodb_redo_log_capacity=2G is specified:

mysql > select round(@@innodb_redo_log_capacity/1024/1024),round(@@innodb_log_file_size/1024/1024),@@innodb_log_files_in_group;
+---------------------------------------------+-----------------------------------------+-----------------------------+
| round(@@innodb_redo_log_capacity/1024/1024) | round(@@innodb_log_file_size/1024/1024) | @@innodb_log_files_in_group |
+---------------------------------------------+-----------------------------------------+-----------------------------+
|                                        2048 |                                      48 |                           2 |
+---------------------------------------------+-----------------------------------------+-----------------------------+
1 row in set (0.00 sec)

2.0G	data/#innodb_redo/

In this case, I would prefer to show innodb_log_file_size as NULL as many older monitoring stuff will report 96M while, in fact it's not true.

* Both variables are specified in the config (innodb_redo_log_capacity=2G, innodb_log_file_size=200M):

mysql > select round(@@innodb_redo_log_capacity/1024/1024),round(@@innodb_log_file_size/1024/1024),@@innodb_log_files_in_group;
+---------------------------------------------+-----------------------------------------+-----------------------------+
| round(@@innodb_redo_log_capacity/1024/1024) | round(@@innodb_log_file_size/1024/1024) | @@innodb_log_files_in_group |
+---------------------------------------------+-----------------------------------------+-----------------------------+
|                                        2048 |                                     200 |                           2 |
+---------------------------------------------+-----------------------------------------+-----------------------------+
1 row in set (0.00 sec)

2.0G	data/#innodb_redo/

2024-03-28T21:45:03.087599Z 0 [Warning] [MY-013869] [InnoDB] Ignored deprecated configuration parameter innodb_log_file_size. Used innodb_redo_log_capacity instead.

In this case, we have the information that the old setting was ignored, yet the variable shows the value from the config file.
Again, I would rather see the innodb_log_file_size as NULL.

IMHO, the best solution would be to get rid of innodb_log_file_size and innodb_log_files_in_group variables entirely in 8.4.0 to avoid further confusion!

Posted by developer:
 
Good news! Or bad news, depending on how you look at it :)

Poking around old work logs showed that Innodb_redo_log_capacity_resized exists! It appears someone mistakenly removed it from the documentation two years ago, but:

mysql> select @@innodb_log_file_size, @@innodb_log_files_in_group, @@innodb_redo_log_capacity;
+------------------------+-----------------------------+----------------------------+
| @@innodb_log_file_size | @@innodb_log_files_in_group | @@innodb_redo_log_capacity |
+------------------------+-----------------------------+----------------------------+
|              209715200 |                           2 |                  104857600 |
+------------------------+-----------------------------+----------------------------+
1 row in set (0.00 sec)

mysql> show global status like 'Innodb_redo_log_capacity_resized';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| Innodb_redo_log_capacity_resized | 419430400 |
+----------------------------------+-----------+
1 row in set (0.00 sec)

Phew! So unless convinced otherwise I'll add this back to the documentation and update the innodb_redo_log_capacity according to our findings, and reference Innodb_redo_log_capacity_resized there.

Posted by developer:
 
Turns out it was not deleted from the main documentation long ago, please forgive my rushed take on the matter. 

In any case, we'll reference it in additional places and clarify the matter.

Thank you, Philip.

Posted by developer:
 
Thank you for the detailed response, and you've been given honorary MySQL developer status... congratulations! :) 

I'm not sure when those variables will be removed but suspect it won't be in 8.4, maybe after that, but I'm not making any future promises ;) That MY-013907 error is misleading and should reference Innodb_redo_log_capacity_resized or something like 'computed InnoDB redo log capacity' instead of innodb_redo_log_capacity=.

In any case, current behavior is now documented in the documentation source and the Innodb_redo_log_capacity_resized status variable that gives the true size is now referenced there. 

Thank you for reporting this!

Thank you, Philip.