Re: BUG #8192: On very large tables the concurrent update with vacuum lag the hot_standby replica

On 02/06/13 01:17, Jeff Janes wrote:
> On Thursday, May 30, 2013, wrote:
>
>     The following bug has been logged on the website:
>
>     Bug reference:      8192
>     Logged by:          Federico Campoli
>     Email address: federico@brandwatch.com <javascript:;>
>     PostgreSQL version: 9.2.4
>     Operating system:   Debian 6.0
>     Description:
>
>     /*
>
>     Description:
>
>     It seems on very large tables the concurrent update with vacuum (or
>     autovacuum),
>     when the slave is in hot standby mode, generates long loops in read on a
>     single wal segment during the recovery process.
>
>     This have two nasty effects.
>     A massive read IO peak and the replay lag increasing as the recovery
>     process
>     hangs for long periods on a pointless loop.
>
>
> Are you observing a loop, and if so how are you observing it?  What is
> it that is looping?

I'm sorry, just guessing it could be a loop.
The read remains stuck on the same segment.
On my testbox I have at least 1 minute to 20 Mb/s.
On the live system the peak is 124 Mb/s for 2 to 3 minutes without any
progress in the wal reply.

I've attached the part of postgresql's log with debug2  from my sandbox
when that happens.

In warm standby everything is fine no lag at all.

At moment as workaround I've switched to warm standby the slaves as,
despite the low wal generation on the master ~200Mb/minute the slave
accumulates a massive lag when the autovacuum processes starts with hot
standby (the peak was 400 GB and was still increasing before switching
to warm standby).

The database is ~ 4 TB costantly updated.

Many thanks
Federico

--
Federico Campoli
Database Administrator brandwatch.com