From 2bf372f079e9cbecd82ce431622f7e683f659596 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot Date: Thu, 7 Nov 2024 14:27:05 +0000 Subject: [PATCH v5 4/4] Add pg_stat_get_backend_io() Adding the pg_stat_get_backend_io() function to retrieve I/O statistics for a particular backend pid. Note that this function does not return any rows if stats_fetch_consistency is set to 'snapshot' and the pid of interest is not our own pid (there is no use case of retrieving other backend's stats with stats_fetch_consistency set to 'snapshot'). --- doc/src/sgml/monitoring.sgml | 17 ++++++++ src/backend/catalog/system_views.sql | 2 +- src/backend/utils/activity/pgstat_io.c | 18 ++++++++ src/backend/utils/adt/pgstatfuncs.c | 27 +++++++++++- src/include/catalog/pg_proc.dat | 14 +++---- src/include/pgstat.h | 1 + src/test/regress/expected/rules.out | 2 +- src/test/regress/expected/stats.out | 57 ++++++++++++++++++++++++++ src/test/regress/sql/stats.sql | 33 +++++++++++++++ 9 files changed, 160 insertions(+), 11 deletions(-) 10.4% doc/src/sgml/ 5.8% src/backend/utils/activity/ 8.6% src/backend/utils/adt/ 16.0% src/include/catalog/ 32.9% src/test/regress/expected/ 24.1% src/test/regress/sql/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index fc6aded3da..a6672548b8 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -4814,6 +4814,23 @@ description | Waiting for a newly initialized WAL file to reach durable storage + + + + pg_stat_get_backend_io + + pg_stat_get_backend_io ( integer ) + setof record + + + Returns I/O statistics about the backend with the specified + process ID. The output fields are exactly the same as the ones in the + pg_stat_io + view. This function does not return any rows if stats_fetch_consistency + is set to snapshot and the process ID is not our own. + + + diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 09af4a40a8..6f17f505f1 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1190,7 +1190,7 @@ SELECT b.fsyncs, b.fsync_time, b.stats_reset -FROM pg_stat_get_my_io() b; +FROM pg_stat_get_backend_io(NULL) b; CREATE VIEW pg_stat_wal AS SELECT diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c index 1384f8103e..1063c6bec3 100644 --- a/src/backend/utils/activity/pgstat_io.c +++ b/src/backend/utils/activity/pgstat_io.c @@ -162,6 +162,24 @@ pgstat_fetch_my_stat_io(void) pgstat_fetch_entry(PGSTAT_KIND_PER_BACKEND, InvalidOid, MyProcNumber); } +/* + * Returns other backend's IO stats or NULL if pgstat_fetch_consistency is set + * to 'snapshot'. + */ +PgStat_Backend * +pgstat_fetch_proc_stat_io(ProcNumber procNumber) +{ + PgStat_Backend *backend_entry; + + if (pgstat_fetch_consistency == PGSTAT_FETCH_CONSISTENCY_SNAPSHOT) + return NULL; + + backend_entry = (PgStat_Backend *) pgstat_fetch_entry(PGSTAT_KIND_PER_BACKEND, + InvalidOid, procNumber); + + return backend_entry; +} + /* * Simpler wrapper of pgstat_per_backend_flush_cb(). */ diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index fd16986156..e2041be237 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1475,7 +1475,7 @@ pg_stat_get_io(PG_FUNCTION_ARGS) } Datum -pg_stat_get_my_io(PG_FUNCTION_ARGS) +pg_stat_get_backend_io(PG_FUNCTION_ARGS) { ReturnSetInfo *rsinfo; PgStat_Backend *backend_stats; @@ -1487,7 +1487,30 @@ pg_stat_get_my_io(PG_FUNCTION_ARGS) InitMaterializedSRF(fcinfo, 0); rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; - backend_stats = pgstat_fetch_my_stat_io(); + if (PG_ARGISNULL(0) || PG_GETARG_INT32(0) == MyProcPid) + backend_stats = pgstat_fetch_my_stat_io(); + else + { + PGPROC *proc; + ProcNumber procNumber; + int pid = PG_GETARG_INT32(0); + + proc = BackendPidGetProc(pid); + + /* maybe an auxiliary process? */ + if (proc == NULL) + proc = AuxiliaryPidGetProc(pid); + + if (proc != NULL) + { + procNumber = GetNumberFromPGProc(proc); + backend_stats = pgstat_fetch_proc_stat_io(procNumber); + if (!backend_stats) + return (Datum) 0; + } + else + return (Datum) 0; + } bktype = backend_stats->bktype; bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype)); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 89eb89efe4..fafee5a947 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5903,14 +5903,14 @@ proargnames => '{backend_type,object,context,reads,read_time,writes,write_time,writebacks,writeback_time,extends,extend_time,op_bytes,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}', prosrc => 'pg_stat_get_io' }, -{ oid => '8806', descr => 'statistics: my backend IO statistics', - proname => 'pg_stat_get_my_io', prorows => '5', proretset => 't', +{ oid => '8806', descr => 'statistics: per backend IO statistics', + proname => 'pg_stat_get_backend_io', prorows => '5', proretset => 't', provolatile => 'v', proparallel => 'r', prorettype => 'record', - proargtypes => '', - proallargtypes => '{text,text,text,int8,float8,int8,float8,int8,float8,int8,float8,int8,int8,int8,int8,int8,float8,timestamptz}', - proargmodes => '{o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}', - proargnames => '{backend_type,object,context,reads,read_time,writes,write_time,writebacks,writeback_time,extends,extend_time,op_bytes,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}', - prosrc => 'pg_stat_get_my_io' }, + proargtypes => 'int4', proisstrict => 'f', + proallargtypes => '{int4,text,text,text,int8,float8,int8,float8,int8,float8,int8,float8,int8,int8,int8,int8,int8,float8,timestamptz}', + proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}', + proargnames => '{backend_pid,backend_type,object,context,reads,read_time,writes,write_time,writebacks,writeback_time,extends,extend_time,op_bytes,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}', + prosrc => 'pg_stat_get_backend_io' }, { oid => '1136', descr => 'statistics: information about WAL activity', proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 13b9c71759..54c9f8b45c 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -576,6 +576,7 @@ extern void pgstat_count_io_op_time(IOObject io_object, IOContext io_context, extern PgStat_IO *pgstat_fetch_stat_io(void); extern PgStat_Backend *pgstat_fetch_my_stat_io(void); +extern PgStat_Backend *pgstat_fetch_proc_stat_io(ProcNumber procNumber); extern const char *pgstat_get_io_context_name(IOContext io_context); extern const char *pgstat_get_io_object_name(IOObject io_object); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index f92516b047..3cc0cfbcb5 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1416,7 +1416,7 @@ pg_my_stat_io| SELECT backend_type, fsyncs, fsync_time, stats_reset - FROM pg_stat_get_my_io() b(backend_type, object, context, reads, read_time, writes, write_time, writebacks, writeback_time, extends, extend_time, op_bytes, hits, evictions, reuses, fsyncs, fsync_time, stats_reset); + FROM pg_stat_get_backend_io(NULL::integer) b(backend_type, object, context, reads, read_time, writes, write_time, writebacks, writeback_time, extends, extend_time, op_bytes, hits, evictions, reuses, fsyncs, fsync_time, stats_reset); pg_policies| SELECT n.nspname AS schemaname, c.relname AS tablename, pol.polname AS policyname, diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 846e693483..846ffb59f2 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -1263,12 +1263,18 @@ SELECT sum(extends) AS io_sum_shared_before_extends FROM pg_stat_io WHERE context = 'normal' AND object = 'relation' \gset SELECT sum(extends) AS my_io_sum_shared_before_extends FROM pg_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset +SELECT sum(extends) AS backend_io_sum_shared_before_extends + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'normal' AND object = 'relation' \gset SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs FROM pg_stat_io WHERE object = 'relation' \gset io_sum_shared_before_ SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs FROM pg_my_stat_io WHERE object = 'relation' \gset my_io_sum_shared_before_ +SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE object = 'relation' \gset backend_io_sum_shared_before_ CREATE TABLE test_io_shared(a int); INSERT INTO test_io_shared SELECT i FROM generate_series(1,100)i; SELECT pg_stat_force_next_flush(); @@ -1293,6 +1299,15 @@ SELECT :my_io_sum_shared_after_extends > :my_io_sum_shared_before_extends; t (1 row) +SELECT sum(extends) AS backend_io_sum_shared_after_extends + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'normal' AND object = 'relation' \gset +SELECT :backend_io_sum_shared_after_extends > :backend_io_sum_shared_before_extends; + ?column? +---------- + t +(1 row) + -- After a checkpoint, there should be some additional IOCONTEXT_NORMAL writes -- and fsyncs in the global stats (not for the backend). -- See comment above for rationale for two explicit CHECKPOINTs. @@ -1331,6 +1346,48 @@ SELECT current_setting('fsync') = 'off' t (1 row) +-- Check the stats_fetch_consistency behavior on per backend I/O stats +SELECT pid AS checkpointer_pid FROM pg_stat_activity + WHERE backend_type = 'checkpointer' \gset +BEGIN; +SET LOCAL stats_fetch_consistency = snapshot; +SELECT sum(extends) AS my_io_sum_shared_before_extends + FROM pg_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset +SELECT sum(extends) AS backend_io_sum_shared_before_extends + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'normal' AND object = 'relation' \gset +INSERT INTO test_io_shared SELECT i FROM generate_series(1,100)i; +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT sum(extends) AS my_io_sum_shared_after_extends + FROM pg_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset +SELECT :my_io_sum_shared_after_extends = :my_io_sum_shared_before_extends; + ?column? +---------- + t +(1 row) + +SELECT sum(extends) AS backend_io_sum_shared_after_extends + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'normal' AND object = 'relation' \gset +SELECT :backend_io_sum_shared_after_extends = :backend_io_sum_shared_before_extends; + ?column? +---------- + t +(1 row) + +-- Don't return any rows if querying other backend's stats with snapshot set +SELECT count(1) = 0 FROM pg_stat_get_backend_io(:checkpointer_pid); + ?column? +---------- + t +(1 row) + +ROLLBACK; -- Change the tablespace so that the table is rewritten directly, then SELECT -- from it to cause it to be read back into shared buffers. SELECT sum(reads) AS io_sum_shared_before_reads diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 9cb14b7182..e597c4d597 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -611,12 +611,18 @@ SELECT sum(extends) AS io_sum_shared_before_extends FROM pg_stat_io WHERE context = 'normal' AND object = 'relation' \gset SELECT sum(extends) AS my_io_sum_shared_before_extends FROM pg_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset +SELECT sum(extends) AS backend_io_sum_shared_before_extends + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'normal' AND object = 'relation' \gset SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs FROM pg_stat_io WHERE object = 'relation' \gset io_sum_shared_before_ SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs FROM pg_my_stat_io WHERE object = 'relation' \gset my_io_sum_shared_before_ +SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE object = 'relation' \gset backend_io_sum_shared_before_ CREATE TABLE test_io_shared(a int); INSERT INTO test_io_shared SELECT i FROM generate_series(1,100)i; SELECT pg_stat_force_next_flush(); @@ -626,6 +632,10 @@ SELECT :io_sum_shared_after_extends > :io_sum_shared_before_extends; SELECT sum(extends) AS my_io_sum_shared_after_extends FROM pg_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset SELECT :my_io_sum_shared_after_extends > :my_io_sum_shared_before_extends; +SELECT sum(extends) AS backend_io_sum_shared_after_extends + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'normal' AND object = 'relation' \gset +SELECT :backend_io_sum_shared_after_extends > :backend_io_sum_shared_before_extends; -- After a checkpoint, there should be some additional IOCONTEXT_NORMAL writes -- and fsyncs in the global stats (not for the backend). @@ -647,6 +657,29 @@ SELECT current_setting('fsync') = 'off' OR (:my_io_sum_shared_after_fsyncs = :my_io_sum_shared_before_fsyncs AND :my_io_sum_shared_after_fsyncs= 0); +-- Check the stats_fetch_consistency behavior on per backend I/O stats +SELECT pid AS checkpointer_pid FROM pg_stat_activity + WHERE backend_type = 'checkpointer' \gset +BEGIN; +SET LOCAL stats_fetch_consistency = snapshot; +SELECT sum(extends) AS my_io_sum_shared_before_extends + FROM pg_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset +SELECT sum(extends) AS backend_io_sum_shared_before_extends + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'normal' AND object = 'relation' \gset +INSERT INTO test_io_shared SELECT i FROM generate_series(1,100)i; +SELECT pg_stat_force_next_flush(); +SELECT sum(extends) AS my_io_sum_shared_after_extends + FROM pg_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset +SELECT :my_io_sum_shared_after_extends = :my_io_sum_shared_before_extends; +SELECT sum(extends) AS backend_io_sum_shared_after_extends + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context = 'normal' AND object = 'relation' \gset +SELECT :backend_io_sum_shared_after_extends = :backend_io_sum_shared_before_extends; +-- Don't return any rows if querying other backend's stats with snapshot set +SELECT count(1) = 0 FROM pg_stat_get_backend_io(:checkpointer_pid); +ROLLBACK; + -- Change the tablespace so that the table is rewritten directly, then SELECT -- from it to cause it to be read back into shared buffers. SELECT sum(reads) AS io_sum_shared_before_reads -- 2.34.1