MySQL 8.0 comes with improved defaults, aiming at the best out of the box experience possible. Here we describe the changes and why they are made.

Introduction

Generally speaking, a good default is “the best choice for most users, most of the time”. In respect of the existing user base we do not want to change a default just for the sake of it, so a change should have a good reason attached. As you will see below there are changes motivated by several, different objectives such as :

• Mainstream hardware is changing. Machines have more CPUs and more memory than before, machines have SSDs rather than spinning disks etc.
• User installations tend to be “bigger” than before, thus on average need more resources such as bigger memory buffers.
• MySQL should work “out of the box”, thus features in general use should be ON by default. For example utf8 character set, binlog enabled, event scheduler enabled, more instrumentation, etc.
• Better trade-offs (based on experience), such as adjusting the InnoDB flushing behavior and bigger buffer for IP connection requests.
• More standard SQL, such as explicit defaults for timestamps.
• More security, such as enabling the password validation plugin.

The overall goal for defaults will always be to be “good enough” so that users will not have to worry about changing them. For more background, see Planning the defaults for MySQL 8.0 and MySQL Replication Defaults After 5.7.

Table : Changed Defaults in MySQL 8.0

Server Defaults

The default value of the character_set_server system variable and command line option –character-set-server  changes from latin1 to utf8mb4. This is the server’s default character set.  UTF8MB4 is the dominating character encoding for the web, and this change will make life easier for the vast majority of MySQL users. The upgrade from 5.7 to 8.0 will not change any character set for any existing database objects. But unless you specify character_set_server back to your previous default or explicitly set the character set then a new schema/table/column will by default be in utf8mb4. We recommend users to move to utf8mb4 whenever possible. See Debugging Character Set Issues by Example.

The default value of the  collation_server system variable  and command line argument –collation-server changes from latin1_swedish_ci to utf8mb4_0900_ai_ci.  This is the server’s default collation (i.e. ordering of characters in a character set). There is a link between collations and character sets as each character set comes with a list of possible collations. The upgrade from 5.7 to 8.0 will not change any collation for any existing database objects, but take effect for new objects. See MySQL 8.0 Collations: Migrating from older collations.

The default value of the system variable explicit_defaults_for_timestamp changes from OFF  (MySQL legacy behavior) to ON (SQL standard behavior). The option was introduced in 5.6 and was OFF in 5.6 and 5.7.  We expect this option to be removed in the future, making explicit defaults for timestamps mandatory.

The default value of the system variable optimizer_trace_max_mem_size changes from 16KB to 1MB. The old default caused the the optimizer trace to be truncated for any non-trivial query. The purpose of the change is to get useful optimizer traces for most queries.

The default value of the system variable validate_password_check_user_name changes from OFF to ON. This means that when the validate_password plugin is enabled, by default it now rejects passwords that match the current session user name.

The autosize algorithm for the system variable back_log has changed.  The value for autosize (-1) is now set to the value of max_connections  which is bigger than the old value which was 50 + (max_connections / 5). The back_log is queuing up incoming IP connect requests in situations where the server is not able to keep up with incoming requests. So in the worst case with max_connections number of clients trying to reconnect at the same time, e.g. after a network failure, they can all be buffered and reject-retry loops will be avoided.

The default value of the system variable max_allowed_packet changes from 4194304 (4M) to 67108864 (64M). The main advantage with this larger default is that fewer users receive errors about insert or query being larger than max_allowed_packet. It should be as big as the largest BLOB you want to use. Users wishing to revert to the previous behavior can set max_allowed_packet=4194304.

The default value of the system variable max_error_count changes from 64 to 1024. The effect is that MySQL will handle a larger number of warnings, e.g. for an UPDATE statement that touches 1000s of rows and many of them give conversion warnings. It is common for many tools to batch updates, to help reduce replication lag. External tools like pt-online-schema-change defaults to 1000, and gh-ost defaults to 100. MySQL 8.0 covers full error history for these two use cases. There are no static allocations, so this change will only affect memory consumption for statements that generate lots of warnings.

The default value of the system variable event_scheduler changes from OFF to ON. I.e. the event scheduler is enabled by default. This is an enabler for new features in SYS, for example “kill idle transactions”.

The default value of the system variable table_open_cache changes from 2000 to 4000. This is a minor change which increases session concurrency on table access.

The default value of the system variable log_error_verbosity changes from 3 (Notes) to 2(Warning). The purpose is to make the MySQL 8.0 error log less verbose by default.

InnoDB Defaults

The default value of the system variable innodb_undo_tablespaces changes from 0 to 2. The number of undo tablespaces used by InnoDB. In MySQL 8.0 the minimum value for innodb_undo_tablespaces is 2 and rollback segments cannot be created in the system tablespace anymore. (Thus, this is a case where you cannot revert back to 5.7 behavior). The purpose of this change is to be able to auto-truncate Undo logs (see next item), reclaiming disk space used by (occasional) long transactions such as a mysqldump.

The default value of the system variable  innodb_undo_log_truncate changes from OFF to ON. When enabled, undo tablespaces that exceed the threshold value defined by innodb_max_undo_log_size are marked for truncation. Only undo tablespaces can be truncated. Truncating undo logs that reside in the system tablespace is not supported. An upgrade from 5.7 to 8.0 automatically converts your system to use  undo tablespaces, using the system tablespace is not an option in 8.0.

The default value of the system variable innodb_flush_method changes from NULL to fsyncon Unix-like systems and from NULL to unbuffered on Windows systems. This is more of a terminology and option cleanup without any tangible impact. For Unix this is just a documentation change as the default was “fsync” also in 5.7 (default NULL meant “fsync”). Similarly on Windows, innodb_flush_method default NULL meant async_unbuffered in 5.7, and is replaced by default “unbuffered” in 8.0 which in combination with the existing default innodb_use_native_aio=ON has the same effect.

The default value of the system variable innodb_autoinc_lock_mode changes from 1(consecutive) to 2 (interleaved). The change to interleaved lock mode as the default setting reflects the change from statement-based to row-based replication as the default replication type, which occurred in MySQL 5.7. Statement-based replication requires the consecutive auto-increment lock mode to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of SQL statements, whereas row-based replication is not sensitive to the execution order of SQL statements. Thus, this change is known to be incompatible with statement based replication, and may break some applications or user-generated test suites that depend on sequential auto increment. The previous default can be restored by setting innodb_autoinc_lock_mode=1;

The default value of the system variable innodb_flush_neighbors from 1 (enable) to 0(disable). This is done because fast IO (SSDs) is now the default for deployment. We expect that for the majority of users, this will result in a small performance gain. Users who are using slower hard drives may see a performance loss, and are encouraged to revert to the previous defaults by setting innodb_flush_neighbors=1.

The default value of the system variable innodb_max_dirty_pages_pct_lwm changes from 0 (%) to 10 (%). With innodb_max_dirty_pages_pct_lwm=10, InnoDB will increase its flushing activity when >10% of the buffer pool contains modified (‘dirty’) pages. The purpose of this change is to trade off peak throughput slightly, in exchange for more consistent performance.

The default value of the system variable innodb_max_dirty_pages_pct changes from 75(%) to 90 (%). This change comes together with the innodb_max_dirty_pages_pct_lwm change (see above) and together they cause a smooth InnoDB flushing behavior, i.e. avoids flushing bursts. Users who wish to revert to the previous behavior can set innodb_max_dirty_pages_pct=75 and innodb_max_dirty_pages_pct_lwm=0.

Performance Schema Defaults

Performance Schema Meta Data Locking  (MDL) instrumentation is turned on by default. The compiled default  for performance-schema-instrument='wait/lock/metadata/sql/%=ON' changes from OFF to ON.  This is an enabler for adding MDL oriented views in SYS.

Performance Schema Memory instrumentation is turned on by default. The compiled default  for performance-schema-instrument='memory/%=COUNTED' changes from OFF to COUNTED. This is important because the accounting is incorrect if instrumentation is enabled after server start, i.e. you could get a negative balance from missing an allocation, but catching a free.

Performance Schema Transaction instrumentation is turned on by default. The compiled default  for  performance-schema-consumer-events-transactions-current=ON, performance-schema-consumer-events-transactions-history=ON, and performance-schema-instrument='transaction%=ON' changes from OFF to ON.

Replication Defaults

The default value of the system variable log_bin changes from OFF to ON (binary logging is enabled by default).  Nearly all production installations have the binary log enabled as it is used for replication and point-in-time recovery. Thus, by enabling binary log by default we eliminate one configuration step for users (enabling it later requires a mysqld restart). Enabling it by default also gives us better test coverage and it becomes easier to spot performance regressions. Remember to also set server_id (see next paragraph). The 8.0 default behavior is as if you specify ./mysqld --log-bin --server-id=1. If you are on 8.0 and want 5.7 behavior you do ./mysqld --skip-log-bin --server-id=0.

The default value of the system variable server_id changes from 0 to 1 (goes together with log_bin=ON, see above). The server can be started with this default ID, but in practice the user must set the server-id according to the replication infrastructure at hand, to avoid having duplicate server ids.

The default value of the system variable log-slave-updates changes from OFF to ON.  This causes a slave to log replicated events into its own binary log. This option ensures correct behavior in various replication chain setups, which have become the norm today. This is also required for Group Replication.

The default value of the system variable expire_log_days changes from 0 to 30. The new default 30 causes mysqld to periodically purge unused binary logs that are older than 30 days. This change will help to prevent excessive amounts of disk space being wasted on binary logs that are no longer needed for replication or recovery purposes. The old value 0will disable any automatic binary log purges.

The default value of the system variables master-info-repository and relay-log-info-repository change from FILE to TABLE. Thus in 8.0, replication meta-data is stored in InnoDB by default. This is done to increase reliability, i.e. to achieve crash safe replication by default.

The default value of the system variable transaction-write-set-extraction changes from from OFF to XXHASH64. This change enables transaction write sets by default. By using Transaction Write Sets, the master has to do slightly more work to generate the write sets which is helpful in conflict detection. This allows users to easily move into Group Replication (GR) since transaction-write-set is a requirement for GR. Also, the new default will make it easy for users to enable binary log writeset parallelization on master to speed up replication.

The default value of the system variable slave_rows_search_algorithms changes from INDEX_SCAN,TABLE_SCAN to INDEX_SCAN,HASH_SCAN.  This change speeds up row-based replication by reducing the number of table scans the slave applier has to do to apply the changes to a table without a primary key.

Closing Remarks 

We believe our defaults to be the best choices for most users, most of the time. So, unless you have some legacy to care about we recommend to go with the defaults.

New Installations

Most of our defaults are reasonably good for both development and production environments. There is one exception to this, we decided to keep the new option called  innodb_dedicated_server OFF although we recommend it to be ON for production environments. The reason for having it OFF by default is that it causes shared environments such as developer laptops to become unusable, because it takes “all” the memory it can find.

For production environments we recommend innodb_dedicated_server ON.  When ON the following InnoDB variables (if not specified explicitly) will be autoscaled based on available memory innodb_buffer_pool_size, innodb_log_file_size, and innodb_flush_method. See documentation and blog post.

Upgrading Existing Installations

Although we believe our new defaults are the best configuration choices for most use cases, we do recognize that there are special cases as well as legacy reasons for sticking to existing 5.7 configuration choices. For example, some will prefer upgrade to 8.0 with as few changes to their applications or operational environment as possible. The argument for this would be “I first want to ensure that 8.0 works as 5.7 did in my environment before I start using new features”. MySQL takes this into account by not introducing unnecessary restrictions, i.e. the configuration choices you used in 5.7 are still valid in 8.0 (there are a few exceptions). But you need to remember to set the old configuration choice explicitly, otherwise the new default will apply.

We recommend to evaluate all new defaults and stick with as many new defaults as you can. Most new defaults can be tested in 5.7, so one can validate the new defaults in 5.7 production before upgrading to 8.0. For the (hopefully) few defaults where you need your old 5.7 value set the corresponding configuration variable or startup option in your operational environment.