Re: Tuning PostgreSQL

On Tue, 22 Jul 2003, Jim C. Nasby wrote:

> On Tue, Jul 22, 2003 at 03:27:20PM +0200, Alexander Priem wrote:
> > Wow, I never figured how many different RAID configurations one could think
> > of :)
> >
> > After reading lots of material, forums and of course, this mailing-list, I
> > think I am going for a RAID5 configuration of 6 disks (18Gb, 15.000 rpm
> > each), one of those six disks will be a 'hot spare'. I will just put the OS,
> > the WAL and the data one one volume. RAID10 is way to expensive :)
> >
> > If I understand correctly, this will give great read-performance, but less
> > write-performance. But since this server will be equipped with an embedded
> > RAID controller featuring 128Mb of battery-backed cache, I figure that this
> > controller will negate that (at least somewhat). I will need to find out
> > whether this cache can be configured so that it will ONLY cache WRITES, not
> > READS....
>
> I think the bigger isssue with RAID5 write performance in a database is
> that it hits every spindle.

This is a common, and wrong misconception.

If you are writing 4k out to a RAID5 of 10 disks, this is what happens:

(assumiung 64k stipes...)
READ data stripe (64k read)
READ parity stripe (64k read)
make changes to data stripe
XOR new data stripe with old parity stripe to get a new parity stripe
write new parity stripe (64k)
write new data stripe (64k)

So it's not as bad as you might think. No modern controller (or sw raid
for linux) hits all the spindles anymore for writes. As you add more
drives to a RAID5 writes actually get faster on average, because there's
less chance of having contention for the same drives (remember, parity
moves about in RAID5 so the parity disk isn't a choke point in RAID5 like
it is in RAID4.)

> The real performance bottleneck you run into
> is latency, especially the latency of positioning the heads. I don't
> have any proof to this theory, but I believe this is why moving WAL
> and/or temp_db to seperate drives from the main database files can be a
> big benefit for some applications; not because of disk bandwidth but
> because it drastically cuts down the amount of time the heads have to
> spend flying around the disk.

This is absolutely true. moving the heads costs hugely. while most
modern drives have SEEK times <10 ms, the SETTLE times tend to be about
that as well, followed by the average of about 3 ms for rotational latency
to allow the proper sector to be under the head (10krpm drives rotate once
about every 6 ms.)