Обсуждение: 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

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

"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

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

Benjamin Krajmalnik wrote:
> I just ran both VACUUM ANALYZE and VACUUM on all of the databases (the
> project database and the database created for the default user).
> All of the commit logs are still there.

Did you vacuum the template1 (and possibly postgres) databases as well?

> Do they get cleared only by VACUUM FULL?

No, VACUUM suffices.

> On another subject, is there a query I can run to find out who the owner
> of a filenode is?

Sure -- pg_class.relowner from pg_class where relfilenode = ' ... '

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

Thanks.
I revacumed and now they are gone.


-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Thursday, May 25, 2006 4:40 PM
To: Benjamin Krajmalnik
Cc: Tom Lane; pgsql-admin@postgresql.org; Matthew T. O'Connor
Subject: Re: [ADMIN] pg_clog questions

Benjamin Krajmalnik wrote:
> I just ran both VACUUM ANALYZE and VACUUM on all of the databases (the
> project database and the database created for the default user).
> All of the commit logs are still there.

Did you vacuum the template1 (and possibly postgres) databases as well?

> Do they get cleared only by VACUUM FULL?

No, VACUUM suffices.

> On another subject, is there a query I can run to find out who the
owner
> of a filenode is?

Sure -- pg_class.relowner from pg_class where relfilenode = ' ... '

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

I just ran both VACUUM ANALYZE and VACUUM on all of the databases (the
project database and the database created for the default user).
All of the commit logs are still there.
Do they get cleared only by VACUUM FULL?
It is my understanding from the docs that vacuum full places exclusive
locks, correct?  In that case, I canot run it because the database is
getting pounded 24x7 with row insertions.

On another subject, is there a query I can run to find out who the owner
of a filenode is?


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, May 25, 2006 2:21 PM
To: Benjamin Krajmalnik
Cc: pgsql-admin@postgresql.org; Alvaro Herrera; Matthew T. O'Connor
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?

> 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

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.

Gourish Singbal wrote:
> Wanted to know when is the feature of automatically removing the  unwanted
> pg_clog files going to be implemented in postgresql ...
>
> http://www.postgresql.org/docs/7.4/interactive/wal-benefits-later.html
>
> this link is not present in 8.1.4 docs ...

Well, we certainly do remove unneeded pg_clog files -- that's why yours
were removed after you vacuumed all databases that needed them.

We just need a better way of marking them as no longer needed.

That section was removed from the docs because it was no longer
relevant.  All features mentioned there are either implemented or
considered not going to ever be implemented (e.g. UNDO).

> >But still these were too many files hence decide to peform vacuum freeze
> >on template1 and postgres databases. on finishing .. checked the pg_clog
> >dir
> >and found that there was just one file there
> >
> >drwx------  10 postgres wheel    632 2006-05-25 23:15 ..
> >drwx------   2 postgres wheel     72 2006-05-25 23:20 .
> >-rw-------   1 postgres wheel 172032 2006-05-25 23:35 016C
> >
> >and if i am not mistaken this is the only file that the postgres is using
> >at this time.

This is the file that your databases need, which is why it's the only
file that remained.

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