Can Huge Pages Boost MySQL Performance? A Practical Guide and Test Results

1. Introduction

Recently I have been doing performance testing and received questions about whether Youzan's database servers have huge pages enabled. I heard huge pages can improve performance, so this article explores the topic. Readers only interested in the conclusion can skip to the end.

2. Huge Page Overview

2.1 Why Huge Pages

On Linux, applications with large memory demands use a default 4 KB page size, which can cause many TLB misses and page‑fault interrupts, severely impacting performance. Using larger pages (e.g., 2 MB) reduces the number of TLB entries and page‑faults, dramatically improving performance.

2.2 Advantages of Huge Pages

Increase TLB hit rate

Pages are locked in memory, reducing swapping

Lock memory to avoid performance loss from allocation/release

Improve overall memory performance and lower CPU load

3. Enabling Huge Pages for MySQL

3.1 Calculate required number of huge pages

First compute the memory MySQL will use plus 10%:

S = (query_cache_size + table_open_cache + innodb_buffer_pool_size + innodb_log_file_size + performance_schema.memory) + 10 %

Then the number of huge pages is:

vm.nr_hugepages=S/2M

3.2 Set MySQL user group for huge pages

Find MySQL's group ID:

# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)

Allow the group to allocate huge pages:

echo 27 > /proc/sys/vm/hugetlb_shm_group

3.3 Give MySQL unlimited memlock

Add the following lines to /etc/security/limits.conf:

@mysql soft memlock unlimited
@mysql hard memlock unlimited

3.4 Enable large pages in MySQL

In my.cnf under [mysqld] add:

large_pages=1

3.5 Verify huge page usage

# cat /proc/meminfo | grep Huge
AnonHugePages:   1294336 kB
HugePages_Total: 5834
HugePages_Free:  1394
HugePages_Rsvd:   921
HugePages_Surp:     0
Hugepagesize:    2048 kB

If HugePages_Free is less than HugePages_Total, huge pages are being used.

3.6 Possible startup issues

Common warnings when huge pages are insufficient:

[Warning] InnoDB: Failed to allocate 140509184 bytes. errno 12
[Warning] InnoDB: Using conventional memory pool

These usually indicate that nr_hugepages is too small or the memlock limits are not set correctly.

3.7 Performance test results

Test environment: CentOS 7.10, kernel 3.10. Two MySQL instances were run, one with huge pages enabled and one without.

Result: No noticeable performance difference, contrary to some online claims.

4. Conclusion

Based on official documentation and my own testing, the steps to enable huge pages are correct, but the performance impact is not consistent; some users may see gains while others do not. Feel free to share your own results for further discussion.