January 2015
« Dec   Feb »

How do I enable huge TLB on MySQL server

To get maximum hardware acceleration via reducing Tranaction Lockside Buffer (TLB) ie
Applications that perform a lot of memory accesses may obtain performance improvements by using large pages due to reduced Translation Lookaside Buffer (TLB) misses.

Here is the quick guide to setup this one for a single core 3.7G RAM AWS instance for the testing.

Server configuration
a. CPU : 1 Core
b. RAM : 3.7G
c. Storage : 50G SSD drive
d. MySQL reserved RAM for innodb_buffer_pool_size : 2G

1. setting up vm.nr_hugepages

First you need to identify the “Huge Pages” which your Operating system currently support.

[root@db01 ~]# grep Hugepagesize /proc/meminfo
Hugepagesize:       2048 kB
[root@db01 ~]#

The output shows that the size of a Huge Page on this system is 2MB. This means if a 2GB Huge Pages pool ( in my scenario, I calculated it for 2.2G) should be allocated, then 1200 (2.2*1024/2) Huge Pages need to be allocated. The number of Huge Pages can be configured and activated by setting nr_hugepages in the proc file system.

Since you are reserving 2GB RAM for innodb-buffer-pool-size, you may needed to consider more RAM while you design vm.nr_hugepages page ie more than 2GB here I choose 2.2G RAM for

ie I’m planning to use innodb-buffer-pool-size to 2GB. So while I’m considering “vm.nr_hugepages” calculation , I increase the RAM count to 2.2G ie 1200 instead of 1024 (2*1024/2) for exact 2GB value)

Now you need to add this value permanent on Linux by

# echo "vm.nr_hugepages=1200" >> /etc/sysctl.conf

To verify

[root@db01 ~]# grep HugePages_Total /proc/meminfo
HugePages_Total:    1200
[root@db01 ~]#

2. Setting up MEMLOCK value

Now you need to set how much memory space this ‘mysql’ user can occupy on this server. Note that Huge Pages are locked in physical memory.The memlock setting is specified in KB and must match the memory size of the number of Huge Pages that mysql should be able to allocate.

# grep Hugepagesize /proc/meminfo
Hugepagesize:     2048 kB

Calculation is vm.nr_hugepages * Hugepagesize ie 1200 X Hugepagesize

ie 1200*2048 =2457600

Open the file /etc/security/limits.conf and add the following line.
mysql soft memlock 2457600
mysql hard memlock 2457600

But I would suggest you to set this value to unlimited to avoid unwanted issues.

[root@db01 ~]# grep "mysql" /etc/security/limits.conf
mysql           soft    memlock         unlimited
mysql           hard    memlock         unlimited
[root@db01 ~]#
[root@db01 ~]# grep HugePages_Free /proc/meminfo
HugePages_Free:     1200
[root@db01 ~]#

3. Authorizing HugeTLB group (vm.hugetlb_shm_group) to access the reserve RAM

You may need to identify the GID of mysql user and add that GID in “/etc/sysctl.conf” file.

[root@db01 ~]# cat /etc/passwd | grep "mysql"
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
[root@db01 ~]#

Note down the 4th colum. to get the GID of MySQL user.

Now add this GID in /etc/sysctl.conf file

[root@db01 ~]# cat /etc/sysctl.conf | grep "27"
[root@db01 ~]#

5. Setting up SHMMAX in /etc/sysctl.conf file

System memory 3.7GB
Linux Kernel 1.2GB
Memory for MySQL 2.5GB (System memory – Linux Kernel)

SHMMAX value should be greater than the value of SGA we given. ie Here we are giving innodb_buffer_pool size is to 2G +300Mb (this storage will be the total size of query _cache_size, table_cache kind of memory consumed for mysql process). So SHMMAX should be greater than 2G+300Mb value. So I set this value to 2.5G. So we will not see the error like “out of memory” anymore.

SHMMAX is the total memory should be allocated for single process. This is the system level limit and we will be setting MySQL to use fixed memory via buffer_pool_size and set the vm.pages (application level) accordingly.

This will be the Mysql allocated memory in bytes
2.5 RAM X 1K X 1K x 1K

2.5 *1024*1024*1024 = 2684354560

[root@db01 ~]# grep "kernel.shmmax" /etc/sysctl.conf
kernel.shmmax = 2684354560

4. Setting up SHMALL Value in /etc/sysctl.conf
we can assign SHMALL the value based on the value of SGA (reserved memory for Database) ie (SHMMAX/4096). But… SHMALL is measured in memory pages not bytes. We need to divide the 2.7GB by the systems page size value. It’s almost always 4K which is the recommended size, but to be safe, you run the following command:

[root@db01 ~]# getconf PAGE_SIZE
[root@db01 ~]#

To calculate SHMALL is,

a. 2.7G x 1K x 1K x 1K gives me 2.5 GB value in bytes ie 2.5 *1024*1024*1024 = 2684354560
b. Total Memory in bytes/PAGE_SIZE ie 2684354560/4096 = 655360

[root@db01 ~]# grep “kernel.shmall” /etc/sysctl.conf
kernel.shmall = 655360
[root@db01 ~]#

6. Apply the new kernel parameter in Linux system

[root@db01 ~]# sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 2684354560
kernel.shmall = 655360
vm.nr_hugepages = 1200
vm.hugetlb_shm_group = 27
[root@db01 ~]#

7. Enable huge TLB on MySQL server config.

Now you need to add “large-pages” under “mysqld” directive inside in “my.cnf” and restart the database service.

user                           = mysql
default-storage-engine         = InnoDB
socket                         = /home/mysqldata/mysql.sock
pid-file                       = /home/mysqldata/

Restarting Mysql service

[root@db01 ~]# service mysqld  start
Starting mysqld:                                           [  OK  ]
[root@db01 ~]

Once you find everything working, you will see a normal logs like this

150129 13:29:57 [Note] Plugin ‘FEDERATED’ is disabled.
150129 13:29:57 InnoDB: The InnoDB memory heap is disabled
150129 13:29:57 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150129 13:29:57 InnoDB: Compressed tables use zlib 1.2.7
150129 13:29:57 InnoDB: Using Linux native AIO
150129 13:29:57 InnoDB: Initializing buffer pool, size = 2.0G
150129 13:29:57 InnoDB: Completed initialization of buffer pool
150129 13:29:57 InnoDB: highest supported file format is Barracuda.
150129 13:29:58 InnoDB: Waiting for the background threads to start
150129 13:29:59 InnoDB: 5.5.40 started; log sequence number 1595675

If there is any problem you will get error or MySQL will skip the huge TLB features and it’s logging like this “InnoDB HugeTLB: Warning: Using conventional memory pool”

Following are the common errors when you receive if the,SHMMAX or SHMALL variables are incorrectly configured.

50129 12:33:59 InnoDB: The InnoDB memory heap is disabled
150129 12:33:59 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150129 12:33:59 InnoDB: Compressed tables use zlib 1.2.7
150129 12:33:59 InnoDB: Using Linux native AIO
150129 12:33:59 InnoDB: Initializing buffer pool, size = 2.0G
InnoDB: HugeTLB: Warning: Failed to allocate 2197815296 bytes. errno 12
InnoDB HugeTLB: Warning: Using conventional memory pool
InnoDB: mmap(2197815296 bytes) failed; errno 12
150129 12:33:59 InnoDB: Completed initialization of buffer pool
150129 12:33:59 InnoDB: Fatal error: cannot allocate memory for the buffer pool
150129 12:33:59 [ERROR] Plugin ‘InnoDB’ init function returned error.
150129 12:33

Note : Based on my experience, you would need to calculate more which is more than “innodb-buffer-pool-size” value declare in my.cnf file. Eg: if you want to use 10G for innodb-buffer-pool-size and you may needed to calculate no. of which is should be more than 10G. I would prefer 12GB in this scenario


Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>