Some hardware and operating system architectures support memory pages greater than the default (usually 4KB). The actual implementation of this support depends on the underlying hardware and operating system. Applications that perform a lot of memory accesses may obtain performance improvements by using large pages due to reduced Translation Lookaside Buffer (TLB) misses.
In MySQL, large pages can be used by
InnoDB, to allocate memory for
its buffer pool and additional memory pool.
Standard use of large pages in MySQL attempts to use the
largest size supported, up to 4MB. Under Solaris, a
“super large pages” feature enables uses of pages
up to 256MB. This feature is available for recent SPARC
platforms. It can be enabled or disabled by using the
MySQL also supports the Linux implementation of large page support (which is called HugeTLB in Linux).
Before large pages can be used on Linux, the kernel must be
enabled to support them and it is necessary to configure the
HugeTLB memory pool. For reference, the HugeTBL API is
documented in the
Documentation/vm/hugetlbpage.txt file of
your Linux sources.
The kernels for some recent systems such as Red Hat Enterprise Linux may have the large pages feature enabled by default. To check whether this is true for your kernel, use the following command and look for output lines containing “huge”:
$> grep -i huge /proc/meminfo HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 4096 kB
The nonempty command output indicates that large page support is present, but the zero values indicate that no pages are configured for use.
If your kernel needs to be reconfigured to support large
pages, consult the
Assuming that your Linux kernel has large page support
enabled, configure it for use by MySQL using the following
commands. Normally, you put these in an
rc file or equivalent startup file that
is executed during the system boot sequence, so that the
commands execute each time the system starts. The commands
should execute early in the boot sequence, before the MySQL
server starts. Be sure to change the allocation numbers and
the group number as appropriate for your system.
# Set the number of pages to be used. # Each page is normally 2MB, so a value of 20 = 40MB. # This command actually allocates memory, so this much # memory must be available. echo 20 > /proc/sys/vm/nr_hugepages # Set the group number that is permitted to access this # memory (102 in this case). The mysql user must be a # member of this group. echo 102 > /proc/sys/vm/hugetlb_shm_group # Increase the amount of shmem permitted per segment # (12G in this case). echo 1560281088 > /proc/sys/kernel/shmmax # Increase total amount of shared memory. The value # is the number of pages. At 4KB/page, 4194304 = 16GB. echo 4194304 > /proc/sys/kernel/shmall
For MySQL usage, you normally want the value of
shmmax to be close to the value of
To verify the large page configuration, check
/proc/meminfo again as described
previously. Now you should see some nonzero values:
$> grep -i huge /proc/meminfo HugePages_Total: 20 HugePages_Free: 20 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 4096 kB
The final step to make use of the
hugetlb_shm_group is to give the
mysql user an “unlimited”
value for the memlock limit. This can be done either by
/etc/security/limits.conf or by
adding the following command to your
ulimit -l unlimited
Adding the ulimit command to
mysqld_safe causes the
root user to set the memlock limit to
unlimited before switching to the
mysql user. (This assumes that
mysqld_safe is started by
Large page support in the MySQL server is disabled by default.
To enable it, start the server with
--large-pages. You can also do
so by adding the following line to the
[mysqld] section of the server
With this option,
InnoDB uses large pages
automatically for its buffer pool and additional memory pool.
InnoDB cannot do this, it falls back to
use of traditional memory and writes a warning to the error
log: Warning: Using conventional memory
You can verify that MySQL is now using large pages by checking
/proc/meminfo again after restarting
mysqld, like this:
$> grep -i huge /proc/meminfo HugePages_Total: 20 HugePages_Free: 20 HugePages_Rsvd: 2 HugePages_Surp: 0 Hugepagesize: 4096 kB