Re: Myth revisited ...

From: Noons <wizofoz2k_at_yahoo.com.au.nospam>
Date: Tue, 18 Nov 2003 00:48:54 +1100
Message-ID: <3fb8d18a$0$13984$afc38c87@news.optusnet.com.au>

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:9u3ub.14700$aT.6285_at_news-server.bigpond.net.au...

>

> The vast majority of Oracle databases sit on top of O/S file systems and
> file systems use buffered I/O (unless direct I/O is set). Therefore,
> although having large block sizes for indexes sounds like a great idea in
> principle, in practice it will actually *hurt* performance. The correct,
> optimal block size for these environments is the I/O buffer size. Setting
> the block size any larger, say twice the I/O buffer size (eg. 8K on AIX)
> would result in every logical I/O on the index requiring two O/S calls.
> Having double (or more) O/S calls per logical read on your indexes is going
> to have a detrimental effect, even if such larger blocks succeed in reducing
> the index's overall height.

Whoa boss! Are you telling me Oracle will divide the block size by the file system block size and issue multiple calls?

Because if it does the usual read() or write() call, all it needs to indicate is that it wants 16K and it's up to the OS to do two I/O requests (which are NOT the same as two calls from Oracle) to get those 16K, assuming of course 8k file system size.

Otherwise you'd be limited on I/O size to the file system block size on each OS call, which is not true at all! You can issue in a single read()/write() call quite large requests. Without any major context switching needed.

So yes, in the example above you get 2 I/O calls, but initiated by the file systems code in the OS itself, which is quite efficient and very close to a readv() in speed. We talking low level I/O calls, the kind you can't do unless you're using raw. What is terribly inefficient is to request an I/O size that is not an exact multiple of the file system block size. For obvious reasons.
But to issue a call that asks for twice the file system block size (or three or four times, for that matter) is not a killer by any stretch of the imag.

Of course, raw would be better. But fs is not THAT bad! Unless of course your fs is totally fragged and the two blocks are on opposite ends of the partition...

> In summary, using multi sized blocks to aid performance, especially in
> buffered I/O environments is likely to end in tears.

Quite true.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam

Received on Mon Nov 17 2003 - 07:48:54 CST