One of MySQL’s continued values (http://www.mysql.com/about/) is to be easy to use. A key part of this, is making sure the default configuration is kept up to date and safe for everyone.
Internally, these configuration changes are made in close cooperation between our engineering and support teams, who are in the best position to provide insight into customer impact.
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
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 will change size to match my.cnf value. Also see innodb_log_compressed_pages and binlog_row_image
innodb_old_blocks_time 0 1000
innodb_open_files 300 300. If innodb_file_per_table is ON, higher of table_open_cache or 300
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
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.
Additional recommendations for new applications
Part of continuing to be easy to use means that as well as having a good set of defaults, users also have an expectation of backwards compatibility. So there is a tradeoff that sometimes needs to be taken to make sure that novice users can upgrade their legacy applications with minimal issues.
This tradeoff doesn’t apply to new applications, and in which case I recommend the following additional configuration changes which should be enabled:
[mysqld] # Force UTF-8 character-set: character-set-server=utf8 collation-server=utf8_general_ci # Force MySQL to be more strict and not allow legacy # defaults which truncate data, accept invalidate dates etc # Shown vertically to be a little easier to read. sql-mode="STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_AUTO_VALUE_ON_ZERO, NO_ENGINE_SUBSTITUTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY"
UTF-8 is a much smarter default than latin1 today. A simple Google search shows that converting is an issue for a number of users. I would even go so far as to encourage setting
skip-character-set-client-handshake to ignore applications trying to set the character-set otherwise.
MySQL (arguably) became popular for not being strict about SQL and allowing you to insert any values. This may have made sense if a poor application did not perform error checking, and the alternative was not to store any data. I think that times have changed now - many users will use frameworks and ORMs, and are happy to catch errors up front, rather than later find their data is corrupt.
We are open to feedback on how we introduce configuration changes. You can leave a comment here, or feel free to contact me.
To get the discussion rolling, what do you think about bundling a small program (
mysql-upgrade-defaults) with packages that can optionally be run after installation?