Morgan and I started an initiative a while back to improve the “out of the box” configuration and behavior defaults for MySQL. Working closely with the Community, we were able to come up with a good list of improvements. This work really began to bear fruit starting with the MySQL 5.7.7 release. I wanted to quickly run down what was changed, and why it provides a better MySQL experience for the average user and installation.

Replication Improvements

• Please see Sujatha’s excellent blog post for the details.

InnoDB Improvements

• innodb_checksum_algorithm — We changed the default from “innodb” to “crc32”. With CRC32 hardware accelerations available in most server machines today, this should offer a modest overall performance boost.
• innodb_page_cleaners — We changed the default from 1 to 4. This makes the process of flushing dirty pages from the buffer pool multi-threaded by default (although it will be automatically resized down if your innodb_buffer_pool_instances is lower). This should help to improve overall performance on busy systems.
• innodb_purge_threads — We changed the default from 1 to 4. This makes the process of purges—removing obsolete values from indexes and physically removing rows that were marked for deletion by previous DELETE statements—multi-threaded by default. This should also improve overall performance on busy systems.
• innodb_strict_mode — We changed the default from OFF to ON. This brings InnoDB in line with our overall goal of making MySQL behavior more strict by default, thus helping you ensure the integrity, validity, and durability of your data. This also falls in line with a parallel goal of making MySQL more SQL standard compliant by default.
• Warm InnoDB Buffer Pool — With the following changes you will maintain a warm cache through restarts of MySQL, retaining the “hottest” 25% of the pages from the Buffer Pool: innodb_buffer_pool_dump_at_shutdown, innodb_buffer_pool_load_at_startup, and innodb_buffer_pool_dump_pct. For additional information, see this excellent blog post by Tony. This helps to lessen the application performance impacts of MySQL restarts.
• innodb_file_format — We’ve made the newest file format—Barracuda—the default. This eliminates any limitations on the row formats that you can use. This makes all available features, such as compression, available by default (avoiding unnecessary MySQL restarts) and results in a better overall user experience.
• innodb_large_prefix — This increases the limit on index key prefixes from 767 bytes, up to 3072 bytes. This is important, especially as more and more users default to Unicode character sets.

Performance Schema Improvements

• Enabling Additional Consumers — We’ve enabled the events_statements_history and events_transactions_history consumers by default. This provides very helpful information to DBAs needing to know what statements and transactions have executed recently on the system. This information is invaluable when tracking down myriad problems (anything related to what queries were being executed at a given time). And thanks to the ongoing efforts to lower the overhead of Performance Schema, we were able to enable these with very minimal impact on performance (see Tarique’s excellent blog post for more information regarding overhead).

Security Improvements

• sql_mode — We made NO_AUTO_CREATE_USER a default. This is to prevent the GRANT statement from unintentionally and automatically creating new user accounts without any authentication information having been specified/provided. This is part of our larger effort around making MySQL safer by default (improved security and data safety), as well as offering more standards compliant behavior by default.
• In addition, the test database and anonymous user accounts will no longer be created as part of a MySQL installation.

Runtime and General Improvements

• table_open_cache_instances — This option is meant to reduce contention on the table cache on systems with many CPU hardware threads (vCPUs: a combination of sockets, cores, and threads). Now that even commodity desktop machines often have 16 hardware threads (2 cpu sockets, each cpu with 4 cores, and each core with 2 threads via SMT/Hyperthreading), we’ve set the default to 16 as this will offer improved performance on modern hardware.
• sql_mode — The STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE sql modes are now enabled as a compiled in default. This has the effect of raising errors for data that would be truncated, is out of range, or otherwise invalid. This is a key part of our dual initiatives to 1. provide greater data validity and integrity guarantees by default 2. offer more SQL standard compliant behavior by default.
  (Note: prior to MySQL 5.7.8 all of these settings were merged into the single STRICT_TRANS_TABLES mode, but as a result of community feedback, they have been de-coupled again as in MySQL 5.6 and earlier releases.)
• show_compatibility_56 — In 5.7 we’ve improved the behavior of SHOW commands, both in adding additional information but also in providing a clean delineation between the GLOBAL and SESSION contexts (which was the result of several oddities and bug reports). In order to better support backwards compatibility concerns when needed, we also introduced a new option called show_compatibility_56 which now defaults to OFF in 5.7.8. For compatibility with earlier releases, a user may wish to turn this setting to ON.
• log_warnings — We changed the default value from 1 to 2, effectively increasing the verbosity of the error log. Please note that 5.7 also deprecates this configuration setting in favor of the newer log_error_verbosity option.

Optimizer Improvements

• sql_mode — The ONLY_FULL_GROUP_BY behavior has been greatly improved, and it’s now enabled by default. You can read more about these changes in Guilhem’s excellent blog post.
• eq_range_index_dive_limit — We changed the default from 10 to 200. This should offer better overall behavior for most cases. You can read more about this change in Jorgen’s excellent blog post.
• We introduced 2 new optimizer switches called condition_fanout_filter and derived_merge, which are also now enabled by default. In most cases users will always want to leave these optimizations on as they will provide better performance. However, there are specific subqueries which will not support derived merge and these special cases have been documented under ‘sql changes’ in the 5.7 upgrade notes.

If you have any questions about these changes, or recommendations for others, please let us know in the comments here.

That’s it for now. As always, THANK YOU for using MySQL. And in this case, helping to make it even better!