From 0e3c2c066698acaaad69824a619e78f2cc16a358 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Wed, 18 Dec 2024 13:48:34 +0900 Subject: [PATCH v10 2/2] Tweaks on top of v9 Other notes for commit: Requires a bump of CATALOG_VERSION_NO. Requires a bump of PGSTAT_FILE_FORMAT_ID. --- src/include/catalog/pg_proc.dat | 3 +- src/include/pgstat.h | 11 +- src/backend/utils/activity/backend_status.c | 2 +- src/backend/utils/activity/pgstat_backend.c | 38 ++- src/backend/utils/activity/pgstat_io.c | 2 - src/backend/utils/adt/pgstatfuncs.c | 305 ++++++++------------ src/test/regress/expected/stats.out | 28 +- src/test/regress/sql/stats.sql | 12 +- doc/src/sgml/monitoring.sgml | 10 +- 9 files changed, 184 insertions(+), 227 deletions(-) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 437157ffa3..2dcc2d42da 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -6061,8 +6061,7 @@ proname => 'pg_stat_reset_single_function_counters', provolatile => 'v', prorettype => 'void', proargtypes => 'oid', prosrc => 'pg_stat_reset_single_function_counters' }, -{ oid => '9987', - descr => 'statistics: reset statistics for a single backend', +{ oid => '8807', descr => 'statistics: reset statistics for a single backend', proname => 'pg_stat_reset_backend_stats', provolatile => 'v', prorettype => 'void', proargtypes => 'int4', prosrc => 'pg_stat_reset_backend_stats' }, diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 479773cfd2..56ed1b8bb3 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -363,11 +363,11 @@ typedef struct PgStat_BktypeIO PgStat_Counter times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES]; } PgStat_BktypeIO; -typedef struct PgStat_BackendPendingIO +typedef struct PgStat_PendingIO { PgStat_Counter counts[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES]; instr_time pending_times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES]; -} PgStat_BackendPendingIO; +} PgStat_PendingIO; typedef struct PgStat_IO { @@ -375,6 +375,9 @@ typedef struct PgStat_IO PgStat_BktypeIO stats[BACKEND_NUM_TYPES]; } PgStat_IO; +/* Backend statistics store the same amount of IO data as PGSTAT_KIND_IO */ +typedef PgStat_PendingIO PgStat_BackendPendingIO; + typedef struct PgStat_Backend { TimestampTz stat_reset_timestamp; @@ -565,9 +568,9 @@ extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void); * Functions in pgstat_backend.c */ -extern PgStat_Backend *pgstat_fetch_proc_stat_io(ProcNumber procNumber); +extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber); extern bool pgstat_tracks_backend_bktype(BackendType bktype); -extern void pgstat_create_backend_stat(ProcNumber procnum); +extern void pgstat_create_backend(ProcNumber procnum); /* * Functions in pgstat_bgwriter.c diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c index e9c3b87ac2..bf33e33a4e 100644 --- a/src/backend/utils/activity/backend_status.c +++ b/src/backend/utils/activity/backend_status.c @@ -428,7 +428,7 @@ pgstat_bestart(void) /* Create the backend statistics entry */ if (pgstat_tracks_backend_bktype(MyBackendType)) - pgstat_create_backend_stat(MyProcNumber); + pgstat_create_backend(MyProcNumber); /* Update app name to current GUC setting */ if (application_name) diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c index e2d83024c2..6b2c9baa8c 100644 --- a/src/backend/utils/activity/pgstat_backend.c +++ b/src/backend/utils/activity/pgstat_backend.c @@ -3,11 +3,17 @@ * pgstat_backend.c * Implementation of backend statistics. * - * This file contains the implementation of backend statistics. It is kept + * This file contains the implementation of backend statistics. It is kept * separate from pgstat.c to enforce the line between the statistics access / - * storage implementation and the details about individual types of statistics. + * storage implementation and the details about individual types of + * statistics. * - * Copyright (c) 2024, PostgreSQL Global Development Group + * This statistics kind uses a proc number as object ID for the hash table + * of pgstats. Entries are created each time a process is spawned, and are + * dropped when the process exits. These are not written to the pgstats file + * on disk. + * + * Copyright (c) 2001-2024, PostgreSQL Global Development Group * * IDENTIFICATION * src/backend/utils/activity/pgstat_backend.c @@ -19,10 +25,10 @@ #include "utils/pgstat_internal.h" /* - * Returns backend's IO stats. + * Returns statistics of a backend by proc number. */ PgStat_Backend * -pgstat_fetch_proc_stat_io(ProcNumber procNumber) +pgstat_fetch_stat_backend(ProcNumber procNumber) { PgStat_Backend *backend_entry; @@ -81,21 +87,21 @@ pgstat_backend_flush_cb(PgStat_EntryRef *entry_ref, bool nowait) void pgstat_flush_backend(bool nowait) { - if (pgstat_tracks_backend_bktype(MyBackendType)) - { - PgStat_EntryRef *entry_ref; + PgStat_EntryRef *entry_ref; - entry_ref = pgstat_get_entry_ref(PGSTAT_KIND_BACKEND, InvalidOid, - MyProcNumber, false, NULL); - (void) pgstat_backend_flush_cb(entry_ref, nowait); - } + if (!pgstat_tracks_backend_bktype(MyBackendType)) + return; + + entry_ref = pgstat_get_entry_ref(PGSTAT_KIND_BACKEND, InvalidOid, + MyProcNumber, false, NULL); + (void) pgstat_backend_flush_cb(entry_ref, nowait); } /* - * Create the backend statistics entry for procnum. + * Create backend statistics entry for proc number. */ void -pgstat_create_backend_stat(ProcNumber procnum) +pgstat_create_backend(ProcNumber procnum) { PgStat_EntryRef *entry_ref; PgStatShared_Backend *shstatent; @@ -113,7 +119,7 @@ pgstat_create_backend_stat(ProcNumber procnum) } /* - * Find or create a local PgStat_BackendPendingIO entry for procnum. + * Find or create a local PgStat_BackendPendingIO entry for proc number. */ PgStat_BackendPendingIO * pgstat_prep_backend_pending(ProcNumber procnum) @@ -136,7 +142,7 @@ pgstat_prep_backend_pending(ProcNumber procnum) * I/O stats are already visible in pg_stat_io and there is only one of those. * * Function returns true if BackendType participates in the backend stats - * subsystem for IO and false if it does not. + * subsystem and false if it does not. * * When adding a new BackendType, also consider adding relevant restrictions to * pgstat_tracks_io_object() and pgstat_tracks_io_op(). diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c index e0c206a453..011a3326da 100644 --- a/src/backend/utils/activity/pgstat_io.c +++ b/src/backend/utils/activity/pgstat_io.c @@ -20,8 +20,6 @@ #include "storage/bufmgr.h" #include "utils/pgstat_internal.h" -typedef PgStat_BackendPendingIO PgStat_PendingIO; - static PgStat_PendingIO PendingIOStats; static bool have_iostats = false; diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index b939551d36..18c74807a4 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1274,8 +1274,9 @@ pg_stat_get_buf_alloc(PG_FUNCTION_ARGS) } /* -* When adding a new column to the pg_stat_io view, add a new enum value -* here above IO_NUM_COLUMNS. +* When adding a new column to the pg_stat_io view and the +* pg_stat_get_backend_io() function, add a new enum value here above +* IO_NUM_COLUMNS. */ typedef enum io_stat_col { @@ -1365,184 +1366,20 @@ pg_stat_us_to_ms(PgStat_Counter val_ms) return val_ms * (double) 0.001; } -Datum -pg_stat_get_io(PG_FUNCTION_ARGS) +/* + * pg_stat_fill_io_data + * + * Helper routine for pg_stat_get_io() and pg_stat_get_backend_io(), + * filling in a result tuplestore with one tuple for each object and each + * context supported by the caller, based on the contents of bktype_stats. + */ +static void +pg_stat_fill_io_data(ReturnSetInfo *rsinfo, + PgStat_BktypeIO *bktype_stats, + BackendType bktype, + TimestampTz stat_reset_timestamp) { - ReturnSetInfo *rsinfo; - PgStat_IO *backends_io_stats; - Datum reset_time; - - InitMaterializedSRF(fcinfo, 0); - rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; - - backends_io_stats = pgstat_fetch_stat_io(); - - reset_time = TimestampTzGetDatum(backends_io_stats->stat_reset_timestamp); - - for (int bktype = 0; bktype < BACKEND_NUM_TYPES; bktype++) - { - Datum bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype)); - PgStat_BktypeIO *bktype_stats = &backends_io_stats->stats[bktype]; - - /* - * In Assert builds, we can afford an extra loop through all of the - * counters checking that only expected stats are non-zero, since it - * keeps the non-Assert code cleaner. - */ - Assert(pgstat_bktype_io_stats_valid(bktype_stats, bktype)); - - /* - * For those BackendTypes without IO Operation stats, skip - * representing them in the view altogether. - */ - if (!pgstat_tracks_io_bktype(bktype)) - continue; - - for (int io_obj = 0; io_obj < IOOBJECT_NUM_TYPES; io_obj++) - { - const char *obj_name = pgstat_get_io_object_name(io_obj); - - for (int io_context = 0; io_context < IOCONTEXT_NUM_TYPES; io_context++) - { - const char *context_name = pgstat_get_io_context_name(io_context); - - Datum values[IO_NUM_COLUMNS] = {0}; - bool nulls[IO_NUM_COLUMNS] = {0}; - - /* - * Some combinations of BackendType, IOObject, and IOContext - * are not valid for any type of IOOp. In such cases, omit the - * entire row from the view. - */ - if (!pgstat_tracks_io_object(bktype, io_obj, io_context)) - continue; - - values[IO_COL_BACKEND_TYPE] = bktype_desc; - values[IO_COL_CONTEXT] = CStringGetTextDatum(context_name); - values[IO_COL_OBJECT] = CStringGetTextDatum(obj_name); - values[IO_COL_RESET_TIME] = reset_time; - - /* - * Hard-code this to the value of BLCKSZ for now. Future - * values could include XLOG_BLCKSZ, once WAL IO is tracked, - * and constant multipliers, once non-block-oriented IO (e.g. - * temporary file IO) is tracked. - */ - values[IO_COL_CONVERSION] = Int64GetDatum(BLCKSZ); - - for (int io_op = 0; io_op < IOOP_NUM_TYPES; io_op++) - { - int op_idx = pgstat_get_io_op_index(io_op); - int time_idx = pgstat_get_io_time_index(io_op); - - /* - * Some combinations of BackendType and IOOp, of IOContext - * and IOOp, and of IOObject and IOOp are not tracked. Set - * these cells in the view NULL. - */ - if (pgstat_tracks_io_op(bktype, io_obj, io_context, io_op)) - { - PgStat_Counter count = - bktype_stats->counts[io_obj][io_context][io_op]; - - values[op_idx] = Int64GetDatum(count); - } - else - nulls[op_idx] = true; - - /* not every operation is timed */ - if (time_idx == IO_COL_INVALID) - continue; - - if (!nulls[op_idx]) - { - PgStat_Counter time = - bktype_stats->times[io_obj][io_context][io_op]; - - values[time_idx] = Float8GetDatum(pg_stat_us_to_ms(time)); - } - else - nulls[time_idx] = true; - } - - tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, - values, nulls); - } - } - } - - return (Datum) 0; -} - -Datum -pg_stat_get_backend_io(PG_FUNCTION_ARGS) -{ - ReturnSetInfo *rsinfo; - PgStat_Backend *backend_stats; - Datum bktype_desc; - PgStat_BktypeIO *bktype_stats; - BackendType bktype; - Datum reset_time; - int num_backends = pgstat_fetch_stat_numbackends(); - int curr_backend; - int pid; - PGPROC *proc; - ProcNumber procNumber; - - InitMaterializedSRF(fcinfo, 0); - rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; - - pid = PG_GETARG_INT32(0); - proc = BackendPidGetProc(pid); - - /* - * Could be an auxiliary process but would not report any stats due to - * pgstat_tracks_backend_bktype() anyway. So don't need an extra call to - * AuxiliaryPidGetProc(). - */ - if (!proc) - return (Datum) 0; - - procNumber = GetNumberFromPGProc(proc); - backend_stats = pgstat_fetch_proc_stat_io(procNumber); - - if (!backend_stats) - return (Datum) 0; - - bktype = B_INVALID; - - /* Look for the backend type */ - for (curr_backend = 1; curr_backend <= num_backends; curr_backend++) - { - LocalPgBackendStatus *local_beentry; - PgBackendStatus *beentry; - - /* Get the next one in the list */ - local_beentry = pgstat_get_local_beentry_by_index(curr_backend); - beentry = &local_beentry->backendStatus; - - /* Looking for specific PID, ignore all the others */ - if (beentry->st_procpid != pid) - continue; - - bktype = beentry->st_backendType; - break; - } - - /* Backend is gone */ - if (bktype == B_INVALID) - return (Datum) 0; - - bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype)); - bktype_stats = &backend_stats->stats; - reset_time = TimestampTzGetDatum(backend_stats->stat_reset_timestamp); - - /* - * In Assert builds, we can afford an extra loop through all of the - * counters checking that only expected stats are non-zero, since it keeps - * the non-Assert code cleaner. - */ - Assert(pgstat_bktype_io_stats_valid(bktype_stats, bktype)); + Datum bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype)); for (int io_obj = 0; io_obj < IOOBJECT_NUM_TYPES; io_obj++) { @@ -1566,8 +1403,8 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS) values[IO_COL_BACKEND_TYPE] = bktype_desc; values[IO_COL_CONTEXT] = CStringGetTextDatum(context_name); values[IO_COL_OBJECT] = CStringGetTextDatum(obj_name); - if (backend_stats->stat_reset_timestamp != 0) - values[IO_COL_RESET_TIME] = reset_time; + if (stat_reset_timestamp != 0) + values[IO_COL_RESET_TIME] = TimestampTzGetDatum(stat_reset_timestamp); else nulls[IO_COL_RESET_TIME] = true; @@ -1618,7 +1455,104 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS) values, nulls); } } +} +Datum +pg_stat_get_io(PG_FUNCTION_ARGS) +{ + ReturnSetInfo *rsinfo; + PgStat_IO *backends_io_stats; + + InitMaterializedSRF(fcinfo, 0); + rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + + backends_io_stats = pgstat_fetch_stat_io(); + + for (int bktype = 0; bktype < BACKEND_NUM_TYPES; bktype++) + { + PgStat_BktypeIO *bktype_stats = &backends_io_stats->stats[bktype]; + + /* + * In Assert builds, we can afford an extra loop through all of the + * counters checking that only expected stats are non-zero, since it + * keeps the non-Assert code cleaner. + */ + Assert(pgstat_bktype_io_stats_valid(bktype_stats, bktype)); + + /* + * For those BackendTypes without IO Operation stats, skip + * representing them in the view altogether. + */ + if (!pgstat_tracks_io_bktype(bktype)) + continue; + + /* Save tuples with data from this PgStat_BktypeIO. */ + pg_stat_fill_io_data(rsinfo, bktype_stats, bktype, + backends_io_stats->stat_reset_timestamp); + } + + return (Datum) 0; +} + +/* + * Returns I/O statistics for a backend with given PID. + */ +Datum +pg_stat_get_backend_io(PG_FUNCTION_ARGS) +{ + ReturnSetInfo *rsinfo; + BackendType bktype; + int pid; + PGPROC *proc; + ProcNumber procNumber; + PgStat_Backend *backend_stats; + PgStat_BktypeIO *bktype_stats; + PgBackendStatus *beentry; + + InitMaterializedSRF(fcinfo, 0); + rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + + pid = PG_GETARG_INT32(0); + proc = BackendPidGetProc(pid); + + /* + * This could be an auxiliary process but these do not report backend + * statistics due to pgstat_tracks_backend_bktype(), so there is no need + * for an extra call to AuxiliaryPidGetProc(). + */ + if (!proc) + return (Datum) 0; + + procNumber = GetNumberFromPGProc(proc); + + backend_stats = pgstat_fetch_stat_backend(procNumber); + + if (!backend_stats) + return (Datum) 0; + + beentry = pgstat_get_beentry_by_proc_number(procNumber); + bktype = beentry->st_backendType; + + /* If PID does not match, leave */ + if (beentry->st_procpid != pid) + return (Datum) 0; + + /* Backend may be gone, so recheck in case */ + if (bktype == B_INVALID) + return (Datum) 0; + + bktype_stats = &backend_stats->stats; + + /* + * In Assert builds, we can afford an extra loop through all of the + * counters checking that only expected stats are non-zero, since it keeps + * the non-Assert code cleaner. + */ + Assert(pgstat_bktype_io_stats_valid(bktype_stats, bktype)); + + /* Save tuples with data from this PgStat_BktypeIO */ + pg_stat_fill_io_data(rsinfo, bktype_stats, bktype, + backend_stats->stat_reset_timestamp); return (Datum) 0; } @@ -1927,6 +1861,9 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS) PG_RETURN_VOID(); } +/* + * Reset statistics of backend with given PID. + */ Datum pg_stat_reset_backend_stats(PG_FUNCTION_ARGS) { @@ -1936,9 +1873,9 @@ pg_stat_reset_backend_stats(PG_FUNCTION_ARGS) proc = BackendPidGetProc(backend_pid); /* - * Could be an auxiliary process but would not report any stats due to - * pgstat_tracks_backend_bktype() anyway. So don't need an extra call to - * AuxiliaryPidGetProc(). + * This could be an auxiliary process but these do not report backend + * statistics due to pgstat_tracks_backend_bktype(), so there is no need + * for an extra call to AuxiliaryPidGetProc(). */ if (!proc) PG_RETURN_VOID(); diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 3447e7b75d..150b6dcf74 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -1249,7 +1249,8 @@ SELECT pg_stat_get_subscription_stats(NULL); (1 row) --- Test that the following operations are tracked in pg_stat_io and in backend stats: +-- Test that the following operations are tracked in pg_stat_io and in +-- backend stats: -- - reads of target blocks into shared buffers -- - writes of shared buffers to permanent storage -- - extends of relations using shared buffers @@ -1335,14 +1336,6 @@ SELECT current_setting('fsync') = 'off' t (1 row) --- Don't return any rows if querying other backend's stats that are excluded --- from the backend stats collection (like the checkpointer). -SELECT count(1) = 0 FROM pg_stat_get_backend_io(:checkpointer_pid); - ?column? ----------- - t -(1 row) - -- 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 @@ -1603,6 +1596,23 @@ SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset; t (1 row) +-- Check invalid input for pg_stat_get_backend_io() +SELECT pg_stat_get_backend_io(NULL); + pg_stat_get_backend_io +------------------------ +(0 rows) + +SELECT pg_stat_get_backend_io(0); + pg_stat_get_backend_io +------------------------ +(0 rows) + +-- Auxiliary processes return no data. +SELECT pg_stat_get_backend_io(:checkpointer_pid); + pg_stat_get_backend_io +------------------------ +(0 rows) + -- test BRIN index doesn't block HOT update CREATE TABLE brin_hot ( id integer PRIMARY KEY, diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 9c925005be..1e7d0ff665 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -595,7 +595,8 @@ SELECT pg_stat_get_replication_slot(NULL); SELECT pg_stat_get_subscription_stats(NULL); --- Test that the following operations are tracked in pg_stat_io and in backend stats: +-- Test that the following operations are tracked in pg_stat_io and in +-- backend stats: -- - reads of target blocks into shared buffers -- - writes of shared buffers to permanent storage -- - extends of relations using shared buffers @@ -650,10 +651,6 @@ 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); --- Don't return any rows if querying other backend's stats that are excluded --- from the backend stats collection (like the checkpointer). -SELECT count(1) = 0 FROM pg_stat_get_backend_io(:checkpointer_pid); - -- 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 @@ -801,6 +798,11 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + FROM pg_stat_get_backend_io(pg_backend_pid()) \gset SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset; +-- Check invalid input for pg_stat_get_backend_io() +SELECT pg_stat_get_backend_io(NULL); +SELECT pg_stat_get_backend_io(0); +-- Auxiliary processes return no data. +SELECT pg_stat_get_backend_io(:checkpointer_pid); -- test BRIN index doesn't block HOT update CREATE TABLE brin_hot ( diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 4a9464da61..d0d176cc54 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -4801,11 +4801,13 @@ description | Waiting for a newly initialized WAL file to reach durable storage 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. The function does not return I/O statistics for the checkpointer, + pg_stat_io view. + + + The function does not return I/O statistics for the checkpointer, the background writer, the startup process and the autovacuum launcher - as they are already visible in the pg_stat_io - view and there is only one of those. + as they are already visible in the pg_stat_io + view and there is only one of each. -- 2.45.2