On Mon, Jan 26, 2009 at 11:58 AM, Jeff <threshar@torgo.978.org> wrote:
> On Jan 26, 2009, at 2:42 PM, David Rees wrote:
>> Lots of people have databases much, much, bigger - I'd hate to imagine
>> have to restore from backup from one of those monsters.
>
> If you use PITR + rsync you can create a binary snapshot of the db, so
> restore time is simply how long it takes to untar / whatever it into place.
Good point - we do that as well and that helps with backup times
(though we still grab daily backups in addition to log-shipping), but
that still doesn't do much to avoid long restore times (at least until
pg-8.4 as Joshua mentions which can do parallel backup/restores).
Going back to the original question -
Trying to come up with a general guide to buying db hardware isn't
easy because of the number of variables like - what's your budget,
what type of workload do you need to support, how big is your db, how
large will you db get, etc...
As the others mentioned having a good RAID controller with a BBU cache
is essential for good performance.
RAID10 is generally the best performing raid configuration, though for
data warehousing where you need maximum storage you might consider a
RAID6 with a RAID1 for the WAL.
The workload will determine whether is more beneficial to go with
quantity rather than speed of processors - As a rough calculation you
can simply look at the raw GHz you're getting (multiply core speed by
number of cores) - more GHz should be faster as long as your workload
is parallelizable.
And yes, the more memory you can squeeze into the machine, the better,
though you'll find that after a certain point, price starts going up
steeply. Of course, if you only have a 15GB database, once you reach
16GB of memory you've pretty much hit the point of diminishing
returns.
-Dave