MySQL 8.4 LTS – new production-ready defaults for InnoDB 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:ONNew Value (8.4 LTS):OFF if MADV_DONTDUMP is supportedelse ONMADV_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 GNew Value (8.4 LTS):If BP <= 1 GB: 1If BP > 1 GB: then the minimum value in the range of 1-64 between:a. (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) / 2b. 1/4 of available logical processorsThe 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:allNew Value (8.4 LTS):noneChange 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:OFFNew Value (8.4 LTS):ONSince 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.



Since MySQL 8.4, innodb_dedicated_server configures the following variables:


innodb_buffer_pool_size128MB 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:ONNew Value (8.4 LTS):OFFAHI (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 * 2New Value (8.4 LTS):2Previously 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:433.226604Z 1 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 2.000000G, instances = 2, chunk size =128.000000M [...]2024-05-01T05:433.288068Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite2024-05-01T05:433.295917Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite2024-05-01T05:433.317319Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.bdblwr' for doublewrite2024-05-01T05:433.317398Z 1 [Note] [MY-013566] [InnoDB] Double write buffer files: 22024-05-01T05:433.317410Z 1 [Note] [MY-013565] [InnoDB] Double write buffer pages per instance: 1282024-05-01T05:433.317423Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite2024-05-01T05:433.317436Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite



We 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):128From 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:fsyncNew 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:200New Value (8.4 LTS):10000For 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 200New Value (8.4 LTS):2 * innodb_io_capacityIf 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 MBNew Value (8.4 LTS):64 MBWe 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:OFFNew Value (8.4 LTS):ONWhen 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 10



In 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:4New Value (8.4 LTS):innodb_buffer_pool_instancesThe 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:4New 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:4New Value (8.4 LTS):1 if logical processors <= 16else 4This 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:4New 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:OFFNew Value (8.4 LTS):ONOn 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 GBNew Value (8.4 LTS):3% of total memory (within a range of 1-4 GThe 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 GBNew Value (8.4 LTS) (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:ONNew Value (8.4 LTS):OFFWhen 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!
https://lefred.be/content/mysq....l-8-4-lts-new-produc