pg_freespacemap question

Am Dienstag, 7. M�rz 2006 15:09 schrieb Tatsuo Ishii:

test=# select * from pg_freespacemap where blockfreebytes = 0;
blockid | relfilenode | reltablespace | reldatabase | relblocknumber | blockfreebytes
---------+-------------+---------------+-------------+----------------+----------------
25 | 2619 | 1663 | 16403 | 0 | 0
63 | 2619 | 1663 | 16384 | 10 | 0
(2 rows)

I've never heard of this thing before but is this column order supposed to make sense?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:

Am Dienstag, 7. M�rz 2006 15:09 schrieb Tatsuo Ishii:

test=# select * from pg_freespacemap where blockfreebytes = 0;
blockid | relfilenode | reltablespace | reldatabase | relblocknumber | blockfreebytes
---------+-------------+---------------+-------------+----------------+----------------
25 | 2619 | 1663 | 16403 | 0 | 0
63 | 2619 | 1663 | 16384 | 10 | 0
(2 rows)

I've never heard of this thing before but is this column order supposed to make sense?

I have another question -- why is the view showing relfilenode and
reltablespace? I imagine it should be showing the relation Oid instead.
And what is this "blockid" thing?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Peter Eisentraut wrote:

Am Dienstag, 7. März 2006 15:09 schrieb Tatsuo Ishii:

test=# select * from pg_freespacemap where blockfreebytes = 0;
blockid | relfilenode | reltablespace | reldatabase | relblocknumber | blockfreebytes
---------+-------------+---------------+-------------+----------------+----------------
25 | 2619 | 1663 | 16403 | 0 | 0
63 | 2619 | 1663 | 16384 | 10 | 0
(2 rows)

I've never heard of this thing before but is this column order supposed to make sense?

I have another question -- why is the view showing relfilenode and
reltablespace? I imagine it should be showing the relation Oid instead.

I guess that's because FSM keeps those info, not relation oid.

And what is this "blockid" thing?

from README.pg_freespacemap:

blockid | | Id, 1.. max_fsm_pages

BTW, I found the answer to my question myself by reading the source
code: if that's an index, then blockfreebytes is explicitly set to 0.
I suggest that this should be noted in the README and in this case
blockfreebytes is better to set to NULL, rather than 0.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

Tatsuo Ishii <ishii@sraoss.co.jp> writes:

Peter Eisentraut wrote:
I have another question -- why is the view showing relfilenode and
reltablespace? I imagine it should be showing the relation Oid instead.

I guess that's because FSM keeps those info, not relation oid.

Right, which is correct because free space is associated with physical
files not logical relations. (TRUNCATE, CLUSTER, etc will completely
change the freespace situation for a rel, but they don't change its OID.)

I do agree with the comment that the column order seems nonintuitive;
I'd expect database/tablespace/relfilenode/blocknumber, or possibly
tablespace first. The names used for the columns could do with
reconsideration. And I don't see the point of the blockid column at
all.

regards, tom lane

Tatsuo Ishii wrote:

Peter Eisentraut wrote:

Am Dienstag, 7. M�rz 2006 15:09 schrieb Tatsuo Ishii:

test=# select * from pg_freespacemap where blockfreebytes = 0;
blockid | relfilenode | reltablespace | reldatabase | relblocknumber | blockfreebytes
---------+-------------+---------------+-------------+----------------+----------------
25 | 2619 | 1663 | 16403 | 0 | 0
63 | 2619 | 1663 | 16384 | 10 | 0
(2 rows)

I've never heard of this thing before but is this column order supposed to make sense?

I have another question -- why is the view showing relfilenode and
reltablespace? I imagine it should be showing the relation Oid instead.

I guess that's because FSM keeps those info, not relation oid.

And what is this "blockid" thing?

from README.pg_freespacemap:

blockid | | Id, 1.. max_fsm_pages

I put that in as a bit of a sanity check - to see if the view was
picking up all the fsm pages - guess it is a bit redundant now.

BTW, I found the answer to my question myself by reading the source
code: if that's an index, then blockfreebytes is explicitly set to 0.
I suggest that this should be noted in the README and in this case
blockfreebytes is better to set to NULL, rather than 0.

Good points! I had not noticed this test case. Probably NULL is better
than zero.

I'll look into making these changes! (good to see people checking the
view out).

Cheers

Mark

Tom Lane wrote:

Tatsuo Ishii <ishii@sraoss.co.jp> writes:

Peter Eisentraut wrote:
I have another question -- why is the view showing relfilenode and
reltablespace? I imagine it should be showing the relation Oid instead.

I guess that's because FSM keeps those info, not relation oid.

Right, which is correct because free space is associated with physical
files not logical relations. (TRUNCATE, CLUSTER, etc will completely
change the freespace situation for a rel, but they don't change its OID.)

I do agree with the comment that the column order seems nonintuitive;
I'd expect database/tablespace/relfilenode/blocknumber, or possibly
tablespace first. The names used for the columns could do with
reconsideration. And I don't see the point of the blockid column at
all.

Tom - agreed, I'll look at making these changes too!

Cheers

Mark

BTW, I found the answer to my question myself by reading the source
code: if that's an index, then blockfreebytes is explicitly set to 0.
I suggest that this should be noted in the README and in this case
blockfreebytes is better to set to NULL, rather than 0.

Good points! I had not noticed this test case. Probably NULL is better
than zero.

Just for curiousity, why FSM gathers info for indexes? I thought FSM
is only good for tables.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

Tatsuo Ishii wrote:

Just for curiousity, why FSM gathers info for indexes? I thought FSM
is only good for tables.

It's part of the implementation of the page-recycling algorithm for
btrees Tom did for 7.4. When a btree page is empty after a vacuum, it's
entered in the free space map. When a page is split, the new page is
taken from the FSM (or the relation is extended if there isn't any.)

That's why the bytes-free number is zero: when a btree page makes it
into the FSM, we are sure it's completely empty.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Mark Kirkwood wrote:

Tatsuo Ishii wrote:

BTW, I found the answer to my question myself by reading the source
code: if that's an index, then blockfreebytes is explicitly set to 0.
I suggest that this should be noted in the README and in this case
blockfreebytes is better to set to NULL, rather than 0.

Good points! I had not noticed this test case. Probably NULL is better
than zero.

Would setting it to 'BLCKSZ - (fixed index header stuff)' be better,
since the btree page is empty? (I'll have to read up on how to calculate
the header stuff!).

regards

Mark

Mark Kirkwood <markir@paradise.net.nz> writes:

Good points! I had not noticed this test case. Probably NULL is better

Would setting it to 'BLCKSZ - (fixed index header stuff)' be better,

No, I don't think so, because that will just make it harder to recognize
what's what (remember that BLCKSZ isn't really a constant, and the index
overhead is not the same for all AMs either). The point here is that
for indexes the FSM tracks whole-page availability, not the amount of
free space within pages. So I think NULL is a reasonable representation
of that. Using NULL will make it easy to filter the results if you want
to see only heap-page data or only index-page data, whereas it will be
very hard to do that if the view adopts an ultimately-artificial
convention about the amount of available space on an index page.

regards, tom lane

Tom Lane wrote:

Mark Kirkwood <markir@paradise.net.nz> writes:

Good points! I had not noticed this test case. Probably NULL is better

Would setting it to 'BLCKSZ - (fixed index header stuff)' be better,

No, I don't think so, because that will just make it harder to recognize
what's what (remember that BLCKSZ isn't really a constant, and the index
overhead is not the same for all AMs either). The point here is that
for indexes the FSM tracks whole-page availability, not the amount of
free space within pages. So I think NULL is a reasonable representation
of that. Using NULL will make it easy to filter the results if you want
to see only heap-page data or only index-page data, whereas it will be
very hard to do that if the view adopts an ultimately-artificial
convention about the amount of available space on an index page.

Right - after suggesting it I realized that coding the different index
overhead for each possible AM would have been ... difficult :-). A patch
is attached to implement the NULL free bytes and other recommendations:

1/ Index free bytes set to NULL
2/ Comment added to the README briefly mentioning the index business
3/ Columns reordered more logically
4/ 'Blockid' column removed
5/ Free bytes column renamed to just 'bytes' instead of 'blockfreebytes'

Now 5/ was only hinted at, but seemed worth doing while I was there
(hopefully I haven't made it too terse now....).

cheers

Mark

Mark,

I have tried your patches and it worked great. Thanks.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

BTW, I noticed difference of outputs from pg_freespacemap and
pgstattuple.

I ran pgbench and inspected "accounts" table by using these tools.

pg_freespacemap:
sum of bytes: 250712

pgstattuple:
free_space: 354880

Shouldn't they be identical?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

Tatsuo Ishii wrote:

BTW, I noticed difference of outputs from pg_freespacemap and
pgstattuple.

I ran pgbench and inspected "accounts" table by using these tools.

pg_freespacemap:
sum of bytes: 250712

pgstattuple:
free_space: 354880

Shouldn't they be identical?

I would have thought so - unless there are not enough pages left in the
FSM...

pg_freespacemap is reporting on what gets into the FSM - so provided I
haven't put a bug in there somewhere (!) - we need to look at how VACUUM
reports free space to the FSM....

cheers

Mark

Tatsuo Ishii wrote:

BTW, I noticed difference of outputs from pg_freespacemap and
pgstattuple.

I ran pgbench and inspected "accounts" table by using these tools.

pg_freespacemap:
sum of bytes: 250712

pgstattuple:
free_space: 354880

Shouldn't they be identical?

No, because (a) pgbench vacuums at the start of the run not the end,
and (b) vacuum/fsm disregard pages with "uselessly small" amounts of
free space (less than the average tuple size, IIRC).

I do notice a rather serious shortcoming of pg_freespacemap in its
current incarnation, which is that it *only* shows you the per-page free
space data, and not any of the information that would let you determine
what the FSM is doing to filter the raw data. The per-relation
avgRequest and lastPageCount fields would be interesting for instance.
Perhaps there should be a second view with one row per relation to
carry the appropriate data.

regards, tom lane

Tom Lane wrote:

Tatsuo Ishii wrote:

BTW, I noticed difference of outputs from pg_freespacemap and
pgstattuple.

I ran pgbench and inspected "accounts" table by using these tools.

pg_freespacemap:
sum of bytes: 250712

pgstattuple:
free_space: 354880

Shouldn't they be identical?

vacuum/fsm disregard pages with "uselessly small" amounts of
free space (less than the average tuple size, IIRC).

Ah - that what I was seeing! Thanks.

I do notice a rather serious shortcoming of pg_freespacemap in its
current incarnation, which is that it *only* shows you the per-page free
space data, and not any of the information that would let you determine
what the FSM is doing to filter the raw data. The per-relation
avgRequest and lastPageCount fields would be interesting for instance.
Perhaps there should be a second view with one row per relation to
carry the appropriate data.

Ok - I did wonder about 2 views, but was unsure if the per-relation
stuff was interesting. Given that it looks like it is interesting, I'll
see about getting a second view going.

Cheers

Mark

Tatsuo Ishii wrote:

BTW, I noticed difference of outputs from pg_freespacemap and
pgstattuple.

I ran pgbench and inspected "accounts" table by using these tools.

pg_freespacemap:
sum of bytes: 250712

pgstattuple:
free_space: 354880

Shouldn't they be identical?

No, because (a) pgbench vacuums at the start of the run not the end,

I ran VACUUM after pbench run and still got the differece.

and (b) vacuum/fsm disregard pages with "uselessly small" amounts of
free space (less than the average tuple size, IIRC).

That sounds strange to me. Each record of accounts tables is actually
exactly same, i.e fixed size. So it should be possible that UPDATE
reuses any free spaces made by previous UPDATE. If FSM neglects those
free spaces "because they are uselessly small", then the unrecycled
pages are getting grow even if they are regulary VACUUMed, no?

I do notice a rather serious shortcoming of pg_freespacemap in its
current incarnation, which is that it *only* shows you the per-page free
space data, and not any of the information that would let you determine
what the FSM is doing to filter the raw data. The per-relation
avgRequest and lastPageCount fields would be interesting for instance.
Perhaps there should be a second view with one row per relation to
carry the appropriate data.

--
Tatsuo Ishii
SRA OSS, Inc. Japan

Tatsuo Ishii <ishii@sraoss.co.jp> writes:

That sounds strange to me. Each record of accounts tables is actually
exactly same, i.e fixed size. So it should be possible that UPDATE
reuses any free spaces made by previous UPDATE. If FSM neglects those
free spaces "because they are uselessly small", then the unrecycled
pages are getting grow even if they are regulary VACUUMed, no?

The point here is that if tuples require 50 bytes, and there are 20
bytes free on a page, pgstattuple counts 20 free bytes while FSM
ignores the page. Recording that space in the FSM will not improve
matters, it'll just risk pushing out FSM records for pages that do
have useful amounts of free space.

regards, tom lane

The point here is that if tuples require 50 bytes, and there are 20
bytes free on a page, pgstattuple counts 20 free bytes while FSM
ignores the page. Recording that space in the FSM will not improve
matters, it'll just risk pushing out FSM records for pages that do
have useful amounts of free space.

Maybe an overloaded pgstattuple function that allows you to request FSM
behavior?

Chris