RE: checkpoints taking as much as 25 secs
It's logical that turning the entire buffer pool every 10 minutes is probably bad, but what is a reasonable time frame? Is there any rule of thumb? -Scott > -----Original Message----- > From: owner-informix-list@iiug.org > [mailto:owner-informix-list@iiug.org]On Behalf Of Art S. Kagel > Sent: Thursday, December 23, 1999 10:50 AM > To: informix-list@iiug.org > Subject: Re: checkpoints taking as much as 25 secs > > > Great answers from Peter, Obnoxio, and Lloyd. Yes you are seeing > a bufwaits > ratio of around 40% which is horrific, we need to try to get that > under 10% > with a preference for a value under 7%. Take Lloyd's or Obnoxio's > recommendations for LRUS and CLEANERS and the LRU_MIN/MAX_DIRTY > parameters > but also look at adding more buffers. You have 42,000 buffers but in the > 21 hours since the engine was bounced (that also resets the stats > BTW) you > have used over 6,148,644 buffers (pagreads plus any newly created pages) > which means that on average you are turning over the entire buffer pool > every 10 minutes! It looks like a combination of slow disks (yes > RAID5 can > be a bottleneck however your level of writes, as you say, is low > so this is > not the big problem) and buffer thrashing. Also you have 384000 > sequential > scans with works out to five per second, if nothing else all > those sequential > scans are a big part of the buffer thrashing! You need to look at your > database design vis-a-vis how your applications are using it and add some > indexes somewhere. > > Art S. Kagel
Scott Huppert wrote: > > It's logical that turning the entire buffer pool every 10 minutes is > probably bad, but what is a reasonable time frame? Is there any rule of > thumb? On a busy server that is NOT primarily a DSS/DW server I'd like to see the buffer pool turnover rate less frequent than hourly. For a DSS/DW system, where you are less likely to want to add indexes and are more likely to read masses of data, you will have to bite the bullet and live with more frequent turnover. Then response times for DSS/DW servers are measured in hours and minutes rather than fractions of a second as they are in an OLTP or small query environment so high buffer pool turnover is less of a problem in that environment. Art S. Kagel > -Scott > > > -----Original Message----- > > From: owner-informix-list@iiug.org > > [mailto:owner-informix-list@iiug.org]On Behalf Of Art S. Kagel > > Sent: Thursday, December 23, 1999 10:50 AM > > To: informix-list@iiug.org > > Subject: Re: checkpoints taking as much as 25 secs > > > > > > Great answers from Peter, Obnoxio, and Lloyd. Yes you are seeing > > a bufwaits > > ratio of around 40% which is horrific, we need to try to get that > > under 10% > > with a preference for a value under 7%. Take Lloyd's or Obnoxio's > > recommendations for LRUS and CLEANERS and the LRU_MIN/MAX_DIRTY > > parameters > > but also look at adding more buffers. You have 42,000 buffers but in the > > 21 hours since the engine was bounced (that also resets the stats > > BTW) you > > have used over 6,148,644 buffers (pagreads plus any newly created pages) > > which means that on average you are turning over the entire buffer pool > > every 10 minutes! It looks like a combination of slow disks (yes > > RAID5 can > > be a bottleneck however your level of writes, as you say, is low > > so this is > > not the big problem) and buffer thrashing. Also you have 384000 > > sequential > > scans with works out to five per second, if nothing else all > > those sequential > > scans are a big part of the buffer thrashing! You need to look at your > > database design vis-a-vis how your applications are using it and add some > > indexes somewhere. > > > > Art S. Kagel