Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tNR2X-00Az5j-CZ for pgsql-hackers@arkaria.postgresql.org; Tue, 17 Dec 2024 06:26:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tNR2U-00FXk4-9Y for pgsql-hackers@arkaria.postgresql.org; Tue, 17 Dec 2024 06:26:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tNR2S-00FXiN-Qj for pgsql-hackers@lists.postgresql.org; Tue, 17 Dec 2024 06:26:39 +0000 Received: from fout-b6-smtp.messagingengine.com ([202.12.124.149]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tNR2O-0006Mx-FG for pgsql-hackers@lists.postgresql.org; Tue, 17 Dec 2024 06:26:37 +0000 Received: from phl-compute-06.internal (phl-compute-06.phl.internal [10.202.2.46]) by mailfout.stl.internal (Postfix) with ESMTP id 62545114008A; Tue, 17 Dec 2024 01:26:30 -0500 (EST) Received: from phl-mailfrontend-02 ([10.202.2.163]) by phl-compute-06.internal (MEProxy); Tue, 17 Dec 2024 01:26:30 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm1; t=1734416790; x=1734503190; bh=KE+2mBzCVR 3W2kQa/PkXJhCTQifsru0lGIes3GA4zKM=; b=KUSCgcrumd2tFHkx6L0n8zV87A jPR+E5M9cRPRnjiFidIizvw436Jdo/2J1F1ro/m2Y2i8RGrt14d1oLGMW6b09xjO rVg+Fu0K0B34Egxkp/U3Aw7+rA6ErHv5RRgZprTUOnTI1y7//gCep+He33tdbE+5 jQLrNDvvIrOXS0TtrjI119Lrd2CkkdZgejQwNTIWvnFX/40latt7sbbiVeEfVnmw o19Xe6ntJNnxdyE4eVnkd3QhgMll4doVlqA4lmLG3W5wmwwT3Qf/F+j2zPsH55rU sb43VdmQw2wU8EULOmKxkpYoXFXiHvGIbwuGKEj7coYnWAmdVH7cfNb80j2g== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t= 1734416790; x=1734503190; bh=KE+2mBzCVR3W2kQa/PkXJhCTQifsru0lGIe s3GA4zKM=; b=HlopYpFB/KtJTqJGWDrmZM24mAD3G68j073oEkP4fLp/X2HoiQN g9v86Yde4nUkwZvelbMTy2KxB8524vN7SO9hzNUMTF4dkWpxfQFYU91mBbeYY99z N0b0K9etUzV88ty9OBZMs5AREGUCaN/U/zcsNbqxbs5M5L3C/FGGUnIr6ib/nSWp cP/KXY76M3lV1AYAVU802+QBKc8yTH1DRTahCX1enXXh7IFeGvSzVXtnt5ncIb3Q 61XzdArLXWU1dAJEB9GcEzTP7bEnaOiOSvW+MLRa8C9JBaampvT/Ur8Ih93PUQyp ToRSyvY3Pi2gJtw7dQithTHK5KNKpJpBr5Q== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefuddrleeggdeliecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpggftfghnshhusghstghrihgsvgdpuffr tefokffrpgfnqfghnecuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnth hsucdlqddutddtmdenfghrlhcuvffnffculdejtddmnecujfgurhepfffhvfevuffkfhgg tggujgesghdtreertddtvdenucfhrhhomhepofhitghhrggvlhcurfgrqhhuihgvrhcuoe hmihgthhgrvghlsehprghquhhivghrrdighiiiqeenucggtffrrghtthgvrhhnpeetleei fedufffhhfdtteelgeeggeffhfekueevteeigfduudevudetgfegiedvjeenucevlhhush htvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpehmihgthhgrvghlsehp rghquhhivghrrdighiiipdhnsggprhgtphhtthhopeehpdhmohguvgepshhmthhpohhuth dprhgtphhtthhopegsvghrthhrrghnuggurhhouhhvohhtrdhpghesghhmrghilhdrtgho mhdprhgtphhtthhopegshigrvhhuiiekudesghhmrghilhdrtghomhdprhgtphhtthhope grlhhvhhgvrhhrvgesrghlvhhhrdhnohdqihhprdhorhhgpdhrtghpthhtohephhhorhhi khihohhtrgdrnhhtthesghhmrghilhdrtghomhdprhgtphhtthhopehpghhsqhhlqdhhrg gtkhgvrhhssehlihhsthhsrdhpohhsthhgrhgvshhqlhdrohhrgh X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 17 Dec 2024 01:26:27 -0500 (EST) Date: Tue, 17 Dec 2024 15:26:02 +0900 From: Michael Paquier To: Bertrand Drouvot Cc: Nazir Bilal Yavuz , Alvaro Herrera , Kyotaro Horiguchi , pgsql-hackers@lists.postgresql.org Subject: Re: per backend I/O statistics Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="SXQ421brA7jDqgb4" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --SXQ421brA7jDqgb4 Content-Type: multipart/mixed; boundary="MeJkKYPZvUKIEAEu" Content-Disposition: inline --MeJkKYPZvUKIEAEu Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Mon, Dec 16, 2024 at 03:42:04PM +0000, Bertrand Drouvot wrote: > It's not necessary per say, but it ensures that pg_stat_get_backend_io() = does not > return rows full of "invalid" combinations. Okay. I am not planning to fight more over this point. > I'm not sure that would be possible for a PGSTAT_KIND_BACKEND kind to ret= urn > false here but I agree that's better to call pgstat_request_entry_refs_gc= () > if that's the case. Done in v9 attached. It seems to me that it could be possible if a different backend keeps a reference to this entry. >> Perhaps there's an argument for an entirely separate >> callback that would run before pgstat is plugged off, like a new >> before_shmem_exit() callback registered after the entry is created? >=20 > As the ProcKill() is run in shmem_exit() (and so after before_shmem_exit(= )), > I think that the way we currently drop the backend stats entry is fine (u= nless > I miss something about your concern). Looking closely at this one, I think that you're right as long as the shutdown callback is registered before the entry is created. So I'm OK because the drop is a no-op if the entry cannot be found, and we would not try a pgstat_request_entry_refs_gc(). Anyway, I have put my hands on v9. A couple of notes, while hacking through it. See v9-0002 for the parts I have modified, that applies on top of your v9-0001. You may have noticed fee2b3ea2ecdg, which led me to fix two comments as these paths are not related to only database objects. Added more documentation, tweaked quite a bit the comments, a bit less the docs (no need for the two linkends) and applied an indentation. pg_stat_get_backend_io() can be simpler, and does not need the loop with the extra LocalPgBackendStatus. It is possible to call once pgstat_get_beentry_by_proc_number() and retrieve the PID and the bktype for the two sanity checks we want to do on them. s/pgstat_fetch_proc_stat_io/pgstat_fetch_stat_backend/. s/pgstat_create_backend_stat/pgstat_create_backend/. I've been wondering for quite a bit about PgStat_BackendPendingIO and PgStat_PendingIO, and concluded to define both in pgstat.h, with the former being defined based on the latter to keep the dependency between both at the same place. -- Michael --MeJkKYPZvUKIEAEu Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v9-0001-per-backend-I-O-statistics.patch" Content-Transfer-Encoding: quoted-printable =46rom d0df4be6cd052fc91970ec99933268f9b55940b7 Mon Sep 17 00:00:00 2001 =46rom: Bertrand Drouvot Date: Mon, 28 Oct 2024 12:50:32 +0000 Subject: [PATCH v9 1/2] per backend I/O statistics While pg_stat_io provides cluster-wide I/O statistics, this commit adds the ability to track and display per backend I/O statistics. It adds a new statistics kind and 2 new functions: - pg_stat_reset_backend_stats() to be able to reset the stats for a given backend pid. - pg_stat_get_backend_io() to retrieve I/O statistics for a given backend p= id. The new KIND is named PGSTAT_KIND_BACKEND as it could be used in the future to store other statistics (than the I/O ones) per backend. The new KIND is a variable-numbered one and has an automatic cap on the maximum number of entries (as its hash key contains the proc number). There is no need to write the per backend I/O stats to disk (no point to see stats for backends that do not exist anymore after a re-start), so using "write_to_file =3D false". Note that per backend I/O statistics are not collected for the checkpointer, the background writer, the startup process and the autovacuum launcher as t= hose are already visible in pg_stat_io and there is only one of those. XXX: Bump catalog version needs to be done. --- src/include/catalog/pg_proc.dat | 14 ++ src/include/pgstat.h | 31 +++- src/include/utils/pgstat_internal.h | 14 ++ src/backend/catalog/system_functions.sql | 2 + src/backend/utils/activity/Makefile | 1 + src/backend/utils/activity/backend_status.c | 4 + src/backend/utils/activity/meson.build | 1 + src/backend/utils/activity/pgstat.c | 21 +++ src/backend/utils/activity/pgstat_backend.c | 182 +++++++++++++++++++ src/backend/utils/activity/pgstat_io.c | 25 ++- src/backend/utils/activity/pgstat_relation.c | 2 + src/backend/utils/adt/pgstatfuncs.c | 169 +++++++++++++++++ src/test/regress/expected/stats.out | 72 +++++++- src/test/regress/sql/stats.sql | 38 +++- doc/src/sgml/config.sgml | 8 +- doc/src/sgml/monitoring.sgml | 37 ++++ src/tools/pgindent/typedefs.list | 3 + 17 files changed, 604 insertions(+), 20 deletions(-) create mode 100644 src/backend/utils/activity/pgstat_backend.c diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.= dat index 0f22c21723..437157ffa3 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5913,6 +5913,15 @@ proargnames =3D> '{backend_type,object,context,reads,read_time,writes,wr= ite_time,writebacks,writeback_time,extends,extend_time,op_bytes,hits,evicti= ons,reuses,fsyncs,fsync_time,stats_reset}', prosrc =3D> 'pg_stat_get_io' }, =20 +{ oid =3D> '8806', descr =3D> 'statistics: backend IO statistics', + proname =3D> 'pg_stat_get_backend_io', prorows =3D> '5', proretset =3D> = 't', + provolatile =3D> 'v', proparallel =3D> 'r', prorettype =3D> 'record', + proargtypes =3D> 'int4', + proallargtypes =3D> '{int4,text,text,text,int8,float8,int8,float8,int8,f= loat8,int8,float8,int8,int8,int8,int8,int8,float8,timestamptz}', + proargmodes =3D> '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}', + proargnames =3D> '{backend_pid,backend_type,object,context,reads,read_ti= me,writes,write_time,writebacks,writeback_time,extends,extend_time,op_bytes= ,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}', + prosrc =3D> 'pg_stat_get_backend_io' }, + { oid =3D> '1136', descr =3D> 'statistics: information about WAL activity', proname =3D> 'pg_stat_get_wal', proisstrict =3D> 'f', provolatile =3D> '= s', proparallel =3D> 'r', prorettype =3D> 'record', proargtypes =3D> '', @@ -6052,6 +6061,11 @@ proname =3D> 'pg_stat_reset_single_function_counters', provolatile =3D> = 'v', prorettype =3D> 'void', proargtypes =3D> 'oid', prosrc =3D> 'pg_stat_reset_single_function_counters' }, +{ oid =3D> '9987', + descr =3D> 'statistics: reset statistics for a single backend', + proname =3D> 'pg_stat_reset_backend_stats', provolatile =3D> 'v', + prorettype =3D> 'void', proargtypes =3D> 'int4', + prosrc =3D> 'pg_stat_reset_backend_stats' }, { oid =3D> '2307', descr =3D> 'statistics: reset collected statistics for a single SLRU', proname =3D> 'pg_stat_reset_slru', proisstrict =3D> 'f', provolatile =3D= > 'v', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index ebfeef2f46..479773cfd2 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -49,14 +49,15 @@ #define PGSTAT_KIND_FUNCTION 3 /* per-function statistics */ #define PGSTAT_KIND_REPLSLOT 4 /* per-slot statistics */ #define PGSTAT_KIND_SUBSCRIPTION 5 /* per-subscription statistics */ +#define PGSTAT_KIND_BACKEND 6 /* per-backend statistics */ =20 /* stats for fixed-numbered objects */ -#define PGSTAT_KIND_ARCHIVER 6 -#define PGSTAT_KIND_BGWRITER 7 -#define PGSTAT_KIND_CHECKPOINTER 8 -#define PGSTAT_KIND_IO 9 -#define PGSTAT_KIND_SLRU 10 -#define PGSTAT_KIND_WAL 11 +#define PGSTAT_KIND_ARCHIVER 7 +#define PGSTAT_KIND_BGWRITER 8 +#define PGSTAT_KIND_CHECKPOINTER 9 +#define PGSTAT_KIND_IO 10 +#define PGSTAT_KIND_SLRU 11 +#define PGSTAT_KIND_WAL 12 =20 #define PGSTAT_KIND_BUILTIN_MIN PGSTAT_KIND_DATABASE #define PGSTAT_KIND_BUILTIN_MAX PGSTAT_KIND_WAL @@ -362,12 +363,23 @@ typedef struct PgStat_BktypeIO PgStat_Counter times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TY= PES]; } PgStat_BktypeIO; =20 +typedef struct PgStat_BackendPendingIO +{ + PgStat_Counter counts[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_T= YPES]; + instr_time pending_times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NU= M_TYPES]; +} PgStat_BackendPendingIO; + typedef struct PgStat_IO { TimestampTz stat_reset_timestamp; PgStat_BktypeIO stats[BACKEND_NUM_TYPES]; } PgStat_IO; =20 +typedef struct PgStat_Backend +{ + TimestampTz stat_reset_timestamp; + PgStat_BktypeIO stats; +} PgStat_Backend; =20 typedef struct PgStat_StatDBEntry { @@ -549,6 +561,13 @@ extern bool pgstat_have_entry(PgStat_Kind kind, Oid db= oid, uint64 objid); extern void pgstat_report_archiver(const char *xlog, bool failed); extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void); =20 +/* + * Functions in pgstat_backend.c + */ + +extern PgStat_Backend *pgstat_fetch_proc_stat_io(ProcNumber procNumber); +extern bool pgstat_tracks_backend_bktype(BackendType bktype); +extern void pgstat_create_backend_stat(ProcNumber procnum); =20 /* * Functions in pgstat_bgwriter.c diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat= _internal.h index 7338bc1e28..811ed9b005 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -450,6 +450,11 @@ typedef struct PgStatShared_ReplSlot PgStat_StatReplSlotEntry stats; } PgStatShared_ReplSlot; =20 +typedef struct PgStatShared_Backend +{ + PgStatShared_Common header; + PgStat_Backend stats; +} PgStatShared_Backend; =20 /* * Central shared memory entry for the cumulative stats system. @@ -604,6 +609,15 @@ extern void pgstat_archiver_init_shmem_cb(void *stats); extern void pgstat_archiver_reset_all_cb(TimestampTz ts); extern void pgstat_archiver_snapshot_cb(void); =20 +/* + * Functions in pgstat_backend.c + */ + +extern void pgstat_flush_backend(bool nowait); + +extern PgStat_BackendPendingIO *pgstat_prep_backend_pending(ProcNumber pro= cnum); +extern bool pgstat_backend_flush_cb(PgStat_EntryRef *entry_ref, bool nowai= t); +extern void pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header,= TimestampTz ts); =20 /* * Functions in pgstat_bgwriter.c diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog= /system_functions.sql index c51dfca802..14eb99cd47 100644 --- a/src/backend/catalog/system_functions.sql +++ b/src/backend/catalog/system_functions.sql @@ -711,6 +711,8 @@ REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_table_c= ounters(oid) FROM public; =20 REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_function_counters(oid) FRO= M public; =20 +REVOKE EXECUTE ON FUNCTION pg_stat_reset_backend_stats(integer) FROM publi= c; + REVOKE EXECUTE ON FUNCTION pg_stat_reset_replication_slot(text) FROM publi= c; =20 REVOKE EXECUTE ON FUNCTION pg_stat_have_stats(text, oid, int8) FROM public; diff --git a/src/backend/utils/activity/Makefile b/src/backend/utils/activi= ty/Makefile index b9fd66ea17..24b64a2742 100644 --- a/src/backend/utils/activity/Makefile +++ b/src/backend/utils/activity/Makefile @@ -20,6 +20,7 @@ OBJS =3D \ backend_status.o \ pgstat.o \ pgstat_archiver.o \ + pgstat_backend.o \ pgstat_bgwriter.o \ pgstat_checkpointer.o \ pgstat_database.o \ diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/util= s/activity/backend_status.c index 22c6dc378c..e9c3b87ac2 100644 --- a/src/backend/utils/activity/backend_status.c +++ b/src/backend/utils/activity/backend_status.c @@ -426,6 +426,10 @@ pgstat_bestart(void) =20 PGSTAT_END_WRITE_ACTIVITY(vbeentry); =20 + /* Create the backend statistics entry */ + if (pgstat_tracks_backend_bktype(MyBackendType)) + pgstat_create_backend_stat(MyProcNumber); + /* Update app name to current GUC setting */ if (application_name) pgstat_report_appname(application_name); diff --git a/src/backend/utils/activity/meson.build b/src/backend/utils/act= ivity/meson.build index f73c22905c..380d3dd70c 100644 --- a/src/backend/utils/activity/meson.build +++ b/src/backend/utils/activity/meson.build @@ -5,6 +5,7 @@ backend_sources +=3D files( 'backend_status.c', 'pgstat.c', 'pgstat_archiver.c', + 'pgstat_backend.c', 'pgstat_bgwriter.c', 'pgstat_checkpointer.c', 'pgstat_database.c', diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activi= ty/pgstat.c index b4e357c8a4..b72c779b2c 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -77,6 +77,7 @@ * * Each statistics kind is handled in a dedicated file: * - pgstat_archiver.c + * - pgstat_backend.c * - pgstat_bgwriter.c * - pgstat_checkpointer.c * - pgstat_database.c @@ -358,6 +359,22 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos= [PGSTAT_KIND_BUILTIN_SIZE] .reset_timestamp_cb =3D pgstat_subscription_reset_timestamp_cb, }, =20 + [PGSTAT_KIND_BACKEND] =3D { + .name =3D "backend", + + .fixed_amount =3D false, + .write_to_file =3D false, + + .accessed_across_databases =3D true, + + .shared_size =3D sizeof(PgStatShared_Backend), + .shared_data_off =3D offsetof(PgStatShared_Backend, stats), + .shared_data_len =3D sizeof(((PgStatShared_Backend *) 0)->stats), + .pending_size =3D sizeof(PgStat_BackendPendingIO), + + .flush_pending_cb =3D pgstat_backend_flush_cb, + .reset_timestamp_cb =3D pgstat_backend_reset_timestamp_cb, + }, =20 /* stats for fixed-numbered (mostly 1) objects */ =20 @@ -602,6 +619,10 @@ pgstat_shutdown_hook(int code, Datum arg) Assert(dlist_is_empty(&pgStatPending)); dlist_init(&pgStatPending); =20 + /* drop the backend stats entry */ + if (!pgstat_drop_entry(PGSTAT_KIND_BACKEND, InvalidOid, MyProcNumber)) + pgstat_request_entry_refs_gc(); + pgstat_detach_shmem(); =20 #ifdef USE_ASSERT_CHECKING diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/util= s/activity/pgstat_backend.c new file mode 100644 index 0000000000..e2d83024c2 --- /dev/null +++ b/src/backend/utils/activity/pgstat_backend.c @@ -0,0 +1,182 @@ +/* -----------------------------------------------------------------------= -- + * + * pgstat_backend.c + * Implementation of backend statistics. + * + * This file contains the implementation of backend statistics. It is kept + * separate from pgstat.c to enforce the line between the statistics acces= s / + * storage implementation and the details about individual types of statis= tics. + * + * Copyright (c) 2024, PostgreSQL Global Development Group + * + * IDENTIFICATION + * src/backend/utils/activity/pgstat_backend.c + * -----------------------------------------------------------------------= -- + */ + +#include "postgres.h" + +#include "utils/pgstat_internal.h" + +/* + * Returns backend's IO stats. + */ +PgStat_Backend * +pgstat_fetch_proc_stat_io(ProcNumber procNumber) +{ + PgStat_Backend *backend_entry; + + backend_entry =3D (PgStat_Backend *) pgstat_fetch_entry(PGSTAT_KIND_BACKE= ND, + InvalidOid, procNumber); + + return backend_entry; +} + +/* + * Flush out locally pending backend statistics + * + * If no stats have been recorded, this function returns false. + */ +bool +pgstat_backend_flush_cb(PgStat_EntryRef *entry_ref, bool nowait) +{ + PgStatShared_Backend *shbackendioent; + PgStat_BackendPendingIO *pendingent; + PgStat_BktypeIO *bktype_shstats; + + if (!pgstat_lock_entry(entry_ref, nowait)) + return false; + + shbackendioent =3D (PgStatShared_Backend *) entry_ref->shared_stats; + bktype_shstats =3D &shbackendioent->stats.stats; + pendingent =3D (PgStat_BackendPendingIO *) entry_ref->pending; + + for (int io_object =3D 0; io_object < IOOBJECT_NUM_TYPES; io_object++) + { + for (int io_context =3D 0; io_context < IOCONTEXT_NUM_TYPES; io_context+= +) + { + for (int io_op =3D 0; io_op < IOOP_NUM_TYPES; io_op++) + { + instr_time time; + + bktype_shstats->counts[io_object][io_context][io_op] +=3D + pendingent->counts[io_object][io_context][io_op]; + + time =3D pendingent->pending_times[io_object][io_context][io_op]; + + bktype_shstats->times[io_object][io_context][io_op] +=3D + INSTR_TIME_GET_MICROSEC(time); + } + } + } + + pgstat_unlock_entry(entry_ref); + + return true; +} + +/* + * Simpler wrapper of pgstat_backend_flush_cb() + */ +void +pgstat_flush_backend(bool nowait) +{ + if (pgstat_tracks_backend_bktype(MyBackendType)) + { + PgStat_EntryRef *entry_ref; + + entry_ref =3D 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. + */ +void +pgstat_create_backend_stat(ProcNumber procnum) +{ + PgStat_EntryRef *entry_ref; + PgStatShared_Backend *shstatent; + + entry_ref =3D pgstat_prep_pending_entry(PGSTAT_KIND_BACKEND, InvalidOid, + procnum, NULL); + + shstatent =3D (PgStatShared_Backend *) entry_ref->shared_stats; + + /* + * NB: need to accept that there might be stats from an older backend, + * e.g. if we previously used this proc number. + */ + memset(&shstatent->stats, 0, sizeof(shstatent->stats)); +} + +/* + * Find or create a local PgStat_BackendPendingIO entry for procnum. + */ +PgStat_BackendPendingIO * +pgstat_prep_backend_pending(ProcNumber procnum) +{ + PgStat_EntryRef *entry_ref; + + entry_ref =3D pgstat_prep_pending_entry(PGSTAT_KIND_BACKEND, InvalidOid, + procnum, NULL); + + return entry_ref->pending; +} + +/* + * Backend statistics are not collected for all BackendTypes. + * + * The following BackendTypes do not participate in the backend stats + * subsystem: + * - The same and for the same reasons as in pgstat_tracks_io_bktype(). + * - B_BG_WRITER, B_CHECKPOINTER, B_STARTUP and B_AUTOVAC_LAUNCHER because= their + * I/O stats are already visible in pg_stat_io and there is only one of th= ose. + * + * Function returns true if BackendType participates in the backend stats + * subsystem for IO and false if it does not. + * + * When adding a new BackendType, also consider adding relevant restrictio= ns to + * pgstat_tracks_io_object() and pgstat_tracks_io_op(). + */ +bool +pgstat_tracks_backend_bktype(BackendType bktype) +{ + /* + * List every type so that new backend types trigger a warning about + * needing to adjust this switch. + */ + switch (bktype) + { + case B_INVALID: + case B_AUTOVAC_LAUNCHER: + case B_DEAD_END_BACKEND: + case B_ARCHIVER: + case B_LOGGER: + case B_WAL_RECEIVER: + case B_WAL_WRITER: + case B_WAL_SUMMARIZER: + case B_BG_WRITER: + case B_CHECKPOINTER: + case B_STARTUP: + return false; + + case B_AUTOVAC_WORKER: + case B_BACKEND: + case B_BG_WORKER: + case B_STANDALONE_BACKEND: + case B_SLOTSYNC_WORKER: + case B_WAL_SENDER: + return true; + } + + return false; +} + +void +pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz= ts) +{ + ((PgStatShared_Backend *) header)->stats.stat_reset_timestamp =3D ts; +} diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/act= ivity/pgstat_io.c index f9883af2b3..e0c206a453 100644 --- a/src/backend/utils/activity/pgstat_io.c +++ b/src/backend/utils/activity/pgstat_io.c @@ -20,13 +20,7 @@ #include "storage/bufmgr.h" #include "utils/pgstat_internal.h" =20 - -typedef struct PgStat_PendingIO -{ - PgStat_Counter counts[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_T= YPES]; - instr_time pending_times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NU= M_TYPES]; -} PgStat_PendingIO; - +typedef PgStat_BackendPendingIO PgStat_PendingIO; =20 static PgStat_PendingIO PendingIOStats; static bool have_iostats =3D false; @@ -87,6 +81,14 @@ pgstat_count_io_op_n(IOObject io_object, IOContext io_co= ntext, IOOp io_op, uint3 Assert((unsigned int) io_op < IOOP_NUM_TYPES); Assert(pgstat_tracks_io_op(MyBackendType, io_object, io_context, io_op)); =20 + if (pgstat_tracks_backend_bktype(MyBackendType)) + { + PgStat_PendingIO *entry_ref; + + entry_ref =3D pgstat_prep_backend_pending(MyProcNumber); + entry_ref->counts[io_object][io_context][io_op] +=3D cnt; + } + PendingIOStats.counts[io_object][io_context][io_op] +=3D cnt; =20 have_iostats =3D true; @@ -148,6 +150,15 @@ pgstat_count_io_op_time(IOObject io_object, IOContext = io_context, IOOp io_op, =20 INSTR_TIME_ADD(PendingIOStats.pending_times[io_object][io_context][io_op= ], io_time); + + if (pgstat_tracks_backend_bktype(MyBackendType)) + { + PgStat_PendingIO *entry_ref; + + entry_ref =3D pgstat_prep_backend_pending(MyProcNumber); + INSTR_TIME_ADD(entry_ref->pending_times[io_object][io_context][io_op], + io_time); + } } =20 pgstat_count_io_op_n(io_object, io_context, io_op, cnt); diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/uti= ls/activity/pgstat_relation.c index faba8b64d2..85e65557bb 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -264,6 +264,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared, * VACUUM command has processed all tables and committed. */ pgstat_flush_io(false); + pgstat_flush_backend(false); } =20 /* @@ -350,6 +351,7 @@ pgstat_report_analyze(Relation rel, =20 /* see pgstat_report_vacuum() */ pgstat_flush_io(false); + pgstat_flush_backend(false); } =20 /* diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pg= statfuncs.c index cdf37403e9..b939551d36 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1474,6 +1474,154 @@ pg_stat_get_io(PG_FUNCTION_ARGS) return (Datum) 0; } =20 +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 =3D pgstat_fetch_stat_numbackends(); + int curr_backend; + int pid; + PGPROC *proc; + ProcNumber procNumber; + + InitMaterializedSRF(fcinfo, 0); + rsinfo =3D (ReturnSetInfo *) fcinfo->resultinfo; + + pid =3D PG_GETARG_INT32(0); + proc =3D 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 =3D GetNumberFromPGProc(proc); + backend_stats =3D pgstat_fetch_proc_stat_io(procNumber); + + if (!backend_stats) + return (Datum) 0; + + bktype =3D B_INVALID; + + /* Look for the backend type */ + for (curr_backend =3D 1; curr_backend <=3D num_backends; curr_backend++) + { + LocalPgBackendStatus *local_beentry; + PgBackendStatus *beentry; + + /* Get the next one in the list */ + local_beentry =3D pgstat_get_local_beentry_by_index(curr_backend); + beentry =3D &local_beentry->backendStatus; + + /* Looking for specific PID, ignore all the others */ + if (beentry->st_procpid !=3D pid) + continue; + + bktype =3D beentry->st_backendType; + break; + } + + /* Backend is gone */ + if (bktype =3D=3D B_INVALID) + return (Datum) 0; + + bktype_desc =3D CStringGetTextDatum(GetBackendTypeDesc(bktype)); + bktype_stats =3D &backend_stats->stats; + reset_time =3D 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)); + + for (int io_obj =3D 0; io_obj < IOOBJECT_NUM_TYPES; io_obj++) + { + const char *obj_name =3D pgstat_get_io_object_name(io_obj); + + for (int io_context =3D 0; io_context < IOCONTEXT_NUM_TYPES; io_context+= +) + { + const char *context_name =3D pgstat_get_io_context_name(io_context); + + Datum values[IO_NUM_COLUMNS] =3D {0}; + bool nulls[IO_NUM_COLUMNS] =3D {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] =3D bktype_desc; + values[IO_COL_CONTEXT] =3D CStringGetTextDatum(context_name); + values[IO_COL_OBJECT] =3D CStringGetTextDatum(obj_name); + if (backend_stats->stat_reset_timestamp !=3D 0) + values[IO_COL_RESET_TIME] =3D reset_time; + else + nulls[IO_COL_RESET_TIME] =3D true; + + /* + * 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] =3D Int64GetDatum(BLCKSZ); + + for (int io_op =3D 0; io_op < IOOP_NUM_TYPES; io_op++) + { + int op_idx =3D pgstat_get_io_op_index(io_op); + int time_idx =3D 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 =3D + bktype_stats->counts[io_obj][io_context][io_op]; + + values[op_idx] =3D Int64GetDatum(count); + } + else + nulls[op_idx] =3D true; + + /* not every operation is timed */ + if (time_idx =3D=3D IO_COL_INVALID) + continue; + + if (!nulls[op_idx]) + { + PgStat_Counter time =3D + bktype_stats->times[io_obj][io_context][io_op]; + + values[time_idx] =3D Float8GetDatum(pg_stat_us_to_ms(time)); + } + else + nulls[time_idx] =3D true; + } + + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, + values, nulls); + } + } + + return (Datum) 0; +} + /* * Returns statistics of WAL activity */ @@ -1779,6 +1927,27 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_A= RGS) PG_RETURN_VOID(); } =20 +Datum +pg_stat_reset_backend_stats(PG_FUNCTION_ARGS) +{ + PGPROC *proc; + int backend_pid =3D PG_GETARG_INT32(0); + + proc =3D 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(). + */ + if (!proc) + PG_RETURN_VOID(); + + pgstat_reset(PGSTAT_KIND_BACKEND, InvalidOid, GetNumberFromPGProc(proc)); + + PG_RETURN_VOID(); +} + /* Reset SLRU counters (a specific one or all of them). */ Datum pg_stat_reset_slru(PG_FUNCTION_ARGS) diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expecte= d/stats.out index 56771f83ed..3447e7b75d 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -1249,7 +1249,7 @@ SELECT pg_stat_get_subscription_stats(NULL); =20 (1 row) =20 --- Test that the following operations are tracked in pg_stat_io: +-- Test that the following operations are tracked in pg_stat_io and in bac= kend stats: -- - reads of target blocks into shared buffers -- - writes of shared buffers to permanent storage -- - extends of relations using shared buffers @@ -1259,11 +1259,19 @@ SELECT pg_stat_get_subscription_stats(NULL); -- be sure of the state of shared buffers at the point the test is run. -- Create a regular table and insert some data to generate IOCONTEXT_NORMAL -- extends. +SELECT pid AS checkpointer_pid FROM pg_stat_activity + WHERE backend_type =3D 'checkpointer' \gset SELECT sum(extends) AS io_sum_shared_before_extends FROM pg_stat_io WHERE context =3D 'normal' AND object =3D 'relation' \gs= et +SELECT sum(extends) AS my_io_sum_shared_before_extends + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context =3D 'normal' AND object =3D 'relation' \gset SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs FROM pg_stat_io WHERE object =3D 'relation' \gset io_sum_shared_before_ +SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE object =3D 'relation' \gset my_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(); @@ -1280,8 +1288,17 @@ SELECT :io_sum_shared_after_extends > :io_sum_shared= _before_extends; t (1 row) =20 +SELECT sum(extends) AS my_io_sum_shared_after_extends + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context =3D 'normal' AND object =3D 'relation' \gset +SELECT :my_io_sum_shared_after_extends > :my_io_sum_shared_before_extends; + ?column?=20 +---------- + t +(1 row) + -- After a checkpoint, there should be some additional IOCONTEXT_NORMAL wr= ites --- and fsyncs. +-- and fsyncs in the global stats (not for the backend). -- See comment above for rationale for two explicit CHECKPOINTs. CHECKPOINT; CHECKPOINT; @@ -1301,6 +1318,31 @@ SELECT current_setting('fsync') =3D 'off' t (1 row) =20 +SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE object =3D 'relation' \gset my_io_sum_shared_after_ +SELECT :my_io_sum_shared_after_writes >=3D :my_io_sum_shared_before_writes; + ?column?=20 +---------- + t +(1 row) + +SELECT current_setting('fsync') =3D 'off' + OR (:my_io_sum_shared_after_fsyncs =3D :my_io_sum_shared_before_fsyncs + AND :my_io_sum_shared_after_fsyncs=3D 0); + ?column?=20 +---------- + t +(1 row) + +-- Don't return any rows if querying other backend's stats that are exclud= ed +-- from the backend stats collection (like the checkpointer). +SELECT count(1) =3D 0 FROM pg_stat_get_backend_io(:checkpointer_pid); + ?column?=20 +---------- + t +(1 row) + -- Change the tablespace so that the table is rewritten directly, then SEL= ECT -- from it to cause it to be read back into shared buffers. SELECT sum(reads) AS io_sum_shared_before_reads @@ -1521,6 +1563,8 @@ SELECT pg_stat_have_stats('io', 0, 0); =20 SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(rea= ds) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_pre_reset FROM pg_stat_io \gset +SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(rea= ds) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_pre_reset + FROM pg_stat_get_backend_io(pg_backend_pid()) \gset SELECT pg_stat_reset_shared('io'); pg_stat_reset_shared=20 ---------------------- @@ -1535,6 +1579,30 @@ SELECT :io_stats_post_reset < :io_stats_pre_reset; t (1 row) =20 +SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(rea= ds) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_post_reset + FROM pg_stat_get_backend_io(pg_backend_pid()) \gset +-- pg_stat_reset_shared() did not reset backend IO stats +SELECT :my_io_stats_pre_reset <=3D :my_io_stats_post_reset; + ?column?=20 +---------- + t +(1 row) + +-- but pg_stat_reset_backend_stats() does +SELECT pg_stat_reset_backend_stats(pg_backend_pid()); + pg_stat_reset_backend_stats=20 +----------------------------- +=20 +(1 row) + +SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(rea= ds) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_post_backend= _reset + FROM pg_stat_get_backend_io(pg_backend_pid()) \gset +SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset; + ?column?=20 +---------- + t +(1 row) + -- 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 7147cc2f89..9c925005be 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -595,7 +595,7 @@ SELECT pg_stat_get_replication_slot(NULL); SELECT pg_stat_get_subscription_stats(NULL); =20 =20 --- Test that the following operations are tracked in pg_stat_io: +-- Test that the following operations are tracked in pg_stat_io and in bac= kend stats: -- - reads of target blocks into shared buffers -- - writes of shared buffers to permanent storage -- - extends of relations using shared buffers @@ -607,20 +607,32 @@ SELECT pg_stat_get_subscription_stats(NULL); =20 -- Create a regular table and insert some data to generate IOCONTEXT_NORMAL -- extends. +SELECT pid AS checkpointer_pid FROM pg_stat_activity + WHERE backend_type =3D 'checkpointer' \gset SELECT sum(extends) AS io_sum_shared_before_extends FROM pg_stat_io WHERE context =3D 'normal' AND object =3D 'relation' \gs= et +SELECT sum(extends) AS my_io_sum_shared_before_extends + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context =3D 'normal' AND object =3D 'relation' \gset SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs FROM pg_stat_io WHERE object =3D 'relation' \gset io_sum_shared_before_ +SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE object =3D 'relation' \gset my_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(); SELECT sum(extends) AS io_sum_shared_after_extends FROM pg_stat_io WHERE context =3D 'normal' AND object =3D 'relation' \gs= et SELECT :io_sum_shared_after_extends > :io_sum_shared_before_extends; +SELECT sum(extends) AS my_io_sum_shared_after_extends + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE context =3D 'normal' AND object =3D 'relation' \gset +SELECT :my_io_sum_shared_after_extends > :my_io_sum_shared_before_extends; =20 -- After a checkpoint, there should be some additional IOCONTEXT_NORMAL wr= ites --- and fsyncs. +-- and fsyncs in the global stats (not for the backend). -- See comment above for rationale for two explicit CHECKPOINTs. CHECKPOINT; CHECKPOINT; @@ -630,6 +642,17 @@ SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs SELECT :io_sum_shared_after_writes > :io_sum_shared_before_writes; SELECT current_setting('fsync') =3D 'off' OR :io_sum_shared_after_fsyncs > :io_sum_shared_before_fsyncs; +SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs + FROM pg_stat_get_backend_io(pg_backend_pid()) + WHERE object =3D 'relation' \gset my_io_sum_shared_after_ +SELECT :my_io_sum_shared_after_writes >=3D :my_io_sum_shared_before_writes; +SELECT current_setting('fsync') =3D 'off' + OR (:my_io_sum_shared_after_fsyncs =3D :my_io_sum_shared_before_fsyncs + AND :my_io_sum_shared_after_fsyncs=3D 0); + +-- Don't return any rows if querying other backend's stats that are exclud= ed +-- from the backend stats collection (like the checkpointer). +SELECT count(1) =3D 0 FROM pg_stat_get_backend_io(:checkpointer_pid); =20 -- Change the tablespace so that the table is rewritten directly, then SEL= ECT -- from it to cause it to be read back into shared buffers. @@ -762,10 +785,21 @@ SELECT :io_sum_bulkwrite_strategy_extends_after > :io= _sum_bulkwrite_strategy_ext SELECT pg_stat_have_stats('io', 0, 0); SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(rea= ds) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_pre_reset FROM pg_stat_io \gset +SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(rea= ds) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_pre_reset + FROM pg_stat_get_backend_io(pg_backend_pid()) \gset SELECT pg_stat_reset_shared('io'); SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(rea= ds) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_post_reset FROM pg_stat_io \gset SELECT :io_stats_post_reset < :io_stats_pre_reset; +SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(rea= ds) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_post_reset + FROM pg_stat_get_backend_io(pg_backend_pid()) \gset +-- pg_stat_reset_shared() did not reset backend IO stats +SELECT :my_io_stats_pre_reset <=3D :my_io_stats_post_reset; +-- but pg_stat_reset_backend_stats() does +SELECT pg_stat_reset_backend_stats(pg_backend_pid()); +SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(rea= ds) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_post_backend= _reset + FROM pg_stat_get_backend_io(pg_backend_pid()) \gset +SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset; =20 =20 -- test BRIN index doesn't block HOT update diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 24bd504c21..fbdd6ce574 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8403,9 +8403,11 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' W= ITH csv; displayed in pg_stat_database, - pg_stat_io, in the output of - when the BUFFERS option - is used, in the output of when + pg_stat_io, in the output of the + + pg_stat_get_backend_io() function, in = the + output of when the BUFFER= S + option is used, in the output of wh= en the VERBOSE option is used, by autovacuum for auto-vacuums and auto-analyzes, when is set and by diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 840d7f8161..4a9464da61 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -4790,6 +4790,25 @@ description | Waiting for a newly initialized WAL fi= le to reach durable storage =20 + + + + 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. The function does not return I/O statistics for the checkpoi= nter, + the background writer, the startup process and the autovacuum laun= cher + as they are already visible in the pg_stat_io + view and there is only one of those. + + + @@ -4971,6 +4990,24 @@ description | Waiting for a newly initialized WAL fi= le to reach durable storage =20 + + + + pg_stat_reset_backend_stats + + pg_stat_reset_backend_stats ( integer ) + void + + + Resets statistics for a single backend with the specified process = ID + to zero. + + + This function is restricted to superusers by default, but other us= ers + can be granted EXECUTE to run the function. + + + diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs= =2Elist index ce33e55bf1..398dd92527 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2121,6 +2121,7 @@ PgFdwSamplingMethod PgFdwScanState PgIfAddrCallback PgStatShared_Archiver +PgStatShared_Backend PgStatShared_BgWriter PgStatShared_Checkpointer PgStatShared_Common @@ -2136,6 +2137,8 @@ PgStatShared_SLRU PgStatShared_Subscription PgStatShared_Wal PgStat_ArchiverStats +PgStat_Backend +PgStat_BackendPendingIO PgStat_BackendSubEntry PgStat_BgWriterStats PgStat_BktypeIO --=20 2.45.2 --MeJkKYPZvUKIEAEu Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v9-0002-Tweaks-on-top-of-v9.patch" Content-Transfer-Encoding: quoted-printable =46rom 8c7fd33e98ec958308979dba9cb7897787ddc48e Mon Sep 17 00:00:00 2001 =46rom: Michael Paquier Date: Tue, 17 Dec 2024 15:15:23 +0900 Subject: [PATCH v9 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 | 53 +++++++++------------ src/test/regress/expected/stats.out | 28 +++++++---- src/test/regress/sql/stats.sql | 12 +++-- doc/src/sgml/monitoring.sgml | 10 ++-- 9 files changed, 86 insertions(+), 73 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 =3D> 'pg_stat_reset_single_function_counters', provolatile =3D> = 'v', prorettype =3D> 'void', proargtypes =3D> 'oid', prosrc =3D> 'pg_stat_reset_single_function_counters' }, -{ oid =3D> '9987', - descr =3D> 'statistics: reset statistics for a single backend', +{ oid =3D> '8807', descr =3D> 'statistics: reset statistics for a single b= ackend', proname =3D> 'pg_stat_reset_backend_stats', provolatile =3D> 'v', prorettype =3D> 'void', proargtypes =3D> 'int4', prosrc =3D> '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_TY= PES]; } PgStat_BktypeIO; =20 -typedef struct PgStat_BackendPendingIO +typedef struct PgStat_PendingIO { PgStat_Counter counts[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_T= YPES]; instr_time pending_times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NU= M_TYPES]; -} PgStat_BackendPendingIO; +} PgStat_PendingIO; =20 typedef struct PgStat_IO { @@ -375,6 +375,9 @@ typedef struct PgStat_IO PgStat_BktypeIO stats[BACKEND_NUM_TYPES]; } PgStat_IO; =20 +/* 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 */ =20 -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); =20 /* * Functions in pgstat_bgwriter.c diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/util= s/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) =20 /* Create the backend statistics entry */ if (pgstat_tracks_backend_bktype(MyBackendType)) - pgstat_create_backend_stat(MyProcNumber); + pgstat_create_backend(MyProcNumber); =20 /* Update app name to current GUC setting */ if (application_name) diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/util= s/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 acces= s / - * storage implementation and the details about individual types of statis= tics. + * 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 f= ile + * 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" =20 /* - * 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; =20 @@ -81,21 +87,21 @@ pgstat_backend_flush_cb(PgStat_EntryRef *entry_ref, boo= l nowait) void pgstat_flush_backend(bool nowait) { - if (pgstat_tracks_backend_bktype(MyBackendType)) - { - PgStat_EntryRef *entry_ref; + PgStat_EntryRef *entry_ref; =20 - entry_ref =3D 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 =3D pgstat_get_entry_ref(PGSTAT_KIND_BACKEND, InvalidOid, + MyProcNumber, false, NULL); + (void) pgstat_backend_flush_cb(entry_ref, nowait); } =20 /* - * 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) } =20 /* - * 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 th= ose. * * 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 restrictio= ns to * pgstat_tracks_io_object() and pgstat_tracks_io_op(). diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/act= ivity/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" =20 -typedef PgStat_BackendPendingIO PgStat_PendingIO; - static PgStat_PendingIO PendingIOStats; static bool have_iostats =3D false; =20 diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pg= statfuncs.c index b939551d36..640e687604 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1474,20 +1474,22 @@ pg_stat_get_io(PG_FUNCTION_ARGS) return (Datum) 0; } =20 +/* + * Returns I/O statistics for a backend with given PID. + */ 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 =3D pgstat_fetch_stat_numbackends(); - int curr_backend; int pid; PGPROC *proc; ProcNumber procNumber; + PgStat_Backend *backend_stats; + PgStat_BktypeIO *bktype_stats; + PgBackendStatus *beentry; =20 InitMaterializedSRF(fcinfo, 0); rsinfo =3D (ReturnSetInfo *) fcinfo->resultinfo; @@ -1496,40 +1498,28 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS) proc =3D BackendPidGetProc(pid); =20 /* - * 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) return (Datum) 0; =20 procNumber =3D GetNumberFromPGProc(proc); - backend_stats =3D pgstat_fetch_proc_stat_io(procNumber); + + backend_stats =3D pgstat_fetch_stat_backend(procNumber); =20 if (!backend_stats) return (Datum) 0; =20 - bktype =3D B_INVALID; + beentry =3D pgstat_get_beentry_by_proc_number(procNumber); + bktype =3D beentry->st_backendType; =20 - /* Look for the backend type */ - for (curr_backend =3D 1; curr_backend <=3D num_backends; curr_backend++) - { - LocalPgBackendStatus *local_beentry; - PgBackendStatus *beentry; + /* If PID does not match, leave */ + if (beentry->st_procpid !=3D pid) + return (Datum) 0; =20 - /* Get the next one in the list */ - local_beentry =3D pgstat_get_local_beentry_by_index(curr_backend); - beentry =3D &local_beentry->backendStatus; - - /* Looking for specific PID, ignore all the others */ - if (beentry->st_procpid !=3D pid) - continue; - - bktype =3D beentry->st_backendType; - break; - } - - /* Backend is gone */ + /* Backend may be gone, so recheck in case */ if (bktype =3D=3D B_INVALID) return (Datum) 0; =20 @@ -1927,6 +1917,9 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_AR= GS) PG_RETURN_VOID(); } =20 +/* + * Reset statistics of backend with given PID. + */ Datum pg_stat_reset_backend_stats(PG_FUNCTION_ARGS) { @@ -1936,9 +1929,9 @@ pg_stat_reset_backend_stats(PG_FUNCTION_ARGS) proc =3D BackendPidGetProc(backend_pid); =20 /* - * 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/expecte= d/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); =20 (1 row) =20 --- Test that the following operations are tracked in pg_stat_io and in bac= kend 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') =3D 'off' t (1 row) =20 --- Don't return any rows if querying other backend's stats that are exclud= ed --- from the backend stats collection (like the checkpointer). -SELECT count(1) =3D 0 FROM pg_stat_get_backend_io(:checkpointer_pid); - ?column?=20 ----------- - t -(1 row) - -- Change the tablespace so that the table is rewritten directly, then SEL= ECT -- 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_ba= ckend_reset; t (1 row) =20 +-- Check invalid input for pg_stat_get_backend_io() +SELECT pg_stat_get_backend_io(NULL); + pg_stat_get_backend_io=20 +------------------------ +(0 rows) + +SELECT pg_stat_get_backend_io(0); + pg_stat_get_backend_io=20 +------------------------ +(0 rows) + +-- Auxiliary processes return no data. +SELECT pg_stat_get_backend_io(:checkpointer_pid); + pg_stat_get_backend_io=20 +------------------------ +(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); =20 =20 --- Test that the following operations are tracked in pg_stat_io and in bac= kend 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') =3D 'off' OR (:my_io_sum_shared_after_fsyncs =3D :my_io_sum_shared_before_fsyncs AND :my_io_sum_shared_after_fsyncs=3D 0); =20 --- Don't return any rows if querying other backend's stats that are exclud= ed --- from the backend stats collection (like the checkpointer). -SELECT count(1) =3D 0 FROM pg_stat_get_backend_io(:checkpointer_pid); - -- Change the tablespace so that the table is rewritten directly, then SEL= ECT -- 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) + s= um(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; =20 +-- 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); =20 -- 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 f= ile 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 checkpoi= nter, + pg_stat_io view. + + + The function does not return I/O statistics for the checkpointer, the background writer, the startup process and the autovacuum laun= cher - 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. =20 --=20 2.45.2 --MeJkKYPZvUKIEAEu-- --SXQ421brA7jDqgb4 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmdhGXoACgkQnvQgOdby QH2Bwg/+NZ4SkkoyTCvjuBRT/pCzEaKNGYRcOPGDVKs8XsIM553zvY0eXwZhFGHq b4M9K52R85PaT0FOUiaaI8W71nN5HuE3ppBnroUWjcMrA81ZBBKAsoPq1NFWLI4U XfnghlyFpNd3lDxqKQ8SGgX3N0jzJsmiJ8LLfN59ZLbR10eycNy+ZUmNOD+SudVU OkfKkP5mX50MMVqGZ5Os6Yp5lJdncbhDlxhcFfJGTm9cwfFFpqRsqjs//8pv3L97 qs05YqiIRf9ZDT2QINBPzVu4iq+ELTp84WoygX7fme4hWUHh4AVYwrPcPUMWuihE V2RZDm1sIzOUDVmPx4swK59s5xQMxmpsZG4MUiSVnww/yV4zpYJPZKBKP8+d3tIU /FFncRs1cLjR/DY/mQkUOFMQSpohpg3lLyGfVRnmWbdpuKcKJcM1XnSt7R/oatd+ VVFSUT6gnG5GaKJ7ypJSDBpyv9USbImzOhAonsTPHyq7JlIn4/VuM0gcXZJxXBo3 5JB4geBJw5sF3tiKwCguhZzmZthpK1DkWl20MqzG+Xoz8HfmupJL1BZn53t+KLu+ zJvZcxwF5wUG/jHEKrIvc8EUi47RZzgKNDgYe+jxqnvxeTY2a7KmTiPQA/aw0a/j 0mXPXznoAhWet3jVkPF1XXbPzc6A3qbYJHy0k9N5v7gotMoaz7M= =aJws -----END PGP SIGNATURE----- --SXQ421brA7jDqgb4--