Re: Tuning PostgreSQL

On Wed, 2003-07-23 at 00:53, 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 :)

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".

RAID-1 (mirroring) or RAID-10 (sort-of-mirrored-RAID-5) is the best
choice.

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

The reason is that a lot of what the database is doing, especially
guaranteeing writes (WAL) and so forth is best handled through a
filesystem that does not get in the way. The fundamentals will not have
changed.

It is for this reason that ext2 is very much likely to be better than
ext3. XFS is possibly (maybe, perhaps) OK, because there are
optimisations in there for databases, but the best optimisation is to
not be there at all. That's why Oracle want direct IO to disk
partitions so they can implement their own "filesystem" (i.e. record
system... table system...) on a raw partition.

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.

Catalyst's last significant project was to write the Domain Name
registration system for .nz (using PostgreSQL). Currently we are
developing the electoral roll for the same country (2.8 million electors
living at 1.4 million addresses). We use Oracle (or Progress, or MySQL)
if a client demands them, but we use PostgreSQL if we get to choose.
Increasingly we get to choose. Good.

Regards,
Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------