Date: Sun, 3 Aug 2014 01:39:34 -0400
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: jesper@krogh.cc
Cc: pgsql-admin@postgresql.org
Subject: Re: Autovacuum not keeping up. (PG 9.2.9)
Message-ID: <20140803053934.GG5475@eldon.alvh.no-ip.org>
References: <26e211945432a41a56dd900440f27e6d.squirrel@shrek.krogh.cc>
 <20140731134025.GB5475@eldon.alvh.no-ip.org>
 <716ad55e559db5d9802de179677d2fb0.squirrel@shrek.krogh.cc>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <716ad55e559db5d9802de179677d2fb0.squirrel@shrek.krogh.cc>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin