Re: RA, bugs, and performance

SaTriGuy wrote: > > >>> most of the pages that were read-ahead. > >>> > >> > >>Wow, that's way too high for even a DSS environment. Rule of thumb I use > >>isto set these to about 32, 16 for a good sized DSS and to about 12,8 for an > >>OLTP envrionment. > > > >Hmm, what you say can only be true on a system very low on memory. > >Our values are RA_PAGES 256 and RA_THRESHOLD 96 and we get very > >good values with this. On the contrary, for DSS I consider these values still > >rather low. > > > > > The optimum values for RA_PAGES/RA_THRESHOLD is very dependent on the > environment. > > If the disk bus width is a bottleneck, then the extra bytes being transfered > could be a problem. This is also true if the site is a little tight on memory. > > Generally what I recommend is that RA_THRESHOLD be placed fairly close to > RA_PAGES. For instant, maybe RA_PAGES at 32 and RA_THRESHOLD at 30, or maybe > 64/62. The object is to try to get the next buffer in memory before the query > thread reaches the end of the current buffer. Yes - this may cause an > additional read, but in the total scan, that additional read is not that big of > an expense. > > This will take a bit of testing to see what is optimal on any given system. I disagree. This is, as noted, VERY environment dependent. If your disk farm is very fast then the gap between RA_PAGES and RA_THRESHOLD can be large indeed with the threshold set to 2 or 4 or 8 pages and you may be able to get away with many fewer RA_PAGES. If your disk farm is slower then you have no choice but to take advantage of read ahead as much as possible. Similarly if you have cacheing controllers that detect sequential I/O and perform their own read ahead, why waste database buffers and chance flushing other needed data? Just set the RA_PAGES high enough to trigger controller read ahead and treat the controller's cache as a very fast disk. What constitutes a fast disk farm? A 32 drive stripe set made up of 5400 RPM drives connected a pair of UltraWide SCSI III controllers (or better yet spread over four controllers) is FAST. A 20 drive stripe of 7800 RPM drives is equivalent as is a 17 or 18 drive stripe made up from 10000 RPM drives. If each drive in that stripe set is actually a mirrored pair, and your stripe/mirror software/firmware supports reading both sides of the mirror, and your controllers cache and read ahead, then, I would venture to say that all of you would get more than sufficient performance from RA_PAGES/RA_THRESHOLD of 16/4. I know I do! So before you go wasting precious buffers and drive your read cache % down, even if your ra_pgsused is good, look at your system's capabilities and take advantage of what is there. Or perhaps enhance what you have if needed. At any rate, my point was that this is VERY dependent on your environment and equipment. Art S. Kagel