[PATCH]pg_buffercache add a buffer state column, Add fuction to decode buffer state
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.
Attachments:
pg_buffercache add a buffer state column and Add function to decode buffer state_V1.diffapplication/octet-stream; name="pg_buffercache add a buffer state column and Add function to decode buffer state_V1.diff"Download
diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile
index 18f7a87..3629337 100644
--- a/contrib/pg_buffercache/Makefile
+++ b/contrib/pg_buffercache/Makefile
@@ -4,7 +4,8 @@ MODULE_big = pg_buffercache
OBJS = pg_buffercache_pages.o $(WIN32RES)
EXTENSION = pg_buffercache
-DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \
+DATA = pg_buffercache--1.4.sql pg_buffercache--1.3--1.4.sql \
+ pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \
pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \
pg_buffercache--unpackaged--1.0.sql
PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time"
diff --git a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
new file mode 100644
index 0000000..c5d3523
--- /dev/null
+++ b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
@@ -0,0 +1,16 @@
+/* contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.4'" to load this file. \quit
+
+-- Upgrade view to 1.4. format
+CREATE OR REPLACE VIEW pg_buffercache AS
+ SELECT P.* FROM pg_buffercache_pages() AS P
+ (bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid,
+ relforknumber int2, relblocknumber int8, isdirty bool, usagecount int2,
+ pinning_backends int4, buffer_state int8);
+
+-- Create a function for print of buffer status.
+CREATE FUNCTION pg_buffercache_state_print(buffer_state int8)
+RETURNS text[] STRICT LANGUAGE 'c'
+AS 'MODULE_PATHNAME', 'pg_buffercache_status_print';
diff --git a/contrib/pg_buffercache/pg_buffercache--1.4.sql b/contrib/pg_buffercache/pg_buffercache--1.4.sql
new file mode 100644
index 0000000..c5b4ff0
--- /dev/null
+++ b/contrib/pg_buffercache/pg_buffercache--1.4.sql
@@ -0,0 +1,28 @@
+/* contrib/pg_buffercache/pg_buffercache--1.4.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pg_buffercache" to load this file. \quit
+
+-- Register the function.
+CREATE FUNCTION pg_buffercache_pages()
+RETURNS SETOF RECORD
+AS 'MODULE_PATHNAME', 'pg_buffercache_pages'
+LANGUAGE C PARALLEL SAFE;
+
+-- Create a view for convenient access.
+CREATE VIEW pg_buffercache AS
+ SELECT P.* FROM pg_buffercache_pages() AS P
+ (bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid,
+ relforknumber int2, relblocknumber int8, isdirty bool, usagecount int2,
+ pinning_backends int4, buffer_state int8);
+
+-- Create a function for print of buffer status.
+CREATE FUNCTION pg_buffercache_state_print(buffer_state int8)
+RETURNS text[] STRICT LANGUAGE 'c'
+AS 'MODULE_PATHNAME', 'pg_buffercache_state_print';
+
+
+-- Don't want these to be available to public.
+REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC;
+REVOKE ALL ON pg_buffercache FROM PUBLIC;
+REVOKE ALL ON FUNCTION pg_buffercache_state_print(buffer_state int8) FROM PUBLIC;
diff --git a/contrib/pg_buffercache/pg_buffercache.control b/contrib/pg_buffercache/pg_buffercache.control
index 8c060ae..a82ae5f 100644
--- a/contrib/pg_buffercache/pg_buffercache.control
+++ b/contrib/pg_buffercache/pg_buffercache.control
@@ -1,5 +1,5 @@
# pg_buffercache extension
comment = 'examine the shared buffer cache'
-default_version = '1.3'
+default_version = '1.4'
module_pathname = '$libdir/pg_buffercache'
relocatable = true
diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c
index b410aaf..055b9b4 100644
--- a/contrib/pg_buffercache/pg_buffercache_pages.c
+++ b/contrib/pg_buffercache/pg_buffercache_pages.c
@@ -13,11 +13,14 @@
#include "funcapi.h"
#include "storage/buf_internals.h"
#include "storage/bufmgr.h"
+#include "utils/builtins.h"
+#define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8
+#define NUM_BUFFERCACHE_PAGES_ELEM 9
+#define NUM_BUFFERCACHE_PAGES_VERSION_1_4_ELEM 10
-#define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8
-#define NUM_BUFFERCACHE_PAGES_ELEM 9
-
+#define NUM_TYPE_OF_STATE 10
+#define LENGTH_OF_STATE_NAME 25
PG_MODULE_MAGIC;
/*
@@ -41,6 +44,8 @@ typedef struct
* because of bufmgr.c's PrivateRefCount infrastructure.
*/
int32 pinning_backends;
+
+ uint32 buffer_state;
} BufferCachePagesRec;
@@ -53,6 +58,11 @@ typedef struct
BufferCachePagesRec *record;
} BufferCachePagesContext;
+struct BufferStateInfomation
+{
+ uint32 state_value;
+ const char state_name[LENGTH_OF_STATE_NAME];
+};
/*
* Function returning data from the shared buffer cache - buffer number,
@@ -95,7 +105,7 @@ pg_buffercache_pages(PG_FUNCTION_ARGS)
elog(ERROR, "return type must be a row type");
if (expected_tupledesc->natts < NUM_BUFFERCACHE_PAGES_MIN_ELEM ||
- expected_tupledesc->natts > NUM_BUFFERCACHE_PAGES_ELEM)
+ expected_tupledesc->natts > NUM_BUFFERCACHE_PAGES_VERSION_1_4_ELEM)
elog(ERROR, "incorrect number of output arguments");
/* Construct a tuple descriptor for the result rows. */
@@ -117,12 +127,15 @@ pg_buffercache_pages(PG_FUNCTION_ARGS)
TupleDescInitEntry(tupledesc, (AttrNumber) 8, "usage_count",
INT2OID, -1, 0);
- if (expected_tupledesc->natts == NUM_BUFFERCACHE_PAGES_ELEM)
+ if (expected_tupledesc->natts == NUM_BUFFERCACHE_PAGES_ELEM ||
+ expected_tupledesc->natts == NUM_BUFFERCACHE_PAGES_VERSION_1_4_ELEM)
TupleDescInitEntry(tupledesc, (AttrNumber) 9, "pinning_backends",
INT4OID, -1, 0);
+ if (expected_tupledesc->natts == NUM_BUFFERCACHE_PAGES_VERSION_1_4_ELEM)
+ TupleDescInitEntry(tupledesc, (AttrNumber) 10, "buffer_state",
+ INT8OID, -1, 0);
fctx->tupdesc = BlessTupleDesc(tupledesc);
-
/* Allocate NBuffers worth of BufferCachePagesRec records. */
fctx->record = (BufferCachePagesRec *)
MemoryContextAllocHuge(CurrentMemoryContext,
@@ -160,6 +173,7 @@ pg_buffercache_pages(PG_FUNCTION_ARGS)
fctx->record[i].blocknum = bufHdr->tag.blockNum;
fctx->record[i].usagecount = BUF_STATE_GET_USAGECOUNT(buf_state);
fctx->record[i].pinning_backends = BUF_STATE_GET_REFCOUNT(buf_state);
+ fctx->record[i].buffer_state = buf_state;
if (buf_state & BM_DIRTY)
fctx->record[i].isdirty = true;
@@ -180,12 +194,11 @@ pg_buffercache_pages(PG_FUNCTION_ARGS)
/* Get the saved state */
fctx = funcctx->user_fctx;
-
if (funcctx->call_cntr < funcctx->max_calls)
{
uint32 i = funcctx->call_cntr;
- Datum values[NUM_BUFFERCACHE_PAGES_ELEM];
- bool nulls[NUM_BUFFERCACHE_PAGES_ELEM];
+ Datum values[NUM_BUFFERCACHE_PAGES_VERSION_1_4_ELEM];
+ bool nulls[NUM_BUFFERCACHE_PAGES_VERSION_1_4_ELEM];
values[0] = Int32GetDatum(fctx->record[i].bufferid);
nulls[0] = false;
@@ -206,6 +219,7 @@ pg_buffercache_pages(PG_FUNCTION_ARGS)
nulls[7] = true;
/* unused for v1.0 callers, but the array is always long enough */
nulls[8] = true;
+ nulls[9] = true;
}
else
{
@@ -226,14 +240,61 @@ pg_buffercache_pages(PG_FUNCTION_ARGS)
/* unused for v1.0 callers, but the array is always long enough */
values[8] = Int32GetDatum(fctx->record[i].pinning_backends);
nulls[8] = false;
+ /* unused for v1.3 callers, but the array is always long enough */
+ values[9] = Int64GetDatum((int64)fctx->record[i].buffer_state);
+ nulls[9] = false;
}
-
/* Build and return the tuple. */
tuple = heap_form_tuple(fctx->tupdesc, values, nulls);
result = HeapTupleGetDatum(tuple);
-
SRF_RETURN_NEXT(funcctx, result);
}
else
SRF_RETURN_DONE(funcctx);
}
+
+PG_FUNCTION_INFO_V1(pg_buffercache_state_print);
+
+static struct BufferStateInfomation state_list[NUM_TYPE_OF_STATE] =
+{
+ {(uint32)BM_LOCKED, "LOCKED"},
+ {(uint32)BM_DIRTY, "DIRTY"},
+ {(uint32)BM_VALID, "VALID"},
+ {(uint32)BM_TAG_VALID, "TAG_VALID"},
+ {(uint32)BM_IO_IN_PROGRESS, "IO_IN_PROGRESS"},
+ {(uint32)BM_IO_ERROR, "IO_ERROR"},
+ {(uint32)BM_JUST_DIRTIED, "JUST_DIRTIED"},
+ {(uint32)BM_PIN_COUNT_WAITER, "PIN_COUNT_WAITER"},
+ {(uint32)BM_CHECKPOINT_NEEDED, "CHECKPOINT_NEEDED"},
+ {(uint32)BM_PERMANENT, "PERMANENT"},
+};
+
+Datum
+pg_buffercache_state_print(PG_FUNCTION_ARGS)
+{
+ int64 buffer_state = PG_GETARG_INT64(0);
+ ArrayType *string_array;
+ Datum *string_data;
+ int array_size = 0;
+ int list_num = 0;
+
+ if( buffer_state == 0 )
+ {
+ string_array = construct_empty_array(TEXTOID);
+ PG_RETURN_POINTER(string_array);
+ }
+
+ string_data = (Datum *) palloc(sizeof(Datum) * NUM_TYPE_OF_STATE );
+
+ for( list_num = 0; list_num < NUM_TYPE_OF_STATE; list_num ++ )
+ {
+ if( buffer_state & state_list[list_num].state_value )
+ string_data[array_size++] =
+ CStringGetTextDatum(state_list[list_num].state_name);
+ }
+
+ string_array = construct_array( string_data, array_size, TEXTOID, -1, false, 'i');
+
+ pfree(string_data);
+ PG_RETURN_POINTER(string_array);
+}
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index 18ac781..b02dc19 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -114,6 +114,13 @@
<entry>Number of backends pinning this buffer</entry>
</row>
+ <row>
+ <entry><structfield>buffer_state</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry></entry>
+ <entry>Hex value of Buffer state</entry>
+ </row>
+
</tbody>
</tgroup>
</table>
@@ -145,6 +152,27 @@
</sect2>
<sect2>
+ <title>General Functions</title>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <function>pg_buffercache_state_print return text[]</function>
+ <indexterm>
+ <primary>pg_buffercache_state_print</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ <function>pg_buffercache_state_print</function> Outputs the value of
+ the buffer status in hexadecimal to text[] in easy-readable.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </sect2>
+ <sect2>
<title>Sample Output</title>
<screen>
@@ -174,6 +202,34 @@ regression=# SELECT c.relname, count(*) AS buffers
</sect2>
<sect2>
+ <title> pg_buffercache_state_print Fuction Sample Output </title>
+<screen>
+regression=# SELECT pg_buffercache_state_print(2203320320);
+
+pg_buffercache_state_print
+------------------------------------
+{LOCKED,VALID,TAG_VALID,PERMANENT}
+(1 row)
+
+regression=# SELECT bufferid, relfilenode, buffer_state, state_text FROM pg_buffercache,
+ LATERAL pg_buffercache_state_print(buffer_state) M(state_text)
+ WHERE bufferid < 10;
+
+bufferid | relfilenode | buffer_state | state_text
+----------+-------------+--------------+-------------------------------------------------------
+ 1 | 1262 | 2203320320 | {LOCKED,VALID,TAG_VALID,PERMANENT}
+ 2 | 1260 | 2202533888 | {LOCKED,VALID,TAG_VALID,PERMANENT}
+ 3 | 1259 | 2480144384 | {LOCKED,DIRTY,VALID,TAG_VALID,JUST_DIRTIED,PERMANENT}
+ 4 | 1259 | 2203320320 | {LOCKED,VALID,TAG_VALID,PERMANENT}
+ 5 | 1259 | 2203320320 | {LOCKED,VALID,TAG_VALID,PERMANENT}
+ 6 | 1249 | 2203320320 | {LOCKED,VALID,TAG_VALID,PERMANENT}
+ 7 | 1249 | 2203320320 | {LOCKED,VALID,TAG_VALID,PERMANENT}
+ 8 | 1249 | 2203320320 | {LOCKED,VALID,TAG_VALID,PERMANENT}
+ 9 | 1249 | 2203320320 | {LOCKED,VALID,TAG_VALID,PERMANENT}
+(9 rows)
+</screen>
+ </sect2>
+ <sect2>
<title>Authors</title>
<para>
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
# I add [hacker] to the mail subject.
Dear Andres Freund.
Thank you for review!
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.
Okay!
I'll not print(or add) the internal value directly.
(and I'll be careful when create another patch).
Thank you
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?
It's it better to output the decode state value from pg_buffercache view?
For example to following output
-----
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 | {LOCKED,VALID,TAG_VALID,PERMANENT}
-----
It's right?
If it is correct, I'll modify patch ASAP.
Regards.
Moon.
Show quoted text
-----Original Message-----
From: Andres Freund [mailto:andres@anarazel.de]
Sent: Tuesday, November 14, 2017 6:07 PM
To: Moon Insung
Cc: 'PostgreSQL Hackers'
Subject: Re: [PATCH]pg_buffercache add a buffer state column, Add fuction to decode buffer stateHi,
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
Import Notes
Resolved by subject fallback
On Tue, Nov 14, 2017 at 6:36 PM, Moon Insung
<Moon_Insung_i3@lab.ntt.co.jp> wrote:
# I add [hacker] to the mail subject.
You should avoid top-posting.
Dear Andres Freund.
Thank you for review!
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.Okay!
I'll not print(or add) the internal value directly.
(and I'll be careful when create another patch).
Thank youOne 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?
+1
It's it better to output the decode state value from pg_buffercache view?
For example to following output-----
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 | {LOCKED,VALID,TAG_VALID,PERMANENT}
-----It's right?
If it is correct, I'll modify patch ASAP.
I think it's better to register this patch to the next commit fest so
as not to forget.
-----Original Message-----
From: Andres Freund [mailto:andres@anarazel.de]
Sent: Tuesday, November 14, 2017 6:07 PM
To: Moon Insung
Cc: 'PostgreSQL Hackers'
Subject: Re: [PATCH]pg_buffercache add a buffer state column, Add fuction to decode buffer stateHi,
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?
Regards,
--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center