MySQL 8.4 LTS: New Production‑Ready Default Settings for InnoDB Variables

On 30 April 2024 MySQL 8.4, the first Long‑Term Support (LTS) release, was officially launched, confirming Oracle’s announced release cadence.

What is an LTS release? What other versions exist?

MySQL follows two main release tracks: LTS (Long‑Term Support) and Innovation. Both include bug and security fixes and are considered production‑grade.

When to choose an LTS release?

Need stable features and a longer support period.

Only the first LTS version removed features; later versions contain only necessary fixes.

LTS follows Oracle’s lifetime support policy (5 years mainstream, 3 years extended).

When to choose an Innovation release?

Desire the latest features and improvements, suitable for fast‑paced development and DBA environments with higher automation and CI.

Includes new features, code refactoring, removal of deprecated functionality, and stricter SQL‑standard compliance.

Supported until the next Innovation release.

MySQL version lifecycle

Refer to Oracle’s official MySQL version lifecycle plan for production version planning.

Changed InnoDB defaults in MySQL 8.4 LTS

Twenty InnoDB variables have new default values to better match real‑world workloads and hardware. Below are the variables, their previous defaults, and the new defaults.

innodb_buffer_pool_in_core_file

Version

Default

Pre‑8.4

ON

8.4 LTS

IF MADV_DONTDUMP supported THEN OFF ELSE ON

MADV_DONTDUMP is a Linux 3.4+ macro (found in sys/mman.h ) used by the non‑POSIX extension madvise() . It is not supported on Windows or most macOS versions.

innodb_buffer_pool_instances

Version

Default

Pre‑8.4

8 (1 if BP < 1 GB)

8.4 LTS

IF BP ≤ 1 GB: 1

IF BP > 1 GB: minimum of (BP/Chunk/2) and (¼ logical CPU)

The previous value of 8 could be too large on some systems; the new calculation follows the guidance in the InnoDB buffer‑pool sizing documentation.

innodb_change_buffering

Version

Default

Pre‑8.4

all

8.4 LTS

none

Change Buffer (CB) defers secondary‑index writes to enable sequential I/O; on modern hardware random I/O is less of a concern.

innodb_dedicated_server

Version

Default

Pre‑8.4

OFF

8.4 LTS

OFF

From MySQL 8.0 onward, when the server runs on dedicated resources, enabling this variable is recommended, and many other InnoDB settings are automatically tuned.

innodb_adaptive_hash_index

Version

Default

Pre‑8.4

ON

8.4 LTS

OFF

Disabling AHI avoids performance regressions on write‑heavy or high‑load workloads; it is generally recommended for predictable response times.

innodb_doublewrite_files

Version

Default

Pre‑8.4

buffer_pool_instances × 2

8.4 LTS

2

The previous calculation was based on the number of buffer‑pool instances; the new default simplifies to a constant of 2.

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.317436Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite

When innodb_doublewrite is set to DETECT_ONLY , only metadata is written to the double‑write buffer; page data is not, which is useful for detecting incomplete writes.

innodb_flush_method

Version

Default

Pre‑8.4

fsync

8.4 LTS

O_DIRECT (or fsync if unsupported)

O_DIRECT bypasses the OS cache, flushing changes directly to disk, which is preferred when supported.

innodb_io_capacity & innodb_io_capacity_max

Version

Default

innodb_io_capacity Pre‑8.4

200

innodb_io_capacity 8.4 LTS

10000

innodb_io_capacity_max Pre‑8.4

2 × innodb_io_capacity (min 2000)

innodb_io_capacity_max 8.4 LTS

2 × innodb_io_capacity

The higher defaults reflect modern SSD/RAID capabilities, preventing the I/O capacity limit from throttling performance.

Other notable changes

innodb_log_buffer_size increased from 16 MB to 64 MB.

innodb_numa_interleave default changed from OFF to ON on NUMA‑aware systems.

innodb_page_cleaners now defaults to the same number as buffer‑pool instances.

innodb_parallel_read_threads defaults to logical‑CPU / 8 (minimum 4).

innodb_purge_threads defaults to 1 on systems with ≤ 16 logical CPUs, otherwise 4.

innodb_read_io_threads defaults to logical‑CPU / 2 (minimum 4).

innodb_use_fdatasync switched from OFF to ON where supported.

Temporary table defaults: temptable_max_ram now 3 % of total memory (capped at 4 GB); temptable_max_mmap disabled; temptable_use_mmap switched OFF.

Summary

The first MySQL LTS release brings a set of production‑ready default changes to InnoDB variables, many of which automatically adapt to the underlying hardware and workload, simplifying configuration and improving performance for typical production servers.

Enjoy MySQL with its new defaults!