Re: BUG #13472: VACUUM ANALYZE hangs on certain tables

Hi,

On 2015-06-26 15:39:37 +0100, Matthew Seaman wrote:
> On 06/26/15 14:53, Andres Freund wrote:
> > Hello,m
> >
> > On 2015-06-26 11:08:30 +0000, matthew.seaman@adestra.com wrote:
> >> We have two tables which we have not been able to vacuum sucessfully on one
> >> of our database servers.  The vacuum process just hangs almost
> >> instantaneously after issuing the 'VACUUM ANALYZE' command.
> >
> > Hm. Is there a chance that your application uses cursors?
>
> We do use cursors, but not all the time.  They're only used for some
> ad-hoc data exports that run occasionally.  There's no cursors in use on
> that database at the moment.

Hm. And there was none when the vacuum was hanging last?

What level of concurrency do you have? Is it possible/likely that there
is a number of accesses to the same page going on all the time?

How large is that table?

> > Could you send the output of:
> > SELECT oid::regclass, txid_current(), relfrozenxid, age(relfrozenxid)
> > FROM pg_class
> > WHERE oid = 'your relation'::regclass;
> >
> > My theory is that there's a page that needs to be frozen, but a pin on
> > the page exists, preventing the cleanup lock from being acquired. Or
> > rather delaying it long enough that little progress is made.
>
> Certainly:
>
> amf_more2=# SELECT oid::regclass, txid_current(), relfrozenxid,
> age(relfrozenxid)
> FROM pg_class
> WHERE oid = 'page_tags'::regclass;
>     oid    | txid_current | relfrozenxid |    age
> -----------+--------------+--------------+-----------
>  page_tags |    633097656 |    370306842 | 262790814
> (1 row)

Hm. So that table is already considerably older than the default
autovacuum_freeze_max_age (200000000). If indeed pins are the problem
they already migh have held up autovacuum for a while.

Is that the value you currently have configured?

One easy way to confirm whether this is the root cause would be to
change autovacuum_freeze_max_age to be significantly higher (just uses a
bit more disk space, but is also more efficient overall); but that
requires a restart which I guess you're not looking forward to doing?

Greetings,

Andres Freund