Re: Question on diff block sizes in DB

On 04/18/2004 04:03:15 PM, Jonathan Lewis wrote:
>
> In your case I'd worry more about the stripe size than
> the block size. Think what's going to happen with a
> multiblock read:
> At 8 blocks per read
> 32K per block, you get a 256K read.
>
> 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.

> 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. Problem with RAID devices is not reading them, they are normally faster to read then a single disk, the problem rears its ugly head when you start writing on them, because then you have to compute checksums, and distribute every single write across all of them, which means that your "write" is slower then the slowest of the disks. Of course, the problem may be solved with the sufficient quantities of write-back cache, which is seldomly free of charge.

>
> 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?

>
>
> As far as use of different block sizes go, I think others
> have already made the key points. You have to know
> what your actual data access paths are so that you can
> predict the pros and cons. 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.

> 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.

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
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
-----------------------------------------------------------------