Some hardware/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.
Currently, MySQL supports only 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 kernel for some recent systems such as Red Hat Enterprise Linux appear to 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”:
shell> cat /proc/meminfo | grep -i huge
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 hugetlbpage.txt
file for
instructions.
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 allowed 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 allowed 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
shmall
.
To verify the large page configuration, check
/proc/meminfo
again as described
previously. Now you should see some nonzero values:
shell> cat /proc/meminfo | grep -i huge
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 by done either by editing
/etc/security/limits.conf
or by adding the
following command to your mysqld_safe script:
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
root
.)
Large page support in MySQL is disabled by default. To enable
it, start the server with the
--large-pages
option. For
example, you can use the following lines in your server's
my.cnf
file:
[mysqld] large-pages
With this option, InnoDB
uses large pages
automatically for its buffer pool and additional memory pool. If
InnoDB
cannot do this, it falls back to use
of traditional memory and writes a warning to the error log:
Warning: Using conventional memory pool
To verify that large pages are being used, check
/proc/meminfo
again:
shell> cat /proc/meminfo | grep -i huge
HugePages_Total: 20
HugePages_Free: 20
HugePages_Rsvd: 2
HugePages_Surp: 0
Hugepagesize: 4096 kB
User Comments
An error will always generate on initial startup of mysqld since the 'mysql_install_db' script is run necessarily prior to 'mysqld_safe' in the daemon startup script. In order to allow the initialization of the database using huge page support, ease the memlock limit by typing 'ulimit -l unlimited' as root at the command prompt before either starting mysqld or executing mysql_install_db. This is in addition to adding it to the mysqld_safe script.
Also, choose values wisely since I believe that the 'nr_hugepages' parameter will actually reserve physical memory. At 2MB per page that means a value of 512 will reserve 1GB.
If you are getting the error 22 or 12 at initialization, both of the above tips should help immensely. (See also http://time.to.pullthepl.ug/story/2008/11/18/9/-MySQL-Large-Pages-errors .)
Add your own comment.