RE: Buffer Waits
I am not an expert on light scans either, but here is what I know. Hope it helps... Light scans are used when a sequential scan tries to scan a table that is larger than your buffer pool. They are allowed under dirty read and repeatable read isolation levels. There is a parameter LIGHT_SCANS that can be set, although I am unfamiliar with it. Most of this came from The DBA survival Guide. (So Joe Lumbley deserves credit). David.tamburin@protegrity.com > -----Original Message----- > From: owner-informix-list@iiug.org > [mailto:owner-informix-list@iiug.org]On Behalf Of Yan Zhu > Sent: Thursday, February 11, 1999 8:33 AM > To: Bob Davis > Cc: informix-list@iiug.org > Subject: Re: Buffer Waits > > > > hmm, could you go into a bit detail on the light-scan? I have a > DSS system but I > am not sure how to make it use light scan. I read something on > the book about it, > but it wasn't very clear on exactly how to utilize it or how to check it. > thanks > yan > > > Bob Davis wrote: > > > In article <79qhb8$fi$1@news.xmission.com>, > > Dale Forrey <forrey@wsu.edu> wrote: > > > > > > Does a properly tuned Informix instance used primarily > for decision > > > support have a buffer wait count near 0? I am working on a relatively > > > small data warehouse running on a 2 processor RS/6000 with > 512 meg of real > > > memory. I tried increasing the number of buffers but that had little > > > effect. I increased the LRUS from 4 to 8 and that had a > noticeable effect. > > > But from what I have read increasing LRUS will eventually have a > > > detrimental effect on processing. What else should I be > trying. Thanks > > > for you help. > > > > > > Dale Forrey, DBA email: forrey@wsu.edu > > > Information Technology FAX: (509) 335-0540 > > > Washington State University Phone: (509) 335-7098 > > > PO Box 641222 > > > Pullman, WA 99164-1222 > > > > > > ADABAS 6.2.1 on OS/390 > > > Natural 2.2.8 > > > Predict 3.4.1 > > > > > > Informix 7.30 UC3-1 on AIX 4.3.1 > > > > > > > A low buffer wait count is a good thing. As long as you are seeing good > > performance don't try to fix what ain't broke. :-) > > > > A really well tuned DSS system could theoretically use nothing > but light scans > > and skip the buffer pool entirely. > > > > Bob > > ------ > > > > -----------== Posted via Deja News, The Discussion Network ==---------- > > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own > >
> > > > > In article <79qhb8$fi$1@news.xmission.com>, > > > Dale Forrey <forrey@wsu.edu> wrote: > > > > > > > > Does a properly tuned Informix instance used primarily > > for decision > > > > support have a buffer wait count near 0? I am working on a relatively > > > > small data warehouse running on a 2 processor RS/6000 with > > 512 meg of real > > > > memory. I tried increasing the number of buffers but that had little > > > > effect. I increased the LRUS from 4 to 8 and that had a > > noticeable effect. > > > > But from what I have read increasing LRUS will eventually have a > > > > detrimental effect on processing. What else should I be > > trying. Thanks > > > > for you help. I missed your original post so forgive the tardy response. Increasing the number of LRUs will NOT have any detrimental on performance that I have ever witnessed. I normally configure 127 LRUS (128 triggers a harmless but annoying error message at startup about zero length logical and physical log files) and run with bufwaits of only a few percent of (bufwrits + dskreads) which is the benchmark I use to judge the effectiveness of my LRU and buffer counts. What more LRUS will do is decrease the number of clashes between threads requesting buffers as a thread must acquire a latch on an LRU in order to modify or load a page into a buffer. If the LRU that is selected by the LRU hash algorithm is already latched the thread will go into a spinwait then check again if the LRU is still not available after a certain number of spins, each of which is a bufwait, then the thread will request another LRU hash to choose another LRU to try to latch. The more users and CPUs that you have the more likely LRU clashes will occur and more LRUs will reduce the number of clashes on a busy system. Just one thing avoid the following values for LRUS as there is an unverified bug at these values that causes bufwaits to go through the roof (7-10x the bufwaits with one more or one less LRU): 64, 96. Try 32 it has worked well for many other users or better yet resist your programmer born instincts and avoid all powers and multiples of 2 use 33 or 65. Art S. Kagel