MySQL Server 5.6 defaults changes
- by user12626240
We're improving the MySQL Server defaults, as announced by Tomas Ulin at MySQL Connect. Here's what we're changing:
Setting
Old
New
Notes
back_log
50
50 + ( max_connections / 5 ) capped at 900
binlog_checksum
off
CRC32
New variable in 5.6
binlog_row_event_max_size
1k
8k
flush_time
1800
Windows changes from 1800 to 0
Was already 0 on other platforms
host_cache_size
128
128 + 1 for each of the first 500 max_connections + 1 for every 20 max_connections over 500, capped at 2000
New variable in 5.6
innodb_autoextend_increment
8
64
Now affects *.ibd files. 64 is 64 megabytes
innodb_buffer_pool_instances
0
8. On 32 bit Windows only, if innodb_buffer_pool_size is greater than
1300M, default is innodb_buffer_pool_size
/ 128M
innodb_concurrency_tickets
500
5000
innodb_file_per_table
off
on
innodb_log_file_size
5M
48M
InnoDB will always change size to match my.cnf value. Also see innodb_log_compressed_pages and binlog_row_image
innodb_old_blocks_time
0
1000
1 second
innodb_open_files
300
300; if innodb_file_per_table is ON, higher of table_open_cache or 300
innodb_purge_batch_size
20
300
innodb_purge_threads
0
1
innodb_stats_on_metadata
on
off
join_buffer_size
128k
256k
max_allowed_packet
1M
4M
max_connect_errors
10
100
open_files_limit
0
5000
See note 1
query_cache_size
0
1M
query_cache_type
on/1
off/0
sort_buffer_size
2M
256k
sql_mode
none
NO_ENGINE_SUBSTITUTION
See later post about default my.cnf for STRICT_TRANS_TABLES
sync_master_info
0
10000
Recommend: master_info_repository=table
sync_relay_log
0
10000
sync_relay_log_info
0
10000
Recommend: relay_log_info_repository=table. Also see Replication Relay and Status Logs
table_definition_cache
400
400 + table_open_cache / 2, capped at 2000
table_open_cache
400
2000
Also see table_open_cache_instances
thread_cache_size
0
8 + max_connections/100, capped at 100
Note 1: In 5.5 there was already a rule to make open_files_limit 10 + max_connections + table_cache_size * 2 if that was higher than the user-specified value. Now uses the higher of that and (5000 or what you specify).
We are also adding a new default my.cnf file and guided instructions on the key settings to adjust. More on this in a later post. We're also providing a page with suggestions for settings to
improve backwards compatibility. The old example files like my-huge.cnf
are obsolete.
Some of the improvements are present from 5.6.6 and the rest are coming. These are ideas, and until they are in an official GA release, they are subject to change. As part of this work I reviewed every old server setting plus many hundreds of emails of feedback and testing results from inside and outside Oracle's MySQL Support team and the many excellent blog entries and comments from others over the years, including from many MySQL Gurus out there, like Baron, Sheeri, Ronald, Schlomi, Giuseppe and Mark Callaghan.
With these changes we're trying to make it easier to set up the server by adjusting only a few settings that will cause others to be set. This happens only at server startup and only applies to variables where you haven't set a value. You'll see a similar approach used for the Performance Schema. The Gurus don't need this but for many newcomers the defaults will be very useful.
Possibly the most unusual change is the way we vary the setting for innodb_buffer_pool_instances for 32-bit Windows. This is because we've found that DLLs with specified load addresses often fragment the limited four gigabyte 32-bit address space and make it impossible to allocate more than about 1300 megabytes of contiguous address space for the InnoDB buffer pool. The smaller requests for many pools are more likely to succeed.
If you change the value of innodb_log_file_size in my.cnf you will see a message like this in the error log file at the next restart, instead of the old error message:
[Warning] InnoDB: Resizing redo log from 2*64 to 5*128 pages, LSN=5735153
One of the biggest challenges for the defaults is the millions of installations on a huge range of systems, from point of sale terminals and routers though shared hosting or end user systems and on to major servers with lots of CPU cores, hundreds of gigabytes of RAM and terabytes of fast disk space. Our past defaults were for the smaller systems and these change that to larger shared hosting or shared end user systems, still with a bias towards the smaller end. There is a bias in favour of OLTP workloads, so reporting systems may need more changes. Where there is a conflict between the best settings for benchmarks and normal use, we've favoured production, not benchmarks.
We're very interested in your feedback, comments and suggestions.
