Re: Minimizing Recovery Time (wal replication)

On Thu, 2009-04-09 at 14:27 -0500, Bryan Murphy wrote:
> I have two hot-spare databases that use wal archiving and continuous
> recovery mode. I want to minimize recovery time when we have to fail
> over to one of our hot spares. Right now, I'm seeing the following
> behavior which makes a quick recovery seem problematic:
>
> (1) hot spare applies 70 to 75 wal files (~1.1g) in 2 to 3 min period
>
> (2) hot spare pauses for 15 to 20 minutes, during this period pdflush
> consumes 99% IO (iotop). Dirty (from /proc/meminfo) spikes to ~760mb,
> remains at that level for the first 10 minutes, and then slowly ticks
> down to 0 for the second 10 minutes.
>
> (3) goto 1

The database is performing too frequent restartpoints.

This has been optimised in PostgreSQL 8.4 by the addition of the
bgwriter running during recovery. This will mean that your hot spare
will not pause while waiting for restartpoint to complete, so will be
significantly faster.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support