Re: Choosing a filesystem - Mailing list pgsql-performance

On Thu, Sep 11, 2008 at 10:29 AM, Laszlo Nagy <gandalf@shopzeus.com> wrote:
> I'm about to buy a new server. It will be a Xeon system with two processors
> (4 cores per processor) and  16GB RAM. Two RAID extenders will be attached
> to an Intel s5000 series motherboard, providing 12 SAS/Serial ATA
> connectors.
>
> The server will run FreeBSD 7.0, PostgreSQL 8, apache, PHP, mail server,
> dovecot IMAP server and background programs for database maintenance. On our
> current system, I/O performance for PostgreSQL is the biggest problem, but
> sometimes all CPUs are at 100%. Number of users using this system:

100% what? sys?  user? iowait?  if it's still iowait, then the newer,
bigger, faster RAID should really help.

> PostgreSQL:  30 connections
> Apache: 30 connections
> IMAP server: 15 connections
>
> The databases are mostly OLTP, but the background programs are creating
> historical data and statistic data continuously, and sometimes web site
> visitors/serach engine robots run searches in bigger tables (with 3million+
> records).

This might be a good application to setup where you slony replicate to
another server, then run your I/O intensive processes against the
slave.

> There is an expert at the company who sells the server, and he recommended
> that I use SAS disks for the base system at least. I would like to use many
> SAS disks, but they are just too expensive. So the basic system will reside
> on a RAID 1 array, created from two SAS disks spinning at 15 000 rpm. I will
> buy 10 pieces of Seagate Barracuda 320GB SATA (ES 7200) disks for the rest.

SAS = a bit faster, and better at parallel work.  However, short
stroking 7200 RPM SATA drives on the fastest parts of the platters can
get you close to SAS territory for a fraction of the cost, plus you
can then store backups etc on the rest of the drives at night.

So, you're gonna put the OS o RAID1, and pgsql on the rest...  Makes
sense.  consider setting up another RAID1 for the pg_clog directory.

> The expert told me to use RAID 5 but I'm hesitating. I think that RAID 1+0
> would be much faster, and I/O performance is what I really need.

The expert is most certainly wrong for an OLTP database.  If your RAID
controller can't run RAID-10 quickly compared to RAID-5 then it's a
crap card, and you need a better one.  Or put it into JBOD and let the
OS handle the RAID-10 work.  Or split it RAID-1 sets on the
controller, RAID-0 in the OS.

> I would like to put the WAL file on the SAS disks to improve performance,

Actually, the WAL doesn't need SAS for good performance really.
Except for the 15K.6 Seagate Cheetahs, most decent SATA drives are
within a few percentage of SAS drives for sequential write / read
speed, which is what the WAL basically does.

> and create one big RAID 1+0 disk for the data directory. But maybe I'm
> completely wrong. Can you please advise how to create logical partitions?
> The hardware is capable of handling different types of RAID volumes on the
> same set of disks. For example, a smaller RAID 0 for indexes and a bigger
> RAID 5 etc.

Avoid RAID-5 on OLTP.

Now, if you have a slony slave for the aggregate work stuff, and
you're doing big reads and writes, RAID-5 on a large SATA set may be a
good and cost effective solution.