Re: separate data/inidex

In article <f369a0eb.0204260548.35235dc0_at_posting.google.com>, you said (and I quote):

I'll jump in two with my $0.02 worth, given that not long ago I did the same on another thread on the same subject for the same reasons.

> snippage
> > >level, each I/O involves two types of activities:
> > >1) moving electrons from disk head to memory. This is instantaneous if
> > > you have enough bandwidth, and

just one minor correction here, from the engineering point of view. The speed of movement of electrons in a solid (a metal conductor) is MUCH LESS than the speed of light in a vacuum (the top speed we see quoted so much). This is one of the two major reasons that miniaturisation is so essential for high processing speed in modern CPUs. The other of course is power dissipation.

> more snippage
> > >2) moving disk head to where your data is. This is mechanical motion and
> > > is tens of thousands of times slower. How far the disk head has to move
> > > determines how slow your I/O is.

and I'd add a third one. It wasn't very relevant many years ago, but it is today, in this day and age of sub-nanosecond CPU cycle speeds. And that is the rotational speed. It hasn't increased even by an order of magnitude in the last 20 years, while processing speed has by quite a few. I'm talking linear rotational speed, not angular speed.

It is nowadays a relevant slow down whereas before it wasn't. It used to be referred to as "rotational latency". Thrown it in into the equation and the argument for splitting tables/indexes becomes even less relevant!

> more snippage
> > >So there is more to tuning disk I/O than simply make your instance read
> > >from many disks at the same time. Depending on your application, you
> > >may have some control on the speed of an average I/O.

Actually, I'd say this a most relevant aspect. You WANT your application to be able to read from as many disks as possible at the same time. WHEN it has to read from disks.

What I think is important is coming up with a method of achieving this, as this implies. Splitting indexes and tables is not the best one, as has been amply demonstrated here. This however doesn't mean we all give up on tuning our I/O!!!

The best approach IME is a heuristic one: sample, measure, then decide which are the "hot spots" for I/O. Then spread those across the devices available. There is a little bit more about it than just this. You don't want to have a situation where you solve a single hot spot and then end up with 50 new hot spots. Ie, the old problem of removing one bottleneck and watch 50 others replace it. We have to be a little more preemptive than just that. This is where knowledge of the application and its patterns of use comes in helpful.

> more snippage
> requests, so location of table/index segments doesn't matter that much. My
> point is we shouldn't generalize it so much to say there is nothing we can do
> to control disk I/O regardless of the application.

Exactly. Of course we can. The following is just one technique.

> more snippage
> > yup, 100% -- always. In that case, what I would like is for my table to be in
> > MANY extents and my INDEX to be in MANY extents and these extents are spread
> > across disk 1 and disk 2 evenly (we do that -- when allocating an extent for a
> > segment we go from file to file). Now I have an even distribution of data
> > across the two devices.
> >
> > If the index is cached -- i don't get a hot disk (from all reads going to DATA)
> >
> > If the index is not cached -- i don't get a hot disk -- the IO is even and since
> > the reads are *random* reads anyway -- it matters NOT whether they are on 1 or
> > 1,000 disks.

Precisely. Very well explained indeed. Of course there are special cases and exceptions. These must be addressed as needed, but in general the "divide and conquer" approach for disk distribution is one of the best.

Add in a good controller cache and/or SAN cache and you have the start of a very smooth and fast I/O distribution. In fact, there are a few variations of this distribution that work particularly well when used with large h/w caches in the I/O subsystems. Add in the multiple buffer caches available since version 8 and the possibilities are tremendous.

We can now partition the I/O cache for logical load as well as physically apportion cache over disk arrays, which can themselves be distributed! If anyone has an I/O performance problem nowadays, they'll have to work hard to sustain it!

> more snippage
> >>Thank you. Exactly what I ahve been looking for. The ribbon has been tied, the
> >> card signed, and the envelope sealed.

Hehehe! Sounds like Xmas gift wrapping, Dan. Don't seal yet, there is more to this I/O saga than just this. It is a work in progress thing. Let's not create another "myth", OK?
¦)

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam