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)
Date: Sun, 3 Aug 2014 01:39:34 -0400
Message-ID: <20140803053934.GG5475@eldon.alvh.no-ip.org> (raw)
In-Reply-To: <716ad55e559db5d9802de179677d2fb0.squirrel@shrek.krogh.cc>
References: <26e211945432a41a56dd900440f27e6d.squirrel@shrek.krogh.cc>
<20140731134025.GB5475@eldon.alvh.no-ip.org>
<716ad55e559db5d9802de179677d2fb0.squirrel@shrek.krogh.cc>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: alvherre@2ndquadrant.com, jesper@krogh.cc
Subject: Re: Autovacuum not keeping up. (PG 9.2.9)
In-Reply-To: <20140803053934.GG5475@eldon.alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox