Thread: [PATCH]pg_buffercache add a buffer state column, Add fuction to decode buffer state
[PATCH]pg_buffercache add a buffer state column, Add fuction to decode buffer state
From
"Moon Insung"
Date:
Dear Hackers.
I'm studied PostgreSQL buffers for the development of new patches.
In particular, using pg_buffercache, is can easily check the status of actual buffer.
Bur there was one inconvenience.
Pg_buffercache was also to check only the dirty state of the buffer.
State of the buffer currently represents 10 states.
Therefore, it seems impossible to check remaining 9 state.
So I add a state column to pg_buffercache view so that I could print a value indicating the state of the buffer.
This is outpu as an unit32 type, and examples are shown below.
-----
postgres=# select * from pg_buffercache where bufferid = 1;
-[ RECORD 1 ]----+-----------
bufferid | 1
relfilenode | 1262
reltablespace | 1664
reldatabase | 0
relforknumber | 0
relblocknumber | 0
isdirty | f
usagecount | 5
pinning_backends | 0
buffer_state | 2203320320 <- it's a new column
-----
With the patches, user can check status values and check the status of the buffers.
However, if do not know source code, or do not know hex values,
It's difficult or impossible to check the actual state even using this patch.
Therefore, add a new function to improve readability when checking state.
When you input a value for state, this function prints out what the actual state.
Examples of actual use are as follows.
-----
postgres=# SELECT bufferid, relfilenode, state_text FROM pg_buffercache,
LATERAL pg_buffercache_state_print(buffer_state) M(state_text)
WHERE bufferid < 10;bufferid | relfilenode | state_text
----------+-------------+------------------------------------------------------- 1 | 1262 |
{LOCKED,VALID,TAG_VALID,PERMANENT} 2 | 1260 | {LOCKED,VALID,TAG_VALID,PERMANENT} 3 | 1259 |
{LOCKED,DIRTY,VALID,TAG_VALID,JUST_DIRTIED,PERMANENT} 4 | 1259 | {LOCKED,VALID,TAG_VALID,PERMANENT} 5
| 1259 | {LOCKED,VALID,TAG_VALID,PERMANENT} 6 | 1249 | {LOCKED,VALID,TAG_VALID,PERMANENT} 7 |
1249 | {LOCKED,VALID,TAG_VALID,PERMANENT} 8 | 1249 | {LOCKED,VALID,TAG_VALID,PERMANENT} 9 |
1249 | {LOCKED,VALID,TAG_VALID,PERMANENT}
(9 rows)
-----
If you use this patch, I think that you can easily judge the state of the buffer.
Regards.
Moon.
Re: [PATCH]pg_buffercache add a buffer state column, Add fuction todecode buffer state
From
Andres Freund
Date:
Hi,
On 2017-11-14 17:57:00 +0900, Moon Insung wrote:
> So I add a state column to pg_buffercache view so that I could print a value indicating the state of the buffer.
> This is outpu as an unit32 type, and examples are shown below.
> -----
> postgres=# select * from pg_buffercache where bufferid = 1;
> -[ RECORD 1 ]----+-----------
> bufferid | 1
> relfilenode | 1262
> reltablespace | 1664
> reldatabase | 0
> relforknumber | 0
> relblocknumber | 0
> isdirty | f
> usagecount | 5
> pinning_backends | 0
> buffer_state | 2203320320 <- it's a new column
> -----
I'm disinclined to exposing state that way. It's an internal
representation that's not unlikely to change. Sure, pg_buffercache is
more of a debugging / investigatory tool, but I nevertheless see no
reason to expose it that way.
If we shared those flags more in a manner like you did below:
> 1 | 1262 | {LOCKED,VALID,TAG_VALID,PERMANENT}
that'd be more acceptable. However doing that by default would have
some performance downsides, because we'd need to create these arrays for
every row.
One way around that would be to create a buffer_state type that's
returned by pg_buffercache and then only decoded when outputting. Doing
that + having a cast to an array seems like it'd provide most of the
needed functionality?
Greetings,
Andres Freund