Review of MySQL 5.6 Defaults ChangesPeter Zaitsev
James Day just posted the great summary of defaults changes in MySQL 5.6 compared to MySQL 5.5
In general there are a lot of good changes and many defaults are now computed instead of hardcoded. Though some of changes are rather puzzling for me. Lets go over them:
back_log = 50 + ( max_connections / 5 ) capped at 900 – The large backlog is needed when there is a high rate (spikes) of connections which is rather unrelated to number of connections. You can get 1000s of connections a second even from as little as 100 connections, hence max_connections would be small. I think this is the case where somewhat higher fixed default (even 900) would be better than trying to be overly smart.
host_cache_size = 128 + 1 for each of the first 500 max_connections + 1 for every 20 max_connections over 500, capped at 2000 Same thing. What number of max connections has to do with number of hosts which can potentially connect to the server ? We also speak about just pennies in terms of memory usage so why do not just default at 2000 ?
innodb_file_per_table =1 Great! We found it is better choice in 95%+ of installations
innodb_log_file_size = 48M This is still too small for any real load but a lot better than 5MB. Good to see innodb log re-sizing is made automatic now.
innodb_old_blocks_time = 1000 Great. This is exactly the value I’ve been recommending for a while.
innodb_stats_on_metadata = off Yet another change I actively recommended
max_connect_errors = 100 10 or 100 does not make much difference if you get some sporadic network errors both can give problems. I would recommend adding support for value 0 which would mean limit disabled. Very few users actually need DOS prevention this variable strives to provide as MySQL is typically protected by Firewall to begin with.
open_files_limit = 5000 strangely enough this is where the smart selection of variable default seems to be removed, while it could be needed as it is easy enough to set max_connections to over 5000 or use more than 5000 tables. There is also little “savings” in keeping this value low – 64K or even more would be good default.
query_cache_size = 1M Query cache remains off by default but default size is raised to 1M which is probably well too small to be useful but leaves impression it does not need to be configured.
sort_buffer_size = 256K Good choice for very small sorts but more complicated queries will suffer causing more frequent file based sorts. This means it makes it even more important to set this variable to higher values for sessions executing complicated queries. I think this piece of code needs general re-factoring to be able to deal with simple and complicated queries efficiently automatically.
join_buffer_size = 256k The default is now double of what it was previously. I wonder why if there are any specific benchmarks which point to this as an optimal value or is it just looking to reduce number of different buffer sizes used.
sync_master_info = 10000 Quite useless change in my opinion. Syncing master info every 10000 events is no better than not syncing it at all as replication will be badly out of sync if slave is to crash. The recommendation to use master_info_repository=table is a good one though default could just be left alone. Same applies to sync_relay_log and sync_relay_log_info settings.
James also promises updated sample my.cnf and getting rid of old my-huge.cnf which are great. Perhaps my jokes about my-huge my-large etc referring to amounts of memory we have in the phones these days, I made in my Optimizing MySQL Configuration Webinar were heard 🙂