Skip navigation links

User Comments

Posted by John Engelke on February 21 2010 6:47am[Delete] [Edit]

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 .)

Posted by John Mancuso on November 8 2010 7:14pm[Delete] [Edit]

I had an issue where large pages were enabled however mysql was not using the whole chunk of large page memory allocated (approx 23 GB) so the server was swapping constantly. This was MySQL 5.1.49 using the new innodb plugin 1.0.10 (also applies to MySQL 5.5 and above most likely). The solution: the amount of large page memory you allocate should be > innodb_buffer_pool size.

I allocated 13,500 large pages for a 24 GB buffer pool and it works fine now.

Posted by Rainer Stumbaum on August 23 2011 12:05pm[Delete] [Edit]

Hi,

following system:
Debian 6 4CPU 20Gbyte RAM dedicated MySQL server

my.cnf:
...
innodb_buffer_pool_size = 12000M
innodb_additional_mem_pool_size = 16M
...

Therefore the following /etc/sysctl.d/mysql.conf
----
# Set the number of pages to be used:
# Add innodb_buffer_pool_size and
# innodb_additional_mem_pool_size
# and divide by Hugepagesize.
# Each page is normally 2MB, so a value of 6100 = 12200MB.
# This command actually allocates memory, so this much
# memory must be available.
# Important:
# ulimit -l unlimited
# and set in my.cnf:
# ...
# [mysqld]
# large-pages
# ...
vm.nr_hugepages = 6100

# Set the group number that is permitted to access this
# memory (110 in this case). The mysql user must be a
# member of this group.
vm.hugetlb_shm_group = 110

# Set the amount of shmem permitted per segment in bytes
# (12199Mb in this case).
kernel.shmmax = 12791578624

# Set the total amount of shared memory. The value
# is the size in pages. At 4KB/page, 3122944 = 12199MB.
kernel.shmall = 3122944
----

I added the ulimit to the startup script and added the large-pages in the mysqld section.

But I get the following error:
....
110823 13:41:00 mysqld_safe Starting mysqld daemon with databases from /mnt/mysql/data
110823 13:41:00 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
110823 13:41:00 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: HugeTLB: Warning: Failed to allocate 12582928384 bytes. errno 28
InnoDB HugeTLB: Warning: Using conventional memory pool
...

The existing samples are not very clear - I would like to know if my example is right and clear enough for calculation the size correctly.

Posted by Rainer Stumbaum on August 23 2011 12:20pm[Delete] [Edit]

Got it to work:
Instead of using the calculated 12200MB I used 15GB.
root@mysql04.dc1:~# ipcs

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x6c1009eb 0 zabbix 600 693504 8
0x00000000 32769 mysql 600 1279262720 1 dest
0x00000000 65538 mysql 600 12585009152 1 dest

root@mysql04.dc1:~# grep -i huge /proc/meminfo
HugePages_Total: 7680
HugePages_Free: 7394
HugePages_Rsvd: 6325
HugePages_Surp: 0
Hugepagesize: 2048 kB
root@mysql04.dc1:~#

So the calculation seeems somehow to be wrong...

Posted by DANGLADE JEAN-SEBASTIEN on November 13 2012 1:10am[Delete] [Edit]

I hope this comment will save severals hours and white nights on production launching...
After folowing every How-to and all's documentation over Google, to enable huge pages... i must give you this post.

For enabling huge pages with Linux Debian 6.0.5 on
Linux 2.6.32-5-amd64 #x86_64 GNU/Linux (64Bits)
and MySQL 5.1, you got to add this your /etc/sysctl.conf :

# Total of allowed memory
vm.nr_hugepages = YYYYYY
# total amount of memory that can be allocated to shared memory, huge pages or not, on the box
kernel.shmall = XXXXXXXXXX
# maximum single shared memory segment, which for me was basically innodb_buffer_pool+1%
kernel.shmmax = XXXXXXXXXX
# Groupe autorisé
vm.hugetlb_shm_group = `id -g mysql`

XXXXX is given by this script shell in bash :

##### SCRIPT START #########
#!/bin/bash
# keep 2go memory for system
# (i got 68Go on this one ans 128Go RAM on other one)
keep_for_system=2097152

mem=$(free|grep Mem|awk '{print$2}')
mem=$(echo "$mem-$marge"|bc)
totmem=$(echo "$mem*1024"|bc)
huge=$(grep Hugepagesize /proc/meminfo|awk '{print $2}')
max=$(echo "$totmem*75/100"|bc)
all=$(echo "$max/$huge"|bc)
echo "kernel.shmmax = $max"
echo "kernel.shmall = $all"
######### SCRIPT END #########

check memory usage before reboot by command :
cat /proc/meminfo | grep -i huge

Reboot your system.
and check memory usage again.

It works !
;-)