Re: Databse File layout on only 4 drives Ideas?

"Joel Garry" <joel-garry_at_home.com> wrote in message
> > >
> > > Even in the case of, say, doing an update on everything in a partition
> > > that takes an hour during an otherwise low-usage time?
> >
> > Probably even in such a case, no. The fact that you're doing a big
update
> > means a lot of stuff will be flushed, fine. Will that be to adjacent
blocks
> > on disk? Not necessarily. The blocks within an extent are not physically
> > adjacent to each other, raw partitions aside.
>
> Well, I decided not to take this at face value. I copied a quiet db
> file, ran it into od -c|more, and searched for some data that I knew
> would be there (ie, a knew a character key was 300007, so I entered /3
> 0 0 0 0 7), and sure enough, I could see the next row, and
> the next, and the next, just like import loads them in.

The import business is irrelevant. The question is: are blocks in an extent necessarily contiguous with each other, physically, on disk.

And the answer is no, they are not. Not necessarily. If nothing is going on in your database, and you allocate an extent, then in all likelihood, the blocks may well be allocated contiguously. But if you have a busy database, then the extent allocation will nab whatever O/S blocks it can lay its hands on as they wander past the disk head, in the moments when it can.

>
> So "not necessarily" is NOT the same as what one can expect is going
> to be out there on a well-managed disk.

When your example is from a "quiet database", it doesn't surprise me to see the results you see (and so do I, being fresh out of hammered OLTP databases at home right now). But on a typical production databases, blocks will likely not be physically contiguous.

We are back to 'inherent', 'likely' and 'possible', I'm afraid. I believe the phrase is 'it depends'. So as to the original poster's question: would a 'cache hot spot' inevitably mean a 'disk hot spot'. Probably not, but it depends.

HJR Received on Tue Feb 04 2003 - 02:40:19 CST