Re: What is a good blocksize to use.

"ben brugman" <ben_at_niethier.nl> wrote in message news:amt7r6$5mf$1_at_reader12.wxs.nl...
>
> "Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message
> news:3d90dc3c_2_at_mk-nntp-1.news.uk.worldonline.com...
> > "Ben Brugman" <benbrugman_at_onbekend.nl> wrote in message
> > news:3d90781f.26180500_at_news.nl.uu.net...
> > > For a transactional machine, where most rows contain a few hundred
> > > byte. Assume a RAID (1/0) device as storage medium.
> > >
> > > What is a good blocksize, and why ?
> > >
> > > What are the effects of going smaller in blocksize ?
> > > What are the effects of going larger in blocksize ?
> > >
> > > ben brugman
> > > Ben Brugman
> >
> > Ben,
> >
> > Insufficient input. Go for 8K, or provide more detail.
>
> I do understand that there is not sufficient input.
> On purpose the question left this open.
> I am trying to get some insight in advantages of
> larger and off smaller blocksizes.
> Especcially with RAID divises and going towards
> 64 K stripes and larger, I would like to know how
> data is handled specifically if blocksizes do not match.
>
> Most go for the answer : Use the same blocksize and
> bigger is better.
>
> But bigger blocks effectively fill the cache faster. If only
> a small amount of data is used from each block, a double
> blocksize allows only half the amount of blocks in cache
> and (depending) only half the amount of usefull data.
>
> Small blocks are often handled by the hardware diskcache
> so there is no latency for disk rotation or head movement.
>
> But I do not know how small (Oracle blocks) are handled
> if OS blocks are larger and RAID blocks are even more
> larger. And would like to know.
>
> ben
>

Ben,

All right. I'll give you some real world advice (which will probably get me fired, or thrown off this newsgroup, or at the very least ignored forever): Forget about head movement, disk latency and all that physics. Get the DESIGN right, and TUNE THE BLOODY SQL THAT THE APPLICATION SUBMITS. Then come back to the database and resume fiddling around with the parameters.

Sorry, not a rant at you: I've had another bad day at the office with a crappily designed application, which is never going to perform well in the real world,
despite all the hardware which will doubtless be thrown at the problem in due course.

Regards,
Paul
Exhausted DBA Received on Wed Sep 25 2002 - 17:16:04 CDT