Re: What is a good blocksize to use.

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Thu, 26 Sep 2002 22:19:00 GMT
Message-ID: <3D9387C6.7BFFFA87@exesolutions.com>

Paul Brewer wrote:

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

Best advice I've seen in awhile. For some crazed reason everyone wants to go tuning where, at most, they are going to get at most a few percentage point improvement in performance. I've yet to see block size make a significant difference. 80+% of the time it is the easy stuff like lousy front-end code, lack of or poorly designed indexes, and of course my personal favorite with CBO ... no statistics.

Daniel Morgan Received on Thu Sep 26 2002 - 17:19:00 CDT