At 12:56 PM 8/30/2005, Joshua D. Drake wrote:
At 08:37 AM 8/30/2005, Alvaro Nunes Melo wrote:
We are about to install a new PostgreSQL server, and despite of
being a very humble configuration compared to the ones we see in
the list, it's the biggest one we've got till now.
The server is a Dual Xeon 3.0 with 2 GB RAM and two SCSI disks.
Our main doubt is what is the best configuration for the disks. We
are thinking about use them in a RAID-0 array. Is this the best
option? What do you suggest on partitioning? Separate partitions
for the OS, data and pg_xlog?
This is _very_ modest HW. Unless your DB and/or DB load is
similarly modest, you are not going to be happy with the
performance of your DBMS.
Well that is a pretty blanket statement. I have many customers who
happily run in less hardware that what is mentioned above.
It all depends on the application itself and how the database is utilized.
If your customers "run happily" on 2 HD's, then IME they have very
modest DB storage and/or DB performance needs. For safety reasons,
the best thing to do if you only have 2 HD's is to run them as a RAID
1 with everything on them. The slightly better performing but
considerably less safe alternative is to put the OS + logs on 1 HD
and the DB on the other. Any resemblance to a semi-serious OLTP load
will reduce either such system to an HD IO bound one with poor IO rates.
If, as above, your DBMS is bounded by the performance of one HD, then
you are AT BEST getting the raw IO rate of such a device: say
~70-80MB/s in average sustained raw sequential IO. Files system
overhead and any seeking behavior will rapidly reduce that number to
considerably less. Consider that the CPU <-> memory IO subsystem is
easily capable of ~3.2GBps. So you are talking about slowing the DB
server to at most ~1/40, maybe even as little as ~1/200, its
potential under such circumstances.
If your DB can fit completely in RAM and/or does light duty write IO,
this may not be a serious issue. OTOH, once you start using those
HD's to any reasonable extent, most of the rest of the investment
you've made in server HW is wasted.
As I keep saying, the highest priority in purchasing a DBMS is to
make sure you have enough HD IO bandwidth. RAM comes second, and CPU
is a distant third.
At a minimum, for safety reasons you want 4 HDs: 2 for a RAID 1 set
for the DB, and 2 for a RAID 1 set for the OS + pg_xlog.
2 extra HDs, even SCSI HDs, is cheap. Especially when compared to
the cost of corrupted or lost data.
Your real test is going to be prototyping the performance you need.
A single RAID 1 mirror (don't use RAID 0) may be more
than enough. However based on the fact that you speced Xeons my
guess is you spent money on CPUs when you should have
spent money on hard drives.
I agree with Josh on both points. Don't use RAID 0 for persistent
data unless you like losing data. Spend more on HDs and RAM and less
on CPU's (fast FSB is far more important than high clock rate. In
general buy the highest FSB with the slowest clock rate.). If fact,
if you are that strapped for cash, exchange those 2 SCSI HD's for
their $ equivalent in SATA HD's. The extra spindles will be well worth it.
If you still have the budget, I would suggest considering either
what Ron suggested or possibly using a 4 drive RAID 10 instead.
IME, with only 4 HDs, it's usually better to split them them into two
RAID 1's (one for the db, one for everything else including the logs)
than it is to put everything on one RAID 10. YMMV.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend