X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 0CF569F9EEB for ; Fri, 26 May 2006 03:39:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 47394-06 for ; Fri, 26 May 2006 03:39:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id D54CB9FA16E for ; Fri, 26 May 2006 03:39:35 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id m3so38156ugc for ; Thu, 25 May 2006 23:39:34 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=YcYgSgbx6QJnRQEwH5ykxDnISPR+rVjcfL0tJNAi1Eal/zSpXOJAdjQqYUA6hddFCJ+jnUnthKgT7YDkFCzyk8ERN71qkuwEvmSUqrZReogjywAG7hoXHaQVZ4aO6yESCyLoBOZY96xPh0lg53sbUucAHeiS+z98GVVxebZ3h7o= Received: by 10.66.222.9 with SMTP id u9mr132890ugg; Thu, 25 May 2006 23:39:33 -0700 (PDT) Received: by 10.66.222.12 with HTTP; Thu, 25 May 2006 23:39:33 -0700 (PDT) Message-ID: <674d1f8a0605252339k248ed8fay33c56e3939843417@mail.gmail.com> Date: Fri, 26 May 2006 12:09:33 +0530 From: "Gourish Singbal" To: "Benjamin Krajmalnik" , "Tom Lane" , pgsql-admin@postgresql.org, "Matthew T. O'Connor" Subject: Re: pg_clog questions In-Reply-To: <20060525223944.GO13700@surnet.cl> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_112120_21463656.1148625573875" References: <8511B4970E0D124898E973DF496F9B432516EA@stash.stackdump.local> <20060525223944.GO13700@surnet.cl> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/374 X-Sequence-Number: 21906 ------=_Part_112120_21463656.1148625573875 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 and if i am not mistaken this is the only file that the postgres is using a= t this time. also age went down to postgres=3D# SELECT datname, age(datfrozenxid) FROM pg_database; datname | age ---------------------+------------ postgres | 73 template1 | 128 template0 | 382360894 ...etc thanks for the info in the email thread. On 5/26/06, Alvaro Herrera 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 owne= r > > of a filenode is? > > Sure -- pg_class.relowner from pg_class where relfilenode =3D ' ... ' > > -- > 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 > --=20 Best, Gourish Singbal ------=_Part_112120_21463656.1148625573875 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
 
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 postgresq= l 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 p= g_clog was 27 MB now.
 
But still these were too many files hence decide to peform vacuum free= ze 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     7= 2 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=3D# SELECT datname, age(datfrozenxid) FROM pg_database;
&n= bsp;      datname     &nb= sp; |    age
---------------------+------------
 = postgres            = |         73
 template1&nbs= p;          |   = ;     128
 template0    &nb= sp;      |  382360894
...etc
 
thanks for the info in the email thread.

 
On 5/26/06, = Alvaro Herrera <alvher= re@commandprompt.com> wrote:
Benjamin Krajmalnik wrote:
&g= t; I just ran both VACUUM ANALYZE and VACUUM on all of the databases (the > project database and the database created for the default user).
&g= t; All of the commit logs are still there.

Did you vacuum the templa= te1 (and possibly postgres) databases as well?

> Do they get clea= red only by VACUUM FULL?

No, VACUUM suffices.

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

Sur= e -- pg_class.relowner from pg_class where relfilenode =3D ' ... '

--
Alvaro Herrera         &= nbsp;           &nbs= p;          http://www.CommandPrompt.com/
PostgreSQ= L 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 c= olumn's datatypes do not
      match



--
Best,
Gourish Singbal=20 ------=_Part_112120_21463656.1148625573875--