Description:
When the innodb_dedicated_server option is enabled, the values for innodb_buffer_pool_size and innodb_redo_log_capacity are automatically calculated based on the available RAM and CPU resources, respectively.
There may be instances where we decide override the value for innodb_buffer_pool_size to a custom value and then later revert this setting because we realize that it is better for innodb_dedicated_server to automatically calculate the values for innodb_buffer_pool_size.
So we need to reset the value for innodb_buffer_pool_size back to "default" using the below statement.
set global innodb_buffer_pool_size=default;
Actual Behavior:
The value for innodb_buffer_pool_size is reset back to the engine default of 128 MB
Expected Behavior:
It is expected that innodb_buffer_pool_size resets to a value calculated by innodb_dedicated_server and not the engine default of 128 MB.
How to repeat:
Consider the below example
1. We have innodb_dedicated_server on a server with the below config.
RAM - 512 GB
VCPUS - 64
So with innodb_dedicated_server enabled on MySQL 8.4
innodb_redo_log_capacity = 16 GB
innodb_buffer_pool_size = 372 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 |
+----------------------------+---------------------------+---------------------------+
| 17179869184 | 398626652160 | 1 |
+----------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)
2. Now, we set a custom value for the innodb_buffer_pool_size variable using the SET GLOBAL statement.
mysql> set global innodb_buffer_pool_size=49862665216;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: '49862665216' |
| Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: '49861885952' |
+---------+------+------------------------------------------------------------------+
2 rows in set (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 |
+----------------------------+---------------------------+---------------------------+
| 17179869184 | 49928994816 | 1 |
+----------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)
3. Now I realize that it is better for innodb_dedicated_server to automatically calculate the value for innodb_buffer_pool_size, and want to remove the override. So I set it back to default.
mysql> set global innodb_buffer_pool_size=default;
Query OK, 0 rows affected (0.00 sec)
==============================================================================================
Note: innodb_buffer_pool_instances should be set to 1 for the above step to work, otherwise you will see the below warning(which ideally should be an error)
mysql> set global innodb_buffer_pool_size=default;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------+
| Warning | 1210 | Cannot update innodb_buffer_pool_size to less than 1GB if innodb_buffer_pool_instances > 1. |
+---------+------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
==============================================================================================
4. Next we check the value for innodb_buffer_pool_size.
Actual Behavior:
When I checked the value for innodb_buffer_pool_size it is reset to 128 MB, which is the default when innodb_dedicated_server=0
mysql> select @@innodb_redo_log_capacity,@@innodb_buffer_pool_size,@@innodb_dedicated_server;
+----------------------------+---------------------------+---------------------------+
| @@innodb_redo_log_capacity | @@innodb_buffer_pool_size | @@innodb_dedicated_server |
+----------------------------+---------------------------+---------------------------+
| 17179869184 | 134217728 | 1 |
+----------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)
Expected behavior:
The value for innodb_buffer_pool_size should fall back to the value calculated by innodb_dedicated_server(372 GB) and not the engine default of 128 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 |
+----------------------------+---------------------------+---------------------------+
| 17179869184 | 49928994816 | 1 |
+----------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)
Workaround:
Restarting mysqld fixes the issue.
mysql> select @@innodb_redo_log_capacity,@@innodb_buffer_pool_size,@@innodb_dedicated_server;
+----------------------------+---------------------------+---------------------------+
| @@innodb_redo_log_capacity | @@innodb_buffer_pool_size | @@innodb_dedicated_server |
+----------------------------+---------------------------+---------------------------+
| 17179869184 | 49928994816 | 1 |
+----------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)
Additional Observatiobns:
One may argue that, since innodb_dedicated_server is static, this is expected behavior. However, the same behavior is not seen with the calculation for innodb_redo_log_capacity. We see the expected behavior where innodb_redo_log_capacity is reset back to the value calculated by innodb_dedicated_server i.e 16 GB and not the engine defaults(100 MB)
Lets illustrate this with an example.
1. Check value for innodb_redo_log_capacity
mysql> show variables like 'innodb_redo_log_capacity';
+--------------------------+-------------+
| Variable_name | Value |
+--------------------------+-------------+
| innodb_redo_log_capacity | 17179869184 |
+--------------------------+-------------+
1 row in set (0.00 sec)
2. Now override and set a custom value.
mysql> set global innodb_redo_log_capacity=16179869184;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> show variables like 'innodb_redo_log_capacity';
+--------------------------+-------------+
| Variable_name | Value |
+--------------------------+-------------+
| innodb_redo_log_capacity | 16179527680 |
+--------------------------+-------------+
1 row in set (0.00 sec)
3. Set the value back to engine default.
mysql> set global innodb_redo_log_capacity=default;
Query OK, 0 rows affected (0.05 sec)
4. We see that the value is reset back to the value calculated by innodb_dedicated_server i.e. 16 GB.
mysql> show variables like 'innodb_redo_log_capacity';
+--------------------------+-------------+
| Variable_name | Value |
+--------------------------+-------------+
| innodb_redo_log_capacity | 17179869184 |
+--------------------------+-------------+
1 row in set (0.00 sec)
Note: There is one exception to the above. If the custom value for innodb_redo_log_capacity was explicitly set in the my.cnf file at startup, the value resets back to the engine default of 100MB and not 16GB. This is a separate issue in its self, so I won't discuss it here and open a separate bug for it. I am adding it here as an FYI.
