From: Bertrand Drouvot Date: Thu, 22 Aug 2024 15:16:50 +0000 Subject: [PATCH v3 1/2] per backend I/O statistics While pg_stat_io provides cluster-wide I/O statistics, this commit adds a new pg_my_stat_io view to display "my" backend I/O statistics. The KIND_IO stats are still "fixed amount" ones as the maximum number of backend is fixed. The statistics snapshot is made for the global stats (the aggregated ones) and for my backend stats. The snapshot is not build in each backend to copy all the others backends stats, as 1/ there is no use case (there is no need to get others backends I/O statistics while taking care of the stats_fetch_consistency) and 2/ that could be memory expensive depending of the number of max connections. A subsequent commit will add a new pg_stat_get_backend_io() function to be able to retrieve the I/O statistics for a given backend pid (each execution re-fetches counters from shared memory because as stated above there is no snapshots being created in each backend to copy the other backends stats). Bump catalog version needs to be done. --- doc/src/sgml/config.sgml | 4 +- doc/src/sgml/monitoring.sgml | 28 ++++++ src/backend/catalog/system_views.sql | 22 +++++ src/backend/utils/activity/pgstat.c | 11 ++- src/backend/utils/activity/pgstat_io.c | 91 ++++++++++++++--- src/backend/utils/activity/pgstat_shmem.c | 4 +- src/backend/utils/adt/pgstatfuncs.c | 114 +++++++++++++++++++++- src/include/catalog/pg_proc.dat | 9 ++ src/include/pgstat.h | 14 ++- src/include/utils/pgstat_internal.h | 14 ++- src/test/regress/expected/rules.out | 19 ++++ src/test/regress/expected/stats.out | 44 ++++++++- src/test/regress/sql/stats.sql | 25 ++++- 13 files changed, 366 insertions(+), 33 deletions(-) 8.5% doc/src/sgml/ 29.4% src/backend/utils/activity/ 23.9% src/backend/utils/adt/ 4.4% src/include/catalog/ 3.1% src/include/utils/ 3.1% src/include/ 14.4% src/test/regress/expected/ 10.0% src/test/regress/sql/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 0aec11f443..2a59b97093 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8331,7 +8331,9 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; displayed in pg_stat_database, - pg_stat_io, in the output of + pg_stat_io, + + pg_my_stat_io, in the output of when the BUFFERS option is used, in the output of when the VERBOSE option is used, by autovacuum diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 933de6fe07..b28ca4e0ca 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -488,6 +488,16 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser + + pg_my_stat_iopg_my_stat_io + + One row for each combination of context and target object containing + my backend I/O statistics. + See + pg_my_stat_io for details. + + + pg_stat_replication_slotspg_stat_replication_slots One row per replication slot, showing statistics about the @@ -2948,6 +2958,24 @@ description | Waiting for a newly initialized WAL file to reach durable storage + + + + <structname>pg_my_stat_io</structname> + + + pg_my_stat_io + + + + The pg_my_stat_io view will contain one row for each + combination of target I/O object and I/O context, showing + my backend I/O statistics. Combinations which do not make sense are + omitted. The fields are exactly the same as the ones in the pg_stat_io + view. + + diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 7fd5d256a1..b4939b7bc6 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1168,6 +1168,28 @@ SELECT b.stats_reset FROM pg_stat_get_io() b; +CREATE VIEW pg_my_stat_io AS +SELECT + b.backend_type, + b.object, + b.context, + b.reads, + b.read_time, + b.writes, + b.write_time, + b.writebacks, + b.writeback_time, + b.extends, + b.extend_time, + b.op_bytes, + b.hits, + b.evictions, + b.reuses, + b.fsyncs, + b.fsync_time, + b.stats_reset +FROM pg_stat_get_my_io() b; + CREATE VIEW pg_stat_wal AS SELECT w.wal_records, diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index a7f2dfc744..deb30cd2fc 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -406,10 +406,12 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .fixed_amount = true, - .snapshot_ctl_off = offsetof(PgStat_Snapshot, io), + .init_backend_cb = pgstat_io_init_backend_cb, + .snapshot_ctl_off = offsetof(PgStat_Snapshot, global_io), .shared_ctl_off = offsetof(PgStat_ShmemControl, io), - .shared_data_off = offsetof(PgStatShared_IO, stats), - .shared_data_len = sizeof(((PgStatShared_IO *) 0)->stats), + /* [de-]serialize global_stats only */ + .shared_data_off = offsetof(PgStatShared_IO, global_stats), + .shared_data_len = sizeof(((PgStatShared_IO *) 0)->global_stats), .flush_fixed_cb = pgstat_io_flush_cb, .have_fixed_pending_cb = pgstat_io_have_pending_cb, @@ -587,6 +589,9 @@ pgstat_shutdown_hook(int code, Datum arg) pgstat_report_stat(true); + /* reset the pgstat_io counter related to this proc number */ + pgstat_reset_io_counter_internal(MyProcNumber, GetCurrentTimestamp()); + /* there shouldn't be any pending changes left */ Assert(dlist_is_empty(&pgStatPending)); dlist_init(&pgStatPending); diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c index cc2ffc78aa..611ca21720 100644 --- a/src/backend/utils/activity/pgstat_io.c +++ b/src/backend/utils/activity/pgstat_io.c @@ -31,6 +31,7 @@ typedef struct PgStat_PendingIO static PgStat_PendingIO PendingIOStats; static bool have_iostats = false; +void pgstat_reset_io_counter_internal(int index, TimestampTz ts); /* * Check that stats have not been counted for any combination of IOObject, @@ -154,11 +155,19 @@ pgstat_count_io_op_time(IOObject io_object, IOContext io_context, IOOp io_op, } PgStat_IO * -pgstat_fetch_stat_io(void) +pgstat_fetch_global_stat_io(void) { pgstat_snapshot_fixed(PGSTAT_KIND_IO); - return &pgStatLocal.snapshot.io; + return &pgStatLocal.snapshot.global_io; +} + +PgStat_Backend_IO * +pgstat_fetch_my_stat_io(void) +{ + pgstat_snapshot_fixed(PGSTAT_KIND_IO); + + return &pgStatLocal.snapshot.my_io; } /* @@ -192,13 +201,16 @@ pgstat_io_flush_cb(bool nowait) { LWLock *bktype_lock; PgStat_BktypeIO *bktype_shstats; + PgStat_BktypeIO *global_bktype_shstats; if (!have_iostats) return false; bktype_lock = &pgStatLocal.shmem->io.locks[MyBackendType]; bktype_shstats = - &pgStatLocal.shmem->io.stats.stats[MyBackendType]; + &pgStatLocal.shmem->io.stat[MyProcNumber].stats; + global_bktype_shstats = + &pgStatLocal.shmem->io.global_stats.stats[MyBackendType]; if (!nowait) LWLockAcquire(bktype_lock, LW_EXCLUSIVE); @@ -212,19 +224,28 @@ pgstat_io_flush_cb(bool nowait) for (int io_op = 0; io_op < IOOP_NUM_TYPES; io_op++) { instr_time time; + PgStat_Counter counter; + + counter = PendingIOStats.counts[io_object][io_context][io_op]; - bktype_shstats->counts[io_object][io_context][io_op] += - PendingIOStats.counts[io_object][io_context][io_op]; + bktype_shstats->counts[io_object][io_context][io_op] += counter; + + global_bktype_shstats->counts[io_object][io_context][io_op] += + counter; time = PendingIOStats.pending_times[io_object][io_context][io_op]; bktype_shstats->times[io_object][io_context][io_op] += INSTR_TIME_GET_MICROSEC(time); + + global_bktype_shstats->times[io_object][io_context][io_op] += + INSTR_TIME_GET_MICROSEC(time); } } } Assert(pgstat_bktype_io_stats_valid(bktype_shstats, MyBackendType)); + Assert(pgstat_bktype_io_stats_valid(global_bktype_shstats, MyBackendType)); LWLockRelease(bktype_lock); @@ -278,13 +299,35 @@ pgstat_io_init_shmem_cb(void *stats) LWLockInitialize(&stat_shmem->locks[i], LWTRANCHE_PGSTATS_DATA); } +void +pgstat_reset_io_counter_internal(int index, TimestampTz ts) +{ + LWLock *bktype_lock = &pgStatLocal.shmem->io.locks[0]; + PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.stat[index].stats; + + + /* + * Use the lock in the first BackendType's PgStat_BktypeIO to protect the + * reset timestamp as well. + */ + LWLockAcquire(bktype_lock, LW_EXCLUSIVE); + + pgStatLocal.shmem->io.stat[index].stat_reset_timestamp = ts; + + memset(bktype_shstats, 0, sizeof(*bktype_shstats)); + LWLockRelease(bktype_lock); +} + void pgstat_io_reset_all_cb(TimestampTz ts) { + for (int i = 0; i < NumProcStatSlots; i++) + pgstat_reset_io_counter_internal(i, ts); + for (int i = 0; i < BACKEND_NUM_TYPES; i++) { LWLock *bktype_lock = &pgStatLocal.shmem->io.locks[i]; - PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.stats.stats[i]; + PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.global_stats.stats[i]; LWLockAcquire(bktype_lock, LW_EXCLUSIVE); @@ -293,7 +336,7 @@ pgstat_io_reset_all_cb(TimestampTz ts) * the reset timestamp as well. */ if (i == 0) - pgStatLocal.shmem->io.stats.stat_reset_timestamp = ts; + pgStatLocal.shmem->io.global_stats.stat_reset_timestamp = ts; memset(bktype_shstats, 0, sizeof(*bktype_shstats)); LWLockRelease(bktype_lock); @@ -303,11 +346,28 @@ pgstat_io_reset_all_cb(TimestampTz ts) void pgstat_io_snapshot_cb(void) { + /* First, our own stats */ + PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.stat[MyProcNumber].stats; + PgStat_BktypeIO *bktype_snap = &pgStatLocal.snapshot.my_io.stats; + LWLock *mybktype_lock = &pgStatLocal.shmem->io.locks[0]; + + LWLockAcquire(mybktype_lock, LW_SHARED); + + pgStatLocal.snapshot.my_io.stat_reset_timestamp = + pgStatLocal.shmem->io.stat[MyProcNumber].stat_reset_timestamp; + + pgStatLocal.snapshot.my_io.bktype = + pgStatLocal.shmem->io.stat[MyProcNumber].bktype; + + *bktype_snap = *bktype_shstats; + LWLockRelease(mybktype_lock); + + /* Now, the global stats */ for (int i = 0; i < BACKEND_NUM_TYPES; i++) { LWLock *bktype_lock = &pgStatLocal.shmem->io.locks[i]; - PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.stats.stats[i]; - PgStat_BktypeIO *bktype_snap = &pgStatLocal.snapshot.io.stats[i]; + PgStat_BktypeIO *bktype_global_shstats = &pgStatLocal.shmem->io.global_stats.stats[i]; + PgStat_BktypeIO *bktype_global_snap = &pgStatLocal.snapshot.global_io.stats[i]; LWLockAcquire(bktype_lock, LW_SHARED); @@ -316,11 +376,11 @@ pgstat_io_snapshot_cb(void) * the reset timestamp as well. */ if (i == 0) - pgStatLocal.snapshot.io.stat_reset_timestamp = - pgStatLocal.shmem->io.stats.stat_reset_timestamp; + pgStatLocal.snapshot.global_io.stat_reset_timestamp = + pgStatLocal.shmem->io.global_stats.stat_reset_timestamp; /* using struct assignment due to better type safety */ - *bktype_snap = *bktype_shstats; + *bktype_global_snap = *bktype_global_shstats; LWLockRelease(bktype_lock); } } @@ -503,3 +563,10 @@ pgstat_tracks_io_op(BackendType bktype, IOObject io_object, return true; } + +void +pgstat_io_init_backend_cb(void) +{ + /* Initialize our backend type in the IO statistics */ + pgStatLocal.shmem->io.stat[MyProcNumber].bktype = MyBackendType; +} diff --git a/src/backend/utils/activity/pgstat_shmem.c b/src/backend/utils/activity/pgstat_shmem.c index ec93bf6902..3cfcba9609 100644 --- a/src/backend/utils/activity/pgstat_shmem.c +++ b/src/backend/utils/activity/pgstat_shmem.c @@ -128,7 +128,7 @@ StatsShmemSize(void) { Size sz; - sz = MAXALIGN(sizeof(PgStat_ShmemControl)); + sz = MAXALIGN(sizeof(PgStat_ShmemControl) + mul_size(sizeof(PgStat_Backend_IO), NumProcStatSlots)); sz = add_size(sz, pgstat_dsa_init_size()); /* Add shared memory for all the custom fixed-numbered statistics */ @@ -172,7 +172,7 @@ StatsShmemInit(void) Assert(!found); /* the allocation of pgStatLocal.shmem itself */ - p += MAXALIGN(sizeof(PgStat_ShmemControl)); + p += MAXALIGN(sizeof(PgStat_ShmemControl) + mul_size(sizeof(PgStat_Backend_IO), NumProcStatSlots)); /* * Create a small dsa allocation in plain shared memory. This is diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 33c7b25560..4c00570396 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1363,14 +1363,17 @@ pg_stat_get_io(PG_FUNCTION_ARGS) InitMaterializedSRF(fcinfo, 0); rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; - backends_io_stats = pgstat_fetch_stat_io(); + backends_io_stats = pgstat_fetch_global_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]; + Datum bktype_desc; + PgStat_BktypeIO *bktype_stats; + + bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype)); + bktype_stats = &backends_io_stats->stats[bktype]; /* * In Assert builds, we can afford an extra loop through all of the @@ -1462,6 +1465,111 @@ pg_stat_get_io(PG_FUNCTION_ARGS) return (Datum) 0; } +Datum +pg_stat_get_my_io(PG_FUNCTION_ARGS) +{ + ReturnSetInfo *rsinfo; + PgStat_Backend_IO *backend_io_stats; + Datum bktype_desc; + PgStat_BktypeIO *bktype_stats; + BackendType bktype; + Datum reset_time; + + InitMaterializedSRF(fcinfo, 0); + rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + + backend_io_stats = pgstat_fetch_my_stat_io(); + + bktype = backend_io_stats->bktype; + bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype)); + bktype_stats = &backend_io_stats->stats; + reset_time = TimestampTzGetDatum(backend_io_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 = 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); + if (backend_io_stats->stat_reset_timestamp != 0) + values[IO_COL_RESET_TIME] = reset_time; + else + nulls[IO_COL_RESET_TIME] = 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] = 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; +} + /* * Returns statistics of WAL activity */ diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index ff5436acac..62658e9a00 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5831,6 +5831,15 @@ 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', + 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' }, + { oid => '1136', descr => 'statistics: information about WAL activity', proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's', proparallel => 'r', prorettype => 'record', proargtypes => '', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index be2c91168a..9d63b1a5b7 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -16,6 +16,7 @@ #include "portability/instr_time.h" #include "postmaster/pgarch.h" /* for MAX_XFN_CHARS */ #include "replication/conflict.h" +#include "storage/proc.h" #include "utils/backend_progress.h" /* for backward compatibility */ #include "utils/backend_status.h" /* for backward compatibility */ #include "utils/relcache.h" @@ -76,6 +77,8 @@ */ #define PGSTAT_KIND_EXPERIMENTAL 128 +#define NumProcStatSlots (MaxBackends + NUM_AUXILIARY_PROCS) + static inline bool pgstat_is_kind_builtin(PgStat_Kind kind) { @@ -351,6 +354,12 @@ typedef struct PgStat_IO PgStat_BktypeIO stats[BACKEND_NUM_TYPES]; } PgStat_IO; +typedef struct PgStat_Backend_IO +{ + TimestampTz stat_reset_timestamp; + BackendType bktype; + PgStat_BktypeIO stats; +} PgStat_Backend_IO; typedef struct PgStat_StatDBEntry { @@ -559,7 +568,8 @@ extern instr_time pgstat_prepare_io_time(bool track_io_guc); extern void pgstat_count_io_op_time(IOObject io_object, IOContext io_context, IOOp io_op, instr_time start_time, uint32 cnt); -extern PgStat_IO *pgstat_fetch_stat_io(void); +extern PgStat_IO *pgstat_fetch_global_stat_io(void); +extern PgStat_Backend_IO *pgstat_fetch_my_stat_io(void); extern const char *pgstat_get_io_context_name(IOContext io_context); extern const char *pgstat_get_io_object_name(IOObject io_object); @@ -568,7 +578,7 @@ extern bool pgstat_tracks_io_object(BackendType bktype, IOObject io_object, IOContext io_context); extern bool pgstat_tracks_io_op(BackendType bktype, IOObject io_object, IOContext io_context, IOOp io_op); - +extern void pgstat_reset_io_counter_internal(int index, TimestampTz ts); /* * Functions in pgstat_database.c diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index bba90e898d..ea4cc6a2c6 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -366,11 +366,12 @@ typedef struct PgStatShared_Checkpointer typedef struct PgStatShared_IO { /* - * locks[i] protects stats.stats[i]. locks[0] also protects - * stats.stat_reset_timestamp. + * locks[i] protects global_stats.stats[i]. locks[0] also protects + * global_stats.stat_reset_timestamp. */ LWLock locks[BACKEND_NUM_TYPES]; - PgStat_IO stats; + PgStat_IO global_stats; + PgStat_Backend_IO stat[FLEXIBLE_ARRAY_MEMBER]; } PgStatShared_IO; typedef struct PgStatShared_SLRU @@ -463,7 +464,6 @@ typedef struct PgStat_ShmemControl PgStatShared_Archiver archiver; PgStatShared_BgWriter bgwriter; PgStatShared_Checkpointer checkpointer; - PgStatShared_IO io; PgStatShared_SLRU slru; PgStatShared_Wal wal; @@ -473,6 +473,8 @@ typedef struct PgStat_ShmemControl */ void *custom_data[PGSTAT_KIND_CUSTOM_SIZE]; + /* has to be at the end due to FLEXIBLE_ARRAY_MEMBER */ + PgStatShared_IO io; } PgStat_ShmemControl; @@ -494,7 +496,8 @@ typedef struct PgStat_Snapshot PgStat_CheckpointerStats checkpointer; - PgStat_IO io; + PgStat_Backend_IO my_io; + PgStat_IO global_io; PgStat_SLRUStats slru[SLRU_NUM_ELEMENTS]; @@ -629,6 +632,7 @@ extern bool pgstat_io_flush_cb(bool nowait); extern void pgstat_io_init_shmem_cb(void *stats); extern void pgstat_io_reset_all_cb(TimestampTz ts); extern void pgstat_io_snapshot_cb(void); +extern void pgstat_io_init_backend_cb(void); /* diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index a1626f3fae..785298d7b0 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1398,6 +1398,25 @@ pg_matviews| SELECT n.nspname AS schemaname, LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'm'::"char"); +pg_my_stat_io| SELECT 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_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); 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 6e08898b18..c489e528e0 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); (1 row) --- Test that the following operations are tracked in pg_stat_io: +-- Test that the following operations are tracked in pg_[my]_stat_io: -- - reads of target blocks into shared buffers -- - writes of shared buffers to permanent storage -- - extends of relations using shared buffers @@ -1261,9 +1261,14 @@ SELECT pg_stat_get_subscription_stats(NULL); -- extends. 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(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_ 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 +1285,16 @@ SELECT :io_sum_shared_after_extends > :io_sum_shared_before_extends; t (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) + -- After a checkpoint, there should be some additional IOCONTEXT_NORMAL writes --- 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 +1314,23 @@ SELECT current_setting('fsync') = 'off' t (1 row) +SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs + FROM pg_my_stat_io + WHERE object = 'relation' \gset my_io_sum_shared_after_ +SELECT :my_io_sum_shared_after_writes >= :my_io_sum_shared_before_writes; + ?column? +---------- + t +(1 row) + +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); + ?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 @@ -1521,6 +1551,8 @@ SELECT pg_stat_have_stats('io', 0, 0); SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + 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(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_pre_reset + FROM pg_my_stat_io \gset SELECT pg_stat_reset_shared('io'); pg_stat_reset_shared ---------------------- @@ -1535,6 +1567,14 @@ SELECT :io_stats_post_reset < :io_stats_pre_reset; t (1 row) +SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_post_reset + FROM pg_my_stat_io \gset +SELECT :my_io_stats_post_reset < :my_io_stats_pre_reset; + ?column? +---------- + 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 d8ac0d06f4..c95cb71652 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); --- Test that the following operations are tracked in pg_stat_io: +-- Test that the following operations are tracked in pg_[my]_stat_io: -- - reads of target blocks into shared buffers -- - writes of shared buffers to permanent storage -- - extends of relations using shared buffers @@ -609,18 +609,26 @@ SELECT pg_stat_get_subscription_stats(NULL); -- extends. 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(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_ 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 = 'normal' AND object = 'relation' \gset 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; -- After a checkpoint, there should be some additional IOCONTEXT_NORMAL writes --- and fsyncs. +-- and fsyncs in the global stats (not for the backend). -- See comment above for rationale for two explicit CHECKPOINTs. CHECKPOINT; CHECKPOINT; @@ -630,7 +638,13 @@ SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs SELECT :io_sum_shared_after_writes > :io_sum_shared_before_writes; SELECT current_setting('fsync') = 'off' OR :io_sum_shared_after_fsyncs > :io_sum_shared_before_fsyncs; - +SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs + FROM pg_my_stat_io + WHERE object = 'relation' \gset my_io_sum_shared_after_ +SELECT :my_io_sum_shared_after_writes >= :my_io_sum_shared_before_writes; +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); -- 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 @@ -762,10 +776,15 @@ 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(reads) + 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(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_pre_reset + FROM pg_my_stat_io \gset SELECT pg_stat_reset_shared('io'); SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + 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(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_post_reset + FROM pg_my_stat_io \gset +SELECT :my_io_stats_post_reset < :my_io_stats_pre_reset; -- test BRIN index doesn't block HOT update -- 2.34.1 --zQrxP4xQumq+YTTN Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0002-Add-pg_stat_get_backend_io.patch"