Re: SATA drives performance - Mailing list pgsql-performance

A couple of thoughts occur to me:

1. For reads, RAID 1 should also be good: it will allow a read to occur
from whichever disk can provide the data fastest.

2. Also, for reads, the more RAM you have, the better (for caching). I'd
suspect that another 8GB of RAM is a better expenditure than a 2nd drive
in many cases.

3. RAID 0 is twice as unreliable as no raid. I'd recommend using RAID 1
intead. If you use the Linux software mdraid, remote admin is easy.

4. If you can tolerate the risk of the most recent transactions being
lost, look at asynchronous commit. Likewise, you *might* consider
operating with a write cache enabled. Otherwise, the time for
fdatasync() is what's critical.

5. For a 2-disk setup, I think that main DB on one, with WAL on the
other will beat having everything on a single RAID0.

6. The WAL is relatively small: you might consider a (cheap) solid-state
disk for it.

7. If you have 3 equal disks, try doing some experiments. My inclination
would be to set them all up with ext4, then have the first disk set up
as a split between OS and WAL; the 2nd disk set up for
/var/lib/postgresql,  and the 3rd disk as a backup for everything (and a
spare OS with SSH access).

8. Lastly, if you need remote administration, and can justify another
£100 or so, the HP "iLO" (integrated lights out) cards are rather
useful: these effectively give you VNC without OS support, even for the
BIOS.

Best wishes,

Richard


Ognjen Blagojevic wrote:
> Hi all,
>
> I'm trying to figure out which HW configuration with 3 SATA drives is
> the best in terms of reliability and performance for Postgres database.
>
> I'm thinking to connect two drives in RAID 0, and to keep the database
> (and WAL) on these disks - to improve the write performance of the SATA
> drives.
>
> The third drive will be used to reduce the cost of the RAID 0 failure
> without reducing the performance. Say, I could configure Postgres to use
> the third drive as backup for WAL files, with archive_timeout set to 15
> minutes. Daily backups will be created on different server. Loss of last
> 15 minute updates is something the customer can afford. Also, one day
> restore time is case of failure is also affordable (to reinstall the OS,
> Postgres, restore backup, and load WALs).
>
> The server will be remotely administered, that is why I'm not going for
> RAID 1, 1+0 or some other solution for which, I beleive, the local
> administion is crucial.
>
> Server must be low budget, that is why I'm avoiding SAS drives. We will
> use CentOS Linux and Postgres 8.4. The database will have 90% of read
> actions, and 10% of writes.
>
> I would like to hear your opinion, is this reasonable or I should
> reconsider RAID 1?
>
> Regards,
> Ognjen
>