Re: Add visibility map information to pg_freespace.

Helle,

I've added visibility map information to pg_freespace for my
utility.

This looks like this,

postgres=# select * from pg_freespace('t'::regclass);
blkno | avail | all_visible
-------+-------+-------------
0 | 7424 | t
1 | 7424 | t
2 | 7424 | t
3 | 7424 | t
4 | 7424 | t
5 | 7424 | t
6 | 7424 | t
7 | 7424 | t
...

What do you think about this?

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

Kyotaro HORIGUCHI wrote:
> Helle,
>
> I've added visibility map information to pg_freespace for my
> utility.

This makes sense to me. I only lament the fact that this makes the
module a misnomer. Do we want to 1) rename the module (how
inconvenient), 2) create a separate module for this (surely not
warranted), or 3) accept it and move on?

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2013-06-14 10:22:19 -0400, Alvaro Herrera wrote:
> Kyotaro HORIGUCHI wrote:
> > Helle,
> >
> > I've added visibility map information to pg_freespace for my
> > utility.
>
> This makes sense to me.

+1

> I only lament the fact that this makes the
> module a misnomer. Do we want to 1) rename the module (how
> inconvenient), 2) create a separate module for this (surely not
> warranted), or 3) accept it and move on?

3). All the others seem to inflict unneccesary pain for not all that
much gain.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Fri, Jun 14, 2013 at 7:23 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> 3). All the others seem to inflict unneccesary pain for not all that
> much gain.

+1. You might want to add a "historical note" about the name to the
pg_freespace documentation, though.

--
Peter Geoghegan

On 14 June 2013 15:22, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> Kyotaro HORIGUCHI wrote:
>> Helle,
>>
>> I've added visibility map information to pg_freespace for my
>> utility.
>
> This makes sense to me. I only lament the fact that this makes the
> module a misnomer. Do we want to 1) rename the module (how
> inconvenient), 2) create a separate module for this (surely not
> warranted), or 3) accept it and move on?

I'm not sure why this is suggested as being part of pg_freespace and
not part of pageinspect? (Which is where all the other inspection
tools live).

If I wanted to see the vismap (and I do...) then I'd like to see the
whole vismap, not just the part that relates to blocks currently in
cache.

If you do want that, you can just join the two things together
(function to see vismap joined to pg_freespace).

(Having said that, I don't have a major objection to it being in
pg_freespace as well).

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Thank you.

> > This makes sense to me. I only lament the fact that this makes the
> > module a misnomer. Do we want to 1) rename the module (how
> > inconvenient), 2) create a separate module for this (surely not
> > warranted), or 3) accept it and move on?

Although I also feel uneasy with the module name, I suppose this
is not so major change as changing the module name.

> I'm not sure why this is suggested as being part of pg_freespace and
> not part of pageinspect? (Which is where all the other inspection
> tools live).

I'm afraid I wasn't aware of that. I think the following
operation shows the info of fsm.

| =# select fsm_page_contents(get_raw_page('t', 'fsm', 0));
| fsm_page_contents
| -------------------
| 0: 147 +
| 1: 147 +
...
| 2047: 147 +
| 4095: 147 +
| fp_next_slot: 0 +

If this is the only way to inspect fsm info with this module, I
can't say it is consise enough just to know the fsm info
corresponds to certain heap block. pg_freespace seems preferable
for such a purpose.

Following the manner shown above, I'll provide vm_page_contents
then command and it'll show result as following.

| =# select vm_page_contents(get_raw_page('t', 'vm', 0));
| v_page_contents
| -------------------
| 0: t +
| 1: f +
...
| 65343: t +

# Too long...

It should useful in other aspects but it seems a bit complicated
just to know about visibility bits for certain blocks.

> If I wanted to see the vismap (and I do...) then I'd like to see the
> whole vismap, not just the part that relates to blocks currently in
> cache.
> If you do want that, you can just join the two things together
> (function to see vismap joined to pg_freespace).

From the aspect of interface, thay look to be separate
functions.

On the other hand there's no problem to add vm_page_contents to
pageinspect, although in another output format. It'll look like,

| v_page_contents
| -------------------
| 0000: 0 0 1 1 1 0 0 0 0 0 1 1 0 0 1 0 +
| 0001: 0 0 0 0 0 0 0 0 0 0 1 0 0 1 0 0 +
| ffff: 0 0 0 0 0 0 0 0 0 0 1 0 0 1 0 0 +
...
| ff30: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

# 't' and 'f' are too confusing beeing shown in a line...

Any suggestions for the output format?

> (Having said that, I don't have a major objection to it being in
> pg_freespace as well).

I prefer to leave that issue this time for anyone - including me :-p

--
Kyotaro Horiguchi
NTT Open Source Software Center

On 19 June 2013 09:19, Kyotaro HORIGUCHI
<horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:

> It should useful in other aspects but it seems a bit complicated
> just to know about visibility bits for certain blocks.

With your current patch you can only see the visibility info for
blocks in cache, not for all blocks. So while you may think it is
useful, it is also unnecessarily limited in scope.

Let's just have something that is easy to use that lets us see the
visibility state for a block, not just blocks in freespace.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2013-06-19 10:03:40 +0100, Simon Riggs wrote:
> On 19 June 2013 09:19, Kyotaro HORIGUCHI
> <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>
> > It should useful in other aspects but it seems a bit complicated
> > just to know about visibility bits for certain blocks.
>
> With your current patch you can only see the visibility info for
> blocks in cache, not for all blocks. So while you may think it is
> useful, it is also unnecessarily limited in scope.

pg_freespace should do more than that? Are you thinking of
pg_buffercache?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 19 June 2013 10:15, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2013-06-19 10:03:40 +0100, Simon Riggs wrote:
>> On 19 June 2013 09:19, Kyotaro HORIGUCHI
>> <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>
>> > It should useful in other aspects but it seems a bit complicated
>> > just to know about visibility bits for certain blocks.
>>
>> With your current patch you can only see the visibility info for
>> blocks in cache, not for all blocks. So while you may think it is
>> useful, it is also unnecessarily limited in scope.
>
> pg_freespace should do more than that? Are you thinking of
> pg_buffercache?

I was... my mistake. Please continue Kyotaro.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

I'm looking into this patch as a reviewer.

(2013/06/19 18:03), Simon Riggs wrote:
> On 19 June 2013 09:19, Kyotaro HORIGUCHI
> <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>
>> It should useful in other aspects but it seems a bit complicated
>> just to know about visibility bits for certain blocks.
>
> With your current patch you can only see the visibility info for
> blocks in cache, not for all blocks. So while you may think it is
> useful, it is also unnecessarily limited in scope.
>
> Let's just have something that is easy to use that lets us see the
> visibility state for a block, not just blocks in freespace.

I think we can have this visibility map statistics also
in pgstattuple function (as a new column) for this purpose.

IMHO, we have several modules for different purposes.

- pageinspect provies several functions for debugging purpose.
- pg_freespace provies a view for monitoring purpose.
- pgstattuple provies several functions for collecting
specific table/index statistics.

So, we can have similar feature in different modules.

Any comments?

Regards,
--
Satoshi Nagayasu <snaga(at)uptime(dot)jp>
Uptime Technologies, LLC. http://www.uptime.jp

On 20 June 2013 04:26, Satoshi Nagayasu <snaga(at)uptime(dot)jp> wrote:
> I'm looking into this patch as a reviewer.
>
>
> (2013/06/19 18:03), Simon Riggs wrote:
>>
>> On 19 June 2013 09:19, Kyotaro HORIGUCHI
>> <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>
>>> It should useful in other aspects but it seems a bit complicated
>>> just to know about visibility bits for certain blocks.
>>
>>
>> With your current patch you can only see the visibility info for
>> blocks in cache, not for all blocks. So while you may think it is
>> useful, it is also unnecessarily limited in scope.
>>
>> Let's just have something that is easy to use that lets us see the
>> visibility state for a block, not just blocks in freespace.
>
>
> I think we can have this visibility map statistics also
> in pgstattuple function (as a new column) for this purpose.
>
> IMHO, we have several modules for different purposes.
>
> - pageinspect provies several functions for debugging purpose.
> - pg_freespace provies a view for monitoring purpose.
> - pgstattuple provies several functions for collecting
> specific table/index statistics.
>
> So, we can have similar feature in different modules.
>
> Any comments?

+1

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Wed, Jun 19, 2013 at 11:26 PM, Satoshi Nagayasu <snaga(at)uptime(dot)jp> wrote:
> - pageinspect provies several functions for debugging purpose.
> - pg_freespace provies a view for monitoring purpose.
> - pgstattuple provies several functions for collecting
> specific table/index statistics.

I think we should be careful to think about upgrade considerations
when adding this functionality. Bumping the module version number to
add new functions is less likely to break things for users than
changing the return value of an existing SRF. Maybe that's too far
down in the weeds to worry about, but it's a thought - especially for
pg_freespace, where there's no real efficiency benefit to have the
same function look at the FSM and the VM anyway.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hello,

> I'm looking into this patch as a reviewer.

I'd appreciate your time to review.

I've had some suggestions so far,

- I should be cautious in changing existing interface.

You're right. It was somehow gone out of my mind. It might be
better to provide a separate function from the compatibility
view despite the loss of the pertinence to stay in this
extension. However, it is too small to be a standalone
extension.

On the other hand the newly-added-column-to-the-tail could be
said to be harmless for the most cases considering the usage of
this extension, I suppose.

- Historical note is needed in pg_freespace doc.

Agreed, I'll provide documents not only for freespace, but for
other modules I'll touch in this patch later.

- How about pageinspect?

I proposed a simple representation format as a basis for
discussion. Nevertheless, the VM pages has no more structure
than a simple bit string. Given the VM info in pg_freespacemap,
I've come in doubt of the necessity of vm_page_contnets() for
the reason besides the orthogonality in the this extension's
interface (which paid no attention before:-).

- How about pgstattuple?

It could even be said to be meaningful to add the number of
not-all-visible pages or the ratio of it in the total pages..

| postgres=# select * from pgstattuple('t');
| -[ RECORD 1 ]----------------+---------
| table_len | 88711168
| tuple_count | 600001
| tuple_len | 26400044
| tuple_percent | 29.76
| dead_tuple_count | 399999
| dead_tuple_len | 17599956
| dead_tuple_percent | 19.84
| free_space | 33607960
| free_percent | 37.88
+ not_all_visible_page_percent | 23.54

# This column name looks too long, though.

In addition, the discussion above about the stability of the
interface is also applicable to this.

Any suggestions?

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

On 26 June 2013 09:09, Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>wrote:

> - How about pageinspect?
>
> I proposed a simple representation format as a basis for
> discussion. Nevertheless, the VM pages has no more structure
> than a simple bit string. Given the VM info in pg_freespacemap,
> I've come in doubt of the necessity of vm_page_contnets() for
> the reason besides the orthogonality in the this extension's
> interface (which paid no attention before:-).
>

I don't think that will be needed, now I understand.

> - How about pgstattuple?
>
> It could even be said to be meaningful to add the number of
> not-all-visible pages or the ratio of it in the total pages..
>
> | postgres=# select * from pgstattuple('t');
> | -[ RECORD 1 ]----------------+---------
> | table_len | 88711168
> | tuple_count | 600001
> | tuple_len | 26400044
> | tuple_percent | 29.76
> | dead_tuple_count | 399999
> | dead_tuple_len | 17599956
> | dead_tuple_percent | 19.84
> | free_space | 33607960
> | free_percent | 37.88
> + not_all_visible_page_percent | 23.54
>
> # This column name looks too long, though.
>

Yes, please.

But name should be all_visible_percent.
Anybody that wants not_all_visible_percent can do the math.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hello,

> > - How about pageinspect?
> >
> > I proposed a simple representation format as a basis for
> > discussion. Nevertheless, the VM pages has no more structure
> > than a simple bit string. Given the VM info in pg_freespacemap,
> > I've come in doubt of the necessity of vm_page_contnets() for
> > the reason besides the orthogonality in the this extension's
> > interface (which paid no attention before:-).
>
> I don't think that will be needed, now I understand.

Ok, I'll drop it from the list.

> > - How about pgstattuple?
> >
> > It could even be said to be meaningful to add the number of
> > not-all-visible pages or the ratio of it in the total pages..
..
> > | free_percent | 37.88
> > + not_all_visible_page_percent | 23.54
> >
> > # This column name looks too long, though.
> >
>
> Yes, please.
>
> But name should be all_visible_percent.
> Anybody that wants not_all_visible_percent can do the math.

You're quite right, plus, negative terms are a bit confusing.

I'll come again with the first implementation of it. And as for
pg_freespacemap, I'll keep the current direction - adding column
to present output records format of pg_freespace(). And
documentation, if possible.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

On 07/08/2013 12:59 AM, Kyotaro HORIGUCHI wrote:
> I'll come again with the first implementation of it. And as for
> pg_freespacemap, I'll keep the current direction - adding column
> to present output records format of pg_freespace(). And
> documentation, if possible.

Do you think you'll be fixing these things in the next couple days?
Otherwise, I would like to mark this returned with feedback, and you can
submit an updated patch to the next CommitFest.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Hello, I've brought visibilitymap extentions for pg_freespacemap
and pgstattuple.

At Mon, 08 Jul 2013 16:59:05 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <20130708(dot)165905(dot)118860769(dot)horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
> I'll come again with the first implementation of it. And as for
> pg_freespacemap, I'll keep the current direction - adding column
> to present output records format of pg_freespace(). And
> documentation, if possible.

pg_freespace_vm_v2.patch:

Interface has been changed from the first patch. The version of
pg_freespace() provided with vm information is named
pg_freespace_with_vminfo() and shows output like following.

| postgres=# select * from pg_freespace_with_vminfo('t'::regclass) limit 10;
| blkno | avail | is_all_visible
| -------+-------+----------------
| 0 | 64 | t
| 1 | 32 | t
| 2 | 96 | t
| 3 | 64 | t
| 4 | 96 | t
| 5 | 96 | t
| 6 | 128 | t
| 7 | 32 | t
| 8 | 96 | t

pgstattuple_vm_v1.patch:

The first version of VM extension for pgstattuple. According to
the previous discussion, the added column is named
'all_visible_percent'.

| postgres=# select * from pgstattuple('t');
| -[ RECORD 1 ]-------+---------
| table_len | 71770112
| tuple_count | 989859
| tuple_len | 31675488
| tuple_percent | 44.13
| dead_tuple_count | 99
| dead_tuple_len | 3168
| dead_tuple_percent | 0
| free_space | 31886052
| free_percent | 44.43
| all_visible_percent | 99.98

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

(2013/07/09 19:55), Kyotaro HORIGUCHI wrote:
> Hello, I've brought visibilitymap extentions for pg_freespacemap
> and pgstattuple.
>
> At Mon, 08 Jul 2013 16:59:05 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <20130708(dot)165905(dot)118860769(dot)horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
>> I'll come again with the first implementation of it. And as for
>> pg_freespacemap, I'll keep the current direction - adding column
>> to present output records format of pg_freespace(). And
>> documentation, if possible.
>
> pg_freespace_vm_v2.patch:
>
> Interface has been changed from the first patch. The version of
> pg_freespace() provided with vm information is named
> pg_freespace_with_vminfo() and shows output like following.
>
> | postgres=# select * from pg_freespace_with_vminfo('t'::regclass) limit 10;
> | blkno | avail | is_all_visible
> | -------+-------+----------------
> | 0 | 64 | t
> | 1 | 32 | t
> | 2 | 96 | t
> | 3 | 64 | t
> | 4 | 96 | t
> | 5 | 96 | t
> | 6 | 128 | t
> | 7 | 32 | t
> | 8 | 96 | t

I think we can simply add is_all_viible column to the existing
pg_freespace(), because adding column would not break
backward-compatibility in general. Any other thoughts?

> pgstattuple_vm_v1.patch:
>
> The first version of VM extension for pgstattuple. According to
> the previous discussion, the added column is named
> 'all_visible_percent'.
>
> | postgres=# select * from pgstattuple('t');
> | -[ RECORD 1 ]-------+---------
> | table_len | 71770112
> | tuple_count | 989859
> | tuple_len | 31675488
> | tuple_percent | 44.13
> | dead_tuple_count | 99
> | dead_tuple_len | 3168
> | dead_tuple_percent | 0
> | free_space | 31886052
> | free_percent | 44.43
> | all_visible_percent | 99.98

It seems working fine.

And I added a regression test for pg_freespacemap and additional
test cases for pgstattuple. Please take a look.

Regards,
--
Satoshi Nagayasu <snaga(at)uptime(dot)jp>
Uptime Technologies, LLC. http://www.uptime.jp

Thank you for the worthwhile additions.

At Tue, 16 Jul 2013 16:04:43 +0900, Satoshi Nagayasu <snaga(at)uptime(dot)jp> wrote in <51E4F08B(dot)3030307(at)uptime(dot)jp>
> > | postgres=# select * from pg_freespace_with_vminfo('t'::regclass) limit
> > | 10;
..
> I think we can simply add is_all_viible column to the existing
> pg_freespace(), because adding column would not break
> backward-compatibility in general. Any other thoughts?

I agree to you. I cannot guess any 'ordinary' application which
uses this function, or someone's craft critically affected by
this change. This decision was merely a safe bet.

I'll remerge _with_vminfo function to pg_freespace() in the next
patch if no objection is raised.

> > pgstattuple_vm_v1.patch:
...
> It seems working fine.
>
> And I added a regression test for pg_freespacemap and additional
> test cases for pgstattuple. Please take a look.

Thank you. This seems fine. I felt a bit uneasy with the absense
of regtests in pg_freespacemap, but I took advantage of the
absense not to add new ones.

I have simply merged the two regtests separately into two
original patches. You will find the two attached files.

pg_freespace_vm_v3.patch :
new patch for pg_freespace with regtests and _with_vminfo

pgstattuple_vm_v2.patch :
new patch for gstattuple with regtests

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

Hmm. I'm sorry to find that this patch is already marked as
'Return with Feedback' on the CF page around the same time when
the preveous review comment has sent. Is it somewhat crossing?

Anyway, I'll take a rain check for this.

> I have simply merged the two regtests separately into two
> original patches. You will find the two attached files.
>
> pg_freespace_vm_v3.patch :
> new patch for pg_freespace with regtests and _with_vminfo
>
> pgstattuple_vm_v2.patch :
> new patch for gstattuple with regtests

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center