Re: RAID + PostgreSQL?

On Mon, 2006-26-06 at 11:08 -0500, Scott Marlowe wrote:
> On Mon, 2006-06-26 at 08:59, Tony Caduto wrote:
> > MG wrote:
> > > Hello,
> > >
> > > we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6.
> > >
> > > When we do a big SELECT-query the whole maschine becomes very very
> > > very slowly or stands.
> > > The maschine has 3 GB RAM, so we suppose it`s the RAID.
> > >
> > > Has anyone some experience with RAID + PostgreSQL?
> > >
> > > Where does PostgreSQL saves his temporary files? Perhaps these are the
> > > reason for the bad performance.
> > >
> > > Greetings
> > >
> > > Michaela
> > What kind of RAID? I know if you have it set up to mirror it becomes
> > slow as pond water.
>
> I have to say this has NOT been my experience. With a pair of U320
> drives on an LSI-Megaraid with battery backed cache (256M or 512M, not
> sure which it was, it's been a few years) our pg server was noticeable
> faster with a mirror set than with a single IDE drive (with cache
> disabled) on the same machine. And faster than a single SCSI drive with
> no RAID controller as well.
>
>
> > I have a server that was a hand me down so I did not have a choice in
> > the RAID and it was set up to mirror with two drives and
> > the performance on large selects was very bad.
>
> Wow, something's wrong then. normally, selects are much faster on
> mirror sets rather than on a single drive. Do you have a lot of sorts
> spilling onto disc?
>
> > The performance would
> > increase by 50 or more percent if
> > fsync = off is set in the postgresql.conf file.
>
> Of selects? Or performance in general? I can see if for performance in
> general, but selects really shouldn't be greatly affected by fsync.
>
> In another vein, I agree with Joshua. There could be LOTS of causes of
> poor performance. I wouldn't just assume it's RAID until it's been
> proven to be the cause of the problem.
>

Confusion Abounds.

Mirroring does not improve performance. Mirroring writes the same data
to two sets of identically sized partitions/slices, and reads the data
from both and verifies the integrity of the returned data. A good RAID
controller will add some latency on first read, but with caching can
improve the speed of some drive operations. Large selects may very
well produce more data than the cache on the controller can hold so
the speed will be limited by the sustained throughput of the drive and
controller with a number of other factors that can cause lower levels
of performance. Some performance improvements can be made using RAID
methods that distribute the data over multiple drives like striping,
but even with striping large selects can still be larger than the
cache, but the data can usually be accessed more quickly than with
a single drive, or straight mirroring.

The main advantage mirroring has is that it can provide redundancy
in the event of premature drive failure, as is usually the least
expensive data redundancy solution.