Re: Choosing a filesystem - Mailing list pgsql-performance

On Thu, 11 Sep 2008, Laszlo Nagy wrote:

> The expert told me to use RAID 5 but I'm hesitating.

Your "expert" isn't--at least when it comes to database performance.
Trust yourself here, you've got the right general idea.

But I can't make any sense out of exactly how your disks are going to be
connected to the server with that collection of hardware.  What I can tell
is that you're approaching that part backwards, probably under the
influence of the vendor you're dealing with, and since they don't
understand what you're doing you're stuck sorting that out.

If you want your database to perform well on writes, the first thing you
do is select a disk controller that performs well, has a well-known stable
driver for your OS, has a reasonably large cache (>=256MB), and has a
battery backup on it.  I don't know anything about how well this Intel
RAID performs under FreeBSD, but you should check that if you haven't
already.  From the little bit I read about it I'm concerned if it's fast
enough for as many drives as you're using.  The wrong disk controller will
make a slow mess out of any hardware you throw at it.

Then, you connect as many drives to the caching controller as you can for
the database.  OS drives can connect to another controller (like the ports
on the motherboard), but you shouldn't use them for either the database
data or the WAL.  That's what I can't tell from your outline of the server
configuration; if it presumes a couple of the SATA disks holding database
data are using the motherboard ports, you need to stop there and get a
larger battery backed caching controller.

If you're on a limited budget and the choice is between more SATA disks or
less SAS disks, get more of the SATA ones.  Once you've filled the
available disk slots on the controller or run out of room in the chassis,
if there's money leftover then you can go back and analyze whether
replacing some of those with SAS disks makes sense--as long as they're
still connected to a caching controller.  I don't know what flexibility
the "SAS/SATA backplane" you listed has here.

You've got enough disks that it may be worthwhile to set aside two of them
to be dedicated WAL volumes.  That call depends on the balance of OLTP
writes (which are more likely to take advantage of that) versus the
reports scans (which would prefer more disks in the database array), and
the only way you'll know for sure is to benchmark both configurations with
something resembling your application.  Since you should always do stress
testing on any new hardware anyway before it goes into production, that's
a good time to run comparisons like that.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD