Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :-

Monday, September 30, 2013

SGA & Hugepages Myth

I'd like to shed some light on how Hugepages works and its relation to SGA in order to eliminate some confusion you might have.

1- These 2 are not related, no need for huegapages to be set, but it's advisable to set it for better memory uil
2- If hugepages is set, then the value is affected by the SGA size
3- If SGA is changing(increase/decrease) , hugepages must be recalculated  accordingly.

Let me illustrate it with an example

 _*Case 1 -- Hugepages not set*_

    * cat  /proc/meminfo and if HugePages_Total = 0 , this indicates that the hugepages not set
    *  Suppose we want to set the hugepages for 5GB SGA, do the following:

         o 1st we must convert the 5GB to MB, so 5*1024 which equals to 5120MB
         o Divide the above value by 2, so 5120/2 = 2560
         o Add 3% (for over head)to the above value, so 2560+85(3%)= 2645 which is value of hugepages
         o Set  vm.nr_hugepages = 2645 in /etc/sysctl.conf

 _*Case 2 - SGA size changed*_

    * If the SGA is changed(buffer cache or shared_pool), then we need to adjust the hugepages accordingly as follows:

    * Suppose we're increasing the SGA by 2GB (from 5GB to 7GB), so we  must increase the hugepages by 2 GB or set it to 7GB if it's not set;

    * Let's assume that the current value is set correctly and we just need to increase it by 2 GB; then we do:

         o Convert the 2GB to MB, so 2*1024 which equals to 2048MB
         o Divide the above value by 2, so 2048/2 = 1024 and add 3%
         o Add the above value to he current hugepages value, so  2645+1054= 3799 which is the new value of hugepages
         o Set vm.nr_hugepages = 3749 in /etc/sysctl.conf

And remember that you must always check the value of kernel.shmmax  & kernel.shmall  in /etc/sysctl.conf
Please refer to the below ****reference table on how to set the value for these parameter:

SGA(GB) kernel.shmmax kernel.shmall
1 1107296256   270336
2 2214592512   540672
4 4429185024   1081344
6,8 8858370048 3282294
16 17716740096 4325376
32 35433480192 8650752

*** This is only a reference table

Sample shell script below does the above explained method for you in a minute (Good News!)

# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.

# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`

# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk {'print $2'}`

# Start from 1 pages to be on the safe side and guarantee 1 free HugePage

# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | awk {'print $5'} | grep "[0-9][0-9]*"`
   MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
   if [ $MIN_PG -gt 0 ]; then
      NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`

# Finish with results
case $KERN in
   '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
          echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
   '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Unrecognized kernel version $KERN. Exiting." ;;

# End