One of MySQL’s continued values 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.
For MySQL 5.6, hats off to James Day who led the project internally. Taken directly from James’ blog, here is the list of changes made:
| 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.
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.
Moving forward
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?