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] MySQL Verification Team 
Hello Tanaka-San,

Thank you for the report.

Thanks,
Umesh
[21 Sep 2016 9:21] MySQL Verification Team 
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.053 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.