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 E8C6E11F6C31 for ; Mon, 24 Apr 2006 15:18:30 -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 54680-01-5 for ; Mon, 24 Apr 2006 15:18:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.198]) by postgresql.org (Postfix) with ESMTP id EE29011F6C2A for ; Mon, 24 Apr 2006 10:09:37 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id o1so809128nzf for ; Mon, 24 Apr 2006 06:09:38 -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=QnjtU2K49F8PSFzPrJBQ6+MjuRHDxY5cJhcfewC4frGukjztZOun1JhIdddVTUoQu6Xcr95NckSXJda0J23/aHHgfJH3h758gLlOlr2AnAJrBJJ5ws41Kwfft7IubnbauXW4Ip0gC7gWJuTFOg++UpdzBT58y5KQvIum79nbyEo= Received: by 10.36.215.21 with SMTP id n21mr6849168nzg; Mon, 24 Apr 2006 06:09:38 -0700 (PDT) Received: by 10.36.68.5 with HTTP; Mon, 24 Apr 2006 06:09:38 -0700 (PDT) Message-ID: <674d1f8a0604240609m3e2d75a2m89c0654ba94df785@mail.gmail.com> Date: Mon, 24 Apr 2006 18:39:38 +0530 From: "Gourish Singbal" To: "pgsql-admin@postgresql.org" Subject: Re: Autovacuuming In-Reply-To: <20060424124155.GA13361@surnet.cl> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_21168_32542414.1145884178110" References: <674d1f8a0604232251v4649b456xc542fd831f8db4@mail.gmail.com> <20060424124155.GA13361@surnet.cl> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/244 X-Sequence-Number: 21413 ------=_Part_21168_32542414.1145884178110 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Show autovacuum displays 'on'. db1=3D# select datname, datvacuumxid, datfrozenxid from pg_database; datname | datvacuumxid | datfrozenxid ---------------------+--------------+-------------- postgres | 499 | 499 db1 | 71677296 | 3292902769 db2 | 99962197 | 3321187670 db3 | 72274322 | 3293587302 template1 | 499 | 499 template0 | 499 | 499 (6 rows) db1=3D# SELECT datname, age(datfrozenxid) FROM pg_database; datname | age ---------------------+------------ postgres | 143239306 db1 | 1145304332 db2 | 1117019431 db3 | 1144619799 template1 | 143239306 template0 | 143239306 (6 rows) Since we are currently 465208401 transactions away from the 1610512733. Can i safely sit back and wait for the autovacuum deamon to do its job without any issues ?. On 4/24/06, Alvaro Herrera wrote: > > Gourish Singbal wrote: > > The Age of the database is 1144759836 . We have enabled autovacuuming . > > The details are:- > > autovacuum =3D on > > Are you sure autovacuum is enabled? Do a "SHOW autovacuum" just to be > sure. > > The test for database-wide vacuum is: > > database age > big_constant > > where: > > big_constant =3D (MaxTransactionId >> 3) * 3 - 100000) =3D 1610512733 > MaxTransactionId =3D 0xFFFFFFFF > database age =3D Max(frozen age, vacuum age) > frozen age =3D next Transaction id - pg_database.datfrozenxid > vacuum age =3D next Transaction id - pg_database.datvacuumxid > > > Your database is still 465752897 transactions away from that apparently. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > -- Best, Gourish Singbal ------=_Part_21168_32542414.1145884178110 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
 
Show autovacuum displays 'on'.
 
db1=3D# select datname, datvacuumxid, datfrozenxid from pg_database;       datname     = ;  | datvacuumxid | datfrozenxid
---------------------+------------= --+--------------
 postgres      &nbs= p;     |        = ;  499 |          499
 db1           |=      71677296 |   3292902769
 db2 |&n= bsp;    99962197 |   3321187670
 db3 =       |     72274322 | &n= bsp; 3293587302
 template1       = ;    |         = 499 |          499
 t= emplate0           | = ;         499 |   &n= bsp;      499
(6 rows)

db1=3D# SELECT datname, age(datfrozenxid) FROM pg_datab= ase;
       datname    = ;   |    age
---------------------+------------=
 postgres         &nb= sp;  |  143239306
 db1      = ;    | 1145304332
 db2 | 1117019431
 db3       | 1144619799
 tem= plate1           |  = 143239306
 template0        = ;   |  143239306
(6 rows)
 
Since we are currently 465208401 transactions away from the 1610512733= .
 
Can i safely sit back and wait for the autovacuum deamon to do its job= without any issues ?.
 
 
On 4/24/06, = Alvaro Herrera <alvher= re@commandprompt.com> wrote:
Gourish Singbal wrote:
> T= he Age of the database is 1144759836 . We have enabled autovacuuming .
> The details are:-
> autovacuum =3D on

Are you sure autova= cuum is enabled?  Do a "SHOW autovacuum" just to be
= sure.

The test for database-wide vacuum is:

database age >= big_constant

where:

big_constant =3D (MaxTransactionId >> 3) * 3 - = 100000) =3D 1610512733
MaxTransactionId =3D 0xFFFFFFFF
database age = =3D Max(frozen age, vacuum age)
frozen age =3D next Transaction id - pg_= database.datfrozenxid
vacuum age =3D next Transaction id - pg_database.datvacuumxid

Your database is still 465752897 transactions away from that apparently.<= br>
--
Alvaro Herrera        =             &nb= sp;            http://www.CommandPrompt.com/
The PostgreSQL Company - Command Promp= t, Inc.



--
Best,
Gou= rish Singbal=20 ------=_Part_21168_32542414.1145884178110--