Re: How to VACUUM this table? "998994633 estimated total rows"

Hi,

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 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-array.

Thanks is advance.

Marco

an(at)mediaroot(dot)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 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-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?

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 <cbbrowne(at)acm(dot)org> wrote:
>
> an(at)mediaroot(dot)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 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-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(at)postgresql(dot)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 .
/ \ -----------------------------------------------------------------

gbarosio(at)gmail(dot)com (Guido Barosio) writes:
> 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.

The trouble with pg_stattuple() is that running it is virtually as
expensive as running the vacuum. For a bit table, you pay all the I/O
cost, and any costs of the super-long-running-transaction and don't
even get any cleanup for that cost.
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/lsf.html
Philosophy is a game with objectives and no rules.
Mathematics is a game with rules and no objectives.

So this presents the fact that pg_stattuple should prevent and guess [taking
a sample?] that a table needs an urgent lookup instead of ending the scan
and presenting real numbers?

g.-

On 2/13/06, Chris Browne <cbbrowne(at)acm(dot)org> wrote:
>
> gbarosio(at)gmail(dot)com (Guido Barosio) writes:
> > 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.
>
> The trouble with pg_stattuple() is that running it is virtually as
> expensive as running the vacuum. For a bit table, you pay all the I/O
> cost, and any costs of the super-long-running-transaction and don't
> even get any cleanup for that cost.
> --
> let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
> http://cbbrowne.com/info/lsf.html
> Philosophy is a game with objectives and no rules.
> Mathematics is a game with rules and no objectives.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
/"\ ASCII Ribbon Campaign .
\ / - NO HTML/RTF in e-mail .
X - NO Word docs in e-mail .
/ \ -----------------------------------------------------------------