Variable's Day Out #11: large_pages

Properties:

Applicable To	MySQL/Linux
Server Startup Option	--large-pages
Scope	Global
Dynamic	No way
Possible Values	True|False flag
Default Value	False
Category	Performance, Administration

Description:

This option, currently available only for Linux, if set enables the usage of large pages by MySQL. Many operating systems/system architectures support optional memory pages bigger than the default size (4 KB). Usually the large page size is 2 MB, this can be checked in the large_page_size variable in MySQL.

For applications that do a lot of memory accesses, as MySQL can, reduced TLB (Translation Lookaside Buffer) misses lead to better performance. Also having large pages, makes MySQL buffers less susceptible of being swapped out.

When to Use?

Try to keep it enabled, I have never heard of any side effects (In other words if you find any do let me know, I will update the blog post :) ). Other things that can be considered are:

1. How much of swapping is happening on the system?
2. What are the other applications running on that system? Though it is a bad idea to run other heavy applications on the same box as MySQL (at least in production), but if you can't get rid of them, using large-pages will make MySQL avoid swapping.

How to configure?

Before large pages can be used on Linux, it is necessary to configure the HugeTLB memory pool.

1. Check your total memory (MemTotal), huge page size (Hugepagesize) etc and decide how many huge pages you want to run with. This information is available in /proc/meminfo file.
2. Set the number of huge pages to be used by the system. use sysctl -w vm.nr_hugepages = <value>
3. Ensure to set memlock for mysql user in /etc/security/limits.conf to allow this user using large memory.
4. Stop MySQL.
5. Restart your system.
6. Start MySQL server with large-pages option.

If nothing goes wrong, you should be all set and going.

Read more:

• MySQL manual entry on large-pages
• Swap file discussion @ mysqlperformanceblog.com
• Large page support in the Linux kernel
• Using Very Large Memory
• MySQL manual entry on large_page_size

 

Hope you enjoyed reading this.