From: Greg Williamson <gwilliamson39@yahoo.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: autovac hitting too many tables at once
Date: Fri, 22 Jun 2012 12:41:29 -0700 (PDT)
Message-ID: <1340394089.27581.YahooMailNeo@web46103.mail.sp1.yahoo.com> (raw)
In-Reply-To: <4FE47DA402000025000489E3@gw.wicourts.gov>
References: <1340391499.61601.YahooMailNeo@web46101.mail.sp1.yahoo.com>
<4FE47DA402000025000489E3@gw.wicourts.gov>
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
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: gwilliamson39@yahoo.com
Subject: Re: autovac hitting too many tables at once
In-Reply-To: <1340394089.27581.YahooMailNeo@web46103.mail.sp1.yahoo.com>
* 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