Re: Why do we let autovacuum give up?

On 24/01/14 12:28, Mark Kirkwood wrote:
> On 24/01/14 12:13, Jeff Janes wrote:
>> On Thu, Jan 23, 2014 at 1:41 PM, Mark Kirkwood <
>> mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> wrote:
>>
>>> On 24/01/14 10:16, Mark Kirkwood wrote:
>>>
>>>> On 24/01/14 10:09, Robert Haas wrote:
>>>>
>>>>> On Thu, Jan 23, 2014 at 4:03 PM, Mark Kirkwood
>>>>> <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> wrote:
>>>>>
>>>>>> On 24/01/14 09:49, Tom Lane wrote:
>>>>>>
>>>>>>> 2. What have you got that is requesting exclusive lock on
>>>>>>> pg_attribute?
>>>>>>> That seems like a pretty unfriendly behavior in itself. regards,
>>>>>>> tom
>>>>>>> lane
>>>>>>>
>>>>>> I've seen this sort of problem where every db session was busily
>>>>>> creating
>>>>>> temporary tables. I never got to the find *why* they needed to
>>>>>> make so
>>>>>> many,
>>>>>> but it seemed like a bad idea.
>>>>>>
>>>>> But... how does that result on a vacuum-incompatible lock request
>>>>> against pg_attribute?
>>>>>
>>>>> I see that it'll insert lots of rows into pg_attribute, and maybe
>>>>> later delete them, but none of that blocks vacuum.
>>>>>
>>>>>
>>>> That was my thought too - if I see it happening again here (was a
>>>> year or
>>>> so ago that I saw some serious pg_attribute bloat) I'll dig deeper.
>>>>
>>>>
>>>>
>>> Actually not much digging required. Running the attached script via
>>> pgbench (8 sessions) against a default configured postgres 8.4 sees
>>> pg_attribute get to 1G after about 15 minutes.
>>>
>> At that rate, with default throttling, it will be a close race whether
>> autovac can vacuum pages as fast as they are being added. Even if it
>> never
>> gets cancelled, it might not ever finish.
>>
>
> Yes - I should have set the cost delay to 0 first (checking that now).
>
>
>

Doing that (and a few other autovac tweaks):

autovacuum_max_workers = 4
autovacuum_naptime = 10s
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_delay = 0ms

Stops excessive bloat - clearly autovacuum *is* able to vacuum
pg_attribute in this case. Back to drawing board for a test case.

Regards

Mark