To set correct system variable values is the essential step to get the correct server behavior against the workload. 
In MySQL, we have many System variables that can be changed at runtime, and most of them can be set at the session or global level.
To change the value of a system variable at the global level in the past, users needed to have SUPER privileges. Once the system variable value is modified as global, the server will change this behavior for the session, and obviously as global scope.
For instance, one of the most commonly adjusted variables is probably max_connections.
If you have max_connection=100 in your my.cnf or as the default value, and during the day as DBA you notice that it is not enough, it is easy just to add new connections on the fly with the command:
SET GLOBAL MAX_CONNECTIONS=500;
This will do the work.
But here is the issue. We had changed a GLOBAL value, that applies to the whole server, but this change is ephemeral and if the server restarts, the setting is lost. In the past, I have seen millions of times servers with different configurations between my.cnf and Current Server settings. To prevent this, or at least keep it under control, good DBAs had developed scripts to check if and where the differences exist and fix them. The main issue is that very often, we forget to update the configuration file while doing the changes, or we do it on purpose to do “Fine-tuning first” and forgot afterward.
What’s new in MySQL8 about that?
Well, we have a couple of small changes. First of all the privileges, as for MySQL8 users can have SYSTEM_VARIABLES_ADMIN or SUPER to modify the GLOBAL system variables. Also, the ability to have GLOBAL changes to variable to PERSIST on disk and finally, to know who did it and when.
The new option for SET command is PERSIST
So, if I have:
(root@localhost) [(none)]>show global variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1500 |
+-----------------+-------+
and I want to change the value of max_connection and be sure this value is reloaded at the restart, I will do this:
(root@localhost) [(none)]>set PERSIST max_connections=150;
(root@localhost) [(none)]>show global variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 150 |
+-----------------+-------+
With the usage of PERSIST, MySQL will write the information related to:
– key (variable name)
– value
– timestamp (including microseconds)
– user
– host
A new file in the data directory: mysqld-auto.cnf contains the information. The file is in Json format and will have the following:
{ "Version" : 1 , "mysql_server" : {
"max_connections" : {
"Value" : "150" , "Metadata" : {
"Timestamp" : 1565363808318848 , "User" : "root" , "Host" : "localhost"
} } } }
The information is also in Performance Schema:
select a.VARIABLE_NAME,b.VARIABLE_value ,SET_TIME,SET_USER,SET_HOST
from performance_schema.variables_info a
join performance_schema.global_variables b
on a.VARIABLE_NAME=b.VARIABLE_NAME
where b.VARIABLE_NAME like 'max_connections'\G
*************************** 1. row ***************************
VARIABLE_NAME: max_connections
VARIABLE_value: 150
SET_TIME: 2019-08-09 11:16:48.318989
SET_USER: root
SET_HOST: localhost
As you see, it reports who did the change, from where, when, and the value. Unfortunately, there is no history here, but this can be easily implemented.
To clear the PERSIST settings, run RESET PERSIST and all the Persistent setting will be removed.
If you have:
{ "Version" : 1 , "mysql_server" : {
"max_connections" : { "Value" : "151" , "Metadata" : { "Timestamp" : 1565367524946371 , "User" : "root" , "Host" : "localhost" } } ,
"wait_timeout" : { "Value" : "151" , "Metadata" : { "Timestamp" : 1565368154036275 , "User" : "root" , "Host" : "localhost" } }
} }
RESET PERSIST will do:
{ "Version" : 1 , "mysql_server" : { }
Which is removing ALL THE SETTINGS, not just one.
Anyhow, why is this a good thing to have?
First, because we have no excuse now when we change a variable, as we have all the tools needed to make sure we will have it up at startup if this is the intention of the change.
Second, it is good because storing the information in a file, and not only showing it from PS, allows us to include such information in any automation tool we have. This is in case we decide to take action or just to keep track of it, like comparison with my.cnf and fixing the discrepancies automatically also at service down or when cloning. On this let me say that WHILE you can change the value in the file mysqld-auto.cnf, have the server at restart use that value as the valid one.
This is not recommended, instead please fix my.cnf and remove the information related to PERSIST.
To touch that file is also dangerous because if you do stupid things like removing a double quote or in any way affecting the Json format, the server will not start, but there will be NO error in the log.
{ "Version" : 1 , "mysql_server" : { "wait_timeout": { "Value : "150" , "Metadata" : { "Timestamp" : 1565455891278414, "User" : "root" , "Host" : "localhost" } } } }
^^^ missing double quote
tusa@tusa-dev:/opt/mysql_templates/mysql-8.0.17futex$ ps aux|grep 8113
tusa 8119 0.0 0.0 14224 896 pts/1 S+ 12:54 0:00 grep --color=auto 8113
[1]+ Exit 1 bin/mysqld --defaults-file=./my.cnf
I have opened a bug for this (https://bugs.mysql.com/bug.php?id=96501).
A short deep dive in the code (you can jump it if you don’t care)
The new feature is handled in the files <source>/sql/persisted_variable.(h/cc). The new structure dealing with the PERSIST actions is:
struct st_persist_var {
std::string key;
std::string value;
ulonglong timestamp;
std::string user;
std::string host;
bool is_null;
st_persist_var();
st_persist_var(THD *thd);
st_persist_var(const std::string key, const std::string value,
const ulonglong timestamp, const std::string user,
const std::string host, const bool is_null);
};
And the main steps are in the constructors st_persist_var. It should be noted that when creating the timestamp, the code is generating a value that is NOT fully compatible with the MySQL functions FROM_UNIXTIME.
The code assigning the timestamp value pass/assign also passes the microseconds from the timeval (tv) structure:
st_persist_var::st_persist_var(THD *thd) {
timeval tv = thd->query_start_timeval_trunc(DATETIME_MAX_DECIMALS);
timestamp = tv.tv_sec * 1000000ULL + tv.tv_usec;
user = thd->security_context()->user().str;
host = thd->security_context()->host().str;
is_null = false;
}
Where:
tv.tv_sec = 1565267482
tv.tc_usec = 692276
will generate:
timestamp = 1565267482692276
This TIMESTAMP is not valid in MySQL and cannot be read from the time functions, while the segment related to tv.tv_sec = 1565267482 works perfectly.
(root@localhost) [(none)]>select FROM_UNIXTIME(1565267482);
+---------------------------+
| FROM_UNIXTIME(1565267482) |
+---------------------------+
| 2019-08-08 08:31:22 |
+---------------------------+
(root@localhost) [(none)]>select FROM_UNIXTIME(1565267482692276);
+---------------------------------+
| FROM_UNIXTIME(1565267482692276) |
+---------------------------------+
| NULL |
+---------------------------------+
This because the timestamp with microseconds is formatted differently in MySQL :
PERSIST_code = 1565267482692276
MySQL = 1565267482.692276
If I run: select FROM_UNIXTIME(1565267482.692276);
I get the right result:
+----------------------------------+
| FROM_UNIXTIME(1565267482.692276) |
+----------------------------------+
| 2019-08-08 08:31:22.692276 |
+----------------------------------+
And of course, I can use the trick:
select FROM_UNIXTIME(1565267482692276/1000000);
+-----------------------------------------+
| FROM_UNIXTIME(1565267482692276/1000000) |
+-----------------------------------------+
| 2019-08-08 08:31:22.6922 |
+-----------------------------------------+
Well, that’s all for the behind the scene info. Keep this in mind if you want to deal with the value coming from the Json file.
SET PERSIST Conclusion
Sometimes the small things can be better than the HUGE shiny things. Many times I saw DBAs in trouble because they do not have this simple feature in MySQL, and many MySQL fails to start or doesn’t behave as expected. Given that, I welcome SET PERSIST and I am sure that the people who manage thousands of servers, with different workloads and automation in place, will see this as a good thing as well.
References:
https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestamp
https://lefred.be/content/where-does-my-mysql-configuration-variable-value-come-from/
https://lefred.be/content/what-configuration-settings-did-i-change-on-my-mysql-server/
Percona announces the release of Percona Server for MySQL 5.6.45-86.1 on August 20, 2019. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.
Percona announces the release of 
While we often run MySQL on larger scale systems in Production for Test and Dev, sometimes we want to run MySQL on the tiniest cloud instances possible or just run it on our laptops. In these cases, MySQL 8 and MySQL 5.7 memory consumption is quite important.
Please join Percona CEO Peter Zaitsev as he presents “Performance Analyses and Troubleshooting Technologies for Databases” on Wednesday, August 7th, 2019 at 11:00 AM PDT (UTC-7).
In this post, we will discuss a new feature – the MySQL 8.0.17 