Using Huge Pages for MySQL on Linux

1. Why huge pages matter for large buffer pools

The Linux kernel manages memory by default in pages of 4 kilobytes. For a 32 gigabyte InnoDB buffer pool, that theoretically means more than eight million individual page table entries that the CPU's memory management unit has to manage and cache in the translation lookaside buffer (TLB). The TLB only has room for a few thousand entries, so with that many small pages, TLB misses occur constantly, each forcing an additional, slower memory access to the page table in RAM.

Huge pages solve this problem by managing memory in significantly larger blocks, typically 2 megabytes instead of 4 kilobytes per page on x86_64. For the same 32 gigabyte buffer pool, the number of page table entries drops to roughly 16,000, which drastically reduces TLB misses. For a Magento database server with a large buffer pool, this reduction can mean measurably lower query execution latency, especially for workloads with many random memory accesses across the entire buffer pool.

The most important point that many administrators overlook: there are two completely different huge pages implementations on Linux, and only one of them is suitable for MySQL. This article shows how to correctly set up huge pages for the InnoDB buffer pool, and why the kernel's automatic alternative can produce exactly the opposite of the desired effect.

2. Transparent Huge Pages: the common misunderstanding

Transparent Huge Pages (THP) is a kernel feature that automatically and transparently combines large memory pages for every application, without the application itself knowing anything about it. That sounds at first like exactly what MySQL needs, but in practice THP regularly causes problems on database workloads. The reason lies in the so called khugepaged kernel thread, which constantly tries to merge small pages into large ones in the background (compaction) and briefly locks memory regions while doing so.

For MySQL with a large, constantly active buffer pool, this compaction activity causes sporadic but clearly noticeable latency spikes, which show up in monitoring tools as short, irregular delays without any recognizable cause in the database log. These huge pages related latency spikes are so widespread that the official MySQL documentation explicitly recommends disabling THP on database servers, while classic huge pages via hugetlbfs should be actively used instead.

3. Permanently disabling Transparent Huge Pages

The status of THP is controlled via the virtual files under /sys/kernel/mm/transparent_hugepage/. Setting enabled to never disables THP completely for new memory allocations, while setting defrag to never additionally prevents the kernel from trying to defragment existing memory regions and convert them into huge pages during runtime.

A change via echo only applies until the next reboot, so a production configuration needs a systemd service that reliably reapplies this setting on every boot, before MySQL itself starts. Without this automation, the server falls back to the THP default after every kernel update or reboot, often unnoticed until the next latency spike occurs.

# Check current THP status (active value is in square brackets)
cat /sys/kernel/mm/transparent_hugepage/enabled
# [always] madvise never

# Disable THP immediately (temporary, lost on reboot without a service)
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

# /etc/systemd/system/disable-thp.service
[Unit]
Description=Disable Transparent Huge Pages before MySQL starts
Before=mysql.service
DefaultDependencies=no

[Service]
Type=oneshot
ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/enabled'
ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/defrag'
RemainAfterExit=yes

[Install]
WantedBy=basic.target

# Enable with: systemctl daemon-reload && systemctl enable --now disable-thp.service

4. Configuring hugetlbfs and vm.nr_hugepages

While THP works transparently and automatically, classic huge pages must be explicitly reserved via hugetlbfs before an application can use them. The sysctl parameter vm.nr_hugepages defines how many 2 megabyte pages the kernel permanently carves out of the general memory pool and reserves for exclusive use by huge pages capable applications. This reservation automatically reduces the regular memory available to other processes accordingly, which must be taken into account when sizing the server.

A critical pitfall: the reservation only works reliably right after a reboot, when memory is still lightly fragmented. If vm.nr_hugepages is increased on an already long running server, the kernel may not be able to find enough contiguous physical 2 megabyte blocks, and the reservation partially fails without producing a clear error message.

# /etc/sysctl.d/60-hugepages.conf
# Reserve 16400 x 2MB = ~32GB for the InnoDB buffer pool
vm.nr_hugepages = 16400

# Apply immediately (best right after boot, before memory fragments):
# sysctl -p /etc/sysctl.d/60-hugepages.conf

# Verify the actual reservation succeeded
grep -i huge /proc/meminfo
# HugePages_Total:   16400
# HugePages_Free:    16400
# Hugepagesize:       2048 kB

5. Setting up MySQL for large_pages

For MySQL to actually use the reserved huge pages, the MySQL configuration option large_pages = 1 must also be set. Without this option, MySQL completely ignores the available huge pages and still allocates the buffer pool with regular 4 kilobyte pages, even if vm.nr_hugepages is correctly configured. In addition, the MySQL OS user must be a member of the group granted access to the reserved pages via vm.hugetlb_shm_group.

After restarting MySQL with the large_pages option enabled, the MySQL error log explicitly shows whether the huge pages could be used successfully. An error message at this point usually indicates that either not enough huge pages were reserved, or that the permissions between the hugetlbfs mount and the MySQL process do not match correctly.

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
large_pages = 1
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 8

# Allow the mysql OS user to access reserved huge pages
# /etc/sysctl.d/60-hugepages.conf (append)
echo 'vm.hugetlb_shm_group = 121' | sudo tee -a /etc/sysctl.d/60-hugepages.conf
# 121 = gid of the 'mysql' group, verify with: getent group mysql

sysctl -p /etc/sysctl.d/60-hugepages.conf
systemctl restart mysql

# Confirm success in the error log
grep -i "large page" /var/log/mysql/error.log

6. Calculating the right number of huge pages

The number of huge pages to reserve must be at least equal to the configured innodb_buffer_pool_size, plus an additional buffer for other internal MySQL memory structures that can also use huge pages. Calculating too tightly means MySQL can only allocate part of the buffer pool with huge pages at startup, which in practice is often worse than not using huge pages at all, because part of the memory then ends up managed through the slower standard path.

The formula is: innodb_buffer_pool_size in megabytes, divided by 2 (megabytes per huge page), plus roughly a 5 percent safety margin for internal MySQL structures such as the adaptive hash index and log buffers. For a 32 gigabyte buffer pool, that works out to 32768 MB divided by 2 MB, or 16384 huge pages, rounded up with the safety margin to the 16400 shown earlier.

7. Validating activation and spotting errors

After restarting MySQL, /proc/meminfo shows under HugePages_Free how many of the reserved huge pages are actually still unused. If this value drops noticeably after MySQL starts, that matches the expected usage by the buffer pool. If HugePages_Free instead stays nearly unchanged at almost the total value, MySQL did not use the huge pages despite the configuration, usually due to missing permissions or an incorrectly set vm.hugetlb_shm_group.

The MySQL error log is the most reliable source for confirming actual huge pages usage. An explicit message about successful usage or a fallback to regular pages is logged at startup, so a regular look at this log after every MySQL restart or server reboot is part of solid operational practice.

8. Common mistakes in huge pages setups

The most common mistake is not disabling THP, assuming classic huge pages and Transparent Huge Pages mutually exclude each other or that THP would automatically step aside once hugetlbfs is configured. Both mechanisms work independently of each other, and active THP can continue to cause latency spikes for every other process on the server in parallel with configured classic huge pages, even if MySQL itself correctly uses classic huge pages.

A second common mistake is setting vm.nr_hugepages on a server with heavily fragmented memory while already running, instead of performing the reservation right after a reboot. The reservation then partially fails without giving the administrator a clear error message immediately, and the buffer pool starts with fewer huge pages than actually intended, partially or completely negating the expected performance gain.

9. THP vs. classic huge pages compared

The table below compares the two huge pages mechanisms on Linux and shows why there is a clear recommendation for MySQL.

For production MySQL servers with a large InnoDB buffer pool, the clear recommendation is: disable Transparent Huge Pages, reserve classic huge pages via hugetlbfs, and point MySQL explicitly to this reservation with large_pages = 1. This combination delivers the TLB benefit without the latency risks of automatic compaction.

10. Summary

Huge pages significantly reduce TLB misses for large InnoDB buffer pools by drastically lowering the number of page table entries that need to be managed. The decisive difference lies between the automatic but latency prone Transparent Huge Pages feature and the explicit, hugetlbfs reserved classic huge pages. For MySQL, the clear recommendation is: disable THP, configure hugetlbfs with sufficient headroom, and explicitly instruct MySQL to use this reservation via large_pages = 1.

The reservation should be made right after a reboot, while memory is still lightly fragmented, and a systemd service should reliably disable THP on every boot before MySQL starts. Anyone who sets up this huge pages configuration correctly and validates it via /proc/meminfo and the MySQL error log gains measurable TLB efficiency without the risk of sporadic latency spikes.

11. FAQ: Using Huge Pages for MySQL on Linux