Bug #82905 SET PERSIST stores previous value of innodb_buffer_pool_size to mysqld-auto.cnf
Submitted: 8 Sep 2016 10:55 Modified: 16 Nov 2016 19:33
Reporter: tsubasa tanaka (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:8.0.0 OS:CentOS (6.6)
Assigned to: CPU Architecture:Any

[8 Sep 2016 10:55] tsubasa tanaka
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?
[8 Sep 2016 10:56] tsubasa tanaka
Please change hidden status, if you mind publishing 8.0's bug now.
[8 Sep 2016 11:26] Umesh Shastry
Hello Tanaka-San,

Thank you for the report.

Thanks,
Umesh
[21 Sep 2016 9:21] Umesh Shastry
Bug #83072 marked as duplicate of this one
[16 Nov 2016 19:33] Paul Dubois
Posted by developer:
 
Noted in 8.0.1 changelog.

SET PERSIST innodb_buffer_pool_size = value stored the original value
of innodb_buffer_pool_size to mysqld-auto.cnf, not the new value.