Re: Hung Vacuum in 8.3

On 22.02.2011 05:43, Mark Kirkwood wrote:
> This is 8.3.14 on Debian Lenny x86-64.
>
> I'm seeing a hung vacuum:
>
> postgres=# select procpid, query_start,waiting, current_query from
> pg_stat_activity where current_query like '%VACUUM%';
> procpid | query_start | waiting | current_query
>
--------+-------------------------------+---------+---------------------------------------------------------------------------------------------
>
> 7347 | 2011-02-22 06:02:02.400247+13 | f | VACUUM ANALYZE;
>
> at approx 1300 (so it has been sitting there for approx 7 hours, normal
> database vacuum time is 10 minutes). Now according to pg_stat_activity
> and pg_locks it is *not* waiting for a lock, but no vacuuming appears to
> be going on. strace says:
>
> $ strace -p 7347
>
> Process 7347 attached - interrupt to quit
> semop(33456157, 0x7ffff512bad0, 1
>
> Ok, so we are waiting on a semaphore - hmm, why is it not showing up as
> waiting on a lock of some kind?
>
> Of interest is this:
>
> postgres=# select procpid, query_start, current_query from
> pg_stat_activity order by query_start limit 1;
> procpid | query_start | current_query
>
---------+-------------------------------+-------------------------------------------------------------------------------------
>
> 25953 | 2011-02-22 04:24:07.417138+13 | SELECT n.node_id, n.node_ --
> long query, joining several large tables - text snipped
>
> So this guy had been running from before the vacuum started, so probably
> vacuum is wanting to do lazy_truncate_heap() on one of the tables in the
> join (no gdb on this box unfortunately). I am however still puzzled
> about why no locks are being waited on.

A long query on the same table can block vacuum. Vacuum needs to take a
so-called "cleanup lock" on each page, which means that it has to wait
until no other backend holds a pin on the page. A long-running query can
keep a page pinned for a long time.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com