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 AB9499DC84A for ; Mon, 13 Feb 2006 08:35:42 -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 05663-04 for ; Mon, 13 Feb 2006 08:35:45 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from uproxy.gmail.com (uproxy.gmail.com [66.249.92.199]) by postgresql.org (Postfix) with ESMTP id 0A0C99DC9F4 for ; Mon, 13 Feb 2006 08:35:39 -0400 (AST) Received: by uproxy.gmail.com with SMTP id m3so280815ugc for ; Mon, 13 Feb 2006 04:35:42 -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=kJAN1RaNHczUcpGV51Aoc7ONuT868e0Gk3pXwtKGOwOsnF2M37TQ0j5uJm8pnqd184IHmcJ6vyVmw6Kd3BNn8x00A4BK4hNsp32uKzB1JOAAQJTh0vKTdkofCK8VyxjJe+GjIZ/zttJC9KSzsyscCZUqn10CxivIhE72DEUYaCU= Received: by 10.66.255.17 with SMTP id c17mr283473ugi; Mon, 13 Feb 2006 04:35:41 -0800 (PST) Received: by 10.66.224.9 with HTTP; Mon, 13 Feb 2006 04:35:40 -0800 (PST) Message-ID: Date: Mon, 13 Feb 2006 12:35:40 +0000 From: Guido Barosio To: Chris Browne Subject: Re: How to VACUUM this table? "998994633 estimated total rows" Cc: pgsql-admin@postgresql.org In-Reply-To: <60ek29e3tw.fsf@dba2.int.libertyrms.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_18690_29912536.1139834140644" References: <43ED56D3.7040404@mediaroot.de> <60ek29e3tw.fsf@dba2.int.libertyrms.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.404 required=5 tests=[AWL=0.403, HTML_MESSAGE=0.001] X-Spam-Score: 0.404 X-Spam-Level: X-Archive-Number: 200602/157 X-Sequence-Number: 20578 ------=_Part_18690_29912536.1139834140644 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline quote: " If you are quite sure it has few if any dead tuples, it might be something to try to avoid VACUUMing except as needed to evade the 2^31 transaction limit..." You may use the pg_stattuple software, included in the /contrib . This will show you the current scenery, and whether you shall clean or not dead tuples. Best regards, Guido. On 2/12/06, Chris Browne wrote: > > an@mediaroot.de (Aldor) writes: > > I'm just curious about a VACUUM plan of this table: > > > > 1st) When to do VAUUM? > > 2nd) How often to do VACUUM? > > 3rd) With which postgresql.conf paremeters to set up vacuum? > > 4th) When to do a VACUUM FULL? > > 5th) Can autovacuum of 8.1 be used in this case? > > > > I'm a little bit afraid about the size of the table, but I think > > somebody should have a solution... > > > > Here is the complete ANALYZE output of the table: > > > > INFO: "tbl1": scanned 300 of 27744713 pages, containing 10802 live row= s > > and 0 dead rows; 300 rows in sample, 998994633 estimated total rows > > > > The size of the data is 340 GB, this are 40% of the disk-array. > > Vacuuming this table is likely to take a rather long time. Hours and > hours; possibly multiple days. > > I don't think you'll *ever* want to VACUUM FULL this table; I'm not > sure you ever want autovacuum to process it either. > > I instead think you want to choose a time which seems best to start a > Very Long Transaction to issue a VACUUM ANALYZE on it. > > If you are quite sure it has few if any dead tuples, it might be > something to try to avoid VACUUMing except as needed to evade the 2^31 > transaction limit... > > I am not sure that's the only opinion you ought to consider on it... > -- > (reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc")) > http://cbbrowne.com/info/sap.html > Why are there interstate highways in Hawaii? > > ---------------------------(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 > -- /"\ ASCII Ribbon Campaign . \ / - NO HTML/RTF in e-mail . X - NO Word docs in e-mail . / \ ----------------------------------------------------------------- ------=_Part_18690_29912536.1139834140644 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline quote: " If you are quite sure it has few if any dead tuples, it might= be
something to try to avoid VACUUMing except as needed to evade the 2^= 31
transaction limit..."

You may use the pg_stattuple softwa= re, included in the /contrib . This will show you the current scenery, and = whether you shall clean or not dead tuples.

Best regards,
Guido.


On 2/12/06, Chris Browne <cbbrowne@acm.org> wrote:
an@mediaroot.de (Aldor) writes:
&= gt; I'm just curious about a VACUUM plan of this table:
>
> 1st= ) When to do VAUUM?
> 2nd) How often to do VACUUM?
> 3rd) With = which=20 postgresql.conf paremeters to set up vacuum?
> 4th) When to do a VACU= UM FULL?
> 5th) Can autovacuum of 8.1 be used in this case?
>> I'm a little bit afraid about the size of the table, but I think
> somebody should have a solution...
>
> Here is the com= plete ANALYZE output of the table:
>
> INFO:  "t= bl1": scanned 300 of 27744713 pages, containing 10802 live rows
>= ; and 0 dead rows; 300 rows in sample, 998994633 estimated total rows
>
> The size of the data is 340 GB, this are 40% of the disk-a= rray.

Vacuuming this table is likely to take a rather long time.&nbs= p; Hours and
hours; possibly multiple days.

I don't think yo= u'll *ever* want to VACUUM FULL this table; I'm not
sure you ever want autovacuum to process it either.

I instead th= ink you want to choose a time which seems best to start a
Very Long Tran= saction to issue a VACUUM ANALYZE on it.

If you are quite sure it ha= s few if any dead tuples, it might be
something to try to avoid VACUUMing except as needed to evade the 2^31<= br>transaction limit...

I am not sure that's the only opinion you ou= ght to consider on it...
--
(reverse (concatenate 'string "moc.e= nworbbc " "@" "enworbbc"))
http://cbbrowne.com/info/sap.html
Why are there in= terstate highways in Hawaii?

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



--
/"\  = ; ASCII Ribbon Campaign  .
\ / - NO HTML/RTF in e-mail &n= bsp;.
X  - NO Word docs in e-mail .
/ \ ------------------= ----------------------------------------------- ------=_Part_18690_29912536.1139834140644--