From szabolcs.balla@confinsystems.com Thu Jan 5 12:23:21 2006 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D91269DC898 for ; Thu, 5 Jan 2006 08:23:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68050-07 for ; Thu, 5 Jan 2006 08:23:24 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id B22F49DC882 for ; Thu, 5 Jan 2006 08:23:19 -0400 (AST) Received: from galagonx.fixdial.interware.hu (galagonx.fixdial.interware.hu [195.70.45.31]) by svr4.postgresql.org (Postfix) with ESMTP id 694605AF073 for ; Thu, 5 Jan 2006 12:23:22 +0000 (GMT) Received: from szballa.intra ([172.20.80.68]) by galagonx.fixdial.interware.hu with esmtp (Exim 3.36 #1 (Debian)) id 1EuU9H-0000mC-00 for ; Thu, 05 Jan 2006 13:23:19 +0100 Subject: Vacuum is needed or not? From: Szabolcs BALLA To: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary="=-PGvPIaeGobuDJIHIwAab" Date: Thu, 05 Jan 2006 13:23:18 +0100 Message-Id: <1136463798.4221.35.camel@bh> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200601/132 X-Sequence-Number: 20220 --=-PGvPIaeGobuDJIHIwAab Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi, How do I know vacuum is need or not? I mean, are there any query to know status of statistics, tables, indexes, etc? (7.4.7) Our database size bigger than just go and do it. :( Thanks Szabek --=-PGvPIaeGobuDJIHIwAab Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit Hi,

How do I know vacuum is need or not? I mean, are there any query to know status of statistics, tables, indexes, etc?
(7.4.7)
Our database size bigger than just go and do it. :(

Thanks
Szabek --=-PGvPIaeGobuDJIHIwAab-- From szballa@confinsystems.com Thu Jan 5 13:12:01 2006 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 0CE249DCAD4 for ; Thu, 5 Jan 2006 09:12:01 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 76157-08 for ; Thu, 5 Jan 2006 09:12:04 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from galagonx.fixdial.interware.hu (galagonx.fixdial.interware.hu [195.70.45.31]) by postgresql.org (Postfix) with ESMTP id C95819DC882 for ; Thu, 5 Jan 2006 09:11:58 -0400 (AST) Received: from szballa.intra ([172.20.80.68]) by galagonx.fixdial.interware.hu with esmtp (Exim 3.36 #1 (Debian)) id 1EuUuP-0001DL-00 for ; Thu, 05 Jan 2006 14:12:01 +0100 Subject: Vacuum is needed or not? From: Szabolcs BALLA To: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary="=-7m99+ftlNzcuzR9bAJqi" Date: Thu, 05 Jan 2006 14:12:01 +0100 Message-Id: <1136466721.4221.39.camel@bh> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[AWL=-0.001, HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200601/69 X-Sequence-Number: 20157 --=-7m99+ftlNzcuzR9bAJqi Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi, How do I know vacuum is need or not? I mean, are there any query to know status of statistics, tables, indexes, etc? (7.4.7) Our database size bigger than just go and do it. :( Thanks, Szabek --=-7m99+ftlNzcuzR9bAJqi Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit Hi,

How do I know vacuum is need or not? I mean, are there any query to know status of statistics, tables, indexes, etc?
(7.4.7)
Our database size bigger than just go and do it. :(

Thanks,
Szabek --=-7m99+ftlNzcuzR9bAJqi-- From systemguards@gmail.com Thu Jan 5 14:37:21 2006 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 554579DC85B for ; Thu, 5 Jan 2006 10:37:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 91658-06 for ; Thu, 5 Jan 2006 10:37:25 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.193]) by postgresql.org (Postfix) with ESMTP id EB51F9DC86D for ; Thu, 5 Jan 2006 10:37:18 -0400 (AST) Received: by wproxy.gmail.com with SMTP id 57so2583626wri for ; Thu, 05 Jan 2006 06:37:23 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Fd5qGBrBqPV88jhyNpO4BUppv9KeBKQPKnHBk3IOkhFwqDFe0zmcwNa83I1a38TjLaPsFuByH4xXJvfAHYZVyq++63Rrcc41IrGjQUCKBLQGd3heGe6q3w8cdVekjGngd1cFAUREeykYHCi9WdHEwd8lFV0LiBQ340/gdMLlKeU= Received: by 10.54.110.19 with SMTP id i19mr3892295wrc; Thu, 05 Jan 2006 06:37:22 -0800 (PST) Received: by 10.54.92.14 with HTTP; Thu, 5 Jan 2006 06:37:22 -0800 (PST) Message-ID: Date: Thu, 5 Jan 2006 09:37:22 -0500 From: Jaime Casanova To: Szabolcs BALLA Subject: Re: Vacuum is needed or not? Cc: pgsql-admin@postgresql.org In-Reply-To: <1136466721.4221.39.camel@bh> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <1136466721.4221.39.camel@bh> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.082 required=5 tests=[AWL=0.082] X-Spam-Score: 0.082 X-Spam-Level: X-Archive-Number: 200601/70 X-Sequence-Number: 20158 On 1/5/06, Szabolcs BALLA wrote: > Hi, > > How do I know vacuum is need or not? I mean, are there any query to know > status of statistics, tables, indexes, etc? > (7.4.7) > Our database size bigger than just go and do it. :( > > Thanks, > Szabek VACUUM is needed at least every billion transactions... execute it in a script periodically... if you are in doubt if you need VACUUM then you need it urgently, execute it right now!!! -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) From systemguards@gmail.com Thu Jan 5 15:48:00 2006 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 818DD9DC945 for ; Thu, 5 Jan 2006 11:48:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 13926-05 for ; Thu, 5 Jan 2006 11:48:05 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.192]) by postgresql.org (Postfix) with ESMTP id 240919DC960 for ; Thu, 5 Jan 2006 11:47:57 -0400 (AST) Received: by wproxy.gmail.com with SMTP id 57so2598379wri for ; Thu, 05 Jan 2006 07:48:03 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=GCAWhLliKQ8Fe7zrcGxmyUeWnCQi7s94hQG2+SpypS0GPThnt+x79BgFFg+NyikwyIM36Zv/m+PAhlgJzscBs3/SbgsZtvgwBKrJTtkkzj6rLvYvMlJTSt1aKz6iTvdiEZOLXUmWm4xPtgpUHwQiHaNBtx56mCvIfNqoXrzo99w= Received: by 10.54.113.6 with SMTP id l6mr4032090wrc; Thu, 05 Jan 2006 07:48:02 -0800 (PST) Received: by 10.54.92.14 with HTTP; Thu, 5 Jan 2006 07:48:02 -0800 (PST) Message-ID: Date: Thu, 5 Jan 2006 10:48:02 -0500 From: Jaime Casanova To: Szabolcs BALLA Subject: Re: Vacuum is needed or not? Cc: postgre In-Reply-To: <1136475086.4221.48.camel@bh> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <1136466721.4221.39.camel@bh> <1136475086.4221.48.camel@bh> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.083 required=5 tests=[AWL=0.083] X-Spam-Score: 0.083 X-Spam-Level: X-Archive-Number: 200601/71 X-Sequence-Number: 20159 On 1/5/06, Szabolcs BALLA wrote: > > VACUUM is needed at least every billion transactions... execute it in a > script periodically... if you are in doubt if you need VACUUM then you need > it urgently, execute it right now!!! > > yes, and i running every weekend a full analyze, but actually i think it'= s > need again, but just for feel i couldn't stop the server. > I need evidence. (oldspice rulez ;)) > > Szabek a plain VACUUM (without full) is enough in most cases... and you don't need to stop the server for a plain VACUUM... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) From gbarosio@gmail.com Thu Jan 5 17:30:21 2006 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 43C249DC817 for ; Thu, 5 Jan 2006 13:30:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 36095-10 for ; Thu, 5 Jan 2006 13:30:19 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.206]) by postgresql.org (Postfix) with ESMTP id 542E29DC812 for ; Thu, 5 Jan 2006 13:30:16 -0400 (AST) Received: by nproxy.gmail.com with SMTP id o60so38822nfa for ; Thu, 05 Jan 2006 09:30:14 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=EDEdatGAbF59k1caw2G3W+z03d8vlKNqLvskn6hWQ3u90mBL1QidWYAPOv3DRApz9xujCDGTpFogq6e8JQKdXfVObxewGk+IZHwlBB0SJssQZH6d1SOleYmg5jbNDEC9ddBaPCka3XOOuj3gX5f4s1aumh4TzHDag+qyQ4pZ90A= Received: by 10.48.254.2 with SMTP id b2mr705422nfi; Thu, 05 Jan 2006 09:30:13 -0800 (PST) Received: by 10.49.2.17 with HTTP; Thu, 5 Jan 2006 09:30:13 -0800 (PST) Message-ID: Date: Thu, 5 Jan 2006 17:30:13 +0000 From: Guido Barosio To: Jaime Casanova Subject: Re: Vacuum is needed or not? Cc: Szabolcs BALLA , postgre In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_29823_13676893.1136482213120" References: <1136466721.4221.39.camel@bh> <1136475086.4221.48.camel@bh> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.08 required=5 tests=[AWL=0.079, HTML_MESSAGE=0.001] X-Spam-Score: 0.08 X-Spam-Level: X-Archive-Number: 200601/72 X-Sequence-Number: 20160 ------=_Part_29823_13676893.1136482213120 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline http://www.postgresql.org/docs/8.0/interactive/maintenance.html Read all, in order to understand the following quote: " Since periodic VACUUM runs are needed anyway for the reasons described earlier, it's unlikely that any table would not be vacuumed for as long as = a billion transactions. But to help administrators ensure this constraint is met, VACUUM stores transaction ID statistics in the system table pg_databas= e. In particular, the datfrozenxid column of a database's pg_database row is updated at the completion of any database-wide VACUUM operation (i.e., VACUUM that does not name a specific table). The value stored in this field is the freeze cutoff XID that was used by that VACUUM command. All normal XIDs older than this cutoff XID are guaranteed to have been replaced by FrozenXID within that database. A convenient way to examine this informatio= n is to execute the query SELECT datname, age(datfrozenxid) FROM pg_database; " Best wishes, Guido On 1/5/06, Jaime Casanova wrote: > > On 1/5/06, Szabolcs BALLA wrote: > > > > > VACUUM is needed at least every billion transactions... > > execute it in a > > script periodically... if you are in doubt if you need > VACUUM then you need > > it urgently, execute it right now!!! > > > > > yes, and i running every weekend a full analyze, but actually i think > it's > > need again, but just for feel i couldn't stop the server. > > I need evidence. (oldspice rulez ;)) > > > > Szabek > > a plain VACUUM (without full) is enough in most cases... and you don't > need to stop the server for a plain VACUUM... > > -- > regards, > Jaime Casanova > (DBA: DataBase Aniquilator ;) > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- "Adopting the position that you are smarter than an automaticoptimization algorithm is generally a good way to achieve lessperformance, not more" - Tom Lane. ------=_Part_29823_13676893.1136482213120 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
http://www.postgresql.org/docs/8.0/interactive/maintenance.html
 
Read all, in order to understand the following quote:
 
"

Since periodic VACUUM runs are needed anyway = for the reasons described earlier, it's unlikely that any table would not b= e vacuumed for as long as a billion transactions. But to help administrator= s ensure this constraint is met,=20 VACUUM stores transaction ID statistics in the s= ystem table pg_database. In particular, the datfrozenxid column of a database's pg_database row is updated at the completion of any database-wide VACUUM operation (i.e., VACUUM= that does not name a specific table). The value stored in this field = is the freeze cutoff XID that was used by that=20 VACUUM command. All normal XIDs older than this = cutoff XID are guaranteed to have been replaced by Fr= ozenXID within that database. A convenient way to examine this informa= tion is to execute the query=20

SELECT datname, age(datfrozenxid) FROM pg=
_database;
"
 
Best wishes,
Guido

 
On 1/5/06, J= aime Casanova <systemguard= s@gmail.com> wrote:
On 1/5/06, Szabolcs BALLA <szballa@confinsystems.com>= ; wrote:
>
>
VACUUM is needed at least every billion transactions...=

execute it in a
> script periodically... if you are in doubt = if you need
VACUUM then you need
> it urgently, execute it right n= ow!!!

>
> yes, and i running every weekend a full analyze, but a= ctually i think it's
> need again, but just for feel i couldn't stop = the server.
> I need evidence. (oldspice rulez ;))
>
> Sz= abek

a plain VACUUM (without full) is enough in most cases... and you do= n't
need to stop the server for a plain VACUUM...

--
regards,<= br>Jaime Casanova
(DBA: DataBase Aniquilator ;)

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



--
"Adopting the = position that you are smarter than an automaticoptimization algorithm is ge= nerally a good way to achieve lessperformance, not more" - Tom Lane.= =20 ------=_Part_29823_13676893.1136482213120-- From systemguards@gmail.com Fri Jan 13 04:21:22 2006 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 704369DCAD5 for ; Fri, 13 Jan 2006 00:21:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05721-01 for ; Fri, 13 Jan 2006 00:21:20 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.195]) by postgresql.org (Postfix) with ESMTP id B25A79DC943 for ; Fri, 13 Jan 2006 00:21:19 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i28so616112wra for ; Thu, 12 Jan 2006 20:21:19 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Om5dWzDKmswwqjvmGm3V4GTMmK6bCXo4k/qxVhxIGj2nCWgrLmOrJid360rMF30kVQN2iJtoUNzL1eT3xpiIneeqCWNuiyULDwVrOZwGwC3cExTimaFDPk8UR3tZ59SQpxOUBzytCPCW9F3cqTFTi9Qe8DYsQy9k+C0XX/OFdow= Received: by 10.54.152.13 with SMTP id z13mr35767wrd; Thu, 12 Jan 2006 20:21:19 -0800 (PST) Received: by 10.54.93.8 with HTTP; Thu, 12 Jan 2006 20:21:19 -0800 (PST) Message-ID: Date: Thu, 12 Jan 2006 23:21:19 -0500 From: Jaime Casanova To: Szabolcs BALLA Subject: Re: Vacuum is needed or not? Cc: pgsql-admin@postgresql.org In-Reply-To: <1136463798.4221.35.camel@bh> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <1136463798.4221.35.camel@bh> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.088 required=5 tests=[AWL=0.088] X-Spam-Score: 0.088 X-Spam-Level: X-Archive-Number: 200601/155 X-Sequence-Number: 20243 On 1/5/06, Szabolcs BALLA wrote: > Hi, > > How do I know vacuum is need or not? I mean, are there any query to know > status of statistics, tables, indexes, etc? at least, once every billion transactions to be safe... > (7.4.7) > Our database size bigger than just go and do it. :( > ahh... then execute it once a week or once a day, for avoid excessive growi= ng... > Thanks > Szabek > > -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) From jnasby@pervasive.com Fri Jan 13 19:18:59 2006 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D74B39DD741 for ; Fri, 13 Jan 2006 15:18:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67246-09 for ; Fri, 13 Jan 2006 15:18:52 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 2306F9DD73F for ; Fri, 13 Jan 2006 15:18:50 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 5C1563982E; Fri, 13 Jan 2006 13:18:47 -0600 (CST) Date: Fri, 13 Jan 2006 13:18:47 -0600 From: "Jim C. Nasby" To: Jaime Casanova Cc: Szabolcs BALLA , pgsql-admin@postgresql.org Subject: Re: Vacuum is needed or not? Message-ID: <20060113191847.GK9017@pervasive.com> References: <1136463798.4221.35.camel@bh> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.088 required=5 tests=[AWL=0.088] X-Spam-Score: 0.088 X-Spam-Level: X-Archive-Number: 200601/163 X-Sequence-Number: 20251 On Thu, Jan 12, 2006 at 11:21:19PM -0500, Jaime Casanova wrote: > On 1/5/06, Szabolcs BALLA wrote: > > Hi, > > > > How do I know vacuum is need or not? I mean, are there any query to know > > status of statistics, tables, indexes, etc? > > at least, once every billion transactions to be safe... > > > (7.4.7) > > Our database size bigger than just go and do it. :( > > > > ahh... then execute it once a week or once a day, for avoid excessive growing... Even once a day can be awefully conservative for many databases. Keep in mind that every single row that is updated or deleted will hang around in the database until you vacuum; on a busy system that can result in a lot of wasted space. And once the bloat is there, it can be hard to get rid of. 7.4.7 has contrib/pg_autovacuum, and I highly recommend it. Also, http://lnk.nu/pervasive-postgres.com/7m4.asp and http://lnk.nu/pervasive-postgres.com/7jd.asp might be interesting reads for you. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461