pg_clog & vacuum oddness

[Jeff]

I have a machine that typically runs good, but more and more recently
when vacuum runs everything (not just pg) comes to a griding halt. If
iostat is to be believed, there isn't too much IO going on - 300kB/sec
write, 0kB read.

Now before we diagnose this as "bah!" I noticed something odd - in my
pg_clog directory I have [currently] 384 files. there seem to be about
3-6/day in there. They all (except for hte last) have the same size of
200someodd-kB.

The DB is fairly active with around 20-100 (varies wildly)
insert/update/delete a second. I had vacuum & fsm tuned so my UnUsed
was rather steady.

The thing that bothers me most is why all that stuff in the clog?
There are entries in there that are older than the last time I spun PG.


Machine is a 2xp3-800mhz, 2gb, linux 2.2.17 [old], pg 7.3.4

[Tom Lane]

[ pg_clog not getting truncated ]

pg_clog is truncated on the basis of the oldest completely vacuumed
database in your installation. Most likely your maintenance script
is failing to vacuum some database(s) (template1, perhaps?) and/or
is doing table-by-table vacuums rather than an unqualified VACUUM.

I doubt this explains any performance problems though. Old pg_clog
segments don't do anything except sit there.

regards, tom lane

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

[Jeff]

On Wed, 29 Oct 2003 11:32:12 -0500

Post by Tom Lane

[ pg_clog not getting truncated ]

pg_clog is truncated on the basis of the oldest completely vacuumed
database in your installation. Most likely your maintenance script
is failing to vacuum some database(s) (template1, perhaps?) and/or
is doing table-by-table vacuums rather than an unqualified VACUUM.
I doubt this explains any performance problems though. Old pg_clog
segments don't do anything except sit there.
regards, tom lane

thanks, that did it - I hadn't been vacuuming a rarely used db we have
tossed on there too.

[DHS Webmaster]

This thread caught my eye and I decided to look at our pg_clog
directory. Sure enough we have got every clog file since we upgraded
back in April, 0000 - 02F8.
We vacuum our working database nightly. Although this is not a 'full',
we don't exclude any tables. We don't do anything with template1
(knowingly), so we do not perform any maintenance on it either.
Questions:
1. Should we be doing a periodic vacuum on template1?
2. Is what I am seeing possibly indicative of something else beside
template1 that would show up the postgres log.
3. It is safe to delete all the clog files prior to the last restart of
postgres, yes?

Post by Tom Lane

[ pg_clog not getting truncated ]

pg_clog is truncated on the basis of the oldest completely vacuumed
database in your installation. Most likely your maintenance script
is failing to vacuum some database(s) (template1, perhaps?) and/or
is doing table-by-table vacuums rather than an unqualified VACUUM.
I doubt this explains any performance problems though. Old pg_clog
segments don't do anything except sit there.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Jeff]

On Wed, 29 Oct 2003 11:53:38 -0500

Post by DHS Webmaster
We vacuum our working database nightly. Although this is not a 'full',
we don't exclude any tables. We don't do anything with template1
(knowingly), so we do not perform any maintenance on it either.

Why not go through the list in pg_database to make sure you didn't
forget about any (like I did).


given that template0 and 1 rarely change.. I don't see why we'd need to
vacuum them

[DHS Webmaster]

Well, you know, I looked at the list and saw only template0, template1 &
our working DB. So just for kicks I vacuumed template1. That cleared the
directory. To my knowledge we did not modify template1 one when we setup
the database initially, so perhaps it just needed a vacuum to set the
stage correctly. Either that or during our data import, there was
something in the import file that referenced template1 which created a
need for vacuuming.
Whatever the case, this was an interesting exercise which I'm sure will
prove useful in the future.
Thank you.
--
Bill MacArthur
Webmaster
DHS Club

Post by Jeff
On Wed, 29 Oct 2003 11:53:38 -0500

Post by DHS Webmaster
We vacuum our working database nightly. Although this is not a 'full',
we don't exclude any tables. We don't do anything with template1
(knowingly), so we do not perform any maintenance on it either.

Why not go through the list in pg_database to make sure you didn't
forget about any (like I did).
given that template0 and 1 rarely change.. I don't see why we'd need to
vacuum them
--
http://www.jefftrout.com/
http://www.stuarthamm.net/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Post by Jeff
given that template0 and 1 rarely change.. I don't see why we'd need to
vacuum them

You have to vacuum template1 periodically if you want pg_clog to shrink.
You may know that you've never changed it, but the VACUUM code doesn't
know that.

template0 need not get vacuumed as long as it's marked datallowconn = false
in pg_database. VACUUM assumes it's not been changed if that flag is
present.

regards, tom lane

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

[Gaetano Mendola]

Post by Tom Lane

Post by Jeff
given that template0 and 1 rarely change.. I don't see why we'd need to
vacuum them

You have to vacuum template1 periodically if you want pg_clog to shrink.

WOW, I saved 200 MB in one shoot vacuuming template 1 !

I learned this after 2 years of postgres, I'm a dummy.



Regards
Gaetano Mendola



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

[Andrzej Schulz]

Hi,

Short question how to fetch datas from Postgres into XML files without
using PHP on server, I need to optimize and to find the best way to do
it. Can someone help me. Is it possible

Rg
Andrzej Schulz


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Christopher Browne]

Post by Jeff
On Wed, 29 Oct 2003 11:53:38 -0500

Post by DHS Webmaster
We vacuum our working database nightly. Although this is not a 'full',
we don't exclude any tables. We don't do anything with template1
(knowingly), so we do not perform any maintenance on it either.

Why not go through the list in pg_database to make sure you didn't
forget about any (like I did).
given that template0 and 1 rarely change.. I don't see why we'd need to
vacuum them

template0 is probably set to 'not changeable' so that you can't even
log in to it.

template1 probably isn't hit a _lot_, but surely not "not at all." It
is accessed at such times as:

- When you run "createdb", data probably gets used from there to
populate the new DB.

- When you update user IDs, that's shared information likely to
"touch" template1.

You don't need to vacuum it often, but "seldom" is not quite the same
as "never."

[Tom Lane]

Post by DHS Webmaster
1. Should we be doing a periodic vacuum on template1?

Yes.

Post by DHS Webmaster
3. It is safe to delete all the clog files prior to the last restart of
postgres, yes?

NO. That WILL destroy your database. Much safer to let VACUUM do
it for you.

regards, tom lane

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