Thread: reading vacuum verbosity

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.

"Ed L." <pgsql@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

On Friday May 21 2004 1:04, Tom Lane wrote:
> "Ed L." <pgsql@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).

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.

"Ed L." <pgsql@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