Re: Tables on multiple disk drives

On Tue, 17 Feb 2004, Craig Thomas wrote:

> > On Tue, 17 Feb 2004, Craig Thomas wrote:
> >
> >> > On Tue, 17 Feb 2004, Konstantin Tokar wrote:
> >> >
> >> >> Hi!
> >> >> Does PostgreSQL allow to create tables and indices of a single
> >> database on multiple disk drives with a purpose of increase
> >> >> performance as Oracle database does? If a symbolic reference is the
> >> only method then the next question is: how can it be determined
> >> what file is referred to what table and index?
> >> >
> >> > You're life will be simpler, and your setup will be faster without
> >> having to muck about with it, if you just buy a good RAID
> >> controller with battery backed cache. LSI/Megaraid and Adaptec
> >> both make serviceable controllers for reasonable prices, and as you
> >> add drives, the speed just goes up, no muddling around with sym
> >> links.
> >>
> >> This works to a limited extent. For very large databases, maximum
> >> throughput of I/O is the paramount factor for database performance.
> >> With raid controllers, your LUN is still limited to a small number of
> >> disks. PostgreSQL can only write on a file system, but Oracle, SAP DB,
> >> DB2, etc can write directly to disk (raw I/O). With large databases
> >> it is advantageous to spread a table across 100's of disks, if the
> >> table is quite large. I don't know of any manufacturer that creates a
> >> 100 disk raid array yet.
> >
> > You can run up to four LSI / Megaraids in one box, each with 3 UW SCSI
> > interfaces, and they act as one unit. That's 3*4*15 = 180 disks max.
> >
> > With FC AL connections and four cards, it would be possible to approach
> > 1000 drives.
> >
> > Of course, I'm not sure how fast any RAID card setup is gonna be with
> > that many drives, but ya never know. My guess is that before you go
> > there you buy a big external RAID box built for speed. We have a
> > couple of 200+ drive external RAID5 storage boxes at work that are
> > quite impressive.
>
> That's a good point. But it seems that the databases that are the
> leaders of the TPC numbers seem to be the Oracles of the world. I
> know that a former company I worked for publised TPC numbers using
> Oracle with Raw I/O to get the performance up.

But keep in mind, that in the TPC benchmarks, doing things that require
lots of dba work don't tend to make the cost in the test go up (you can
hide a lot of admin work in those things) while in real life, they do
drive up the real cost of maintenance.

I'd imagine that with Postgresql coming along nicely, it may well be that
in a year or two, in the real world, you can just take the money you'd
have spend on Oracle licenses and Oracle DBAs and just throw more drives
at a problem to solve it.

And still spend less money than you would on Oracle. :-)