Re: Autovacuum not keeping up. (PG 9.2.9)

jesper(at)krogh(dot)cc wrote:
> > jesper(at)krogh(dot)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?
>
> Not really, how would you normally pick that out?
> 2014-08-01 10:49:39.171 db=# select relname, age(relfrozenxid),
> relfrozenxid FROM pg_class WHERE relkind = 'r' and relname = 'job';
> relname | age | relfrozenxid
> ---------+-----------+--------------
> job | 111893622 | 796259097
> (1 row)
>
> Time: 1.913 ms
>
> This shouldn't qualify for a freeze vacuum, should it?

I misspoke -- I was referring to a full-table scan, not a for-wraparound
vacuum. A full table scan is triggered when the table reaches the
freeze_table_age.

> > 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.
>
> Can I force it to tell me if it does the for-wraparound cleanup or normal?

Don't think so, unless you're open to patching the source.

> > See
> > http://www.postgresql.org/docs/9.2/static/runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE
>
> I tried to read through that, but that is a GUC not a storage level
> parameter. So I (hopefully correct) figured out that the storage level
> parameter that need to be set were autovacuum_freeze_min_age and set that
> one to 1.000.000.000 for the table. But apparently it didnt cause any
> changes. It is still waiting for the lock. Is a database restart required
> after setting storage parameters, or will autovacuum pick up the new one
> when it starts over with the table.

It is autovacuum_freeze_table_age. You don't need to restart;
autovacuum picks up new values from storage parameters immediately.
(Workers that are already running will ignore changes for the table they
are vacuuming at that moment.)

> > 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().
>
> That should only be once per vacuuming .. right? That doesn't fit the
> pattern either.

There's a retry loop in there (see src/backend/commands/vacuumlazy.c):

/*
* Timing parameters for truncate locking heuristics.
*
* These were not exposed as user tunable GUC values because it didn't seem
* that the potential for improvement was great enough to merit the cost of
* supporting them.
*/
#define VACUUM_TRUNCATE_LOCK_CHECK_INTERVAL 20 /* ms */
#define VACUUM_TRUNCATE_LOCK_WAIT_INTERVAL 50 /* ms */
#define VACUUM_TRUNCATE_LOCK_TIMEOUT 5000 /* ms */

...

while (true)
{
if (ConditionalLockAcquire( ... ))
break;

if (++lock_retry > (VACUUM_TRUNCATE_LOCK_TIMEOUT /
VACUUM_TRUNCATE_LOCK_WAIT_INTERVAL))
{
/* give up */
return;
}

pg_usleep(VACUUM_TRUNCATE_LOCK_WAIT_INTERVAL);
}

Note the sleeps are always of the same duration.

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