Topic
  • 4 replies
  • Latest Post - ‏2013-09-25T20:55:37Z by Tom_Glaser
Tom_Glaser
Tom_Glaser
27 Posts

Pinned topic Monitoring memory

‏2013-08-22T20:15:52Z |

Hi,

We have limited experience on monitoring memory with solidDB as our tables are not that large.  Some of our in-memory tables will be growing and looking to be about 300gig in memory size.  Monitoring memory is going to be more important now.  In reading about monitoring solidDB, the manual points out to monitor: 

  • - the amount of physical memory in the computer
  • - the amount of memory used by the operating system
  • - the amount of memory used by solidDB® (the program itself)
  • - the amount of memory set aside for the solidDB server's cache (the CacheSize solid.iniconfiguration parameter)
  • - the amount of memory required by the connections, transactions and statements running concurrently in the server. The   more concurrent connections and active statements there are in the server, the more working memory the server requires. Typically, you should allocate at least 0.5 MB of memory for each client connection in the server.
  • - the memory used by other processes (programs and data) that are running in the computer       

Does anyone have some sort of shell script you can share that will display this information?  We are running AIX.  We are a mainframe support group with limited knowledge of AIX.  From a solidDB perspective, I can find the following:

ADMIN COMMAND 'info imdbsize';

and

ADMIN COMMAND 'memory'

Any other commands we should be using?  Or the shell script should be using?

Thanks, Tom

  • Timo Kaski
    Timo Kaski
    4 Posts

    Re: Monitoring memory

    ‏2013-09-25T08:41:12Z  

    Here's an AIX/UNIX script example I've used for QA purposes. It collects memory-usage-related information, mostly by using the solidDB admin commands.

    The fetched data is exact only after checkpoint. To execute checkpoint manually, enter "admin command 'mcp'". Please note that executing checkpoints will have effect on solidDB performance. Checkpoint are usually executed all the time, so no manual execution is usually required (depending on your solid.ini settings).


    #---start of script---
    while true
    do
    #Number of active clients (connections):
    numberofclients=`solsql -e "admin command 'ul'" -xonlyresults 'dbconnectinfo' user pass | wc -l`

    #solidDB queried information of OS processsize
    infoprocesssize=`solsql -e "admin command 'info processsize'" -xonlyresults 'dbconnectinfo' user pass | awk '{ print $2 }'`

    #solidDB used memory
    mem=`solsql -e "admin command 'mem'" -xonlyresults 'dbconnectinfo' user pass | awk '{ print $2 }'`

    #solidDB sys_cardinal sum size, sum size of data (rows) in all tables, excluding indexes etc. (queries the SYS_CARDINAL system table)
    syscardinalsum=`solsql -e "select sum(size) from sys_cardinal" -xonlyresults 'dbconnectinfo' user pass | awk '{ print $NF }'`

    #solidDB logsize
    logsize=`solsql -e "admin command 'info logsize'" -xonlyresults 'dbconnectinfo' user pass | awk '{ print $2 }'`

    #solidDB infoimdbsize, size of in-memory tables
    imdbsize=`solsql -e "admin command 'info imdbsize'" -xonlyresults 'dbconnectinfo' user pass | awk '{ print $2 }'`

    #solidDB infodbsize, size of disk tables
    dbsize=`solsql -e "admin command 'info dbsize'" -xonlyresults 'dbconnectinfo' user pass | awk '{ print $2 }'`

    #Finally echo these to a file
    echo $numberofclients $infoprocesssize $mem $syscardinalsum $logsize $imdbsize $dbsize >> statistics.txt

    #sleep 5 minutes
    sleep 300
    done
    #---end of script---


    In addition, you can collect solidDB Performance counters with admin command 'perfmon diff'.
    http://pic.dhe.ibm.com/infocenter/soliddb/v7r0/topic/com.ibm.swg.im.soliddb.admin.doc/doc/producing.a.continuous.performance.monitoring.report.html

    solsql -e "admin command 'perfmon diff start filename interval'" 'dbconnectoptions' user pass


    AIX operating system statistics can be collected with a tool called NMON (included in AIX). There are excellent guides available how to use NMON in DeveloperWorks. E.g. https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Power Systems/page/nmon (https://www.ibm.com/developerworks/community/wikis/home?lang=en#%21/wiki/Power Systems/page/nmon)

    We are using Eclipse BIRT (http://www.eclipse.org/birt) to visualize the collected data, it is easy to use for very basic reports like this and free too. In our case, the statistics are written to a centralized database and visualized from there, but you can just output a csv-file and use your favorite visualization tool instead.

  • Tom_Glaser
    Tom_Glaser
    27 Posts

    Re: Monitoring memory

    ‏2013-09-25T19:08:57Z  

    Here's an AIX/UNIX script example I've used for QA purposes. It collects memory-usage-related information, mostly by using the solidDB admin commands.

    The fetched data is exact only after checkpoint. To execute checkpoint manually, enter "admin command 'mcp'". Please note that executing checkpoints will have effect on solidDB performance. Checkpoint are usually executed all the time, so no manual execution is usually required (depending on your solid.ini settings).


    #---start of script---
    while true
    do
    #Number of active clients (connections):
    numberofclients=`solsql -e "admin command 'ul'" -xonlyresults 'dbconnectinfo' user pass | wc -l`

    #solidDB queried information of OS processsize
    infoprocesssize=`solsql -e "admin command 'info processsize'" -xonlyresults 'dbconnectinfo' user pass | awk '{ print $2 }'`

    #solidDB used memory
    mem=`solsql -e "admin command 'mem'" -xonlyresults 'dbconnectinfo' user pass | awk '{ print $2 }'`

    #solidDB sys_cardinal sum size, sum size of data (rows) in all tables, excluding indexes etc. (queries the SYS_CARDINAL system table)
    syscardinalsum=`solsql -e "select sum(size) from sys_cardinal" -xonlyresults 'dbconnectinfo' user pass | awk '{ print $NF }'`

    #solidDB logsize
    logsize=`solsql -e "admin command 'info logsize'" -xonlyresults 'dbconnectinfo' user pass | awk '{ print $2 }'`

    #solidDB infoimdbsize, size of in-memory tables
    imdbsize=`solsql -e "admin command 'info imdbsize'" -xonlyresults 'dbconnectinfo' user pass | awk '{ print $2 }'`

    #solidDB infodbsize, size of disk tables
    dbsize=`solsql -e "admin command 'info dbsize'" -xonlyresults 'dbconnectinfo' user pass | awk '{ print $2 }'`

    #Finally echo these to a file
    echo $numberofclients $infoprocesssize $mem $syscardinalsum $logsize $imdbsize $dbsize >> statistics.txt

    #sleep 5 minutes
    sleep 300
    done
    #---end of script---


    In addition, you can collect solidDB Performance counters with admin command 'perfmon diff'.
    http://pic.dhe.ibm.com/infocenter/soliddb/v7r0/topic/com.ibm.swg.im.soliddb.admin.doc/doc/producing.a.continuous.performance.monitoring.report.html

    solsql -e "admin command 'perfmon diff start filename interval'" 'dbconnectoptions' user pass


    AIX operating system statistics can be collected with a tool called NMON (included in AIX). There are excellent guides available how to use NMON in DeveloperWorks. E.g. https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Power Systems/page/nmon (https://www.ibm.com/developerworks/community/wikis/home?lang=en#%21/wiki/Power Systems/page/nmon)

    We are using Eclipse BIRT (http://www.eclipse.org/birt) to visualize the collected data, it is easy to use for very basic reports like this and free too. In our case, the statistics are written to a centralized database and visualized from there, but you can just output a csv-file and use your favorite visualization tool instead.

    Hi Timo,

    Thanks for your help.  A few follow-up questions.  You are running this query:

    select sum(size) from sys_cardinal

     …I'm not sure what the purpose of this query is for.  What if half the tables are on disk and the other half is in memory?  What about something like:

    SELECT B.TABLE_NAME, B.TABLE_SCHEMA, B.ID

    FROM SYS_TABLEMODES A,

         SYS_TABLES B

    WHERE A.ID = B.ID

    and MODE = 'MAINMEMORY'

    ORDER BY TABLE_NAME;

    ….and from this list, add up those rows?

     Here's another thought.  What if I took the following:

     $ lparstat

     System configuration: type=Shared mode=Uncapped smt=4 lcpu=8 mem=204800MB psize=3 ent=2.00

     …this shows memory on the server of 204800mb

     We then take the output of info imdbsize:

     $ PEAT2$ solcon -e "info imdbsize" "tcp 11100" dbinst1 xxxx

    IBM solidDB Remote Control - Version 7.0.0.5 Build 2013-02-19

    Copyright Oy International Business Machines Ab 1993, 2013

    32721010

    .

    .

    .which is equal to 31.2gb or 31954mg

    Memory:  (31954mb / 204800mb) * 100 = 15.6 percent used.  If I used this in a shell aix/unix script, think this would be a good indication of available memory for solidDB?  Maybe I need to add other things, like the number of threads???

    Thanks, Tom

  • Timo Kaski
    Timo Kaski
    4 Posts

    Re: Monitoring memory

    ‏2013-09-25T20:19:58Z  

    Hi Timo,

    Thanks for your help.  A few follow-up questions.  You are running this query:

    select sum(size) from sys_cardinal

     …I'm not sure what the purpose of this query is for.  What if half the tables are on disk and the other half is in memory?  What about something like:

    SELECT B.TABLE_NAME, B.TABLE_SCHEMA, B.ID

    FROM SYS_TABLEMODES A,

         SYS_TABLES B

    WHERE A.ID = B.ID

    and MODE = 'MAINMEMORY'

    ORDER BY TABLE_NAME;

    ….and from this list, add up those rows?

     Here's another thought.  What if I took the following:

     $ lparstat

     System configuration: type=Shared mode=Uncapped smt=4 lcpu=8 mem=204800MB psize=3 ent=2.00

     …this shows memory on the server of 204800mb

     We then take the output of info imdbsize:

     $ PEAT2$ solcon -e "info imdbsize" "tcp 11100" dbinst1 xxxx

    IBM solidDB Remote Control - Version 7.0.0.5 Build 2013-02-19

    Copyright Oy International Business Machines Ab 1993, 2013

    32721010

    .

    .

    .which is equal to 31.2gb or 31954mg

    Memory:  (31954mb / 204800mb) * 100 = 15.6 percent used.  If I used this in a shell aix/unix script, think this would be a good indication of available memory for solidDB?  Maybe I need to add other things, like the number of threads???

    Thanks, Tom

    Hi Tom,

     

    The purpose of the select sum(size) from sys_cardinal is to figure out how much actual data is in the database. It excludes all indices or other db structures. Sometimes it is useful information.

    'Info imdbsize' is not the right way to check memory utilization. It only tells you how much the In-memory engine is using memory. There are caches etc. left out as well as disk table related memory consumption. The right way to see how much solidDB is using memory is 'info processsize'. In solidDB non SMA server it is the same value as operating system reported vsz value:

    ps -o vsz -T pidofsolid

     

    Number of available threads does not have effect on resource consumption.

     

    Thanks,

    Timo

  • Tom_Glaser
    Tom_Glaser
    27 Posts

    Re: Monitoring memory

    ‏2013-09-25T20:55:37Z  

    Hi Tom,

     

    The purpose of the select sum(size) from sys_cardinal is to figure out how much actual data is in the database. It excludes all indices or other db structures. Sometimes it is useful information.

    'Info imdbsize' is not the right way to check memory utilization. It only tells you how much the In-memory engine is using memory. There are caches etc. left out as well as disk table related memory consumption. The right way to see how much solidDB is using memory is 'info processsize'. In solidDB non SMA server it is the same value as operating system reported vsz value:

    ps -o vsz -T pidofsolid

     

    Number of available threads does not have effect on resource consumption.

     

    Thanks,

    Timo

    To find tune this, what about the following:

    > info processsize
    34495244    (equal to 33686.76mb)

    $ lparstat

    System configuration: type=Shared mode=Uncapped smt=4 lcpu=8 mem=204800MB

    So....(33686.76 / 204800) * 100 = 16.4% of the memory available has been used.

    Timo, would this be a more accurate number to use? 

    Thanks, Tom