reading vacuum verbosity

[Ed L.]

I am trying to better understand diskspace leakage and the
relationship to vacuum, max_fsm_pages, and max_fsm_relations.
Below are 3 snippets from 3 successive vacuums on a table
with ~284K rows which receives many many UPDATEs and a few
INSERTs (there were also a few runs of ANALYZE in between
these VACUUMs):

INFO: Pages 22652: Changed 4, Empty 0; Tup 284139: Vac 927, Keep 0, UnUsed 936.
INFO: Pages 22652: Changed 7, Empty 0; Tup 284151: Vac 423, Keep 0, UnUsed 1559.
INFO: Pages 22652: Changed 4, Empty 0; Tup 284155: Vac 221, Keep 0, UnUsed 1823.

This is on a newly-installed 7.3.4 cluster with max_fsm_pages
set to 3,000,000 (allowing for ~24GB of DB disk pages) and
max_fsm_relations = 2000.

Questions:

1) Do the increasing values for "UnUsed" indicate leakage?
Looks to me like the number of new rows were 12 and 4
respectively between vacuum runs. But the UnUsed values
seem to be jumping maybe roughly with the number of updates.
It's early, but I would expect vacuum to keep UnUsed low.

2) I understand max_fsm_relations needs to be at least as
high as the number of tables for which I want to track free
space. I have far fewer than 2000 user tables, but if I count
system tables and index relations, then I exceed 2000 by 10%
or so. Should I count system tables when setting max_fsm_relations?

3) Should I count index relations when setting max_fsm_relations?

TIA.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

"Ed L." <pg***@bluepolka.net> writes:

Below are 3 snippets from 3 successive vacuums on a table
with ~284K rows which receives many many UPDATEs and a few
INSERTs (there were also a few runs of ANALYZE in between
these VACUUMs): INFO: Pages 22652: Changed 4, Empty 0; Tup 284139: Vac 927, Keep 0, UnUsed 936.
INFO: Pages 22652: Changed 7, Empty 0; Tup 284151: Vac 423, Keep 0, UnUsed 1559.
INFO: Pages 22652: Changed 4, Empty 0; Tup 284155: Vac 221, Keep 0, UnUsed 1823.
That looks okay to me considering that the physical table size (Pages)
isn't growing.
1) Do the increasing values for "UnUsed" indicate leakage?
I'm not sure. It seems a bit odd ... could you track this over a longer
interval? An unused tuple slot will only take 4 bytes so it might take
awhile to see any real consequence.
Should I count system tables when setting max_fsm_relations?
Yes.
3) Should I count index relations when setting max_fsm_relations?

As of 7.4, yes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Ed L.]

On Friday May 21 2004 1:04, Tom Lane wrote:

"Ed L." <pg***@bluepolka.net> writes:

Below are 3 snippets from 3 successive vacuums on a table
with ~284K rows which receives many many UPDATEs and a few
INSERTs (there were also a few runs of ANALYZE in between
these VACUUMs):

INFO: Pages 22652: Changed 4, Empty 0; Tup 284139: Vac 927, Keep 0,
UnUsed 936. INFO: Pages 22652: Changed 7, Empty 0; Tup 284151: Vac
423, Keep 0, UnUsed 1559. INFO: Pages 22652: Changed 4, Empty 0; Tup
284155: Vac 221, Keep 0, UnUsed 1823.

That looks okay to me considering that the physical table size (Pages)
isn't growing.

1) Do the increasing values for "UnUsed" indicate leakage?

I'm not sure. It seems a bit odd ... could you track this over a longer
interval? An unused tuple slot will only take 4 bytes so it might take
awhile to see any real consequence.

Here's a longer interval, or at least a longer sequence:

INFO: Pages 22652: Changed 4, Empty 0; Tup 284139: Vac 927, Keep 0, UnUsed 936.
INFO: Pages 22652: Changed 7, Empty 0; Tup 284151: Vac 423, Keep 0, UnUsed 1559.
INFO: Pages 22652: Changed 4, Empty 0; Tup 284155: Vac 221, Keep 0, UnUsed 1823.
INFO: Pages 22652: Changed 2, Empty 0; Tup 284164: Vac 655, Keep 0, UnUsed 1592.
INFO: Pages 22652: Changed 1, Empty 0; Tup 284169: Vac 87, Keep 0, UnUsed 2184.
INFO: Pages 22652: Changed 1, Empty 0; Tup 284170: Vac 121, Keep 0, UnUsed 2179.
INFO: Pages 22652: Changed 0, Empty 0; Tup 284170: Vac 0, Keep 0, UnUsed 2300.
INFO: Pages 22652: Changed 0, Empty 0; Tup 284170: Vac 0, Keep 0, UnUsed 2300.
INFO: Pages 22652: Changed 1, Empty 0; Tup 284171: Vac 94, Keep 0, UnUsed 2230.
INFO: Pages 22652: Changed 0, Empty 0; Tup 284171: Vac 0, Keep 0, UnUsed 2324.
INFO: Pages 22652: Changed 0, Empty 0; Tup 284171: Vac 0, Keep 0, UnUsed 2324.
INFO: Pages 22652: Changed 1, Empty 0; Tup 284172: Vac 97, Keep 0, UnUsed 2232.
INFO: Pages 22652: Changed 1, Empty 0; Tup 284173: Vac 106, Keep 0, UnUsed 2242.
INFO: Pages 22652: Changed 0, Empty 0; Tup 284203: Vac 36, Keep 0, UnUsed 2311.

I see the UnUsed number stabilizing a bit. (I realize a few of these
vacuums were unnecessary).

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Ed L.]

On Friday May 21 2004 10:48, Ed L. wrote:

1) Do the increasing values for "UnUsed" indicate leakage?

I'm not sure. It seems a bit odd ... could you track this over a
longer interval? An unused tuple slot will only take 4 bytes so it
might take awhile to see any real consequence.

Here's a longer interval, or at least a longer sequence:

INFO: Pages 22652: Changed 2, Empty 0; Tup 284164: Vac 655, Keep 0, UnUsed 1592.
INFO: Pages 22652: Changed 1, Empty 0; Tup 284169: Vac 87, Keep 0, UnUsed 2184.
INFO: Pages 22652: Changed 1, Empty 0; Tup 284170: Vac 121, Keep 0, UnUsed 2179.
INFO: Pages 22652: Changed 1, Empty 0; Tup 284171: Vac 94, Keep 0, UnUsed 2230.
INFO: Pages 22652: Changed 1, Empty 0; Tup 284172: Vac 97, Keep 0, UnUsed 2232.
INFO: Pages 22652: Changed 1, Empty 0; Tup 284173: Vac 106, Keep 0, UnUsed 2242.
INFO: Pages 22652: Changed 0, Empty 0; Tup 284203: Vac 36, Keep 0, UnUsed 2311.
INFO: Pages 22693: Changed 82, Empty 0; Tup 284278: Vac 2355, Keep 0, UnUsed 1364.
INFO: Pages 22693: Changed 10, Empty 0; Tup 284293: Vac 882, Keep 0, UnUsed 3098.

One oddity: Even immediately after a vacuum or analyze, I notice that
pg_class.reltuples is way off for this table, reporting 919373 rows
when there are only ~284K. pg_class.relpages looks precisely correct.
This is PostgreSQL 7.3.4 on hppa2.0w-hp-hpux11.00, compiled by cc -Ae.

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

[Tom Lane]

"Ed L." <pg***@bluepolka.net> writes:

One oddity: Even immediately after a vacuum or analyze, I notice that
pg_class.reltuples is way off for this table, reporting 919373 rows
when there are only ~284K. pg_class.relpages looks precisely correct.
This is PostgreSQL 7.3.4 on hppa2.0w-hp-hpux11.00, compiled by cc -Ae.

I'd expect reltuples to be correct after a VACUUM (or VACUUM ANALYZE).
But a plain ANALYZE sets it on the basis of a statistical estimate that
can be off quite a bit. (Manfred's been looking at developing a better
estimate, which I hope will make it into 7.5.)

regards, tom lane

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