Re: Autovacuum stuck for hours, blocking queries

On Fri, Feb 17, 2017 at 10:36 AM, Tim Bellis <Tim(dot)Bellis(at)metaswitch(dot)com>
wrote:

>
>
>
>
> *From:* Jeff Janes [mailto:jeff(dot)janes(at)gmail(dot)com]
> *Sent:* 17 February 2017 02:59
> *To:* Tim Bellis <Tim(dot)Bellis(at)metaswitch(dot)com>
> *Cc:* pgsql-general(at)postgresql(dot)org
> *Subject:* Re: [GENERAL] Autovacuum stuck for hours, blocking queries
>
>
>
> On Wed, Feb 15, 2017 at 9:30 AM, Tim Bellis <Tim(dot)Bellis(at)metaswitch(dot)com>
> wrote:
>
> I have a postgres 9.3.4 database table which (intermittently but reliably)
> gets into a state where queries get blocked indefinitely (at least for many
> hours) behind an automatic vacuum. I was under the impression that vacuum
> should never take any blocking locks for any significant period of time,
> and so would like help resolving the issue.
>
> The process blocking the query is:
> postgres 21985 11304 98 Feb13 ? 1-14:20:52 postgres: autovacuum
> worker process <db_name>
> which is running the query
> autovacuum: VACUUM public.<table_name>
>
>
>
> Are you sure it doesn't really say:
>
>
>
> autovacuum: VACUUM public.<table_name> (to prevent wraparound)
>
> *[Tim Bellis] It doesn’t. I was using the query from *
> *https://wiki.postgresql.org/wiki/Lock_Monitoring*
> <https://wiki.postgresql.org/wiki/Lock_Monitoring>* and looking at the
> ‘current_statement_in_blocking_process’ column. Is there a different query
> I should be using?*
>

That query seems to be a bit mangled. At one time, it only found row-level
locks. Someone changed that, but didn't remove the comment "these only
find row-level locks, not object-level locks"

Also, the "WHERE NOT blocked_locks.GRANTED" should perhaps be:

WHERE NOT blocked_locks.GRANTED and blocking_locks.GRANTED;

As it is, every waiting query reports that it is waiting on all of its
fellow victims as well as the thing(s) actually blocking it. But my WHERE
clause is not really correct either, as it is possible that it is one
blocked thing is being blocked by a different blocked thing which is ahead
of it in the queue, when without that intervening blocked requestor it
could be immediately granted if its request mode is compatible with the
held mode(s). I don't think there is a query that can reveal what is most
immediately blocking it.

But, I don't see how this explains what you see. An autovacuum without
"(to prevent wraparound)" should not block anything for much more than a
second (unless you changed deadlock_timeout) and should not be blocked by
anything either as it just gives up on the operation if the lock is not
immediately available.

Cheers,

Jeff