Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Shared Memory Allocation

  • 1.  Shared Memory Allocation

    Posted Tue June 09, 2020 06:38 PM
    Dear Informix group user,

        Greeting from Amit Patel , hope all are safe and healthy in Covid-19 Era.

         I need a suggestion .

       In my new project , my customer has 1 TB of RAM and using only 38 GB as SHARED MEMORY with having 10 TB data size and they are feeling Server slowness.

    So far I've not got into the system , as project is likely to start and only got information from customer side.

    So what could be the assumption ?

    Should I say customer to share 75-80% RAM as SHARED MEMORY ? (means around 800 GB)
    or
    Should I increase Shared Memory around 100 GB and BUFFERPOOL , SHMVERTSIZE parameter?

    Please advise.

    Thanks 
    Amit Patel

    ------------------------------
    AMIT PATEL
    ------------------------------


  • 2.  RE: Shared Memory Allocation

    Posted Tue June 09, 2020 06:52 PM
    Unless they are adding shared memory segments regularly, they probably do not need more SHMVIRTSIZE, but check that out first.

    Most bang for the buck will likely be in adding to the buffer pool(s). Check the BTR3 (aka ABTR) as reported by my new_ratios.ksh script in the ratios.shr_ak package in the IIUG Repository (www.iiug.org/software). If it is close to or over 10 turns per hour, more buffers will be used.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.








  • 3.  RE: Shared Memory Allocation

    Posted Tue June 09, 2020 10:38 PM
    Hello Amit,

    Modifying the onconfig setting may improve server performance, but I think other aspects should also be considered.
    Even if the total data size is 10 TB, the size of frequently accessed data may be smaller.
    (I think the data size is large due to the accumulation of historical data.)
    Of course, if it is an application that needs to frequently access and fetch large volumes of data, it is a different story.
    It may also be helpful to track the frequently used tables and inefficient queries in your application to find improvements.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 4.  RE: Shared Memory Allocation

    Posted Wed June 10, 2020 12:55 AM

    Amit,

     

     

    As a starter, if the server has 1 TB of shared memory, it is a bit stupid to use only only 38 Gb (not even 4% of the server's RAM...)
    I was going to say check you Buffer Turnover ratio before anything, but Art was still awake to answer that ( and he is the author of the formula ). This ratio will tell you if you need more BUFFERPOOL, but I am sure you do in a mean way.

    How many users on the server? SHMVIRTSIZE will mainly depend on how many users are connected, then on how their queries consume shmvirt. To tune it, if possible, set an initial value, a bit short, and see how many additional segments get assigned during the day. This will be the 'cruising' value or initial value you will want to set for the server.


    But you can have many possible reasons for slowness. Badly written queries, lack of indexes or bad indexes as a starter.

    Eric Vercelletto
    Data Management Architect and Owner / Begooden IT Consulting
    Board of Directors, International Informix Users group
    IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020
    ibm-champion-rgb-130px

    Tel:     +33(0) 298 51 3210
    Mob : +33(0)626 52 50 68
    skype: begooden-it
    Google Hangout: eric.vercelletto@begooden-it.com
    Email:
    eric.vercelletto@begooden-it.com
    www :
    http://www.vercelletto.com
    www  https://kandooerp.org

    image001.jpg@01CDC3E9.1425CBB0

    image002.jpg@01CDC3E9.1425CBB0

    image003.jpg@01CDC3E9.1425CBB0