SEO

I have a Postgres 8.1.8 system with three databases. Although
autovacuum is enabled (I'm using all default autovacuum configuration
settings) and pg_autovacuum is empty, the logs indicate that only
template1 and one of the three databases are being processed by
autovacuum. Are there other reasons why the other two databases might
be skipped, or some way I could diagnose the issue?

Thanks,

--Ian



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Ian Westmacott wrote:
> I have a Postgres 8.1.8 system with three databases. Although
> autovacuum is enabled (I'm using all default autovacuum configuration
> settings) and pg_autovacuum is empty, the logs indicate that only
> template1 and one of the three databases are being processed by
> autovacuum. Are there other reasons why the other two databases might
> be skipped, or some way I could diagnose the issue?

Is autovacuum dying before being able to finish the vacuuming of
template1 or the other database?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Tue, 2008-04-29 at 16:33 -0400, Alvaro Herrera wrote:
> Is autovacuum dying before being able to finish the vacuuming of
> template1 or the other database?

Not as far as I can tell. There are no indications of any crash or
error in the log file (I just bumped the log level up to debug1). Just
autovacuum processing template1 or the one other database every minute.
I ran VACUUM ANALYZE on both template1 and the one database manually
just to make sure there wasn't a lock or something preventing
completion, but they both completed without error.

--Ian



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Ian Westmacott wrote:
> On Tue, 2008-04-29 at 16:33 -0400, Alvaro Herrera wrote:
> > Is autovacuum dying before being able to finish the vacuuming of
> > template1 or the other database?
>
> Not as far as I can tell. There are no indications of any crash or
> error in the log file (I just bumped the log level up to debug1). Just
> autovacuum processing template1 or the one other database every minute.
> I ran VACUUM ANALYZE on both template1 and the one database manually
> just to make sure there wasn't a lock or something preventing
> completion, but they both completed without error.

Okay.

On 8.2 you could have the problem that leftover temp tables are messing
up the frozenxid calculations, but I don't think that can happen on 8.1
at all.

Can you verify whether age(pg_database.datfrozenxid) is shrinking after
vacuuming? If the age() of a database is higher than the applicable
max_freeze_age, then it will always be chosen.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Tue, 2008-04-29 at 17:12 -0400, Alvaro Herrera wrote:
> Can you verify whether age(pg_database.datfrozenxid) is shrinking after
> vacuuming? If the age() of a database is higher than the applicable
> max_freeze_age, then it will always be chosen.

One of the databases is about 1.5TB, so that could take a while.
Anything else I could look at in the meantime? The age of these
databases right now is about 290039577. Also, autovacuum doesn't always
choose the same DB, it alternates between template1 and one of mine --
going back through a couple of months of logs I don't see it ever
choosing the same one twice in a row.

I'll note that I have 5 of these essentially identical systems (same
hardware/software platform, databases, configuration, and load). Two of
them have this issue (autovacuum processing multiple databases, but not
all) and the other three don't. A commonality between the two that have
this issue is that template1 is processed but postgres is not. One of
the two is processing the 1.5TB DB but the other is not.

Thanks,

--Ian



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Ian Westmacott wrote:
> On Tue, 2008-04-29 at 17:12 -0400, Alvaro Herrera wrote:
> > Can you verify whether age(pg_database.datfrozenxid) is shrinking after
> > vacuuming? If the age() of a database is higher than the applicable
> > max_freeze_age, then it will always be chosen.
>
> One of the databases is about 1.5TB, so that could take a while.
> Anything else I could look at in the meantime? The age of these
> databases right now is about 290039577. Also, autovacuum doesn't always
> choose the same DB, it alternates between template1 and one of mine --
> going back through a couple of months of logs I don't see it ever
> choosing the same one twice in a row.

Right -- if you have two databases over the wraparound edge, it will
alternate between them.

I still think that the autovac is dying before completing the task. Did
you investigate whether there are "ERROR" messages coming from
autovacuum? No PG crashes would happen.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Right -- if you have two databases over the wraparound edge, it will
> alternate between them.

> I still think that the autovac is dying before completing the task. Did
> you investigate whether there are "ERROR" messages coming from
> autovacuum? No PG crashes would happen.

AFAICS, if it's triggering a whole_db run and the run fails to get to
the point of updating pg_database, then the next run will pick the very
same db.

I'm noticing though that the 8.1 logic pays attention to both
datvacuumxid and datfrozenxid. Could we see the age() of both of
those columns for all the databases?

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Wed, 2008-04-30 at 12:27 -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > I still think that the autovac is dying before completing the task. Did
> > you investigate whether there are "ERROR" messages coming from
> > autovacuum? No PG crashes would happen.

There are no ERROR messages at all. Sample log snippet:

2008-04-30 12:03:36 EDT LOG: autovacuum: processing database
"template1"
2008-04-30 12:04:36 EDT LOG: autovacuum: processing database
"itvtrackdatapos"
2008-04-30 12:05:36 EDT LOG: autovacuum: processing database
"template1"
2008-04-30 12:06:36 EDT LOG: autovacuum: processing database
"itvtrackdatapos"
2008-04-30 12:07:36 EDT LOG: autovacuum: processing database
"template1"
2008-04-30 12:08:36 EDT LOG: autovacuum: processing database
"itvtrackdatapos"
2008-04-30 12:09:36 EDT LOG: autovacuum: processing database
"template1"
2008-04-30 12:10:36 EDT LOG: autovacuum: processing database
"itvtrackdatapos"


> I'm noticing though that the 8.1 logic pays attention to both
> datvacuumxid and datfrozenxid. Could we see the age() of both of
> those columns for all the databases.

The are all identical:

itvtrackdata=> select datname,age(datfrozenxid),age(datvacuumxid) from
pg_database;
datname | age | age
------------------+-----------+-----------
postgres | 295995059 | 295995059
itvtrackdata | 295995059 | 295995059
itvtrackdatauser | 295995059 | 295995059
itvtrackdatapos | 295995059 | 295995059
template1 | 295995059 | 295995059
template0 | 295995059 | 295995059
(6 rows)

itvtrackdata=>


--Ian



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Ian Westmacott <ianw@intellivid.com> writes:
>> I'm noticing though that the 8.1 logic pays attention to both
>> datvacuumxid and datfrozenxid. Could we see the age() of both of
>> those columns for all the databases.

> The are all identical:

Oh, that's really strange. Could we see

select ctid,xmin,* from pg_database

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Wed, 2008-04-30 at 13:07 -0400, Tom Lane wrote:
> Oh, that's really strange. Could we see
>
> select ctid,xmin,* from pg_database

itvtrackdata=> select ctid,xmin,* from pg_database;
ctid | xmin | datname | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datvacuumxid | datfrozenxid | dattablespace | datconfig | datacl
-------+----------+------------------+--------+----------+---------------+--------------+--------------+---------------+--------------+--------------+---------------+-----------+------------------------
(0,1) | 564 | postgres | 10 | 6 | f | t | -1 | 10792 | 499 | 499 | 1663 | |
(0,2) | 577 | itvtrackdata | 16384 | 6 | f | t | -1 | 10792 | 499 | 499 | 1663 | |
(0,3) | 605 | itvtrackdatauser | 16384 | 6 | f | t | -1 | 10792 | 499 | 499 | 1663 | |
(0,5) | 18350176 | itvtrackdatapos | 16384 | 6 | f | t | -1 | 10792 | 499 | 499 | 1663 | |
(0,6) | 557 | template1 | 10 | 6 | t | t | -1 | 10792 | 499 | 499 | 1663 | | {postgres=CT/postgres}
(0,7) | 558 | template0 | 10 | 6 | t | f | -1 | 10792 | 499 | 499 | 1663 | | {postgres=CT/postgres}
(6 rows)

itvtrackdata=>

--Ian



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin