Paul has it right. For oltp 8k is good, for olap use a bigger blocksize.
We have made several tests with different blocksizes and perf diffs were
minimal. Don't waste your time, focus on application/transaction and
database design and specially tune the application sql. And stay patient
with the application people!
"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message
news:3d92362b_2_at_mk-nntp-1.news.uk.worldonline.com...
> "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 - 20:07:44 CDT
