Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2010
    Posts
    2

    Unanswered: Slow after purging record and update statistics.

    Hi all, i got a problem in informix ids 9.4 under linux.

    Every sunday 5:00 am, we doing purging of some records, and every day 3 am, we doing update statistics. However, recently we found some problem which don't know how to trace the cause. I suspect that, after purging every sunday, and after update statistic on next day 3:00 am, our user start to complain, some transactions not even able to go through. Only restart the database will help to solve the problem temporary.

    Is there any things could cause such issue? how i know what is the cause? i have check the crontab in linux, found there is no other process that making the system running at that moment. and i have request help from informix engineer, they can't also find whats wrong with it.

    Is any one know what is the problem? does any one else experience it?

    Thanks in advance.
    Last edited by bpteoh; 02-15-10 at 23:03.

  2. #2
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    Something to check would be the number of virtual segments.

    Execute onstat -g seg and look for the number of "V" in the class column, there should be only one. I'm told that more than one virtual segment will negatively affect performance.

    If you have more than one "V" segment, edit your onconfig file & increase the size of SHMVIRTSIZE so you won't be creating more virtual segments using SHMADD. You will need to bounce IDS for this change to take effect.

    If you're running with one virtual segment, someone else will have to suggest a solution.

  3. #3
    Join Date
    Feb 2010
    Posts
    2
    Dear Sir,

    Thanks for the reply, after i get the onstat -g seg from client, this is the result:

    IBM Informix Dynamic Server Version 9.40.UC9 -- On-Line -- Up 4 days 01:27:24 -- 288764 Kbytes

    Segment Summary:
    id key addr size ovhd class blkused blkfree
    65536 1381386241 44000000 236974080 224112 R 57827 28
    98305 1381386242 521ff000 8388608 896 V 2048 0
    131074 1381386243 529ff000 8388608 896 V 2048 0
    229380 1381386244 531ff000 8388608 896 V 2046 2
    262149 1381386245 539ff000 8388608 896 V 1520 528
    688142 1381386246 541ff000 8388608 896 V 2 2046
    720911 1381386247 549ff000 8388608 896 V 1 2047
    819206 1381386248 551ff000 8388608 896 V 2 2046
    Total: - - 295694


    This this going to create a lots of problem? Why i only see "V" in the class column?

  4. #4
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    The 1st detail line in your output from running onstat -g seg indicates that you have one "R" (reserved) segment and the rest are "V" (virtual) segments.

    The SHMVIRTSIZE parameter in the onconfig file definitely needs to be increased, the first "V" segment is very small small and the other "V" segments are also very small so I'll bet the SHMVIRTSIZE and SHMADD parameters are the same value. It's my guess that you're using the standard as-delivered IDS onconfig file and nothing has been changed.

    If the onconfig has been modified then perhaps an environment variable is set incorrectly. IDS on AIX uses the ONCONFIG environment variable (no idea what Linux uses) to tell IDS which onconfig file to use when starting so perhaps this is pointing to the vanilla onconfig file rather than a modified onconfig.

    I'm not familiar with Linux but some OS will limit the total number of "V" segments and this may be the ultimate source of your problem but you can overcome this easily by eliminating the need for IDS to add more "V" segments.

    I can't recommend specific sizes for these parameters, every site is different, hardware is different, and customer requirements are different, but take a look at these documents & maybe they'll point you in the right direction.

    http://docs.rinet dot ru/InforSmes/ch13/ch13.htm and edit this, replace dot ru to make this useable

    Oninit: Onstat Reference

    Onconfig changes should only be made by someone who knows what they are doing, haphazard tweaking can leave IDS dead-in-the-water, running very poorly, or running more efficiently.
    Last edited by mjldba; 02-19-10 at 10:08.

  5. #5
    Join Date
    Jun 2009
    Location
    Lisboa, Portugal
    Posts
    78
    Hi,

    At Red hat Linux, the environment variable is the same ONCONFIG.
    How much fisical memory has your server?
    Do please this command if you are using Red Hat: free

    Then i will try to help you as you can configure your SHMVIRTSIZE and SHMADD, which depends on your free memory of your server.

  6. #6
    Join Date
    Mar 2010
    Location
    dallas, texas, usa
    Posts
    1
    This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It’s the old what goes around comes around routine.

  7. #7
    Join Date
    Mar 2010
    Posts
    5
    It would also help if you re-index your index files and then run the update statistics so that the query optimizer has more accurate table statistics to make better decisions. I dont know how many rows you are purging. The purged rows will physically remain in the .dat files unless you: unload the tables, drop tables, create tables, load the tables, create the indexes and update the statistics, which is what I do on a daily basis to my database. This always keeps everything in optimum condition.

Tags for this Thread

Posting Permissions

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