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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD
