MySQL 5.5 and MySQL 5.6 default variable values differences

Comments (13)

• 

  James Day Reply

  Peter,

  In most case when we made something smaller it was because of long experience backed up by testing that showed smaller was a better choice for OLTP. People should be cautious about increasing the sizes of things that we have made smaller. They could easily hurt performance instead of helping. The reductions are probably also helpful to 5.5 installations. Users of 5.5 and 5.6 should really take a look at https://blogs.oracle.com/supportingmysql/entry/server_defaults_changes_in_mysql , in documentation now at http://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html , see where we adjusted settings and try the settings for any OLTP workloads they have.

  back_log is calculated based on max_connections. You’re right that higher values are desirable at high connection rates. max_connections was the best proxy we had for expected high connection rates when dynamically configuring it at startup.

  join_buffer_size is bigger to help queries that don’t use indexes and that do use some of the new optimizer features.

  query_cache_size RAM is allocated even if query_cache_type is 0. So useful to have some small value but efficiency on smaller installations (the most common case) makes it desirable not to be too big. In practice we tend to see query cache sizes that are far too big more than ones that are too small. Agree that bigger – up to a few tens of megabytes, maybe more rarely larger, is likely to be useful if it’s helpful for a workload to have qc on. QC off tends to be best for well tuned OLTP and web sites using queries that mostly return a single or few rows after an inexpensive bit of lookup work. It’s more useful for those that have a fair proportion of more costly queries and/or low query rates.

  sort_buffer_size is much improved over the past 2M but still a bit too big for the maximum possible speed for OLTP that tends to involve lots of small sorts We considered going smaller but went with 256 as a nice compromise because it’s an efficient size to allocate on Linux. People with OLTP workloads should try 16k (no smaller) and 32k and look at SHOW GLOBAL STATUS Sort_merge_passes as an initial tool to see whether there’s much chance of benefit from a bigger size. A few passes per second or tens of passes per second at high query rates is likely to be optimal until full benchmarking to determine perfect size can be done. It’s vital not to set this too big, there can be very sever adverse performance effects if it is – maybe 30% drop in throughput using 2M instead of 32k on some workloads. Any queries that benefit from a larger size should have that done in their connection settings instead of making other queries slower by increasing the global value.

  innodb_purge_batch_size is a performance enhancement, Should also be used in 5.5 to reduce purge overhead.

  innodb_open_files is now dynamic at startup, based on the table_open_cache setting because we expect most tables to be InnoDB and use innodb_file_per_table.

  sql_mode also uses STRICT_TRANS_TABLES by default. You were probably caught out because we don’t do it in the server defaults but in the default my.cnf file that we ship. It’s also done by the Windows installer and some of our connectors by default. This is because the setting can cause data loss for applications that rely on the old behavior. You can expect that we will make it the server default in 5.7 – one more release to give people time to get applications upgraded or using connection settings if that’s not possible. More on this at https://blogs.oracle.com/supportingmysql/entry/mysql_server_5_6_default .

  thread_cache_size is quite low, and dynamic based on max_connections, just to be cautious about an increase from 0 that means many more people will be using it.

  We didn’t turn on some of the new 5.6 features because of caution or backwards compatibility. Say the new innodb checksums in hardware, that 5.5 doesn’t understand, or the ability to write less information in the binary log when using row-based logging. Both of those are good and I recommend them after reading about how they work.

  I’m currently preparing our recommendations for a baseline setup for OLTP at higher workloads. It’ll be very much like the ones Dimitri (and our QA) use, accompanied by more tips about how and when to change the values. This will eventually end up in our documentation and be tweaked based on experience with 5.6 in production.

  We’re trying to be much more explicit in telling people what settings they should use and helping them to get those settings in place with as little work and study as possible. That still won’t replace an expert but it should improve the general quality of configuration overall.

  Views are my own. For an official Oracle view, consult a PR person.

  James Day, MySQL Senior Principal Support Engineer, Oracle

  February 21, 2013 at 3:40 pm
• 

  marc castrovinci Reply

  Nice to see that the default innodb log file has finally been increased from the dismal 5MB.

  February 21, 2013 at 4:46 pm
• 

  Peter Zaitsev Reply

  James,

  Thank you for detailed explanations and comments !

  February 22, 2013 at 5:05 pm
• 

  ruc Reply

  The defaults max_connect_errors=10, I use the bellow script at another machine to connect the server
  for i in seq 1000 ; do mysql -hxxx -uxxx -pzxx; done (then wrong password)
  why it does not block user?

  February 23, 2013 at 12:07 pm
• 

  Peter Zaitsev Reply

  ruc,

  There seems to be confusion around that variable. It does not really block hosts for repeated invalid passwords but for aborted connections due to network errors.

  February 24, 2013 at 9:24 am
• 

  ruc Reply

  peter ，
  thank you very much

  February 24, 2013 at 9:50 pm
• 

  Gui Reply

  Peter, there’s a tiny typo in the configuration directives’ descriptions : innodb_purge_*p*atch_size => innodb_purge_*b*atch_size

  February 25, 2013 at 12:04 pm
• 

  David Reply

  Great article Peter. We’re still on 5.5 but tuned a few variables up with confidence since 5.6 sets them higher by default.

  Another minor correction:

  innodb_auto_extend_increment should be innodb_autoextend_increment (no space in autoextend)

  I made the mistake of copying the directive and using it in my.cnf which caused some trouble with a restart. 🙁

  March 31, 2013 at 5:26 am
• 

  Alfie John Reply

  Is the value above for old_passwords still the case for Percona Server 5.6.12?

  I’ve got a fresh install but I’ve got old_passwords = 1 when it first starts up. This is annoying because as far as I can see old_passwords cannot be set from the command line or a config file (i.e. it can only be changed via a SET command).

  July 30, 2013 at 11:45 pm
• 

  Alfie John Reply

  For more info, I only found out about this after doing a GRANT and got:

  “The password hash doesn’t have the expected format…”

  Now, I’m not setting –default-authentication-plugin so I assume in 5.6.12 it’s still set to mysql_native_password?

  To get the GRANT working, I’ve had to set old_passwords=0.

  July 30, 2013 at 11:48 pm
• 

  Alfie John Reply

  After more reading, the reason I’m getting an error is because it seems like “default-authentication-plugin=sha256_password” by default in Percona 5.6.12.

  From the MySQL docs:

  “If old_passwords has a value other than 2, an error occurs for attempts to set the password for an account that requires a SHA-256 password: ”

  On startup, old_passwords = 1 which explains this message. However I’ve explicitly set default-authentication-plugin to mysql_native_password within the filename and on the command line at yet am still getting the error.

  The problem is, you can’t do a “SHOW @@default-authentication-plugin” as it’s not in scope.

  July 31, 2013 at 12:09 am
• 

  Alfie John Reply

  Arhh… got it!

  The @@old_password session variable is dependant on the currently logged in user’s password type.

  If I login with a pre-4.1.1 password, “SELECT @@old_password” = 1.

  If I login with a mysql_native_password, “SELECT @@old_password” = 0.

  August 1, 2013 at 12:35 am
• 

  Mousam Mondal Reply

  i install WAMP server 2.4, but in phpmyadmin->Database, i unable to see any option to create database, rather it shows the comment “No Privileges”.
  What is the default id & password for wamp server 2.4

  September 24, 2013 at 2:52 pm