Hello,
I have a server running PostgreSQL 8.1.15-0etch1 (Debian etch) that was
recently put into production. Last week a developer started having a problem
with his psql connection being terminated every couple of minutes when he
was running a query. When I look through the logs, I noticed this message.
2009-01-09 08:09:46 CST LOG: autovacuum process (PID 15012) was terminated
by signal 11
I looked through the logs some more and I noticed that this was occurring
every minute or so. The database is a pretty heavily utilized system
(judging by the age(datfrozenxid) from pg_database, the system had run
approximately 500 million queries in less than a week). I noticed that right
before every autovacuum termination, it tried to autovacuum a database.
2009-01-09 08:09:46 CST LOG: transaction ID wrap limit is 4563352, limited
by database "database_name"
It was always showing the same database, so I decided to manually vacuum the
database. Once that was done (it was successful the first time without
errors), the problem seemed to go away. I went ahead and manually vacuumed
the remaining databases just to take care of the potential xid wraparound
issue.
I figured it was just an isolated incident, until it started happening again
this week. Same scenario as before: over 500 million queries since the
beginning of this week and autovacuum dying on the same database every time.
However, the problematic database was different than last time, so it
doesn't seem to be specific to one particular database.
Looking through the archives I've seen this (exact?) same problem crop up
before, but it was addressed in Postgres 8.1.1
http://archives.postgresql.org/pgsql-bugs/2006-01/msg00014.php
This article also mentioned the previous bug was related to triggers on the
table, but the second time this happened to me, the database in question
only has two simple tables (no triggers, one foreign key linking them, a few
btree indices).
What else can I do to determine the cause of this? For the time being, I
should be able to setup a cron job to run a manual vacuum every other day to
ensure that age(datfrozenxid) stays low, but I'd like to understand what
would be causing this.
Justin Pasher
