Sunday, May 18, 2008

Variable's Day Out #11: large_pages


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


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:


Hope you enjoyed reading this.


Shlomo Priymak said...

Nice summary, this is a good starting point for this topic.

Perhaps you have or heard of a benchmark comparing huge pages with regular configuration?
I couldn't find it anywhere online...

Anonymous said...

Just a note to add to your instruction set. At least for Redhat you may also need to increase kernel.shmmax and kernel.shmall in /etc/sysctl.conf

It's listed in bytes so for 6GB should be something like:
kernel.shmall = 6442450944
kernel.shmmax = 6442450944

Anonymous said...

I think kernel.shmall is in pages and not in bytes so setting them to be the same it not right.

so 6gb of 2m pages should be:

kernel.shmall = 3145728