Hello,
I'm trying to understand if this is normal. This is a Postgres 8.1.15
server (the same one in use when I reported the autovacuum problem here:
http://archives.postgresql.org/pgsql-general/2009-01/msg00404.php).
Since most of our servers are still stuck on Postgres 7.4, I don't have
nearly the same experience in regards to the inner workings of PG8, so
I'm trying to understand if this is normal.
I currently have autovacuum disabled due to the segfault problem under
8.1.15 (waiting for Debian to release a new 8.1.16 stable package). What
I've noticed is that the age(datfrozenxid) of the template0 database
grows quite a bit over a short period of time (under two weeks). Here is
a snippet from the current server:
postgres=# SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2;
datname | age
---------------------------+------------
database1 | 1389525200
database2 | 1389726011
database3 | 1389780361
...
template0 | 1722583868
(32 rows)
As you can see, it is starting to approach the 2 billion mark. If I
change the datallowconn setting to TRUE, connect to template0, then
perform a VACUUM FREEZE, the age(datfrozenxid) goes way down (probably
around the 3 million mark). However, over time (typically less than two
weeks), the number slowly creeps higher and higher, until our custom
monitoring software starts spitting out a warning/error that the
database should be vacuumed soon. If I look at a PG7 database where I
have done the same thing in the past few weeks, the number is still
really low (around 5 million).
Because I have autovacuum disabled for the time being, I have a simple
smart_vacuumdb script that runs every day to keep the databases
vacuumed. It simple selects the top X% of the databases (based upon
age(datfrozenxid)), and calls the Postgres vacuumdb script if the age()
is greater than a certain threshold (around 1.5 billion). This helps
keep the normal database away from xid wraparound, but since template0
does not allow connections, it never gets vacuumed.
What I'm trying to figure out is whether this is normal, expected
behavior. It doesn't seem to happen on the PG7.4 server (or at least not
nearly to this degree), but the work load on that machine is much
different than this PG8.1 server. The PG8.1 server runs a lot more
queries across all databases (and probably quite a few within
transactions). The smart_vacuumdb script helps keep the important
databases in check, but I'm worried about template0. During the time I
started this email and now, the age(datfrozenxid) of the database has
gone up by 4 million.
Thanks.
--
Justin Pasher