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

On 04/06/13 14:14, Andres Freund wrote:
> 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@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.
>
> 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?

Please find attached the profile file and the postgresql log generated
in the test run on my sandbox.

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

The master's wal_level is hot_standby.

I set the warm standby on the slave changing to off the hot_standby
parameter.

Cheers
Federico

--
Federico Campoli
Database Administrator brandwatch.com