Re: Question on diff block sizes in DB

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 19 Apr 2004 19:29:53 +0100
Message-ID: <017b01c4263c$5084cbf0$7102a8c0@Primary>

Notes in-line.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar July 2004 USA West Coast, Optimising Oracle Seminar August 2004 Charlotte NC, Optimising Oracle Seminar September 2004 USA East Coast, Optimising Oracle Seminar September2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Mladen Gogala" <mgogala_at_adelphia.net> To: <oracle-l_at_freelists.org> Sent: Sunday, April 18, 2004 9:21 PM Subject: Re: Question on diff block sizes in DB

On 04/18/2004 04:03:15 PM, Jonathan Lewis wrote:
>
> With a stripe size of 64k, That's a minimum of 4 discs
> kicked into action on a single read.

Is this bad? That means that every read larger then 64k will be done in parallel, and not sequentially.

Putting it another way - the query won't complete
until the read that went into the longest queue gets
to complete.

> Now imagine you
> kick off a parallel tablescan: how many devices do you
> actually have for your 17GB, and how much I/O contention
> will a single parallel request cause on your system.

That is not necessarily true. Contention will be caused if and only if the channel between the RAID device and the RAM doesn't have sufficient throughput for transferring all that data, which is normally not the case, at least not with FC/AL type channels.

I was thinking more of the case where you have four
slaves making a request, which turn into a total of 16
I/O requests, against a small number of devices. It
doesn't take many parallel queries before the device
queue lengths become the significant bottleneck.

>
> If you are stuck with a 64K stripe size, I'd consider
> keeping your multi-block read size down to 64K in
> the hope of minimising contention..
>

You don't want to read in parallel? Why would that be good?

It could be good for a single-user system, but I was
assuming the OP was expecting more than one person
to use the database.

> But as a special
consideration,
> can you guarantee that the boundary of a 32K Oracle data
> block will always synchronize with the boundary of a 64K
> stripe ?

On EMC Symmetrix I was told that it is possible to align raw devices in such a way. I have never managed an EMC array, so I cannot tell for certain.

I believe it's not usually a problem when you use raw
devices - so long as you remember to do it. I believe
that it isn't all that hard on (some) filesystems - so long
as you realise it may be necessary. (I think Cary has a
story about that).

> If you can't then perhaps one block out of every
> two will span two stripes, and therefore activate two
> disk drives. If you switch to 8K blocks then it's only
> one block in eight that would have the problem.

Well, that's an old wisdom which doesn't necessarily hold water with the latest Symmetrix and Shark boxes any more.

"necessarily" is a good word.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Apr 19 2004 - 13:30:41 CDT