Re: Tuning PostgreSQL

> Andrew McMillan wrote:
>
> The general heuristic is that RAID-5 is not the way to deal
> with databases.  Now surely someone will disagree with me,
> but as I understand it RAID-5 has a bottleneck on a single
> disk for the
> (checksum) information.  Bottleneck is not the word you want
> to hear in the context of "database server".
That's indeed incorrect. There is no single disk "special" in a Raid-5,
you might be mistaking it for Raid-3/4 (where a single disk holds the
checksum). In raid-5 the checksums are scattered around on all the
harddisks.
Raid-5's problem is the write-performance, but with a decent
raid-controller it outperforms a less-decent raid-controller (with the
same harddisks) on both read- and writeperformance which is running a
raid-10.
With a decent raid-controller you end up with "about the same" write
performance as with raid-1, but slightly lower read performance.

At least, that's what I was able to gather from some tests of a
colleague of mine with different raid-setups.

> RAID-1 (mirroring) or RAID-10 (sort-of-mirrored-RAID-5) is
> the best choice.
Raid-10 is _not_ similar to raid-5, it is raid1+0 i.e. a mirroring set
of stripes (raid-0 is more-or-less a stripe).

For databases, raid-10 is supposed to be the fastest, since you have the
advantage of the striping for both reading and writing. While you also
have the advantage of the mirroring for reading.

The main disadvantage of raid-1 (and also of raid-10) is the heavy waste
of harddisk space. Another advantage of raid-5 over raid-10 is that when
you don't care about space, raid-5 is more save with four harddrives
than raid-10 (i.e. set it up with a 3-disk+1spare).

> As far as FS performance goes, a year or two ago I remember
> someone doing an evaluation of FS performance for PostgreSQL
> and they found that the best performance was...
>
> FAT
>
> Yep: FAT
FAT has a few disadvantages afaik, I wouldn't use it for my database at
least.

> Personally I don't plan to reboot my DB server more than once
> a year (if that (even my_laptop currently has 37 days uptime,
> not including suspend).  On our DB servers I use ext2 (rather
> than ext3) mounted with noatime, and I bite the 15 minutes to
> fsck (once a year) rather than screw general performance with
> journalling database on top of journalling FS.  I split
> pg_xlog onto a separate physical disk, if performance
> requirements are extreme.
Well, reboting is not a problem with ext2, but crashing might be... And
normally you don't plan a systemcrash ;)
Ext3 and xfs handle that much better.

Regards,

Arjen