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