SEO

I have autovacuum turned on with the default settings.
The issue is that we are mostly inserting records, sometimes updating,
very seldom deleting. The ones which get deleted are in partitioned
tables, and will be done away with via truncate once the data retention
period is over.

Logs show that autovacuum is running on all databases.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, May 25, 2006 2:01 PM
To: Benjamin Krajmalnik
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_clog questions

"Benjamin Krajmalnik" <kraj@illumen.com> writes:
> While checking our server, I noticed quite a few files in the pg_clog
> directory.

> Is there a maintenance task which can be run to purge the files which
> are no longer needed?

VACUUM. If they're not disappearing, you aren't running an adequate
vacuum regime --- missing out some databases, perhaps?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Benjamin Krajmalnik" <kraj@illumen.com> writes:
>>> While checking our server, I noticed quite a few files in the pg_clog
>>> directory.
>>> Is there a maintenance task which can be run to purge the files which
>>> are no longer needed?

>> VACUUM. If they're not disappearing, you aren't running an adequate
>> vacuum regime --- missing out some databases, perhaps?

> I have autovacuum turned on with the default settings.

Hm. I believe that autovac only does database-wide vacuums when it
thinks they're necessary to prevent transaction wraparound failures.
Which would mean that it'd let pg_clog grow to something on the order
of half a gig before any truncation would happen. That's probably
insufficiently aggressive :-(

Alvaro, Matthew, any thoughts about improving that? It strikes me that
Alvaro's work-in-progress on maintaining per-table xmin info would allow
truncation of clog without actually insisting on a database-wide VACUUM,
but that's not going to be any help for existing releases.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane wrote:
> "Benjamin Krajmalnik" <kraj@illumen.com> writes:
> >>> While checking our server, I noticed quite a few files in the pg_clog
> >>> directory.
> >>> Is there a maintenance task which can be run to purge the files which
> >>> are no longer needed?
>
> >> VACUUM. If they're not disappearing, you aren't running an adequate
> >> vacuum regime --- missing out some databases, perhaps?
>
> > I have autovacuum turned on with the default settings.
>
> Hm. I believe that autovac only does database-wide vacuums when it
> thinks they're necessary to prevent transaction wraparound failures.
> Which would mean that it'd let pg_clog grow to something on the order
> of half a gig before any truncation would happen. That's probably
> insufficiently aggressive :-(
>
> Alvaro, Matthew, any thoughts about improving that?

Hum, IIRC there is a test somewhere to check pg_database.datvacuumxid
and issue a database-wide vacuum if it gets too old, but the test uses
a-little-less-than-2-billion to fire :-(

This is the code:

/*
* We look for the database that most urgently needs a database-wide
* vacuum. We decide that a database-wide vacuum is needed 100000
* transactions sooner than vacuum.c's vac_truncate_clog() would
* decide to start giving warnings. If any such db is found, we
* ignore all other dbs.
*
* Unlike vacuum.c, we also look at vacuumxid. This is so that
* pg_clog can be kept trimmed to a reasonable size.
*/
freeze_age = (int32) (nextXid - tmp->frozenxid);
vacuum_age = (int32) (nextXid - tmp->vacuumxid);
tmp->age = Max(freeze_age, vacuum_age);

this_whole_db = (tmp->age >
(int32) ((MaxTransactionId >> 3) * 3 - 100000));

Maybe it was foolish to use such a large constant in the vacuumxid case
:-(

> It strikes me that Alvaro's work-in-progress on maintaining per-table
> xmin info would allow truncation of clog without actually insisting on
> a database-wide VACUUM, but that's not going to be any help for
> existing releases.

Yeah. The patch is almost ready BTW and I was about to post it on
Saturday but I got distracted. I have a problem with sinval messages
:-( I'll post it later today.

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Alvaro Herrera wrote:
> Tom Lane wrote:
>> Hm. I believe that autovac only does database-wide vacuums when it
>> thinks they're necessary to prevent transaction wraparound failures.
>> Which would mean that it'd let pg_clog grow to something on the order
>> of half a gig before any truncation would happen. That's probably
>> insufficiently aggressive :-(
>>
>> Alvaro, Matthew, any thoughts about improving that?

Not really. Alvara mentioned reducing the constant that we test against
for DB wide vacuum. I'm a little concerned that might result is
database-wide vacuums more often than desired, but I don't see a better
answer off the top of my head.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match