While doing some testing (that I published later here) on the still-in-development MySQL 5.7 I wanted to do some analysis on the configuration to see if the changes in performance were due to the code changes or just to the new MySQL defaults (something that is very common in the migration from 5.5 to 5.6 due to the default transaction log size and other InnoDB parameters). This is a quick post aiming to identify the global variables changed between these two versions.
You could tell me that you could just read the release notes, but my experience (and this is not an exception, as you will see) tells me to check these changes by myself.
I do not include changes in the
performance_schema tables, as I was running these particular tests with
performance_schema = OFF. I also do not include “administrative changes”, my name for variables that do not influence the behaviour or performance of mysql, like
server_uuid which will be unique for different instances and
innodb_version, which obviously have been changed from
5.7.4-m14. Please note that some changes have also been back-ported to 5.6, so not being shown here, or were already available in previous releases of 5.7.
Variables that have changed its value
|variable name||5.6.20 value||5.7.4 value|
|variable name||5.7.4 value|
|variable name||5.6.20 value|
- Regarding potential incompatibilities, all deprecated variables but one were literally useless, and I did not find them setup usually except for
innodb_additional_mem_pool_size, which was, in my experience, always configured by mistake, as it had absolutely no effect in recent versions of InnoDB. The exception is
binlogging_impossible_mode, which had been added in 5.6.20 and probably not merged in time for this 5.7 milestone. It will probably be added in the future with equivalent functionality. An interesting feature, I would add.
eq_range_index_dive_limitchanged from 10 to 200 is a very reasonable change, made after a Facebook suggestion. This variable was added in MySQL 5.6, and while it solved the problem of getting more reliable statistics for IN expressions with multiple values, Facebook was completely right that IN clauses have commonly more than 10 items (as it is a feature that many developers/frameworks like).
have_statement_timeoutcomes from the merge or reimplementation of the Twitter Statement Timeout functionality. Nice change to see upstream.
default_authentication_pluginis not a new feature, it just has been moved from a server parameter to a full global variable that can be inspected (but not changed) at runtime. The real change here is
default_password_lifetime, which was really missing on the 5.6 release- automatic password expiration (without having to do manually
PASSWORD EXPIRE). What I find amusing is the default value: 360 (passwords expire approximately once a year). I am not saying that that is a right or wrong default, but I predict a lot of controversy/confusion over that. There is more to talk about about authentication changes, but I will not expand it here, as it does not concern configuration variables.
- By changing the
LOGICAL_CLOCK, mysql allows for more fine-grained parallel replication, much better than the limited 5.6 option (only useful in multi-tenant setups)
- Some interesting additions to InnoDB, too, like the
innodb_page_cleanersvariable, allowing multiple threads for flushing pages from the buffer pool in parallel, and which was the subject of a recent discussion about a certain benchmark. Also we have additions like some extra flexibility regarding the transaction log caching configuration and the location of temporary tables in InnoDB format, but I consider those lesser changes to go over them in detail.
log_warningshas changed and it has not been documented. But to be honest, its functionality is being deprecated for
log_error_verbosity, a newly introduced variable that makes by default all errors, warnings and notes to be logged by default. I have submitted
bug #73745(now fixed) about this.
- A new variable,
rbr_exec_mode, seems to have been added in 5.7.1, but it is not documented anywhere in the server variables section or on the release notes, only on that developer’s blog. It allows setting at session level an IDEMPOTENT mode when replicating events in row format, ignoring all conflicts found. I’ve created a
bug #73744 for this issue(now fixed).
- There has been several performance_schema changes; I will not go over each of them here. Please note that
performance_schema_max_statement_classesis not a real change, as that is calculated at startup time, it does not have a fixed value.
- Session tracking variables were added for notification of session changes when using the C connector
In summary, some interesting changes, only one default change that may alter the performance (
eq_range_index_dive_limit), and nothing that will create problems for a migration, with only two own-predicted exceptions:
Instances of the (useless for a long time, as mentioned above) variable
innodb_additional_mem_pool_size failing with:
[ERROR] unknown variable 'innodb_additional_mem_pool_size=X'
, which just should be deleted from the configuration file.
And the expiration time set by default to 1 year, that may create lots of:
ERROR 1862 (HY000): Your password has expired.
or even create some difficult-to-debug problems in older drivers, as we had experienced with this functionality in 5.6. I would like in particular your opinion about software defaults for password expiration, as I do not consider myself a security expert. As usual, you can comment here or on Twitter.
EDIT: Morgan Tocker, from Oracle, has commented via twitter that “
innodb_additional_mem_pool_size had been useless for a long time (since the plugin), and that the reason for the change now is the additional problems of parsing but ignoring options“. I am not complaining about those changes, I actually think that they should have been done long time ago to prevent those very errors, I am just putting here a solution for what I think can be frequent mistakes on migration. Incompatibility is sometimes the way to go.