Re: Fragmentation opinion wanted.

From: Chuck Renaud <crenaud@informix.com> > >Not necessarily. > >You want to hit as many spindles of RELEVANT data as possible to maximize >performance. > > > It depends on your queries. Assuming that fragment elimination will help > > performance is a generalisation that has caught many people out. Pushing > > a large number of threads onto a single disk, rather than spreading them > > out over many can hurt performance, depending on the type of operation. > > There, is that verbose enough? :-) > >Yeah, but it doesn't really speak to what I'm saying. The goal would >ideally be to get a combination of parallelism AND fragment elimination. >Not easy to do with 7.x or 9.x engines, but the hash scheme in XPS is >perfect for this. > > > And I guess you could generalise now that fragment elimination is good > > for OLTP and bad for DSS. ;-) Well, I suppose that's closer to it for > > most sites, but not all. > >I say it's good for both, depending on the data itself, of course. > >Here's a situation where it is good for DSS, and I'm going to drastically >generalize here. Let's say the disks we're using here can handle no more >than one I/O thread each. > >I run monthly reports summarizing by region. In this particular company, >say we have five regions. We have five physical disks to work with. Each >region has 100 million rows in 1 GB of data. > >A query on round robin fragmentation to five dbspaces, one per disk >means: > >* Five parallel threads each reading in 100 million rows. 5 GB total. >But all you need is 100 million rows in 1 GB. > >So round-robin wins on load time, but basically sucks for DSS queries. >Sure you get some parallelism, but no elimination > >Okay, so let's try expression-based fragmentation (on region number). One >region per disk/dbspace. My region-based query gives: > >* One thread reading in 100 million rows. 1 GB total. > >So we get elimination, but no parallelism. Easier on the system, but >probably not a significant time saver over round-robin on an idle system. > >So finally, we get hash fragmentation. We'll divvy up 5 dbspaces per disk >for a total of 25 dbspaces of 200MB each: > >* Fragment expression will use region number for expression and then round >robin each region across 5 dbspaces (one on each disk). > >So I run my report and here's what happens: > >Starting with 25 dbspaces, I eliminate 20 of them--benefit of >expression-based fragmentation. > >I'm left with 5 dbspaces, each of those on a separate disk--benefit of >round-robin fragmentation. > >So instead of the first two options, each of which involves a thread >scanning 100 million rows / 1 GB of data, I have: > >Five parallel threads, each reading from a separate disk and each one only >scanning 20 million rows / 2 MB each. > >All things being equal, you just cut your query time to a FIFTH of round >robin fragmentation. YMMV, but any load time you lose with >hash/expression fragmentation is easily made up in query savings. > >Now there are obviously a boatload of factors that can influence this >(adminstrative overhead, system load, number of querys, total refresh vs. >CDC, etc)...that's why it gets dangerous to generalize, especially from a >third-party vendor who obviously doesn't understand the database >architecture. Well, my $0.02 = fragment elimination is a wonderful objective, but every site I have ever worked in has had no way of identifying a useful expression that would actually work every way it was needed. So you wind up with something that works some of the time and does nothing the rest of the time. Unless we're all just too dumb to work it out... :-) ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com