From 0cf23ff24939406f28e021b706f36944c8c189a4 Mon Sep 17 00:00:00 2001 From: Nazir Bilal Yavuz Date: Thu, 23 Jan 2025 13:52:31 +0300 Subject: [PATCH v10 3/3] Fetch timing columns from pg_stat_io in the pg_stat_wal view In the prior commit WAL I/Os' timing stats are started to be tracked in the pg_stat_io view. So, to avoid double accounting and to have same timing values in both pg_stat_io and pg_stat_wal; fetch timing columns from pg_stat_io in the pg_stat_wal view. --- src/include/catalog/pg_proc.dat | 6 +++--- src/include/pgstat.h | 4 ---- src/backend/catalog/system_views.sql | 14 ++++++++++--- src/backend/utils/activity/pgstat_wal.c | 2 -- src/backend/utils/adt/pgstatfuncs.c | 14 +++---------- src/test/regress/expected/rules.out | 27 ++++++++++++++++--------- doc/src/sgml/monitoring.sgml | 10 ++++++--- 7 files changed, 41 insertions(+), 36 deletions(-) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 2aafdbc3e93..aaf2432cc17 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5928,9 +5928,9 @@ { oid => '1136', descr => 'statistics: information about WAL activity', proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's', proparallel => 'r', prorettype => 'record', proargtypes => '', - proallargtypes => '{int8,int8,numeric,int8,int8,int8,float8,float8,timestamptz}', - proargmodes => '{o,o,o,o,o,o,o,o,o}', - proargnames => '{wal_records,wal_fpi,wal_bytes,wal_buffers_full,wal_write,wal_sync,wal_write_time,wal_sync_time,stats_reset}', + proallargtypes => '{int8,int8,numeric,int8,int8,int8,timestamptz}', + proargmodes => '{o,o,o,o,o,o,o}', + proargnames => '{wal_records,wal_fpi,wal_bytes,wal_buffers_full,wal_write,wal_sync,stats_reset}', prosrc => 'pg_stat_get_wal' }, { oid => '6248', descr => 'statistics: information about WAL prefetching', proname => 'pg_stat_get_recovery_prefetch', prorows => '1', proretset => 't', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 0a456858c9c..faac508015e 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -477,8 +477,6 @@ typedef struct PgStat_WalStats PgStat_Counter wal_buffers_full; PgStat_Counter wal_write; PgStat_Counter wal_sync; - PgStat_Counter wal_write_time; - PgStat_Counter wal_sync_time; TimestampTz stat_reset_timestamp; } PgStat_WalStats; @@ -493,8 +491,6 @@ typedef struct PgStat_PendingWalStats PgStat_Counter wal_buffers_full; PgStat_Counter wal_write; PgStat_Counter wal_sync; - instr_time wal_write_time; - instr_time wal_sync_time; } PgStat_PendingWalStats; diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 46868bf7e89..9448e1ec355 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1180,6 +1180,13 @@ SELECT FROM pg_stat_get_io() b; CREATE VIEW pg_stat_wal AS + WITH pgsio_sum_write_fsync_time AS ( + SELECT + sum(write_time) as sum_write_time, + sum(fsync_time) as sum_fsync_time + FROM pg_stat_io + WHERE context = 'normal' and object = 'wal' + ) SELECT w.wal_records, w.wal_fpi, @@ -1187,10 +1194,11 @@ CREATE VIEW pg_stat_wal AS w.wal_buffers_full, w.wal_write, w.wal_sync, - w.wal_write_time, - w.wal_sync_time, + p.sum_write_time as write_time, + p.sum_fsync_time as fsync_time, w.stats_reset - FROM pg_stat_get_wal() w; + FROM pg_stat_get_wal() w + CROSS JOIN pgsio_sum_write_fsync_time p; CREATE VIEW pg_stat_progress_analyze AS SELECT diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c index 18fa6b2936a..8ee650cdc30 100644 --- a/src/backend/utils/activity/pgstat_wal.c +++ b/src/backend/utils/activity/pgstat_wal.c @@ -126,8 +126,6 @@ pgstat_wal_flush_cb(bool nowait) WALSTAT_ACC(wal_buffers_full, PendingWalStats); WALSTAT_ACC(wal_write, PendingWalStats); WALSTAT_ACC(wal_sync, PendingWalStats); - WALSTAT_ACC_INSTR_TIME(wal_write_time); - WALSTAT_ACC_INSTR_TIME(wal_sync_time); #undef WALSTAT_ACC_INSTR_TIME #undef WALSTAT_ACC diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 0f5e0a9778d..5de31979bc0 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1609,7 +1609,7 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS) Datum pg_stat_get_wal(PG_FUNCTION_ARGS) { -#define PG_STAT_GET_WAL_COLS 9 +#define PG_STAT_GET_WAL_COLS 7 TupleDesc tupdesc; Datum values[PG_STAT_GET_WAL_COLS] = {0}; bool nulls[PG_STAT_GET_WAL_COLS] = {0}; @@ -1630,11 +1630,7 @@ pg_stat_get_wal(PG_FUNCTION_ARGS) INT8OID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 6, "wal_sync", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 7, "wal_write_time", - FLOAT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 8, "wal_sync_time", - FLOAT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 9, "stats_reset", + TupleDescInitEntry(tupdesc, (AttrNumber) 7, "stats_reset", TIMESTAMPTZOID, -1, 0); BlessTupleDesc(tupdesc); @@ -1657,11 +1653,7 @@ pg_stat_get_wal(PG_FUNCTION_ARGS) values[4] = Int64GetDatum(wal_stats->wal_write); values[5] = Int64GetDatum(wal_stats->wal_sync); - /* Convert counters from microsec to millisec for display */ - values[6] = Float8GetDatum(((double) wal_stats->wal_write_time) / 1000.0); - values[7] = Float8GetDatum(((double) wal_stats->wal_sync_time) / 1000.0); - - values[8] = TimestampTzGetDatum(wal_stats->stat_reset_timestamp); + values[6] = TimestampTzGetDatum(wal_stats->stat_reset_timestamp); /* Returns the record as Datum */ PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls))); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 856a8349c50..a15e0096d9c 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2241,16 +2241,23 @@ pg_stat_user_tables| SELECT relid, autoanalyze_count FROM pg_stat_all_tables WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); -pg_stat_wal| SELECT wal_records, - wal_fpi, - wal_bytes, - wal_buffers_full, - wal_write, - wal_sync, - wal_write_time, - wal_sync_time, - stats_reset - FROM pg_stat_get_wal() w(wal_records, wal_fpi, wal_bytes, wal_buffers_full, wal_write, wal_sync, wal_write_time, wal_sync_time, stats_reset); +pg_stat_wal| WITH pgsio_sum_write_fsync_time AS ( + SELECT sum(pg_stat_io.write_time) AS sum_write_time, + sum(pg_stat_io.fsync_time) AS sum_fsync_time + FROM pg_stat_io + WHERE ((pg_stat_io.context = 'normal'::text) AND (pg_stat_io.object = 'wal'::text)) + ) + SELECT w.wal_records, + w.wal_fpi, + w.wal_bytes, + w.wal_buffers_full, + w.wal_write, + w.wal_sync, + p.sum_write_time AS write_time, + p.sum_fsync_time AS fsync_time, + w.stats_reset + FROM (pg_stat_get_wal() w(wal_records, wal_fpi, wal_bytes, wal_buffers_full, wal_write, wal_sync, stats_reset) + CROSS JOIN pgsio_sum_write_fsync_time p); pg_stat_wal_receiver| SELECT pid, status, receive_start_lsn, diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index c6f2c384512..516c4b96112 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -4965,7 +4965,9 @@ description | Waiting for a newly initialized WAL file to reach durable storage io: Reset all the counters shown in the - pg_stat_io view. + pg_stat_io view. This resets the timing + counter stored in the pg_stat_wal view + too. @@ -4982,8 +4984,10 @@ description | Waiting for a newly initialized WAL file to reach durable storage - wal: Reset all the counters shown in the - pg_stat_wal view. + wal: Reset all the counters except timings shown + in the pg_stat_wal view. The timing + counters can be reset by calling pg_stat_reset_shared set to + io. -- 2.47.2