Re: Tuning PostgreSQL - Mailing list pgsql-performance

From Arjen van der Meijden
Subject Re: Tuning PostgreSQL
Date
Msg-id 000001c3535f$4af7acd0$3ac15e91@acm
Whole thread Raw
In response to Re: Tuning PostgreSQL  (Andrew McMillan <andrew@catalyst.net.nz>)
Responses Re: Tuning PostgreSQL
Re: Tuning PostgreSQL
List pgsql-performance
> 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




pgsql-performance by date:

Previous
From: Rauf Kuliyev
Date:
Subject: SUBSCRIBE
Next
From: Tom Lane
Date:
Subject: Re: Tuning PostgreSQL