Proposal to change additional defaults in MySQL 5.7

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:

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

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.

  • Varryl

    Yeah, slave_net_timeout definitely needs to be shorter than an hour.

  • Why is innodb_buffer_pool_dump_at_shutdown enabled by default, but innodb_buffer_pool_load_at_startup still defaults to disabled? I would think you’d enable both of them if you enable either one of them.

    • Human error 🙂 I have updated the table above to include innodb_buffer_pool_load_at_startup.

  • Federico Razzoli

    lock_wait_timeout default value looks somewhat exaggerated 🙂
    query_cache_size default value sometimes confuses users – they think that MySQL uses the query cache by default

  • Simon J Mudd

    Most of the suggested changes seem to make sense.

    I am not so sure about NO_AUTO_CREATE_USER which may cause issues if you replicate grants from a master to a slave. This is something which I do frequently. If you need to make the commands “replication safe” then you have to be sure that the user created on the master does not exist on the slave already, or you will break replication, or that the user exists before being dropped.

    Up until now I’ve done the following to create a user:
    GRANT USAGE ON *.* TO user@somewhere IDENTIFIED BY PASSWORD ‘some_hash’;

    and the following to drop a user:
    GRANT USAGE ON *.* TO user@somewhere IDENTIFIED BY PASSWORD ‘some_hash’;
    DROP USER user@somewhere.

    The proposed change is likely to break that and also if you don’t take into account the master may be running 5.6 during a migration to 5.7 this will also cause issues with commands run on a 5.6 master replicating to 5.7 slaves.

    See:
    http://bugs.mysql.com/bug.php?id=15287
    http://dev.mysql.com/worklog/task/?id=3129

    for a talk of stuff which is currently a pain because of the “missing” IF EXISTS/NOT EXISTS functionality (or something equivalent).

    So please pay very careful attention to MySQL 5.6 to 5.7 migration. I have already seen some issues in 5.7 slaves replicating from a 5.6 master and changes like this may lead to more problems if not thought out carefully.

    The intention to tighten up MySQL 5.7 to make it cleaner and stricter is good, but you need to take into account those of us who are already using MySQL 5.6 and need to migrate existing MySQL 5.6 servers to 5.7. If you do not make it possible to move to 5.7 seemlessly you may leave a lot of people with a painful migration, or force us to explicitly configure MySQL to be as lax as it is now to keep compatibility.

    • Kristian Köhntopp

      What Simon says here. CREATE USER and DROP USER are not idempotent.

      mysql> create user ‘demo’@’localhost’ identified by ‘keks’;
      Query OK, 0 rows affected (0.02 sec)

      mysql> create user ‘demo’@’localhost’ identified by ‘keks’;
      ERROR 1396 (HY000): Operation CREATE USER failed for ‘demo’@’localhost’

      mysql> drop user ‘demo’@’localhost’;
      Query OK, 0 rows affected (0.00 sec)

      mysql> drop user ‘demo’@’localhost’;
      ERROR 1396 (HY000): Operation DROP USER failed for ‘demo’@’localhost’

      There is no conditional syntax to make it idempotent, CREATE USER IF NOT EXISTS and DROP USER IF EXISTS are missing.

      You should not disallow the GRANT thing before you add idempotent conditional syntax for CREATE and DROP