Re: n00b RAID + wal hot standby question

On Dec 4, 2006, at 1:11 PM, Anton Melser wrote:

> Hi,
> I am just starting at a company and we are inheriting a previously
> built solution. It looks pretty good but my previous experience with
> pg is seriously small-time compared with this...
> I am very new at the job, and don't know what hd config we have but it
> will be RAID-something I imagine (hey I was working with desktop
> "servers" before this!). If that is very important I can find out. We
> seem to be saving our WAL to the same partition as PGDATA, and I
> notice that we are maxing out a reasonable looking server. The db is
> not very big (~4gig, 400meg pgdump), and though I can't see any vacuum
> strategy (autovacuum on a 8.1.4 is disabled), we didn't have as much
> consistent CPU usage at my old job (with a 6 gig db and MUCH less CPU
> and RAM, and probably as many connections), and my vacuum strategy was
> also pitiful!  Sure, completely different environments, but I am
> thinking that WAL replication could be a factor.
> So my question... being in complete ignorance of how RAID works (the
> performance details)... would it be better to try and separate the WAL
> destination from PGDATA? How much of a difference could it make?
> Should we wait till the customer starts complaining (no explosion in
> traffic/db size realistic for the foreseeable future...)?
> Any abuse welcome.

When you say maxing out, what do you mean? Posting some vmstat output
under load would be very helpful (assuming *nix, otherwise can't help
you ;^).

My very general RAID recommendation would be:

Put the wal on a 2 disk RAID-1 array either by itself or together
with the system if you expect little disk activity from the rest of
the system.

Put the data on a RAID-10 array (at least 4 disks, but more would be
much better).

As for the hardware itself, we've had good luck with 10k rpm WD
raptor SATA drives + 3ware 95xx raid controllers (the 9550SX being
preferred due to their smarter caching and higher bandwidth).

THe most important thing though is too keep as much of the database
in RAM as possible. That means putting as much RAM in the database
box as you can afford and not sharing it with other processes (i.e.,
move other RAM-hungry things to another box). And configure postgres
to use the available RAM by tuning shared_buffers, work_mem, etc.

Tuning checkpoint_segments and wal_buffers can help with write
performance.

And running autovacuum (or regular full database vacuums) is very
important to the performance of the database over time. Otherwise you
will definitely notice significant performance degradation as the
garbage in the tables mounts (unless of course the db is read-only).
If it's been off for a long time (or never run), you'll probably need
to spend some quality time doing a "VACUUM FULL" and possibly a
"REINDEX DATABASE" first.

-Casey