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

On 2013-06-04 13:57:58 +0100, Federico Campoli wrote:
> 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(at)brandwatch(dot)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.

Well, if you check the output in that file you can see that 'apply' is
progressing, so it's not stuck in some specific area.
Is the startup process cpu bound during that time? If so, any chance to
get a profile?

> 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).

By switching to a warm standby you mean disabling hot_standby=on in the
standbys or changing wal_level?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services