Thread: pg_clog questions
While checking our server, I noticed quite a few files in the pg_clog directory.
These files date from today back to the day when I installed PostgreSQL.
Is there a maintenance task which can be run to purge the files which are no longer needed?
I assume the majority of them are not needed, since the data has already been commited.
I back up nightly with pg_dump, but this obviously does not reset these files.
Do I need to do a backup with archiving in order to clear these out?
TIA,
Benjamin
"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.
Ok After reading this mail i too went to check out pg_clog and noticed that there were 368 file dating back to the day we had installed postgresql and occuping 92 MB od space.
So i decided to vacuum the postgres and template1 databases since the other databases are database - wide vacuumed daily.
vacuuming postgres database did not do any good so went to template1 . On completion found that the files had reduced to 109 number and size of pg_clog was 27 MB now.
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
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.
also age went down to
postgres=# SELECT datname, age(datfrozenxid) FROM pg_database;
datname | age
---------------------+------------
postgres | 73
template1 | 128
template0 | 382360894
...etc
datname | age
---------------------+------------
postgres | 73
template1 | 128
template0 | 382360894
...etc
thanks for the info in the email thread.
On 5/26/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
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
---------------------------(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
--
Best,
Gourish Singbal
Wanted to know when is the feature of automatically removing the unwanted pg_clog files going to be implemented in postgresql ...
this link is not present in 8.1.4 docs ...
Thanks for all the help
On 5/26/06, Gourish Singbal <gourish@gmail.com> wrote:
Ok After reading this mail i too went to check out pg_clog and noticed that there were 368 file dating back to the day we had installed postgresql and occuping 92 MB od space.So i decided to vacuum the postgres and template1 databases since the other databases are database - wide vacuumed daily.vacuuming postgres database did not do any good so went to template1 . On completion found that the files had reduced to 109 number and size of pg_clog was 27 MB now.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 theredrwx------ 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.also age went down topostgres=# SELECT datname, age(datfrozenxid) FROM pg_database;
datname | age
---------------------+------------
postgres | 73
template1 | 128
template0 | 382360894
...etcthanks for the info in the email thread.
On 5/26/06, Alvaro Herrera <alvherre@commandprompt.com > wrote: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
---------------------------(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--
Best,
Gourish Singbal
--
Best,
Gourish Singbal
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