Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: How to Check/Calculate available shared memory ??

    Experts,

    Can some one help me find the current free shared memory on ids 10.

    I need to find why an application Cannot initiate sort.

    Code:
    echo "select substr(cf_name,1,20)name,substr(cf_effective,1,30) value from sysconfig where cf_name in ('SHMVIRTSIZE','SHMADD','SHMTOTAL')" | dbaccess sysmaster 2>/dev/null 
    
      	name                 value
    
    	SHMVIRTSIZE          0x32000
    	SHMADD               0xc800000
    	SHMTOTAL             0x0
    I need to use smi tables ( client side query).

    Thanks,
    DBFinder

  2. #2
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    take a look at this excellent article by Informix guru Authur Kagel, yes it's old (1998) but much useful information about set-up & performance. Tuning your onconfig file parameters takes a while but he has some good suggestions.

    PRS Technologies, Inc. and Peter Schmidt - a resource for Informix Database Administration

    Use onstat -g seg to display memory segment statistics. All these numbers are site specific but I think your SHMVIRTSIZE is too small & SHMADD is pretty large for adding segments ..... perhaps they should be reversed so you start-out with a large memory segment & then add to it when necessary.

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by mjldba View Post
    Tuning your onconfig file parameters takes a while but he has some good suggestions.
    ..... perhaps they should be reversed so you start-out with a large memory segment & then add to it when necessary.
    Thanks for the link to the article; I saved this link for thotough reading.

    Basically, I am not going to tune anything. These settings are on dev servers of a big company in Toronto. What I need to do is to watch the usage of memory segments, even that not with ONSTAT instead SMI tables.

    I need to find out which tables and columns map to onstat -g seg command, so that I can use them in SQL queries in my script.

    Thanks anyway,
    DBFinder

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi Db,

    long time no see. Also take a look at the message log file, it will tell you that a session
    needs more shmvirtsize, and you'll see the shm segment allocation there.

    It is important to watch the sessions ( onstat -g ses ),namely "total memory" and "used memory" to identify which sessions request more shm segments.

    This is probably due to a nasty query executing a big sort/group by query, or a hash join due to lack of index.

    Not sure you have it in your version, but look for using explain_stats and sqltrace ( in onconfig file ), where can help you track the responsible query!

    As per finding which sysmaster table matches onstat -g seg, look at $INFORMIXDIR/etc/sysmaster.sql file, which contains the full and rather well commented schema of the sysmaster table. You should find what you are looking for in this file

    Hope this helps
    Eric

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks Eric,

    As of current role, I am supporting dev team. They usually have issues with informix system parameters, because writing new application they rely on DBA to tune it to database environment.

    So in current role I need to write short scripts for watching behavior of new born application/software. In most cases this new born application will not be just normal.

    Although informix has a wealth of tools/commands available, I am needing more references on using SMI or any other way that may allow SQL queries for querying database performance.

    Yes, sysmaster schema will be helpful , thanks.


    DBFinder

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Also if someone can help with AIX and HP-UX system performance of following

    1. CPU , DISK , MEMORY and NETWORK resource availability monitoring code ( ksh preferably).

    Thanks all,

  7. #7
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    play with vmstat that will give you CPU I/O wait and memory
    iostat will give you the detail of i/o per disk

    for informix, you can correlate with onstat -g iof -r IntervalInSeconds
    which will give you chunks I:O details

    vmstat IntervalInSeconds NumberOfIterations
    iostat IntervalInSeconds NumberOfIterations

    I would definaately recommend to install the dev evironment with at least a 11.10 to take
    benefit of "SQLTRACE" and/or onmode -Y SessionNumber 1.

    THis functionality will provide you full detail on query stats, times and plans, which is what you want to evaluate .... And is the worst enemy of developpers because it generally points to their lack of schema design knowlege :-)

    Why not provide them with Innovator-C Edition for dev ?

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Sure, just a beginning, I will as I go along another cycle of production.

    Providing them innovator-c version needs a convincing atmosphere, this belongs to end-client.

    I think they cannot install anything themselves, even a client for themselves needs explicit permission.

    Let me write something; along with it I will provide some recomendations.


    Thanks for your time

    DBFinde

  9. #9
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    In addition to vmstat & iostat you can also use topas in your AIX environment. You must be logged in as root to use it and, once it's launched, press h to bring up the help screen to present you with a list of options to modify the display

  10. #10
    Join Date
    Mar 2012
    Posts
    2
    Code:
    echo "select substr(cf_name,1,20)name,substr(cf_effective,1,30) value from sysconfig where cf_name in ('SHMVIRTSIZE','SHMADD','SHMTOTAL')" | dbaccess sysmaster 2>/dev/null
    name value
    SHMVIRTSIZE 0x32000
    SHMADD 0xc800000
    SHMTOTAL 0x0

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Max2314 View Post
    Code:
    echo "select substr(cf_name,1,20)name,substr(cf_effective,1,30) value from sysconfig where cf_name in ('SHMVIRTSIZE','SHMADD','SHMTOTAL')" | dbaccess sysmaster 2>/dev/null
    name value
    SHMVIRTSIZE 0x32000
    SHMADD 0xc800000
    SHMTOTAL 0x0
    I did not really understand well, can you please elaborate ?
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •