reading vacuum verbosity

Started by Ed L.almost 22 years ago5 messagesgeneral
Jump to latest
#1Ed L.
pgsql@bluepolka.net

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.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ed L. (#1)
Re: reading vacuum verbosity

"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

#3Ed L.
pgsql@bluepolka.net
In reply to: Tom Lane (#2)
Re: reading vacuum verbosity

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

#4Ed L.
pgsql@bluepolka.net
In reply to: Ed L. (#3)
Re: reading vacuum verbosity

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.

#5Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ed L. (#4)
Re: reading vacuum verbosity

"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