Using MySQL 8 Persisted System Variables

This blog discusses new features about the persisted system variables and how we can use it to make variable changes persistent. The MySQL server maintains system variables that control its operations. The dynamic variables used prior to the MySQL 8 release are not persistent and are reset upon restart. These variables can be changed at runtime using the SET statement to affect the operation of the current server instance but we have to manually update my.cnf config file to make them persistent. In many cases, updating my.cnf from the server-side is not a convenient option, and leaving the variable just updated dynamically reverts on the subsequent restart without any history.

Persisted system variables are one of the useful features introduced in MySQL 8. The new functionality helps DBAs update the variables dynamically and register them without touching the configuration files from the server-side.

How to Persist the Global System Variables?

Like SET GLOBAL, SET PERSIST is the command that can be used for updating the system variables at runtime and make them persistent across restarts. When we use the PERSIST keyword, the variable changes are updated to the mysqld-auto.cnf option file in the data directory. The mysqld-auto.cnf is a JSON format file created only upon the first execution of the PERSIST or PERSIST_ONLY statement.

Let’s see how this feature works using an example updating the max connections.

mysql> SET PERSIST max_connections = 1000;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@max_connections\G
*************************** 1. row ***************************
@@max_connections: 1000
1 row in set (0.00 sec)

The resulting mysqld-auto.cnf looks like this:

cat /var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "1000" , "Metadata" : { "Timestamp" : 1602543199335909 , "User" : "root" , "Host" : "localhost" } } } }

How to Persist the Read-Only System Variables?

When there is a need to change variables that are read-only, we need to use the PERSIST_ONLY keyword. This clause updates the change in the mysqld-auto.cnf file but does not apply in the MySQL, persisting upon the next MySQL restart. This makes PERSIST_ONLY suitable for configuring read-only system variables that can be set only at server startup.

mysql>  SET PERSIST innodb_log_file_size=50331648*2;
ERROR 1238 (HY000): Variable 'innodb_log_file_size' is a read-only variable
mysql> set persist_only innodb_log_file_size=50331648*2;
Query OK, 0 rows affected (0.01 sec)

How to Clear the Persisted System Variable Settings?

We can use the RESET PERSIST command to remove persisted settings from mysqld-auto.cnf. Be careful when running the command without a specific variable name as it will remove all the settings from the configuration file. In effect, it removes the persisted settings from mysqld-auto.cnf but not from MySQL.

See the below examples.

mysql> RESET PERSIST;
Query OK, 0 rows affected (0.00 sec)
cat /var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : {  } }

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|              1000 |
+-------------------+
1 row in set (0.01 sec)

If you may want to clear a specific variable instead of clearing all settings from the configuration file, the following example shows us how to do this. If we try to remove a variable that does not exist in the mysqld-auto.cnf it results in an error as shown below and we can use the IF EXISTS clause to suppress the error.

mysql> RESET PERSIST max_connections;
Query OK, 0 rows affected (0.00 sec)
mysql> RESET PERSIST innodb_max_dirty_pages_pct;
ERROR 3615 (HY000): Variable innodb_max_dirty_pages_pct does not exist in persisted config file
mysql>
mysql> RESET PERSIST IF EXISTS innodb_max_dirty_pages_pct;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
| Warning | 3615 | Variable innodb_max_dirty_pages_pct does not exist in persisted config file |

Is There a Way to Disable Persistence?

Yes, the persisted_globals_load parameter is used to enable or disable the persisted system variables. When disabled, the server startup sequence ignores the mysqld-auto.cnf file. Manually changing to the mysqld-auto.cnf file may result in a parse error at server startup. In this case, the server reports an error and exits. If this issue occurs, then you have to start the server with the persisted_globals_load system variable disabled or with the –no-defaults option as given in the example below.

mysql> select @@persisted_globals_load ;
+-------------------------------------+
| @@persisted_globals_load            |
+-------------------------------------+
|                 1                   |
+-------------------------------------+
1 row in set (0.00 sec)

grep -i persisted_globals_load /etc/my.cnf
persisted_globals_load=0

mysql> restart;
Query OK, 0 rows affected (0.00 sec)

mysql>  select @@persisted_globals_load ;
+-----------------------------------+
|    @@persisted_globals_load       |
+-----------------------------------+
|                 0                 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               500 |
+-------------------+
1 row in set (0.00 sec)

What Grants Are Required?

Considering security, the right permissions for the right user is definitely a best practice. The SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN are the required privileges for a user to use the SET PERSIST_ONLY to persist global system variables to the mysqld-auto.cnf.

The user also needs to have the SHUTDOWN privilege to be able to use the RESTART command. It provides a way to restart MySQL from the client session without requiring command-line access on the server host.

mysql> CREATE USER 'admin_persist'@'localhost' IDENTIFIED BY '*********';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT SYSTEM_VARIABLES_ADMIN, PERSIST_RO_VARIABLES_ADMIN, SHUTDOWN on *.* to 'admin_persist'@'localhost';
Query OK, 0 rows affected (0.03 sec)

How to Monitor the Variables

To list the variables that have been updated using the PERSIST option, we can query the performance_schema.persisted_variables table joining with a couple of other tables as shown below. This is a simple example of how to monitor the variables from the MySQL side and based on your needs, you can modify the query.

mysql> select v.VARIABLE_NAME,g.VARIABLE_VALUE current_value,p.VARIABLE_VALUE as persist_value,SET_TIME,SET_USER,VARIABLE_SOURCE,VARIABLE_PATH from performance_schema.variables_info v JOIN performance_schema.persisted_variables p USING(VARIABLE_NAME) JOIN performance_schema.global_variables g USING(VARIABLE_NAME)\G
*************************** 1. row ***************************
  VARIABLE_NAME: innodb_log_file_size
  current_value: 50331648
  persist_value: 100663296
       SET_TIME: 2020-10-12 18:54:35.725177
       SET_USER: arun
VARIABLE_SOURCE: COMPILED
  VARIABLE_PATH:

*************************** 2. row ***************************
  VARIABLE_NAME: max_connections
  current_value: 1000
  persist_value: 1000
       SET_TIME: 2020-10-12 18:53:19.336115
       SET_USER: root
VARIABLE_SOURCE: DYNAMIC
  VARIABLE_PATH:
2 rows in set (0.06 sec)

mysql> restart;
Query OK, 0 rows affected (0.01 sec)

select v.VARIABLE_NAME,g.VARIABLE_VALUE current_value,p.VARIABLE_VALUE as persist_value,SET_TIME,SET_USER,VARIABLE_SOURCE,VARIABLE_PATH from performance_schema.variables_info v JOIN performance_schema.persisted_variables p USING(VARIABLE_NAME) JOIN performance_schema.global_variables g USING(VARIABLE_NAME)\G
*************************** 1. row ***************************
  VARIABLE_NAME: innodb_log_file_size
  current_value: 100663296
  persist_value: 100663296
       SET_TIME: 2020-10-12 18:54:35.725177
       SET_USER: arun
VARIABLE_SOURCE: PERSISTED
  VARIABLE_PATH: /var/lib/mysql/mysqld-auto.cnf

*************************** 2. row ***************************
  VARIABLE_NAME: max_connections
  current_value: 1000
  persist_value: 1000
       SET_TIME: 2020-10-12 18:53:19.335909
       SET_USER: root
VARIABLE_SOURCE: PERSISTED
  VARIABLE_PATH: /var/lib/mysql/mysqld-auto.cnf
2 rows in set (0.16 sec)

Hopefully, this blog helped to explain what “Persisted System Variables” is and how it operates.

by Arunjith Aravindan via Percona Database Performance Blog