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 | NONE |
CRC32 |
New variable in 5.6. 5.5 doesn't accept the checksums.
Use NONE if you want to read binlog with 5.5 or on master if
have replication slaves using 5.5. |
binlog-row-event-max-size | 1k | 8k |
no binlog_row_event_max_size variable, just the option
form. |
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 |
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 | 0 |
1 |
|
innodb_log_file_size |
5M |
48M |
InnoDB will 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 were already rules to ask the OS for the
highest of (10 + max_connections + table_cache_size * 2) or
(max_connections * 5) or the specified/default open_files_limit.
The default is now calculated but the other rules are still used.
If the OS refuses to allow as many as requested, max_connections
and table_cache_size are reduced and you will see a "Changed
limits" warning in the error log, as before.
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 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 the value of innodb_log_file_size or innodb_log_files_in_group
changes to be different from the files on disk, 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.
2012-10-15 Correction: the initial version of this post wrongly
said that innodb_autoextend_increment "Now affects *.ibd files".
It still does not and I've updated the table.