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 🙂
How about the innodb_spin_wait_delay? I recommend it with smaller value of 30, even 0. In this way, ut_delay would not on the top of PerfTop.
Is there any reason not to use innodb_file_per_table=1?
Hickey,
innodb_spin_wait_delay (or number of rounds) is very workload specific, sometimes it needs to be set higher and sometimes lower depending on workload. Higher numbers do indeed cause waste of CPU but often end up giving better performance in the end.
Alfie,
innodb_file_per_table=0 may make sense if you have very large number of innodb tables (tens of thousands) as in this case overhead of innodb_file_per_table may become large and the recovery speed can be slow. Though not even every “large number of tables case” is best with disabled. There are some niche special cases when it is needed too.
hickey.liu, we haven’t changed innodb_spin_wait_delay; it remains at 6. Try 6, 24 and 256 and perhaps 1 and see which is best for your specific server, then refine from there. Systems with many CPUs, say 40 in four sockets or 80 in 8 using CPUs like the Xeon E7-8850 up, are more likely to benefit from higher values than single CPUs with a few cores.
I’ll probably comment more on that and the other items later. I don’t want to possibly discourage fruitful discussion about what’s best for a broad range of servers from embedded to shared hosting to dedicated by replying too much now. Peter’s thoughts make a lot of sense for what I presume to be Percona’s target audience.
James Day, MySQL Senior Principal Support Engineer, Oracle.
innodb_file_per_table=1 makes TRUNCATE TABLE and DROP TABLE much slower, as in they could stall a server for 1 second. The DROP TABLE stall is fixed in 5.6. The TRUNCATE TABLE STALL remains. Hopefully TRUNCATE will get fixed and then using innodb_file_per_table=1 will be all good news.
Peter, one more big change from “defaults” (not configuration, but traditional setup) is that “mysql” database is now contains a mix of MyISAM and InnoDB tables. InnoDB tables are: innodb_index_stats.ibd innodb_table_stats.ibd slave_master_info.ibd slave_relay_log_info.ibd slave_worker_info.ibd
I expect it will cause a lot of confusions.
I noticed this posted on Baron’s twitter feed last week -> https://blogs.oracle.com/supportingmysql/entry/server_defaults_changes_in_mysql
I’ve found that innodb_buffer_pool_instances is also an easy win worth a change in default. My large mysql installations have been using 4 for a while after some initial testing with Percona 5.5 some time ago. As I plan the upgrade to 5.6 I’ll be looking to repeat this testing with the new default.
Updated defaults are overdue, especially for larger mysql installations. Glad they’re coming through!
Tim, for Oracle MySQL Server we’ve gone with 8 as the default. That tends to work well, though the optimal value does vary, even for the same workload on different hardware. You’ll also need to look out for tuning differences between different flavours of the server. Sometimes the optimal tuning can be different due to the differences between ways that things are implemented.
I’m effectively the product manager for the defaults changes, an initiative coming from Oracle’s Support team, so good to read that you’re happy to see them. 🙂
As Peter has noted there are some things that we might have done differently if our defaults target was only bigger boxes, but the defaults need to have a bias towards the much more common ones that aren’t professionally managed, so there’s some inherent tension there between what I want in default and what I want when tuning a busy server. We try to find flexible defaults that will work well across a good range, though. I and others will be looking to cover the differences in some associated documentation… more on that later, in another blog post.
James Day, MySQL Senior Principal Support Engineer, Oracle.
RE: sort buffer… I have also noticed in 5.6 a innodb specific property.
mysql> show global variables like ‘%sort_buffer%’;
+————————-+———+
| Variable_name | Value |
+————————-+———+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 262144 |
| sort_buffer_size | 262144 |
+————————-+———+