Following on from my earlier proposal to change Replication + InnoDB settings, in the MySQL team, we are proposing to make the following additional changes to defaults in MySQL 5.7:
| Setting | Old Default | New Default |
| binlog_error_action | IGNORE_ERROR | ABORT_SERVER |
| innodb_checksum_algorithm | INNODB | CRC32 |
| innodb_page_cleaners | 1 | 4 |
| innodb_purge_threads | 1 | 4 |
| innodb_strict_mode | OFF | ON |
| innodb_log_file_size | 48M | 128M |
| innodb_buffer_pool_dump_at_shutdown | OFF | ON |
| innodb_buffer_pool_load_at_startup | OFF | ON |
| innodb_buffer_pool_dump_pct | 100 | 25 |
| innodb_file_format | Antelope | Barracuda |
| innodb_large_prefix | OFF | ON |
| binlog_gtid_recovery_simplified | OFF | ON |
| sync_binlog | 0 | 1 |
| slave_net_timeout | 3600 | 60 |
| P_S config | Enable events_statements_history and events_transactions_history consumers by default | |
| MySQL CLI Prompt | mysql> | prompt=”\u@\h [\d] > “ |
| sql-mode | +NO_AUTO_CREATE_USER |
To explain the motivation behind these changes:
- The binlog_error_action variable was introduced in MySQL 5.6 after the GA release, and for compatibility defaulted to previous behavior (ignore errors). Since some applications depend on reading from slaves after writing to masters, we feel that the better behavior is to abort.
- The InnoDB CRC32 checksum was introduced in MySQL 5.6, and offers CPU accelerated page checksum computation. Since previous versions of MySQL were unable to read the new checksum, enabling this feature would have restricted downgrades to dump-and-restore. With MySQL 5.6 supporting the CRC32 checksum, the timing now makes sense to make this the default in 5.7.
- In some workloads the InnoDB purge and page cleaning operations will not be able to keep up with modifications made inside InnoDB. In combination with newer storage devices that offer very high throughput and accommodate parallel writes, we feel that having multiple threads is a better default.
- Currently the InnoDB storage engine ignores CREATE TABLE options that it does not understand (innodb_strict_mode=0). We feel this negatively impacts user experience, and are proposing to enable InnoDB strict mode in advance. The downside of this change is that some DDL statements that worked in 5.6 failing by default in 5.7. Thank you to Daniël van Eeden for providing this suggestion.
- Since the InnoDB buffer pool now supports online resizing (and the innodb log files do not), we believe it makes sense to increase the size of the log files slightly in anticipation of an increased buffer pool size. This comes at a small cost of additional disk space usage. We felt that 128M was a good balance in size, since while larger log files are better for performance, there is also a trade off point where the log files benefit from filesystem caches. Thank you to Justin Swanhart for providing this suggestion.
- With the increased availability of large memory, waiting for caches to warm back up after restarts has been an increased operational concern. MySQL 5.6 introduced a feature to be able to dump buffer pool page addresses (at a small fraction of total size) to assist in warming caches after restarts. MySQL 5.7 introduces an additional feature to dump only a percentage of the buffer pool, and we felt that by limiting this to 25% was a good tradeoff to enable this by default. Thank you to Daniel Black and Daniël van Eeden for this suggestion.
- The InnoDB Barracuda file format was first introduced in the InnoDB plugin for MySQL 5.1, and enables InnoDB to use the new DYNAMIC row format (with improved BLOB handling) and COMPRESSED tables. Setting the InnoDB file format to Barracuda depended on innodb-file-per-table=1 (default changed in MySQL 5.6), and introduced an incompatibility that would prevent downgrades to earlier releases. With both MySQL 5.5 and 5.6 supporting Barracuda, the timing now makes sense to enable this by default. Thank you to Daniël van Eeden for providing this suggestion.
- The innodb_large_prefix prefix option was introduced for Barracuda tables as an option to support much longer index key prefixes (up to 3072 bytes). It was disabled by default so that users did not accidentally create Barracuda tables that would be incompatible on Antelope systems (which would not allow downgrading). With Barracuda becoming the default, and our friends at WordPress and Drupal wanting to create much larger indexes to support utf8mb4 encoded columns, we decided to change this to default to on.
- The binlog_gtid_recovery_simplified option was released after MySQL 5.6 was released as GA, and defaulted to OFF for backwards compatibility. We feel that ON is the better default, and plan to make this change for MySQL 5.7.
- With the addition of binary log group commit in MySQL 5.6, the impact from enabling sync_binlog=1 on many non-synthetic workloads should be reduced considerably. This change makes MySQL binary logs durable/crash safe by default.
- The slave_net_timeout defaults to one hour, which is too long for most modern systems. In addition: MySQL 5.7 now uses this value divided by two to configure the default master heartbeat period. We believe that a one minute default is more appropriate. Thank you to Miguel Angel Nieto for providing this suggestion.
- In MySQL 5.7, performance_schema can now instrument transactions, which can be incredibly useful when enabled by default. In addition, enabling statements history (introduced in 5.6) provides a lot of useful meta-data for diagnosing performance problems. Thank you to Daniël van Eeden for providing this suggestion.
- The MySQL command line client has long supported the ability to change the default prompt from “mysql >” to include additional details such as the username and database selected. This is mostly a cosmetic change, but it is useful when you are managing multiple servers. Thank you to Daniël van Eeden for providing this suggestion.
- In MySQL 5.7 we have already made changes to the default SQL mode: enabling ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES by default. There is a long standing usability issue with user grants, where a typo in the username could result in a new user being created. We have decided that it is best to disable this behavior by enabling the NO_AUTO_CREATE_USER sql mode by default. Thank you to Daniël van Eeden for providing this suggestion.
In addition to these changes, we have decided to withdraw our proposal to change the default isolation level to READ-COMMITTED. We will re-evaluate this decision for future releases.
For those wishing to test out these new configuration settings, I have configuration file available to try out:
- For MySQL 5.7
- For MySQL 5.6 (enables all 5.7 options where applicable)
Please let us know what you think of these changes!
You can leave a comment here, or get in touch via email.