Description:
SET PERSIST innodb_buffer_pool_size doesn't store new value into mysqld-auto.cnf.
This affects only innodb_buffer_pool_size, other variables are store new-value correctly.
How to repeat:
SET PERSIST changes innodb_buffer_pool_size correctly,
mysql80> SELECT variable_name, variable_source, variable_path, variable_value FROM performance_schema.variables_info JOIN performance_schema.global_variables USING(variable_name) WHERE variable_name = 'innodb_buffer_pool_size';
+-------------------------+-----------------+---------------+----------------+
| variable_name | variable_source | variable_path | variable_value |
+-------------------------+-----------------+---------------+----------------+
| innodb_buffer_pool_size | COMPILED | | 134217728 |
+-------------------------+-----------------+---------------+----------------+
1 row in set (0.00 sec)
mysql80> SET PERSIST innodb_buffer_pool_size= 256 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)
mysql80> SELECT variable_name, variable_source, variable_path, variable_value FROM performance_schema.variables_info JOIN performance_schema.global_variables USING(variable_name) WHERE variable_name = 'innodb_buffer_pool_size';
+-------------------------+-----------------+---------------+----------------+
| variable_name | variable_source | variable_path | variable_value |
+-------------------------+-----------------+---------------+----------------+
| innodb_buffer_pool_size | DYNAMIC | | 268435456 |
+-------------------------+-----------------+---------------+----------------+
1 row in set (0.01 sec)
But stored value in mysqld-auto.cnf is
mysql80> \! cat /usr/mysql/8.0.0/data/mysqld-auto.cnf
{ "mysql_server": {"innodb_buffer_pool_size": "134217728" } }mysql80>
Previous value of innodb_buffer_pool_size appears repeatedly.
mysql80> SET PERSIST innodb_buffer_pool_size= 512 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)
mysql80> \! cat /usr/mysql/8.0.0/data/mysqld-auto.cnf
{ "mysql_server": {"innodb_buffer_pool_size": "268435456" } }mysql80>
mysql80> SET PERSIST innodb_buffer_pool_size= 1024 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)
mysql80> \! cat /usr/mysql/8.0.0/data/mysqld-auto.cnf
{ "mysql_server": {"innodb_buffer_pool_size": "536870912" } }mysql80>
mysql80> SET PERSIST innodb_buffer_pool_size= 256 * 1024 * 1024;
Query OK, 0 rows affected (0.01 sec)
mysql80> \! cat /usr/mysql/8.0.0/data/mysqld-auto.cnf
{ "mysql_server": {"innodb_buffer_pool_size": "1073741824" } }mysql80>
Suggested fix:
Maybe online resizing of InnoDB buffer pool is asynchronous.
Does SET PERSIST store value of innodb_buffer_pool_size when async-resizing start?
Description: SET PERSIST innodb_buffer_pool_size doesn't store new value into mysqld-auto.cnf. This affects only innodb_buffer_pool_size, other variables are store new-value correctly. How to repeat: SET PERSIST changes innodb_buffer_pool_size correctly, mysql80> SELECT variable_name, variable_source, variable_path, variable_value FROM performance_schema.variables_info JOIN performance_schema.global_variables USING(variable_name) WHERE variable_name = 'innodb_buffer_pool_size'; +-------------------------+-----------------+---------------+----------------+ | variable_name | variable_source | variable_path | variable_value | +-------------------------+-----------------+---------------+----------------+ | innodb_buffer_pool_size | COMPILED | | 134217728 | +-------------------------+-----------------+---------------+----------------+ 1 row in set (0.00 sec) mysql80> SET PERSIST innodb_buffer_pool_size= 256 * 1024 * 1024; Query OK, 0 rows affected (0.00 sec) mysql80> SELECT variable_name, variable_source, variable_path, variable_value FROM performance_schema.variables_info JOIN performance_schema.global_variables USING(variable_name) WHERE variable_name = 'innodb_buffer_pool_size'; +-------------------------+-----------------+---------------+----------------+ | variable_name | variable_source | variable_path | variable_value | +-------------------------+-----------------+---------------+----------------+ | innodb_buffer_pool_size | DYNAMIC | | 268435456 | +-------------------------+-----------------+---------------+----------------+ 1 row in set (0.01 sec) But stored value in mysqld-auto.cnf is mysql80> \! cat /usr/mysql/8.0.0/data/mysqld-auto.cnf { "mysql_server": {"innodb_buffer_pool_size": "134217728" } }mysql80> Previous value of innodb_buffer_pool_size appears repeatedly. mysql80> SET PERSIST innodb_buffer_pool_size= 512 * 1024 * 1024; Query OK, 0 rows affected (0.00 sec) mysql80> \! cat /usr/mysql/8.0.0/data/mysqld-auto.cnf { "mysql_server": {"innodb_buffer_pool_size": "268435456" } }mysql80> mysql80> SET PERSIST innodb_buffer_pool_size= 1024 * 1024 * 1024; Query OK, 0 rows affected (0.00 sec) mysql80> \! cat /usr/mysql/8.0.0/data/mysqld-auto.cnf { "mysql_server": {"innodb_buffer_pool_size": "536870912" } }mysql80> mysql80> SET PERSIST innodb_buffer_pool_size= 256 * 1024 * 1024; Query OK, 0 rows affected (0.01 sec) mysql80> \! cat /usr/mysql/8.0.0/data/mysqld-auto.cnf { "mysql_server": {"innodb_buffer_pool_size": "1073741824" } }mysql80> Suggested fix: Maybe online resizing of InnoDB buffer pool is asynchronous. Does SET PERSIST store value of innodb_buffer_pool_size when async-resizing start?