Re: Side Effects of Setting Tables Memory Resident

To add onto Andrew's comments, we set a good deal of smaller tables resident, he's right, the buffers are running low which is causing the writes. Solution, remove some of these puppies from the cache or just shove thier indexes in there. Or of course, increase your buffers. What is your checkpoint time set to? brett Dave Thacker wrote: > > 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 -- ----------------------------------------------------------------- Brett's 13th law of UNIX administration... The 13th sucks, we'll skip this one ----------------------------------------------------------------- Brett Geer - UNIX Admin/Analyst/Programmer - Intratex Holdings. Tel. +27 31 717 4000 Direct. +27 31 717 4146 Fax. +27 31 717 4001 ----------------------------------------------------------------- The little voices are talking to me again, telling me of a long forgotten rhyme... the Rhyme of the Ancient Sysadmin # ps -ef | kill -9 `awk '/albatross/{print $2}'` -----------------------------------------------------------------