Side Effects of Setting Tables Memory Resident

In an effort to cut down on disk I/0, I have set 8 of our most heavily used tables to memory resident. The checkpoints have gone through the roof. I was averaging 1-3 seconds and am now stuck at 8-9, with spikes to 15. Checkpoint interval is 300 seconds. onstat -F says: Fg Writes LRU Writes Chunk Writes 517 130130 384588 So it appears that the page cleaners are not working well enough. MAX_DIRTY is set to 2, MIN_DIRTY set to 1, so they ought to be working all the time. The amount of memory used has stayed the same at 472816 Kbytes. Since loading the tables was the only change I made to the engine configuration, they are undoutedly the cause of the increased checkpoints. What else can I tune to keep these tables in memory and brint the checkpoints back below 3 seconds? onstat -p follows, this is at 1245 local time and stats were reset atmidnight local time. omni-res.omnihotels.com: /home/davet>onstat -p Informix Dynamic Server Version 7.30.UC8 -- On-Line -- Up 1 days 09:47:13 -- 472816 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 7278146 6924272 3491048613 99.79 670365 1855032 8520224 92.13 isamtot open start read write rewrite delete commit rollbk 435213349 14830244 39610413 265807062 1132799 1068738 558371 1699313 84238 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 529 51332.50 2987.25 162 324 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 462021 2437 1068488212 0 0 3741 53683 1083122 ixda-RA idx-RA da-RA RA-pgsused lchwaits 2870159 180963 1667856 2465618 281805 Dave Thacker, DBA Omni Hotels Reservation Center dthacker@omnihotels.com

Dave Thacker wrote in message ... >In an effort to cut down on disk I/0, I have set 8 of our most heavily used >tables to memory resident. The checkpoints have gone through the roof. I >was averaging 1-3 seconds and am now stuck at 8-9, with spikes to 15. >Checkpoint interval is 300 seconds. onstat -F says: > >Fg Writes LRU Writes Chunk Writes >517 130130 384588 > >So it appears that the page cleaners are not working well enough. >MAX_DIRTY is set to 2, MIN_DIRTY set to 1, so they ought to be working all >the time. >The amount of memory used has stayed the same at 472816 Kbytes. Since >loading the tables was the only change I made to the engine configuration, >they are undoutedly the cause of the increased checkpoints. What else can >I tune to keep these tables in memory and brint the checkpoints back below 3 >seconds? > I'd say the resident tables are being kept in the buffer pool, thereby effectively shrinking the buffer pool by the same amount, forcing mad LRU activity AND STILL large checkpoints. If your resident tables are large, then think twice, or add heaps more buffers. Sometimes there's no substitute for improving the quality of the SQL.

You may want to set your physical log to the maximum, 2Gb. i.e. PHYSFILE=2000000. Of course you will need to resize the PHYSDBS too. Your LRU max and min are set to 2% and 1% .... try setting them to 1% and 0% to get rid of the Chunk Writes. Check you have enough LRUs and CLEANERS .... we use 64 for each since we had "aiowrite" problems setting them above 80. Yours -- Earle A Long (Senior DBA) SinglePoint Limited "Dave Thacker" <d_thacker@hotmail.com> wrote in message news:RxVi6.16024$2U3.1712511@nntp1.onemain.com... > In an effort to cut down on disk I/0, I have set 8 of our most heavily used > tables to memory resident. The checkpoints have gone through the roof. I > was averaging 1-3 seconds and am now stuck at 8-9, with spikes to 15. > Checkpoint interval is 300 seconds. onstat -F says: > > Fg Writes LRU Writes Chunk Writes > 517 130130 384588 > > So it appears that the page cleaners are not working well enough. > MAX_DIRTY is set to 2, MIN_DIRTY set to 1, so they ought to be working all > the time. > The amount of memory used has stayed the same at 472816 Kbytes. Since > loading the tables was the only change I made to the engine configuration, > they are undoutedly the cause of the increased checkpoints. What else can > I tune to keep these tables in memory and brint the checkpoints back below 3 > seconds? > > onstat -p follows, this is at 1245 local time and stats were reset at> midnight local time. > > omni-res.omnihotels.com: /home/davet>onstat -p > > Informix Dynamic Server Version 7.30.UC8 -- On-Line -- Up 1 days > 09:47:13 -- 472816 Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 7278146 6924272 3491048613 99.79 670365 1855032 8520224 92.13 > > isamtot open start read write rewrite delete commit > rollbk > 435213349 14830244 39610413 265807062 1132799 1068738 558371 1699313 > 84238 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 0 0 0 0 0 0 0 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 529 51332.50 2987.25 162 324 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 462021 2437 1068488212 0 0 3741 53683 1083122 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 2870159 180963 1667856 2465618 281805 > > > > Dave Thacker, DBA > Omni Hotels Reservation Center > dthacker@omnihotels.com > > >