From: Kevin Grittner <kgrittn@ymail.com>
To: Rural Hunter <ruralhunter@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: wrong database name in error message?
Date: Sun, 15 Sep 2013 10:31:19 -0700 (PDT)
Message-ID: <1379266279.54874.YahooMailNeo@web162902.mail.bf1.yahoo.com> (raw)
In-Reply-To: <523507DE.3060909@gmail.com>
References: <5233BA90.3020107@gmail.com>
<20130914045519.GD5033@eldon.alvh.no-ip.org>
<5233F3A9.30901@gmail.com>
<1379178368.98727.YahooMailNeo@web162901.mail.bf1.yahoo.com>
<523507DE.3060909@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Rural Hunter <ruralhunter@gmail.com> wrote:
> I'm on Ubuntu 12.04.1 64bit with 32 cores and 377G memory. The
> data is stored on several rai10 SAS 15k disks.
With a machine that beefy I have found it necessary to make the
autovacuum settings more aggressive. Otherwise the need for
vacuuming can outpace the ability of autovacuum to keep up.
> autovacuum_freeze_max_age | 2000000000 | configuration file
> vacuum_freeze_table_age | 1000000000 | configuration file
There's your problem. You left so little space between when
autovacuum would kick in for wraparound prevention (2 billion
transactions) and when the server prevents new transactions in
order to protect your data (2 ^ 31 - 1000000 transactions) that
autovacuum didn't have enough time to complete its effort to do so.
Changing a setting to ten times its default value is something
which should always be approached with caution. In this case you
changed the threshold for starting the work to prevent data loss
from a little under 10% of the distance to the disastrous condition
to a little under 100% of that distance.
You could play with non-standard setting for these, but if you go
anywhere near this extreme you risk downtime like you have just
experienced. Personally, I have never had a reason to change these
from the defaults.
To ensure that autovacuum can keep up with the activity on a
machine like this, I have generally gone to something like:
autovacuum_cost_limit = 800
If you have more than a couple large tables which take long enough
to scan to prevent small, frequently-updated tables from getting
attention soon enough, you might want to boost
autovacuum_max_workers, too.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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: kgrittn@ymail.com, ruralhunter@gmail.com, alvherre@2ndquadrant.com
Subject: Re: wrong database name in error message?
In-Reply-To: <1379266279.54874.YahooMailNeo@web162902.mail.bf1.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