Re: Tuning for warm standby

On 9/27/07, Kevin Kempter <kevin(at)kevinkempterllc(dot)com> wrote:
> Hi All;
>
> I'm preparing to fire up WAL archiving on 8 production servers We will follow
> up with implementing a warm standby scenariio.
>
> Does anyone have any thoughts per how to maximize performance, yet minimize
> the potential for data loss assuming we were not able to retrieve the final
> un-archived WAL segment from the original pg_xlog dir in the case of a crash?

the standby mechanism is actually very simple and there is very little
to do for efficient operation. all the hard work is done inside the
wal algorithms and from the outside it works like a fancy rsync.

some performance tips:
* don't use encrypted channel (scp) to transfer wal segments from
primary to secondary.
* make sure the link between servers is gigabit at least. bonded
ethernet couldn't hurt if you can easily fit it in your topology
* do not directly write wal segments (nfs, cifs) to the remote folder.
whatever you use, make sure it puts files into the remote folder
atomically unless it is specifically designed to handle wal segments.
* there's not much to do on the standby side.

I've set up a few warm standby systems with pg_standby...it works
great. I find it works best using link mode (-l) and at lest 256 wal
file before it prunes.

'archive_timeout' is a way to guarantee your last transferred file is
no older than 'x' seconds. I am not a big fan of setting this...most
of the servers I work with are fairly busy and I'd prefer to let the
server decide when to flip files. I would only consider setting this
in a server that had very little writing going on but what did get
written was important.

There is a new player in warm standby systems (developed by skype!):
http://pgfoundry.org/projects/skytools/

I haven't looked at it yet, but supposedly it can stream WAL files
over real time. definately worth looking in to. This would moot some
of the other advice I've given here.

merlin