RE: Side Effects of Setting Tables Memory Resident

A rule I use for myself, is to only set tables that are being read from, memory resident, and not tables that are being written to (inserts / updates/ deletes). Regards Dirk Reach Technologies -----Original Message----- From: owner-informix-list@iiug.iiug.org [mailto:owner-informix-list@iiug.iiug.org]On Behalf Of Dave Thacker Sent: Thursday, February 15, 2001 8:48 PM To: informix-list@iiug.org Subject: 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