Re: Autovacuum not keeping up. (PG 9.2.9)

jesper@krogh.cc wrote:
> > 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?
>
> 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