Introduction

MySQL exposes many configuration variables to modify its behavior and enable certain features. The configuration giving the best reliability and performance depends not only on the type of workload, but also the system resources available to the server. This makes it challenging to determine the ideal configuration when moving the MySQL server to production. Therefore, we decided to make the defaults production ready.

With MySQL 8.4, we’ve introduced changes to the default values of the built-in configurations when compared to MySQL 8.0. The default values of the configuration parameters are designed to automatically adapt to the machine running the MySQL server. Nothing has been removed and you can continue to use your preferred 8.0 values after upgrading to 8.4. Simply override the default configuration by setting the values in your 8.4 configuration file. These changes are available in MySQL 8.4 Community and Enterprise editions, and HeatWave.

Technical overview

You can now move the MySQL server to production using the default configuration by simply setting these two variables and using the built-in defaults for the rest:

[mysqld]
  max_connections = N
  innodb_dedicated_server = ON

 

max_connections controls the maximum number of clients that can connect to the server concurrently. Its default value is 151 and maximum value is 100000.

innodb_dedicated_server tells InnoDB that the server is run on a dedicated machine. This allows InnoDB to determine the best value for its critical configurations for the given system. It enables InnoDB to scale innodb_buffer_pool_size and innodb_redo_log_capacity as follows:

  • innodb_buffer_pool_size is determined based on available physical memory (say M) as:
    • 128 MB if M < 1 GB
    • M * 0.50 GB if 1 GB ≤ M ≤ 4 GB
    • M * 0.75 GB if M > 4 GB
  • innodb_redo_log_capacity is determined based on the number of logical CPUs (say N) as (N/2) GB and is capped at 16 GB

innodb_dedicated_server is disabled by default. This is maintained to keep MySQL download friendly. You can download and start MySQL on your laptop without MySQL grabbing too much of the systems resources. The default values are maintained for innodb_buffer_pool_size at 128 MB and innodb_redo_log_capacity at 1 GB in both MySQL 8.0 and MySQL 8.4.

The MySQL 8.4 defaults are made production ready for any machine by allowing the server to auto-tune the defaults. The server determines the best value of the configurations based on the number of logical CPUs, the total physical memory, the operating system and the values of other dependent configurations like innodb_buffer_pool_size and innodb_buffer_pool_instances. This allows MySQL to adapt and scale according to the type of machine it runs on.

This table summarizes the changes in the defaults in MySQL 8.4 compared against MySQL 8.0. You can observe that some of the features are disabled by default in MySQL 8.4. This is done to provide a safe experience for the users and does not indicate deprecation. Users are advised to be aware of the potential side effects before enabling these features.

 

Configuration Variable

MySQL 8.0 default (OLD)

MySQL 8.4 default (NEW)
innodb_adaptive_hash_index ON OFF
innodb_buffer_pool_in_core_file ON

OFF if supported

ON otherwise
innodb_buffer_pool_instances
innodb_page_cleaners 4 innodb_buffer_pool_instances
innodb_change_buffering all none
innodb_redo_log_capacity when innodb_dedicated_server is turned ON evaluated based on available physical memory (see here for details)

[ (1/2) * number of logical CPUs ] GB

capped at 16 GB
innodb_doublewrite_files 2 * innodb_buffer_pool_instances 2
innodb_doublewrite_pages innodb_write_io_threads (4 by default) 128
innodb_flush_method
  • Unix:
    • O_DIRECT if supported
    • fsync otherwise
    • The default value is independent of the setting of innodb_dedicated_server
  • Windows:
    • unbuffered (no change)
innodb_use_fdatasync OFF ON
innodb_io_capacity 100 10000
innodb_io_capacity_max Minimum ( 2 * innodb_io_capacity, 2000) 2 * innodb_io_capacity
innodb_log_buffer_size 16 MB 64 MB
innodb_numa_interleave OFF ON if supported, OFF otherwise
innodb_parallel_read_threads 4

{ (1/8) * number of logical CPUs }

clamped to [4, 256]
innodb_purge_threads 4
  • 1 if number of logical CPUs ≤ 16
  • 4 otherwise
innodb_read_io_threads 4

{ (1/2) * number of logical CPUs }

clamped to [4, 64]
temptable_max_mmap 1 GB 0
temptable_max_ram 1 GB

3% of available physical memory

clamped to [1, 4] GB
temptable_use_mmap ON OFF

Reverting Changes

The changes only affect the default values, so you can always revert these changes by overriding them. Here is a sample configuration file containing the 8.0 default values of the configurations changed in 8.4. Be sure to manually edit the values specific to your operating system and dependent configurations:

[mysqld]
  innodb_adaptive_hash_index=ON
  innodb_buffer_pool_in_core_file=ON
  innodb_change_buffering=all
  innodb_io_capacity=200
  innodb_io_capacity_max=2000
  innodb_log_buffer_size=16777216
  innodb_numa_interleave=OFF
  innodb_page_cleaners=4
  innodb_parallel_read_threads=4
  innodb_purge_threads=4
  innodb_read_io_threads=4
  innodb_use_fdatasync=OFF
  temptable_max_mmap=1073741824
  temptable_max_ram=1073741824
  temptable_use_mmap=ON
 
  # When innodb_buffer_pool_size >= 1 GB
  innodb_buffer_pool_instances=8
 
  # When innodb_buffer_pool_size < 1 GB
  innodb_buffer_pool_instances=1
 
  # innodb_doublewrite_files was 2 * innodb_buffer_pool_instances.
  # By default, this is either 2 * 1 = 2; or 2 * 8 = 16.
  innodb_doublewrite_files=2
 
  # innodb_doublewrite_pages was innodb_write_io_threads. This is 4
  innodb_doublewrite_pages=4
 
  # Unix:
  innodb_flush_method=fsync
 
  # Windows:
  innodb_flush_method=unbuffered

Conclusion:

MySQL 8.4 is made production ready out of the box, meaning it is secure, performant and robust. It comes with the defaults recommended for production environments that scale with available system resources like number of logical CPUs and physical memory.

You can easily move the MySQL server to production by using the default configuration, enabling innodb_dedicated_server and setting max_connections appropriately.

Again, nothing has been removed and you can continue to use your preferred 8.0 values after upgrading to 8.4. All of this is available in MySQL 8.4 Community and Enterprise editions, and HeatWave.

Thanks for using MySQL!