Re: Autovacuum not keeping up. (PG 9.2.9)

jesper@krogh.cc wrote:
> Hi.
>
> I have a large  database with a message queue table, that has high
> activity. The database supports 1-300 client connection concurrently,
>  having transactions open in up to 30 minutes each.
>
> Recently I am seeing autuvacuum being issued, but it takes
> ages to get through the message queue table, with strace showing waiting
> for semop's for 10's to 100's of seconds.

Do you have data on how relfrozenxid advances for that table?

Vacuuming needs to grab a "cleanup lock" on each page it's going to
vacuum, which is a special kind of lock that requires that no other
process is even looking at the page at that moment (we call this "to
have the page pinned"), which is even weaker than having a shared lock
on the page.  If traffic to some pages is high, it might be difficult
for vacuum to acquire this.

Normally, vacuum doesn't break much sweat about this: if it cannot
acquire the cleanup lock, it ignores the page, keeps calm and carries
on.  But if it's a for-wraparound vacuuming, it will need to wait until
it is able to acquire cleanup lock.

I think one idea might be to try to manually vacuum the table once in a
while with a reduced value of min_freeze_table_age.  This will cause a
full table scan (i.e. cleanup lock for all pages is waited for), which
decreases the "frozen xid age", which moves the need to do this again
further in the future; so the autovacuum-invoked vacuums will be able to
skip the pages on which it cannot get cleanup lock.

Another idea is to increase min_freeze_table_age for the queue table
through ALTER TABLE, the idea being that you can delay forced vacuuming
of hot pages for long enough that they can wait until they have cooled
off.  Default value is 150 million transactions, which you can raise
tenfold and even higher.

See
http://www.postgresql.org/docs/9.2/static/runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE


The other idea is that heap truncation is what's causing the problem,
but AFAICS that uses conditional lock acquisition so you shouldn't be
seeing stalls in semop().

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services