Skip site navigation (1) Skip section navigation (2)

Re: autovac hitting too many tables at once

From: Greg Williamson <gwilliamson39(at)yahoo(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: autovac hitting too many tables at once
Date: 2012-06-22 19:41:29
Message-ID: 1340394089.27581.YahooMailNeo@web46103.mail.sp1.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-admin
Kevin --

<...>

>>    a) quickly relieve the immediate pain
> 
> Set autovacuum_cost_limit to a smaller value.  (Change the
> postgresql.conf file and do a reload.)  I would start by cutting the
> current value in half.
> 

Thanks -- would not have known how to start. But for now, in
observance of your idea on XID wraparound, I am letting
everything run (this is actually a low point in regular traffic for this
site and I am not seeing firect proof that autovac is in fact doing
anything out of the ordinary).

>>  and
>> 
>>    b) prevent such issues in the future (other than going to manual
>>       vacuuming on a schedule).
> 
> If it is suddenly doing this on lots of big tables at once, it seems
> somewhat likely that you've hit the transaction wraparound
> protection threshold.  Because the vacuum necessary for this can be
> painful, and they tend to come at the worst possible time (the more
> your workload looks like a really heavy OLTP workload at any given
> moment, the higher the probability that this is about to happen), I
> always follow a bulk load (like from restoring pg_dump output) with
> a VACUUM FREEZE ANALYZE.
> 
> You might also want to consider running off-hours vacuums to
> supplement autovacuum.  Upgrading to a more recent version of
> PostgreSQL is likely to help some, too.
> 
Duly noted. 

Thanks!

Greg


In response to

Responses

pgsql-admin by date

Next:From: Radovan JablonovskyDate: 2012-06-23 02:14:08
Subject: terminating autovacuum process due to administrator command
Previous:From: Kevin GrittnerDate: 2012-06-22 19:13:56
Subject: Re: autovac hitting too many tables at once

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group