Thread: pg_clog & vacuum oddness
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 -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
Jeff <threshar@torgo.978.org> writes:
> [ 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
On Wed, 29 Oct 2003 11:32:12 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Jeff <threshar@torgo.978.org> writes: > > [ 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. -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
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? Tom Lane wrote: > > Jeff <threshar@torgo.978.org> writes: > > [ 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 -- Bill MacArthur Webmaster DHS Club
On Wed, 29 Oct 2003 11:53:38 -0500 DHS Webmaster <webmaster@dhs-club.com> wrote: > 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 -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
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 Jeff wrote: > > On Wed, 29 Oct 2003 11:53:38 -0500 > DHS Webmaster <webmaster@dhs-club.com> wrote: > > > 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 > > -- > Jeff Trout <jeff@jefftrout.com> > http://www.jefftrout.com/ > http://www.stuarthamm.net/
Jeff <threshar@torgo.978.org> writes:
> 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
DHS Webmaster <webmaster@dhs-club.com> writes:
> 1. Should we be doing a periodic vacuum on template1?
Yes.
> 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
Tom Lane wrote: > Jeff <threshar@torgo.978.org> writes: > >>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
threshar@torgo.978.org (Jeff) writes:
> On Wed, 29 Oct 2003 11:53:38 -0500
> DHS Webmaster <webmaster@dhs-club.com> wrote:
>
>> 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."
--
output = ("cbbrowne" "@" "libertyrms.info")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)
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