Yesterday, MySQL 8.4, the very first LTS version of MySQL was released.
A lot of deprecations have finally been removed, and several InnoDB variable default values have been modified to match current workloads and hardware specifications.
The default value of 20 InnoDB variables has been modified!
Let’s have a look at those variables and explain the reason for such modification:
innodb_buffer_pool_in_core_file
| Previous Value: | ON |
| New Value (8.4 LTS): | OFF if MADV_DONTDUMP is supported else ON |
MADV_DONTDUMP is a macro supported in Linux 3.4 and later, (“sys/mman.h” header file is present and contains the symbol MADV_DONTDUMP, a non-POSIX extension to madvise()), this is not supported on Windows machines or most MacOS systems.
In summary, this means that by default on Linux systems, the content of the Buffer Pool is not dumped in core file.
innodb_buffer_pool_instances
| Previous Value: | 8 (or 1 if BP < 1 GB) |
| New Value (8.4 LTS): | If BP <= 1 GB: 1 If BP > 1 GB: then the minimum value in the range of 1-64 between: a. (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) / 2 b. 1/4 of available logical processors |
The old value of 8 could have been too large on some systems. The manual contains nice examples of the BP size calculation, see the Configuring InnoDB Buffer Pool Size.
innodb_change_buffering
| Previous Value: | all |
| New Value (8.4 LTS): | none |
Change buffering is a technique that was beneficial for favoring sequential I/O by delaying write operations to secondary indexes. On most recent hardware, random I/O is not a problem anymore.
innodb_dedicated_server
| Previous Value: | OFF |
| New Value (8.4 LTS): | OFF |
Since MySQL 8.0 we recommend enabling this variable and not modifying manually the InnoDB settings taken in charge by this variable when MySQL is running on a dedicated server where all resources are available for the database.
The default value is the same for this variable, but the variables controlled by enabling innodb_dedicated_server are different.
Since MySQL 8.4, innodb_dedicated_server configures the following variables:
- innodb_buffer_pool_size
- 128MB is the server has less than 1 GB memory.
- detected server memory * 0.5 if the server has between 1GB and 4GB memory.
- detected server memory * 0.75 if the server had more than 4GB memory.
- innodb_redo_log_capacity: (number of available logical processors/2) GB, with a maximum of 16GB.
innodb_flush_method is not automatically configured when innodb_dedicated_server is enabled.
innodb_adaptive_hash_index
| Previous Value: | ON |
| New Value (8.4 LTS): | OFF |
AHI (InnoDB Adaptive Hash Index) has long been the cause of some performance issues. Every experienced DBA always advises just disabling it, almost like the old Query Cache. I’m surprised that there wasn’t an AHI Tuner like the Query Cache Tuner from Domas Mituzas 😉
AHI may provide some benefit on read queries (SELECT) when none of the data is changed and is fully cached in the Buffer Pool. As soon as there are write operations, or a higher load on the system, or if all the data required for the read cannot be cached, the Adaptive Hash Index becomes a massive bottleneck.
To have a more predictable response time, it’s recommended to disable it.
innodb_doublewrite_files
| Previous Value: | innodb_buffer_pool_instances * 2 |
| New Value (8.4 LTS): | 2 |
Previously the default value was calculated according to the number of buffer pools, to simplify, the default is now 2.
The documentation states that this value defines the number of double write files for each buffer pool. But I’ve the impression that his it global independently of the amount of buffer pool instances.
From the MySQL error log:
2024-05-01T05:43:03.226604Z 1 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 2.000000G, instances = 2, chunk size =128.000000M
[...]
2024-05-01T05:43:03.288068Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2024-05-01T05:43:03.295917Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite
2024-05-01T05:43:03.317319Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.bdblwr' for doublewrite
2024-05-01T05:43:03.317398Z 1 [Note] [MY-013566] [InnoDB] Double write buffer files: 2
2024-05-01T05:43:03.317410Z 1 [Note] [MY-013565] [InnoDB] Double write buffer pages per instance: 128
2024-05-01T05:43:03.317423Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2024-05-01T05:43:03.317436Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewriteWe see that we have 2 Buffer Pool instances, but still only 2 double write buffer files. I would expect 4 according to the documentation. The third file, #ib_16384_0.bdblwr, is created to be used when innodb_doublewrite is set to “DETECT_ONLY“.
With DETECT_ONLY, only metadata is written to the doublewrite buffer. Database page content is not written to the doublewrite buffer, and recovery does not use the doublewrite buffer to fix incomplete page writes. This lightweight setting is intended for detecting incomplete page writes only.
innodb_doublewrite_pages
| Previous Value: | innodb_write_io_threads (4 by default) |
| New Value (8.4 LTS): | 128 |
From our testing and for performance reasons, we realized that having a larger value as default was better as we often recommended to increase it.
innodb_flush_method
| Previous Value: | fsync |
| New Value (8.4 LTS): | O_DIRECT (or fsync) |
When supported, O_DIRECT has always been the preferred value and we recommended using it to bypass the filesystem cache to flush InnoDB changes to disk (for data files and log files).
If O_DIRECT is not supported, we use the old fsync method. This is for Unix, on Windows, the default value is unbuffered.
innodb_io_capacity
| Previous Value: | 200 |
| New Value (8.4 LTS): | 10000 |
For recent systems (RAIDs, SSDs, … ), the default I/O capacity was too low. As the variable defines the number of IOPS available to InnoDB background operations, having a too-low value was limiting the performance.
innodb_io_capacity_max
| Previous Value: | 2 * innodb_io_capacity (min 2000) |
| New Value (8.4 LTS): | 2 * innodb_io_capacity |
If InnoDB needs to flush more aggressively, this variable defines the maximum number of IOPS InnoDB can use to perform the background operations. The new default is simpler as it’s just double the innodb_io_capacity.
innodb_log_buffer_size
| Previous Value: | 16 MB |
| New Value (8.4 LTS): | 64 MB |
We increased the default because a large log buffer enables large transactions to run without requiring the log to be written to disk before the transactions commit.
innodb_numa_interleave
| Previous Value: | OFF |
| New Value (8.4 LTS): | ON |
When the system supports NUMA, the new default sets the NUMA memory policy to MPOL_INTERLEAVE for mysqld during the allocation of the InnoDB Buffer Pool. This operation balances memory allocation randomly to all numa nodes, causing better spread between those nodes.
Of course, you benefit from this only if your system has multiple NUMA nodes.
This is how to verify the number of nodes:
$ numactl --hardware
available: 2 nodes (0-1)
node 0 size: 16160 MB
node 0 free: 103 MB
node 1 size: 16130 MB
node 1 free: 83 MB
node distances:
node 0 1
0: 10 20
1: 20 10In the example above, we can see that the CPU has two nodes.
You can also use lstopo to display the architecture and display the NUMA cores. This is another example:
innodb_page_cleaners
| Previous Value: | 4 |
| New Value (8.4 LTS): | innodb_buffer_pool_instances |
The new default is to use as many threads to flush dirty pages from buffer pool instances as there are buffer pool instances.
innodb_parallel_read_threads
| Previous Value: | 4 |
| New Value (8.4 LTS): | logical processors / 8 (min 4) |
For performance reason, on systems with a large amount of logical CPUs, the number of threads used for parallel clustered index reads is automatically increased.
innodb_purge_threads
| Previous Value: | 4 |
| New Value (8.4 LTS): | 1 if logical processors <= 16 else 4 |
This variable is somehow also auto configured for systems with a large amount (>=16) of vCPUs. But we also realised that having 4 purge threads can be problematic on some smaller systems. For such system, we reduced the default value to 1.
innodb_read_io_threads
| Previous Value: | 4 |
| New Value (8.4 LTS): | logical processors / 2 (min 4) |
This variable also increase automatically in case of the system has more than 8 vCPUs.
innodb_use_fdatasync
| Previous Value: | OFF |
| New Value (8.4 LTS): | ON |
On systems supporting it, a fdatasync() call does not flush changes to file metadata unless required. This provides a performance benefit.
temptable_max_ram
| Previous Value: | 1 GB |
| New Value (8.4 LTS): | 3% of total memory (within a range of 1-4 GB) |
The default now auto-increases if the system benefits from a large amount of memory. But the default cap to 4GB. So for systems having more than 132GB of memory, by default the value of temptable_max_ram will be set to 4GB.
temptable_max_mmap
| Previous Value: | 1 GB |
| New Value (8.4 LTS): | 0 (disabled) |
The new default disables the allocation of memory from memory-mapped temporary files (no creation of files in tmpdir).
temptable_use_mmap
| Previous Value: | ON |
| New Value (8.4 LTS): | OFF |
When temptable_use_mmap is disabled (new default), the TempTable storage engine uses InnoDB on-disk internal temporary tables instead of allocating space for internal in-memory temporary tables as memory-mapped temporary files in the tmpdir when the amount of the TempTable storage engine exceeds the limit defined by the temptable_max_ram variable.
Conclusion
With this brand new version of MySQL, the very first LTS, we’ve had the chance to change the default values of certain InnoDB variables to bring them more into line with the reality of production servers.
Some are now auto-tuned to match better the system on which MySQL is running.
Enjoy MySQL and enjoy the new defaults!
hi Lefred,
I found the default value for innodb_dedicated_server is still OFF in MySQL 8.4 LTS.
Yes, you are right, and a colleague pointed it to me too yesterday.
I think this was a proposal that in the end, we didn’t choose. I’ll update the blog post according.
Cheers,