Re: Why do we let autovacuum give up?

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