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?
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.
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.
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.
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.