Improving MySQL’s default configuration

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?

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I’ve also worked at Percona and InPowered.

  • Peter Laursen

    “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.”

    I would rather like to see that option go! Or at least it should be exposed as a variable as well (refer: http://bugs.mysql.com/bug.php?id=69997). Besides setting skip-character-set-client-handshake to FALSE will only have effect when a charset is specified in mysql_options(). Any client can execute SET NAMES after connection (and I believe that is the way most applications do – the only practical difference between the two is support for non-server-default-charset-characters in username and password).

    Setting skip-character-set-client-handshake to FALSE does not prevent SET NAMES after connection. And in PHP for instance there is no – to my best knowledge – implementation of mysql_options() charset setting anyway.

    Besides I totally agree with you about using UTF8 in the client.

    • Subscribed to #69997 – thanks for pointing it out.

  • I don’t really understand why the binlog checksums were introduced without increasing the binlog format version from v4 to v5. I’d rather have the client say: This version of mysqlbinlog is not compatible with the binlog format, please upgrade mysqlbinlog. (Submitted Bug #70287 for this)

  • Simon J Mudd

    Some thing I’ve asked for in the past, and still want, is that SHOW GLOBAL VARIABLES (or an equivalent table in I_S) shows the DEFAULT VALUES. This makes checking for non-default values much easier. If the default settings may not be generated dynamically then that should be shown too as otherwise people will get confused if my default value is different to yours…

    The same applies to whether the configuration setting can be adjusted dynamically or not. I see some settings are completely static and others can only be adjusted for example if the replication threads are stopped. Again indicating this is important as it avoids us having to write our own infrastructure to determine whether or not we can change something and under what circumstances.

    So please get this into 5.7. For those of us who record the configuration of our servers, or try to update the configuration dynamically having this information queryable directly from the server would be a great help.

    • Doesn’t completely answer your question – but there are two things you can do not everyone is aware of:

      $ my_print_defaults mysqld
      (aggregates configuration settings for mysqld program across all config files)

      mysql> SET my_setting = DEFAULT;
      (Sets my_setting to the default value for current session).

      I like your suggestion though. The only thing I would add to it, is if it is going to be part of the SHOW command, make it SHOW GLOBAL EXTENDED VARIABLES (backwards compatible). Do you have an open bug report for the feature request?

      • Simon J Mudd

        my_print_defaults does not work _inside_ mysqld (ie from SQL) and not everyone may have access to use it. Using mysqld to set a value to a DEFAULT value that you can not see until after it has been changed is also not ideal.

        Your are right, both of these options do work but they are not ideal.

        I do have an open feature request: http://bugs.mysql.com/68451. It asks for a few more things too.

        To be honest I would prefer to do a SELECT from I_S.GLOBAL_VARIABLES and look at the new columns I need. Adjusting SHOW GLOBAL VARIABLES and extending an already non-standard command (in the sense it is not standard SQL) is not really necessary when you can just do this from a normal SELECT.

        • Thanks for the bug number – I’ve subscribed. I agree with your comments about my_print_defaults – it also doesn’t help when changes are made post-startup, and a lot more settings are dynamic now.

  • Pingback: database mysql 5.6初始配置调优 | 极客521()