HugePages Calculator for Oracle

Updated Code 2/5/13 (Note: I’m finding much of the code was masked because of html issue, hopefully this one will work. Please let me know if you don’t have success downloading and compiling)

I recently had to configure a machine for hugepages and like some other stories I’ve heard it didn’t go perfectly. There is much material on how to set it up and many have different approaches. Coming from an engineering background, my profs would be marking many wrong for missing units. I went to Enkitec’s blog by Rick Miners and many of the Oracle Notes. Thought I understood it perfectly only to find I needed to do a bit more work.

Since I’m generally a bit of a lazy DBA, I know that good notes and perhaps a bit more will be helpful for the next time. Here’s my to attempt to create a calculator to do the simple calculations for the next time. Some of the calculators calculate the hugepages for existing databases. This one would be for those not created yet, or not yet configured. Yes it is PL/SQL and needs a database to run. A copy of Oracle XE on my laptop for things like this.

I compared it to some other calculators and got some different results. At first I was a little upset that my perfect calculator (or imperfect calculator) didn’t match exactly. But once I looked a little more I think the differences are from the different requirements in OS’s and goals of those making the settings. Let me know what you think.

I’ve tried to put enough in the settings so that it can be tweaked to match different approaches. Try changing the constants to fit your approach, OS, etc.

-- Start of DDL Script for Package ORADBA.PKG_HUGEPAGE_UTIL


CREATE OR REPLACE PACKAGE pkg_hugepage_util
IS
    /*
    Purpose: To keep notes and huge page caluclators in one place

    Notes:
    Check this out for calculating hugepages for existing DB's

    Jeffrey M. Hunter Site:

    http://www.idevelopment.info/data/Oracle/DBA_scripts/UNIX_Shell_Scripts/hugepages_settings.sh

    Rick Miner

    http://blog.enkitec.com/2012/07/hugepages-configuration-and-monitoring/

    Files:
    /etc/sysctl.conf
    /etc/security/limits.conf

    Commands:
    cat /proc/meminfo

    Oracle Note ID's:
    15566.1 - Tech: Unix Semaphores and Shared Memory Explained
    361468.1 - Hugepages on Oracle Linux 64-bit
    461662.1 - Issues when not using Hugepages
    567506.1 - Maximum SHMMAX values for Linux x86 and X86-64
    361323.1 - Hugepages on Linux: What it is ... and What it is Not
    30793.1 - Pre-Page SGA
    803238.1 - Oracle not Utilizing Hugepages

    MODIFICATION HISTORY
    Person Date Comments
    --------- ------ -------------------------------------------
    dcox 11/14/2012 Initial Build
    dcox 1/14/2013  modified p_hugepage_parms
    dcox 1/16/2013  modified p_hugepage_parms
    dcox 1/22/2013  modified p_hugepage_parms

    */

    FUNCTION fn_parse_byte_units(p_char_value IN VARCHAR2, p_byte_value IN OUT NUMBER)
        RETURN NUMBER;

    PROCEDURE p_hugepage_parms(p_physical_memory   IN     VARCHAR2
                              , -- physical memory with K,M,G or no suffixes
                               p_max_sga_size      IN     VARCHAR2
                              , -- target max sga size for single DB - with M,G suffixes - assumes G if no trailing letter
                               p_total_sga_size    IN     VARCHAR2
                              , -- total of all SGA's on the machine
                               p_no_of_dbs         IN     INTEGER
                              , -- number of databases
                               p_hugepages         IN OUT NUMBER
                              , -- for vm.nr_hugepages in /etc/sysctl.conf
                               p_shmmax            IN OUT NUMBER
                              , -- kernel.shmmax in /etc/sysctl.conf
                               p_shmall            IN OUT NUMBER
                              , -- kernel.shmall in /etc/sysctl.conf - same as memlock value/pagesize in bytes
                               p_soft_hard_limit   IN OUT NUMBER -- /etc/security/limits.conf - oracle soft/hard - this is the same for both hard and soft
                                                                );
END pkg_hugepage_util; -- Package spec pkg_hugepage_util
/

CREATE OR REPLACE PACKAGE BODY pkg_hugepage_util
IS
    /*
    Purpose: To keep notes and huge page caluclators in one place

    Notes:
    Check this out for calculating hugepages for existing DB's

    Jeffrey M. Hunter Site:

    http://www.idevelopment.info/data/Oracle/DBA_scripts/UNIX_Shell_Scripts/hugepages_settings.sh

    Rick Miner

    http://blog.enkitec.com/2012/07/hugepages-configuration-and-monitoring/

    Files:
    /etc/sysctl.conf
    /etc/security/limits.conf

    Commands:
    cat /proc/meminfo

    Oracle Note ID's:
    15566.1 - Tech: Unix Semaphores and Shared Memory Explained
    361468.1 - Hugepages on Oracle Linux 64-bit
    461662.1 - Issues when not using Hugepages
    567506.1 - Maximum SHMMAX values for Linux x86 and X86-64
    361323.1 - Hugepages on Linux: What it is ... and What it is Not
    30793.1 - Pre-Page SGA
    803238.1 - Oracle not Utilizing Hugepages

    MODIFICATION HISTORY
    Person Date Comments
    --------- ------ -------------------------------------------
    dcox 11/14/2012 Initial Build
    dcox 1/14/2013  modified p_hugepage_parms
    dcox 1/16/2013  modified p_hugepage_parms
    dcox 1/22/2013  modified p_hugepage_parms
    */

    PROCEDURE p_hugepage_parms(p_physical_memory   IN     VARCHAR2
                              , -- physical memory with K,M,G or no suffixes
                               p_max_sga_size      IN     VARCHAR2
                              , -- target max sga size for single DB - with M,G suffixes - assumes G if no trailing letter
                               p_total_sga_size    IN     VARCHAR2
                              , -- total of all SGA's on the machine
                               p_no_of_dbs         IN     INTEGER
                              , -- number of databases
                               p_hugepages         IN OUT NUMBER
                              , -- for vm.nr_hugepages in /etc/sysctl.conf
                               p_shmmax            IN OUT NUMBER
                              , -- kernel.shmmax in /etc/sysctl.conf
                               p_shmall            IN OUT NUMBER
                              , -- kernel.shmall in /etc/sysctl.conf - same as memlock value/pagesize in bytes
                               p_soft_hard_limit   IN OUT NUMBER -- /etc/security/limits.conf - oracle soft/hard - this is the same for both hard and soft
                                                                )
    IS
        /*

        Purpose: Calculate Hugepage Parameters base on target SGA

        MODIFICATION HISTORY
        Person Ver Date         Comments
        --------- ---- ------   -------------------------------------------
        dcox 1.0 11/14/2012     Initial Build
        dcox 1.0.1 1/11/2013    Added dbms_output note p_soft_hard_limit for soft & hard limits
                                Fixed units on shmall
                                Increased OH from 3 to 7 pct
        dcox 1.0.2  1/16/13     Changed unites on memlock parameter to bytes
        dcox 1.0.3  1/22/13     Changed ouptput to make it easy to cut and paste

        */
        v_line_no INTEGER := 0; -- debug line no
        v_max_sga_size NUMBER; -- in MBytes
        v_total_sga_size NUMBER; -- in MBytes
        v_physical_memory NUMBER; -- in MBytes
        v_huge_page_oh NUMBER; -- overhead for huge pages
        v_additional_shmmax_pct_over INTEGER := 5; -- percent padding over hugepages for shmmax
        v_shmmax NUMBER; -- bytes shmmax raw value
        vc_shmmax_limiter_char CONSTANT VARCHAR2(10) := '4G'; -- character version SHMMAX LIMIT TO LEAVE ROOM FOR OS OR OTHER MEM with K,M,G or no suffixes
        vc_os_limiter_char CONSTANT VARCHAR2(10) := '3G'; -- character version OVERHEAD TO LEAVE FOR OS MEMORY or other MEM with K,M,G or no suffixes
        v_shmmax_limiter NUMBER; -- units mbytes limit to leave enough room for OS or other software- here for shmmax calc - may vary for each OS/hardware
        v_os_limiter NUMBER; -- units mbytes limit set to leave this much space for OS on all calculations - will override shmmax_limiter
        vc_memory_page_size CONSTANT INTEGER := 4096; -- "getconf PAGE_SIZE" to verify
        vc_huge_pages_oh_min CONSTANT INTEGER := 200; -- Adds to the hugepage calculation over the v_target_sga_size to leave a few pages empty - min overhead
        vc_huge_pages_oh_pct CONSTANT INTEGER := 7; -- pct of overhead for hugepages - added in to overall size for any overhead
        vc_huge_page_size CONSTANT INTEGER := 2048; -- units in K, NOTE: "grep Hugepagesize /proc/meminfo" to verify
        v_return NUMBER; -- error code returned
    BEGIN
        v_line_no := 100; -- debug line no
        -- Parse and check units ----------------

        -- Parse vc_shmmax_limiter_char
        v_return := fn_parse_byte_units(vc_shmmax_limiter_char, v_shmmax_limiter);
        DBMS_OUTPUT.put_line(SUBSTR('Value of v_shmmax_limiter=' || TO_CHAR(v_shmmax_limiter), 1, 255));

        -- Check for errors
        IF v_return < 0
        THEN
            raise_application_error(-20001, 'Error parsing vc_shmmax_limiter_char: ' || vc_shmmax_limiter_char);
        END IF;

        -- Parse vc_shmmax_limiter_char
        v_return := fn_parse_byte_units(vc_os_limiter_char, v_os_limiter);
        DBMS_OUTPUT.put_line(SUBSTR('Value of v_os_limiter=' || TO_CHAR(v_os_limiter), 1, 255));

        -- Check for errors
        IF v_return < 0
        THEN
            raise_application_error(-20001, 'Error parsing vc_shmmax_limiter_char: ' || vc_os_limiter_char);
        END IF;

        -----------
        -- Physical Size -- Translate to number

        v_return := fn_parse_byte_units(p_physical_memory, v_physical_memory);
        DBMS_OUTPUT.put_line(SUBSTR('Value of v_physical_memory=' || TO_CHAR(v_physical_memory), 1, 255));

        -- Check for errors
        IF v_return < 0
        THEN
            raise_application_error(-20001, 'Error parsing p_physical_memory: ' || p_physical_memory);
        END IF;

        DBMS_OUTPUT.put_line(SUBSTR('Value of v_physical_memory=' || TO_CHAR(v_physical_memory), 1, 255));
        v_line_no := 200; -- debug line no
        -----------

        -- MAX Target SGA -- translate to number
        v_return := fn_parse_byte_units(p_max_sga_size, v_max_sga_size);

        -- Check for errors
        IF v_return < 0
        THEN
            raise_application_error(-20001, 'Error parsing p_max_sga_size: ' || p_max_sga_size);
        END IF;

        v_line_no := 300; -- debug line no
        -----------

        -- Total Target SGA -- translate to number
        v_return := fn_parse_byte_units(p_total_sga_size, v_total_sga_size);

        -- Check for errors
        IF v_return < 0
        THEN
            raise_application_error(-20001, 'Error parsing p_total_sga_size: ' || p_total_sga_size);
        END IF;

        -- total must be equal or greater than max size
        IF v_total_sga_size > v_max_sga_size AND p_no_of_dbs = 1
        THEN
            raise_application_error(-20002, 'There is only 1 db and "total sga size" > max sga size". This is not correct.');
        END IF;

        -- check total memory size to limit later calculation on hugepages
        --IF v_physical_memory 1/2 physical then use sga+pct_over otherwise user 1/2 physical for reference
        IF (v_physical_memory / 2) < v_max_sga_size
        THEN
            v_shmmax := (v_max_sga_size + (v_max_sga_size * (v_additional_shmmax_pct_over / 100)));
            DBMS_OUTPUT.put_line(SUBSTR('Value of v_shmmax=' || TO_CHAR(v_shmmax), 1, 255));
        ELSE
            v_shmmax := v_physical_memory / 2;
        END IF;

        DBMS_OUTPUT.put_line(
            SUBSTR(
                   'Value of v_physical_memory - GREATEST(v_shmmax_limiter, v_os_limiter)='
                || TO_CHAR(v_physical_memory - GREATEST(v_shmmax_limiter, v_os_limiter))
               ,1
               ,255));
        DBMS_OUTPUT.put_line(SUBSTR('Value of v_shmmax=' || TO_CHAR(v_shmmax), 1, 255));

        -- Check limit for shmmax
        IF v_physical_memory - GREATEST(v_shmmax_limiter, v_os_limiter) < v_shmmax
        THEN
            -- too big- fix shmmax
            v_shmmax := v_physical_memory - v_shmmax_limiter;
        END IF;

        -- Trim on page boundary
        v_shmmax := v_shmmax * 1024 * 1024; -- convert to bytes from MB
        v_shmmax := v_shmmax - MOD(v_shmmax, vc_memory_page_size);
        p_shmmax := v_shmmax;
        v_line_no := 500; -- debug line no
        -- End Calc shmmax ------------------------------

        -- Calc hugepages ------------------------------
        -- Overhead
        v_huge_page_oh := GREATEST((vc_huge_pages_oh_pct / 100) * v_total_sga_size * 1024, vc_huge_pages_oh_min); -- calc (pct,min_val)

        -- huge pages
        p_hugepages := ROUND(((v_total_sga_size * 1024) + v_huge_page_oh) / (vc_huge_page_size), 0);

        --- End hugepage calculation ------------------------------------------

        -- Calc p_soft_hard_limit ---------------------------------------------

        p_soft_hard_limit := v_physical_memory - v_os_limiter;
        p_soft_hard_limit := p_soft_hard_limit * 1024; -- convert to B units
        v_line_no := 600; -- debug line no
        -- end p_soft_hard_limit calc ----------------------------------------

        -- p_shmall
        p_shmall := ROUND(((v_physical_memory - v_os_limiter) * 1024 * 1024) / vc_memory_page_size, 0);

        -- Echo to dbms_output for info only
        DBMS_OUTPUT.put_line(SUBSTR('Value of v_physical_memory=' || TO_CHAR(v_physical_memory), 1, 255));
        DBMS_OUTPUT.put_line(SUBSTR('Value of v_os_limiter=' || TO_CHAR(v_os_limiter), 1, 255));
        DBMS_OUTPUT.put_line(SUBSTR('Value of vc_memory_page_size=' || TO_CHAR(vc_memory_page_size), 1, 255));

        -- Echo to dbms_output so they can be cut and pasted easily
        DBMS_OUTPUT.put_line(SUBSTR('vm.nr_hugepages=' || TO_CHAR(p_hugepages) || ' in /etc/sysctl.conf', 1, 255));
        DBMS_OUTPUT.put_line(SUBSTR('kernel.shmmax =' || TO_CHAR(p_shmmax) || ' In /etc/sysctl.conf', 1, 255));
        DBMS_OUTPUT.put_line(SUBSTR('kernel.shmall=' || TO_CHAR(p_shmall) || ' in  /etc/sysctl.conf  ', 1, 255));
        DBMS_OUTPUT.put_line(SUBSTR('p_soft_hard_limit=' || TO_CHAR(p_soft_hard_limit) || ' in /etc/security/limits.conf', 1, 255));
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
            RAISE PROGRAM_ERROR;
    END p_hugepage_parms; -- Procedure p_hugepage_parms

    -------------------------------------------------------------------------------------------------------------------------
    FUNCTION fn_parse_byte_units(p_char_value IN VARCHAR2, p_byte_value IN OUT NUMBER)
        RETURN NUMBER
    IS
    /*

    Purpose: Parse and check byte units

    MODIFICATION HISTORY
    Person Date Comments
    --------- ------ -------------------------------------------
    dcox 11/14/2012 Initial Build

    */

    BEGIN
        p_byte_value := TRANSLATE(UPPER(p_char_value), '0123456789,ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*()-_=+`\|/~,', '0123456789.');
        DBMS_OUTPUT.put_line(SUBSTR('Value of p_byte_value=' || TO_CHAR(p_byte_value), 1, 255));

        IF INSTR(UPPER(p_char_value), 'K') > 0
        THEN
            p_byte_value := p_byte_value / 1024;
        ELSIF INSTR(UPPER(p_char_value), 'M') > 0
        THEN
            p_byte_value := p_byte_value;
        ELSIF INSTR(UPPER(p_char_value), 'G') > 0
        THEN
            p_byte_value := p_byte_value * 1024;
        ELSE
            p_byte_value := p_byte_value / (1024 * 1024);
        END IF;

        DBMS_OUTPUT.put_line(SUBSTR('Value of p_byte_value=' || TO_CHAR(p_byte_value), 1, 255));
        p_byte_value := p_byte_value - MOD(p_byte_value, 1024);
        DBMS_OUTPUT.put_line(SUBSTR('Value of p_byte_value=' || TO_CHAR(p_byte_value), 1, 255));
        RETURN 0; -- error code
    EXCEPTION
        WHEN OTHERS
        THEN
            RETURN SQLCODE;
    END fn_parse_byte_units; -- function fn_parse_byte_units
-----------------------------------------------------------------------------------------------------------

END pkg_hugepage_util; -- pkg body pkg_hugepage_util
/

One thought on “HugePages Calculator for Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s