Bug #115759 Issue resetting innodb_redo_log_capacity with innodb_dedicated_server
Submitted: 3 Aug 5:55 Modified: 16 Aug 21:53
Reporter: Mershad Irani Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Jon Stephens CPU Architecture:Any

[3 Aug 5:55] Mershad Irani
Description:
When innodb_redo_log_capacity is explicitly set in the my.cnf file with innodb_dedicated_server=1, resetting it to "default" using "set global" sets it to the engine default of 100 MB. However, if the values was not set in the my.cnf file and instead set dynamically using "set global", resetting the value sets it to the value calculated by innodb_dedicated_server(nVPCU/2 GB)

Expected Behavior: If innodb_dedicated_server=1, on resetting the parameter innodb_redo_log_capacity, the value should be reset to the value calculated by innodb_dedicated_server, irrespective of weather or not the the value is present in the my.cnf file. 

This issue is a variant of https://bugs.mysql.com/bug.php?id=115757, but not the same issue as that of innodb_buffer_pool_size. In case of innodb_buffer_pool_size the value resets to the engine default irrespective of weather or not the value is set in the my.cnf file. 

How to repeat:
1. We have innodb_dedicated_server enabled on a server with the below config. 

RAM - 32 GB  
VCPUS - 8

So with innodb_dedicated_server enabled on MySQL 8.4

innodb_redo_log_capacity = 4 GB
innodb_buffer_pool_size = 23.25 GB

mysql> select @@innodb_redo_log_capacity,@@innodb_buffer_pool_size,@@innodb_dedicated_server;
+----------------------------+---------------------------+---------------------------+
| @@innodb_redo_log_capacity | @@innodb_buffer_pool_size | @@innodb_dedicated_server |
+----------------------------+---------------------------+---------------------------+
|                 4294967296 |               24964497408 |                         1 |
+----------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)

2. Now, override the value for innodb_redo_log_capacity in the my.cnf file and set it to 2294284288  and restart mysqld.  

3. On restart we see the value for innodb_redo_log_capacity is overridden. 

mysql> select @@innodb_redo_log_capacity,@@innodb_buffer_pool_size,@@innodb_dedicated_server;
+----------------------------+---------------------------+---------------------------+
| @@innodb_redo_log_capacity | @@innodb_buffer_pool_size | @@innodb_dedicated_server |
+----------------------------+---------------------------+---------------------------+
|                 2294284288 |               24964497408 |                         1 |
+----------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)

4. Now set it to the engine default. 

mysql> set global innodb_redo_log_capacity=default;
Query OK, 0 rows affected (0.00 sec)

5. We see that innodb_redo_log_capacity reset to the engine default of 100 MB. 

mysql> select @@innodb_redo_log_capacity,@@innodb_buffer_pool_size,@@innodb_dedicated_server;
+----------------------------+---------------------------+---------------------------+
| @@innodb_redo_log_capacity | @@innodb_buffer_pool_size | @@innodb_dedicated_server |
+----------------------------+---------------------------+---------------------------+
|                  104857600 |               24964497408 |                         1 |
+----------------------------+---------------------------+---------------------------+
1 row in set (0.01 sec)

Workaround: While a restart of mysqld fixes the problem, it is expected that this be done dynamically so that customers don't have to take a down time for this activity. 

Now repeat the same steps in a separate setup, but instead of setting innodb_redo_log_capacity in the my.cnf file, set it dynamically using "set global" without a restart. 

1. Set innodb_redo_log_capacity explicitly using "set global" 

mysql> set global innodb_redo_log_capacity=2294284288;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@innodb_redo_log_capacity,@@innodb_buffer_pool_size,@@innodb_dedicated_server;
+----------------------------+---------------------------+---------------------------+
| @@innodb_redo_log_capacity | @@innodb_buffer_pool_size | @@innodb_dedicated_server |
+----------------------------+---------------------------+---------------------------+
|                 2294284288 |               24964497408 |                         1 |
+----------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)

2. Now reset the parameter. 

mysql> set global innodb_redo_log_capacity=default;
Query OK, 0 rows affected (0.01 sec)

3. We see that the value is reset back to the value calculated by innodb_dedicated_server i.e. 4 GB , as opposed to the previous behavior(when set in my.cnf), where it was reset to 100 MB instead. 

mysql> select @@innodb_redo_log_capacity,@@innodb_buffer_pool_size,@@innodb_dedicated_server;
+----------------------------+---------------------------+---------------------------+
| @@innodb_redo_log_capacity | @@innodb_buffer_pool_size | @@innodb_dedicated_server |
+----------------------------+---------------------------+---------------------------+
|                 4294967296 |               24964497408 |                         1 |
+----------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)

So the expected behavior is that innodb_redo_log_capacity should be set to 4 GB(calculated based on innodb_dedicated_server).
[5 Aug 9:42] MySQL Verification Team
Hi Mr. Irani,

Thank you for your bug report.

However, this is not a bug.

There are no two defaults for any of MySQL settings.

Simply, dedicated server does not have it's own defaults. It just sets parameters for the environment running only MySQL server.

Hence, there are only built-in defaults and not other.

Not a bug.
[5 Aug 16:16] Mershad Irani
The statement 'There are no two defaults for any of MySQL settings' may not be entirely accurate. Based on the reproduction steps provided, it appears that resetting innodb_redo_log_capacity to default has two different default values depending on the configuration

So to rephrase the bug. 

"With innodb_dedicated_server=1, when innodb_redo_log_capacity is set using the 'SET GLOBAL' statement, resetting the parameter (SET GLOBAL innodb_redo_log_capacity=DEFAULT) sets it back to the value calculated by innodb_dedicated_server (nVCPU/2 GB). 

However, with innodb_dedicated_server=1, when innodb_redo_log_capacity is set in the my.cnf file, resetting the parameter (SET GLOBAL innodb_redo_log_capacity=DEFAULT) sets it back to the engine default (128 MB), which is inconsistent behavior."

I did some further digging on this.  The behavior is only seen on 8.4.1  and not on 8.0 

The below commit in 8.4, introduces a new function named "innodb_redo_log_capacity_update_default", which sets the default for innodb_redo_log_capacity based on innodb_dedicated_server(nVCPU/2 GB) . 

https://code.amazon.com/packages/RDSMySQL/commits/9943d0013e897cc6750de465a2469179c08a46be...

Expected behavior is that both configuration methods of the variable innodb_redo_log_capacity must honor the innodb_redo_log_capacity_update_default function logic.
[5 Aug 16:24] Mershad Irani
Please use this link for the commit and disregard the above link. https://github.com/mysql/mysql-server/commit/9943d0013e897cc6750de465a2469179c08a46be
[6 Aug 9:52] MySQL Verification Team
Hi Mr. Irani,

What you are writing about is fully described in our Reference Manual for 8.4:

https://dev.mysql.com/doc/refman/8.4/en/innodb-dedicated-server.html

Not a bug.
[12 Aug 23:20] Jervin Real
The documentation states the following. 

"""
Automatically configured settings are evaluated and reconfigured if necessary each time the MySQL server is started.
"""

At a minimum, I would consider this as feature request.

However, the described behavior here is dangerous as it contradicts the dynamic nature of both innodb_redo_log_capacity and innodb_buffer_pool_size. This should be a bug (including documentation) where a change in the two variables to their default values are both "necessary" changes to trigger innodb_dedicated_server recalculation.
[13 Aug 9:39] MySQL Verification Team
Hi Mr. Real,

We have informed our Documentation team to clarify this matter in our Reference Manual.
[14 Aug 13:32] Jon Stephens
The issue here appears to be that the automatic setting is (re)calculated and applied *only* on server start.

Verified as a documentation issue, assigning to myself for handling.
[14 Aug 15:01] MySQL Verification Team
Thank you, Jon.
[16 Aug 21:53] Jon Stephens
Fixed in all versions of the Manual 8.0 and up, in mysqldoc rev 79343 and mysql-relnotes rev 28883.

Clarified that (1) the automatic settings take effect only when the server is started and not at any other time; (2) setting any of the affected variables to DEFAULT means the documented default value and not any value calculated; (3) setting any of the variables affected by this option at any time overrides any values that would otherwise be determined by this option. 

In addition, we now document the --innodb-dedicated-server startup option rather than the corresponding system variable since the system variable is read-only and can be changed only by restarting the server with a different value for the option.

Note also that the Manual covers the standalone MySQL Server and cannot be expected to reflect variations in behaviour in other settings e.g. AWS for which we are not responsible.

Closed.
[19 Aug 9:55] MySQL Verification Team
Thank you, Jon.