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(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?

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