Proposal to change additional defaults in MySQL 5.7 (February Edition)

Following on from my two earlier posts, in the MySQL team we are proposing a new set of changes to defaults for MySQL 5.7:

Setting Old Default New Default
log_slow_admin_statements OFF ON
log_slow_slave_statements OFF ON
long-query-time 10 2
log-queries-not-using-indexes OFF ON
min-examined-row-limit 0 1000
MySQL Command Line Client +show_warnings
group_concat_max_len 1024 1M
max_allowed_packet 4M 64M
symbolic-links OFF
temp-pool 1 0
table_open_cache_instances 1 16

To explain these changes in more details:

  • The slow query log remains disabled by default, but when enabled our hope is that we can make it immediately useful. A query exceeding 2 seconds will now be considered slow, and since queries that do not use indexes have the highest chance of causing future problems, they will also be logged unless they examine fewer than 1000 rows. The number of 1000 was chosen because it should eliminate the majority of false positives from queries retrieving a static set of records such as navigation menu items, which are well suited to tablescans.

    In proposing this change, we considered the defaults that other products use for considering a query slow. A popular web performance tool uses a default of 0.5 seconds for an acceptable time to render a full page server side, with 2 seconds (4x) slow enough to warrant logging. Our own MySQL Enterprise Monitor considers 100ms acceptable, and 400ms (4x) as slow. We also considered that setting the min-examined-row-limit to a non zero value will now require an additional step for those that set their long-query-time to zero seconds. We would like to thank Daniel Black for suggesting we change our slow query log options.

  • The MySQL command line client has the ability to automatically print warnings out to screen as they occur. We feel like ON is the more useful default, as a user will typically not intend to execute statements that cause warnings. This behavior applies to both interactive and batch modes, and can be disabled with show_warnings=0. We would like to thank Ryuta Kamizono for this suggestion.
  • We find the GROUP_CONCAT() function in MySQL to be incredibly useful for summarizing results in an aggregate query. We also believe that the default maximum length for grouping values has not kept pace with the larger amounts of memory available on modern hardware. We would like to thank Shlomi Noach for this suggestion.
  • In MySQL 5.6 we increased the max_allowed_packet from 1M to a conservative 4M. We have received a lot of feedback on this change, and are proposing to increase the default to 64M for MySQL 5.7. We would like to thank Shlomi Noach for this suggestion.
  • Many of our configuration files for MySQL packages (as well as those that ship with Linux distributions) default to disabling symbolic links for security reasons. We feel that changing a de facto default to a compiled default will improve user experience (as well as security). We would like to thank Honza Horak for this suggestion.
  • The temp-pool option was originally created as a means to work around potential filesystem issues on Linux when using internal MyISAM based temp tables. The underlying OS issues have since been resolved, and disabling this option removes mutex contention for all disk based temp table engines (MyISAM and InnoDB).
  • We are proposing to increase the default table_open_cache_instances to 16 in order to reduce contention on internal mutexes when opening tables.

For those of you who would like to test out these changes (along with previous changes proposed), I have sample configuration files available:

Please let us know what you think of these changes!
You can leave a comment here, or get in touch via email.