agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v7 4/4] Change policy of XLog read-buffer allocation 9+ messages / 2 participants [nested] [flat]
* [PATCH v7 4/4] Change policy of XLog read-buffer allocation @ 2019-09-05 12:29 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Kyotaro Horiguchi @ 2019-09-05 12:29 UTC (permalink / raw) Page buffer in XLogReaderState was allocated by XLogReaderAllcoate but actually it'd be the responsibility to the callers of XLogReadRecord, which now actually reads in pages. This patch does that. --- src/backend/access/transam/twophase.c | 2 ++ src/backend/access/transam/xlog.c | 2 ++ src/backend/access/transam/xlogreader.c | 3 --- src/backend/replication/logical/logical.c | 2 ++ src/bin/pg_rewind/parsexlog.c | 6 ++++++ src/bin/pg_waldump/pg_waldump.c | 2 ++ 6 files changed, 14 insertions(+), 3 deletions(-) diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c index f5f6278880..6f5e6e5e56 100644 --- a/src/backend/access/transam/twophase.c +++ b/src/backend/access/transam/twophase.c @@ -1336,6 +1336,7 @@ XlogReadTwoPhaseData(XLogRecPtr lsn, char **buf, int *len) (errcode(ERRCODE_OUT_OF_MEMORY), errmsg("out of memory"), errdetail("Failed while allocating a WAL reading processor."))); + xlogreader->readBuf = palloc(XLOG_BLCKSZ); XLogBeginRead(xlogreader, lsn); while (XLogReadRecord(xlogreader, &record, &errormsg) == @@ -1366,6 +1367,7 @@ XlogReadTwoPhaseData(XLogRecPtr lsn, char **buf, int *len) *buf = palloc(sizeof(char) * XLogRecGetDataLen(xlogreader)); memcpy(*buf, XLogRecGetData(xlogreader), sizeof(char) * XLogRecGetDataLen(xlogreader)); + pfree(xlogreader->readBuf); XLogReaderFree(xlogreader); } diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index a3eded30ad..3f7f3e4d4f 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -6411,6 +6411,7 @@ StartupXLOG(void) (errcode(ERRCODE_OUT_OF_MEMORY), errmsg("out of memory"), errdetail("Failed while allocating a WAL reading processor."))); + xlogreader->readBuf = palloc(XLOG_BLCKSZ); xlogreader->system_identifier = ControlFile->system_identifier; /* @@ -7813,6 +7814,7 @@ StartupXLOG(void) close(readFile); readFile = -1; } + pfree(xlogreader->readBuf); XLogReaderFree(xlogreader); /* diff --git a/src/backend/access/transam/xlogreader.c b/src/backend/access/transam/xlogreader.c index ad85f50c77..6b9287d68a 100644 --- a/src/backend/access/transam/xlogreader.c +++ b/src/backend/access/transam/xlogreader.c @@ -106,7 +106,6 @@ XLogReaderAllocate(int wal_segment_size, const char *waldir) MCXT_ALLOC_NO_OOM); if (!state->errormsg_buf) { - pfree(state->readBuf); pfree(state); return NULL; } @@ -119,7 +118,6 @@ XLogReaderAllocate(int wal_segment_size, const char *waldir) if (!allocate_recordbuf(state, 0)) { pfree(state->errormsg_buf); - pfree(state->readBuf); pfree(state); return NULL; } @@ -143,7 +141,6 @@ XLogReaderFree(XLogReaderState *state) pfree(state->errormsg_buf); if (state->readRecordBuf) pfree(state->readRecordBuf); - pfree(state->readBuf); pfree(state); } diff --git a/src/backend/replication/logical/logical.c b/src/backend/replication/logical/logical.c index 7782e3ad2f..858c537558 100644 --- a/src/backend/replication/logical/logical.c +++ b/src/backend/replication/logical/logical.c @@ -174,6 +174,7 @@ StartupDecodingContext(List *output_plugin_options, ereport(ERROR, (errcode(ERRCODE_OUT_OF_MEMORY), errmsg("out of memory"))); + ctx->reader->readBuf = palloc(XLOG_BLCKSZ); ctx->read_page = read_page; ctx->reorder = ReorderBufferAllocate(); @@ -518,6 +519,7 @@ FreeDecodingContext(LogicalDecodingContext *ctx) ReorderBufferFree(ctx->reorder); FreeSnapshotBuilder(ctx->snapshot_builder); + pfree(ctx->reader->readBuf); XLogReaderFree(ctx->reader); MemoryContextDelete(ctx->context); } diff --git a/src/bin/pg_rewind/parsexlog.c b/src/bin/pg_rewind/parsexlog.c index 55337b65f1..48502151a1 100644 --- a/src/bin/pg_rewind/parsexlog.c +++ b/src/bin/pg_rewind/parsexlog.c @@ -58,6 +58,7 @@ extractPageMap(const char *datadir, XLogRecPtr startpoint, int tliIndex, xlogreader = XLogReaderAllocate(WalSegSz, datadir); if (xlogreader == NULL) pg_fatal("out of memory"); + xlogreader->readBuf = pg_malloc(XLOG_BLCKSZ); XLogBeginRead(xlogreader, startpoint); do @@ -86,6 +87,7 @@ extractPageMap(const char *datadir, XLogRecPtr startpoint, int tliIndex, } while (xlogreader->ReadRecPtr != endpoint); + pg_free(xlogreader->readBuf); XLogReaderFree(xlogreader); if (xlogreadfd != -1) { @@ -109,6 +111,7 @@ readOneRecord(const char *datadir, XLogRecPtr ptr, int tliIndex) xlogreader = XLogReaderAllocate(WalSegSz, datadir); if (xlogreader == NULL) pg_fatal("out of memory"); + xlogreader->readBuf = pg_malloc(XLOG_BLCKSZ); XLogBeginRead(xlogreader, ptr); while (XLogReadRecord(xlogreader, &record, &errormsg) == @@ -128,6 +131,7 @@ readOneRecord(const char *datadir, XLogRecPtr ptr, int tliIndex) } endptr = xlogreader->EndRecPtr; + pg_free(xlogreader->readBuf); XLogReaderFree(xlogreader); if (xlogreadfd != -1) { @@ -169,6 +173,7 @@ findLastCheckpoint(const char *datadir, XLogRecPtr forkptr, int tliIndex, xlogreader = XLogReaderAllocate(WalSegSz, datadir); if (xlogreader == NULL) pg_fatal("out of memory"); + xlogreader->readBuf = pg_malloc(XLOG_BLCKSZ); searchptr = forkptr; for (;;) @@ -218,6 +223,7 @@ findLastCheckpoint(const char *datadir, XLogRecPtr forkptr, int tliIndex, searchptr = record->xl_prev; } + pg_free(xlogreader->readBuf); XLogReaderFree(xlogreader); if (xlogreadfd != -1) { diff --git a/src/bin/pg_waldump/pg_waldump.c b/src/bin/pg_waldump/pg_waldump.c index 0cd0d132af..ac6740f21d 100644 --- a/src/bin/pg_waldump/pg_waldump.c +++ b/src/bin/pg_waldump/pg_waldump.c @@ -1033,6 +1033,7 @@ main(int argc, char **argv) if (!xlogreader_state) fatal_error("out of memory"); + xlogreader_state->readBuf = palloc(XLOG_BLCKSZ); /* first find a valid recptr to start from */ first_record = XLogFindNextRecord(xlogreader_state, private.startptr, @@ -1109,6 +1110,7 @@ main(int argc, char **argv) (uint32) xlogreader_state->ReadRecPtr, errormsg); + pfree(xlogreader_state->readBuf); XLogReaderFree(xlogreader_state); return EXIT_SUCCESS; -- 2.18.2 ----Next_Part(Tue_Mar_24_18_24_13_2020_275)---- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v8 3/5] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/utils/activity/pgstat.c | 16 ++++++++++ src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/pgstat.h | 6 ++-- src/include/utils/guc_hooks.h | 1 + .../test_custom_stats/t/001_custom_stats.pl | 6 ++-- 7 files changed, 66 insertions(+), 6 deletions(-) 51.8% doc/src/sgml/ 13.3% src/backend/utils/activity/ 13.6% src/backend/utils/misc/ 11.3% src/include/ 9.8% src/test/modules/test_custom_stats/t/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index faf0bdb62aa..03875b490b7 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8932,6 +8932,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which certain statistics, which can be updated while a + transaction is in progress, are made visible. These include WAL activity + and I/O operations. + Such statistics are refreshed at the specified interval and can be observed + during active transactions in monitoring views such as + <link linkend="monitoring-pg-stat-wal-view"><structname>pg_stat_wal</structname></link> + and + <link linkend="monitoring-pg-stat-io-view"><structname>pg_stat_io</structname></link>. + If the value is specified without a unit, milliseconds are assumed. + The default is 10 seconds (<literal>10s</literal>), which is generally + the smallest practical value for long-running transactions. + </para> + <note> + <para> + This parameter does not affect statistics that are only reported at + transaction end, such as the columns of <structname>pg_stat_all_tables</structname> + (for example, <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + and <structfield>n_tup_del</structfield>). These statistics are always + flushed at the end of a transaction. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index a4ff64dc5ce..dd85a27c52f 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -123,6 +123,8 @@ * ---------- */ +/* minimum interval non-forced stats flushes.*/ +#define PGSTAT_MIN_INTERVAL 1000 /* how long until to block flushing pending stats updates */ #define PGSTAT_MAX_INTERVAL 60000 /* when to call pgstat_report_stat() again, even when idle */ @@ -203,6 +205,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2164,6 +2167,19 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) + { + disable_timeout(ANYTIME_STATS_UPDATE_TIMEOUT, false); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); + } +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index 271c033952e..d2734caafea 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2801,6 +2801,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f938cc65a3a..8bd37a25b38 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -688,6 +688,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/include/pgstat.h b/src/include/pgstat.h index b340a680614..ef856dbf55b 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -35,9 +35,6 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" -/* Minimum interval non-forced stats flushes */ -#define PGSTAT_MIN_INTERVAL 1000 - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -548,7 +545,7 @@ extern void pgstat_force_next_flush(void); #define pgstat_schedule_anytime_update() \ do { \ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) \ - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); \ + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); \ } while (0) extern void pgstat_reset_counters(void); @@ -828,6 +825,7 @@ extern PgStat_WalStats *pgstat_fetch_stat_wal(void); extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index 9c90670d9b8..9b5d2a90387 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 7be1b281776..22e2a75dcb9 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -164,10 +164,11 @@ $node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); my $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select 'anytime:'||numcalls from test_custom_stats_fixed_report(); @@ -183,11 +184,12 @@ like($result, qr/^anytime:2/m, $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_var_anytime_update('entry2'); select test_custom_stats_var_anytime_update('entry2'); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select * from test_custom_stats_var_report('entry2'); -- 2.34.1 --OI5irqZsxWxBW9nT Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v8-0004-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v10 3/5] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/utils/activity/pgstat.c | 13 ++++++++ src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/backend/utils/misc/timeout.c | 6 ++++ src/include/pgstat.h | 6 ++-- src/include/utils/guc_hooks.h | 1 + src/include/utils/timeout.h | 1 + .../test_custom_stats/t/001_custom_stats.pl | 6 ++-- 9 files changed, 70 insertions(+), 6 deletions(-) 51.0% doc/src/sgml/ 10.6% src/backend/utils/activity/ 15.9% src/backend/utils/misc/ 3.6% src/include/utils/ 9.0% src/include/ 9.6% src/test/modules/test_custom_stats/t/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 20dbcaeb3ee..1eed71007a7 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8929,6 +8929,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which certain statistics, which can be updated while a + transaction is in progress, are made visible. These include WAL activity + and I/O operations. + Such statistics are refreshed at the specified interval and can be observed + during active transactions in monitoring views such as + <link linkend="monitoring-pg-stat-wal-view"><structname>pg_stat_wal</structname></link> + and + <link linkend="monitoring-pg-stat-io-view"><structname>pg_stat_io</structname></link>. + If the value is specified without a unit, milliseconds are assumed. + The default is 10 seconds (<literal>10s</literal>), which is generally + the smallest practical value for long-running transactions. + </para> + <note> + <para> + This parameter does not affect statistics that are only reported at + transaction end, such as the columns of <structname>pg_stat_all_tables</structname> + (for example, <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + and <structfield>n_tup_del</structfield>). These statistics are always + flushed at the end of a transaction. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index ddd331e2c81..fd6ab0db16f 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -124,6 +124,8 @@ * ---------- */ +/* minimum interval non-forced stats flushes.*/ +#define PGSTAT_MIN_INTERVAL 1000 /* how long until to block flushing pending stats updates */ #define PGSTAT_MAX_INTERVAL 60000 /* when to call pgstat_report_stat() again, even when idle */ @@ -204,6 +206,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2171,6 +2174,16 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_all_timeouts_initialized()) + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index 9507778415d..073e08c7892 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2801,6 +2801,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f938cc65a3a..8bd37a25b38 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -688,6 +688,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/backend/utils/misc/timeout.c b/src/backend/utils/misc/timeout.c index ddba5dc607c..85c4260d1db 100644 --- a/src/backend/utils/misc/timeout.c +++ b/src/backend/utils/misc/timeout.c @@ -828,3 +828,9 @@ get_timeout_finish_time(TimeoutId id) { return all_timeouts[id].fin_time; } + +bool +get_all_timeouts_initialized(void) +{ + return all_timeouts_initialized; +} diff --git a/src/include/pgstat.h b/src/include/pgstat.h index b011a315679..90237c70829 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -34,9 +34,6 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" -/* Minimum interval non-forced stats flushes */ -#define PGSTAT_MIN_INTERVAL 1000 - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -548,7 +545,7 @@ extern void pgstat_force_next_flush(void); do { \ if (IsUnderPostmaster && !pgstat_pending_anytime) \ { \ - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); \ + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); \ pgstat_pending_anytime = true; \ } \ } while (0) @@ -831,6 +828,7 @@ extern PGDLLIMPORT bool pgstat_pending_anytime; extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index 9c90670d9b8..9b5d2a90387 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, diff --git a/src/include/utils/timeout.h b/src/include/utils/timeout.h index 10723bb664c..fe7327de209 100644 --- a/src/include/utils/timeout.h +++ b/src/include/utils/timeout.h @@ -93,5 +93,6 @@ extern bool get_timeout_active(TimeoutId id); extern bool get_timeout_indicator(TimeoutId id, bool reset_indicator); extern TimestampTz get_timeout_start_time(TimeoutId id); extern TimestampTz get_timeout_finish_time(TimeoutId id); +extern bool get_all_timeouts_initialized(void); #endif /* TIMEOUT_H */ diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 7be1b281776..22e2a75dcb9 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -164,10 +164,11 @@ $node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); my $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select 'anytime:'||numcalls from test_custom_stats_fixed_report(); @@ -183,11 +184,12 @@ like($result, qr/^anytime:2/m, $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_var_anytime_update('entry2'); select test_custom_stats_var_anytime_update('entry2'); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select * from test_custom_stats_var_report('entry2'); -- 2.34.1 --NVvBxFuyV/R+1/8/ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v10-0004-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v4 2/4] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/storage/lmgr/proc.c | 2 +- src/backend/tcop/postgres.c | 2 +- src/backend/utils/activity/pgstat.c | 15 +++++++++ src/backend/utils/init/postinit.c | 2 +- src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/pgstat.h | 1 + src/include/utils/guc_hooks.h | 1 + 9 files changed, 63 insertions(+), 3 deletions(-) 55.5% doc/src/sgml/ 5.3% src/backend/storage/lmgr/ 12.6% src/backend/utils/activity/ 5.3% src/backend/utils/init/ 14.9% src/backend/utils/misc/ 3.9% src/include/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 5560b95ee60..3136816a933 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8834,6 +8834,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which non-transactional statistics are made visible + during running transactions. Non-transactional statistics include, for + example, WAL activity and I/O operations. + They become visible at that interval in monitoring views such as + <link linkend="monitoring-pg-stat-io-view"> <structname>pg_stat_io</structname></link> + and <link linkend="monitoring-pg-stat-wal-view"> <structname>pg_stat_wal</structname></link> + during running transactions. + If this value is specified without units, it is taken as milliseconds. + The default is 10 seconds (<literal>10s</literal>), which is probably + about the smallest value you would want in practice for long running + transactions. + </para> + <note> + <para> + This parameter does not affect transactional statistics such as + <structname>pg_stat_all_tables</structname> columns (like + <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + <structfield>n_tup_del</structfield>), which are always flushed at transaction + boundaries to maintain consistency. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c index 012705a2ee6..caa6eecca88 100644 --- a/src/backend/storage/lmgr/proc.c +++ b/src/backend/storage/lmgr/proc.c @@ -1669,7 +1669,7 @@ ProcSleep(LOCALLOCK *locallock) } while (myWaitStatus == PROC_WAIT_STATUS_WAITING); if (anytime_timeout_was_active) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); /* * Disable the timers, if they are still running. As in LockErrorCleanup, diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 132fae61423..c0e81cb13d0 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -3543,7 +3543,7 @@ ProcessInterrupts(void) /* Schedule next timeout */ enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, - PGSTAT_MIN_INTERVAL); + pgstat_flush_interval); } if (ProcSignalBarrierPending) diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index ab4d9088a9a..ca08dd49cd7 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -113,6 +113,7 @@ #include "utils/memutils.h" #include "utils/pgstat_internal.h" #include "utils/timestamp.h" +#include "utils/timeout.h" /* ---------- @@ -202,6 +203,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2165,6 +2167,19 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) + { + disable_timeout(ANYTIME_STATS_UPDATE_TIMEOUT, false); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); + } +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c index 6076f531c4a..c7c0d618671 100644 --- a/src/backend/utils/init/postinit.c +++ b/src/backend/utils/init/postinit.c @@ -768,7 +768,7 @@ InitPostgres(const char *in_dbname, Oid dboid, IdleStatsUpdateTimeoutHandler); RegisterTimeout(ANYTIME_STATS_UPDATE_TIMEOUT, AnytimeStatsUpdateTimeoutHandler); - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); } /* diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index f0260e6e412..3bb43362e51 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2782,6 +2782,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index c4f92fcdac8..6ce5a250170 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -669,6 +669,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 1651f16f966..e0f222695bf 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -816,6 +816,7 @@ extern PgStat_WalStats *pgstat_fetch_stat_wal(void); extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index b6ecb0e769f..3a2ae6c41cd 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, -- 2.34.1 --wR/mWGXukst4NraF Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v4-0003-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v7 3/5] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/utils/activity/pgstat.c | 16 ++++++++++ src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/pgstat.h | 6 ++-- src/include/utils/guc_hooks.h | 1 + .../test_custom_stats/t/001_custom_stats.pl | 6 ++-- 7 files changed, 66 insertions(+), 6 deletions(-) 51.8% doc/src/sgml/ 13.3% src/backend/utils/activity/ 13.6% src/backend/utils/misc/ 11.3% src/include/ 9.8% src/test/modules/test_custom_stats/t/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 6bc2690ce07..383bbe3a132 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8923,6 +8923,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which certain statistics, which can be updated while a + transaction is in progress, are made visible. These include WAL activity + and I/O operations. + Such statistics are refreshed at the specified interval and can be observed + during active transactions in monitoring views such as + <link linkend="monitoring-pg-stat-wal-view"><structname>pg_stat_wal</structname></link> + and + <link linkend="monitoring-pg-stat-io-view"><structname>pg_stat_io</structname></link>. + If the value is specified without a unit, milliseconds are assumed. + The default is 10 seconds (<literal>10s</literal>), which is generally + the smallest practical value for long-running transactions. + </para> + <note> + <para> + This parameter does not affect statistics that are only reported at + transaction end, such as the columns of <structname>pg_stat_all_tables</structname> + (for example, <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + and <structfield>n_tup_del</structfield>). These statistics are always + flushed at the end of a transaction. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index a4ff64dc5ce..dd85a27c52f 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -123,6 +123,8 @@ * ---------- */ +/* minimum interval non-forced stats flushes.*/ +#define PGSTAT_MIN_INTERVAL 1000 /* how long until to block flushing pending stats updates */ #define PGSTAT_MAX_INTERVAL 60000 /* when to call pgstat_report_stat() again, even when idle */ @@ -203,6 +205,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2164,6 +2167,19 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) + { + disable_timeout(ANYTIME_STATS_UPDATE_TIMEOUT, false); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); + } +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index 271c033952e..d2734caafea 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2801,6 +2801,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f938cc65a3a..8bd37a25b38 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -688,6 +688,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/include/pgstat.h b/src/include/pgstat.h index b340a680614..ef856dbf55b 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -35,9 +35,6 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" -/* Minimum interval non-forced stats flushes */ -#define PGSTAT_MIN_INTERVAL 1000 - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -548,7 +545,7 @@ extern void pgstat_force_next_flush(void); #define pgstat_schedule_anytime_update() \ do { \ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) \ - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); \ + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); \ } while (0) extern void pgstat_reset_counters(void); @@ -828,6 +825,7 @@ extern PgStat_WalStats *pgstat_fetch_stat_wal(void); extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index 9c90670d9b8..9b5d2a90387 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 7be1b281776..22e2a75dcb9 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -164,10 +164,11 @@ $node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); my $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select 'anytime:'||numcalls from test_custom_stats_fixed_report(); @@ -183,11 +184,12 @@ like($result, qr/^anytime:2/m, $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_var_anytime_update('entry2'); select test_custom_stats_var_anytime_update('entry2'); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select * from test_custom_stats_var_report('entry2'); -- 2.34.1 --C2xzVbxFmVrP7k6O Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v7-0004-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v9 3/5] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/utils/activity/pgstat.c | 13 ++++++++ src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/backend/utils/misc/timeout.c | 6 ++++ src/include/pgstat.h | 6 ++-- src/include/utils/guc_hooks.h | 1 + src/include/utils/timeout.h | 1 + .../test_custom_stats/t/001_custom_stats.pl | 6 ++-- 9 files changed, 70 insertions(+), 6 deletions(-) 51.0% doc/src/sgml/ 10.6% src/backend/utils/activity/ 15.9% src/backend/utils/misc/ 3.6% src/include/utils/ 9.0% src/include/ 9.6% src/test/modules/test_custom_stats/t/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index faf0bdb62aa..03875b490b7 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8932,6 +8932,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which certain statistics, which can be updated while a + transaction is in progress, are made visible. These include WAL activity + and I/O operations. + Such statistics are refreshed at the specified interval and can be observed + during active transactions in monitoring views such as + <link linkend="monitoring-pg-stat-wal-view"><structname>pg_stat_wal</structname></link> + and + <link linkend="monitoring-pg-stat-io-view"><structname>pg_stat_io</structname></link>. + If the value is specified without a unit, milliseconds are assumed. + The default is 10 seconds (<literal>10s</literal>), which is generally + the smallest practical value for long-running transactions. + </para> + <note> + <para> + This parameter does not affect statistics that are only reported at + transaction end, such as the columns of <structname>pg_stat_all_tables</structname> + (for example, <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + and <structfield>n_tup_del</structfield>). These statistics are always + flushed at the end of a transaction. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index 419dc512d9b..578c575dbfb 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -123,6 +123,8 @@ * ---------- */ +/* minimum interval non-forced stats flushes.*/ +#define PGSTAT_MIN_INTERVAL 1000 /* how long until to block flushing pending stats updates */ #define PGSTAT_MAX_INTERVAL 60000 /* when to call pgstat_report_stat() again, even when idle */ @@ -203,6 +205,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2170,6 +2173,16 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_all_timeouts_initialized()) + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index 271c033952e..d2734caafea 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2801,6 +2801,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f938cc65a3a..8bd37a25b38 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -688,6 +688,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/backend/utils/misc/timeout.c b/src/backend/utils/misc/timeout.c index ddba5dc607c..85c4260d1db 100644 --- a/src/backend/utils/misc/timeout.c +++ b/src/backend/utils/misc/timeout.c @@ -828,3 +828,9 @@ get_timeout_finish_time(TimeoutId id) { return all_timeouts[id].fin_time; } + +bool +get_all_timeouts_initialized(void) +{ + return all_timeouts_initialized; +} diff --git a/src/include/pgstat.h b/src/include/pgstat.h index f0f546d419a..7829c563316 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -35,9 +35,6 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" -/* Minimum interval non-forced stats flushes */ -#define PGSTAT_MIN_INTERVAL 1000 - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -549,7 +546,7 @@ extern void pgstat_force_next_flush(void); do { \ if (IsUnderPostmaster && !pgstat_pending_anytime) \ { \ - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); \ + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); \ pgstat_pending_anytime = true; \ } \ } while (0) @@ -833,6 +830,7 @@ extern PGDLLIMPORT bool pgstat_pending_anytime; extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index 9c90670d9b8..9b5d2a90387 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, diff --git a/src/include/utils/timeout.h b/src/include/utils/timeout.h index 10723bb664c..fe7327de209 100644 --- a/src/include/utils/timeout.h +++ b/src/include/utils/timeout.h @@ -93,5 +93,6 @@ extern bool get_timeout_active(TimeoutId id); extern bool get_timeout_indicator(TimeoutId id, bool reset_indicator); extern TimestampTz get_timeout_start_time(TimeoutId id); extern TimestampTz get_timeout_finish_time(TimeoutId id); +extern bool get_all_timeouts_initialized(void); #endif /* TIMEOUT_H */ diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 7be1b281776..22e2a75dcb9 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -164,10 +164,11 @@ $node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); my $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select 'anytime:'||numcalls from test_custom_stats_fixed_report(); @@ -183,11 +184,12 @@ like($result, qr/^anytime:2/m, $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; -- Accumulate stats select test_custom_stats_var_anytime_update('entry2'); select test_custom_stats_var_anytime_update('entry2'); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select * from test_custom_stats_var_report('entry2'); -- 2.34.1 --aq/6bi8L6WORnI+9 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v9-0004-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v11 3/5] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/utils/activity/pgstat.c | 13 ++++++++ src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/backend/utils/misc/timeout.c | 6 ++++ src/include/pgstat.h | 6 ++-- src/include/utils/guc_hooks.h | 1 + src/include/utils/timeout.h | 1 + .../test_custom_stats/t/001_custom_stats.pl | 6 ++-- 9 files changed, 70 insertions(+), 6 deletions(-) 51.0% doc/src/sgml/ 10.6% src/backend/utils/activity/ 15.9% src/backend/utils/misc/ 3.6% src/include/utils/ 9.0% src/include/ 9.6% src/test/modules/test_custom_stats/t/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 20dbcaeb3ee..1eed71007a7 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8929,6 +8929,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which certain statistics, which can be updated while a + transaction is in progress, are made visible. These include WAL activity + and I/O operations. + Such statistics are refreshed at the specified interval and can be observed + during active transactions in monitoring views such as + <link linkend="monitoring-pg-stat-wal-view"><structname>pg_stat_wal</structname></link> + and + <link linkend="monitoring-pg-stat-io-view"><structname>pg_stat_io</structname></link>. + If the value is specified without a unit, milliseconds are assumed. + The default is 10 seconds (<literal>10s</literal>), which is generally + the smallest practical value for long-running transactions. + </para> + <note> + <para> + This parameter does not affect statistics that are only reported at + transaction end, such as the columns of <structname>pg_stat_all_tables</structname> + (for example, <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + and <structfield>n_tup_del</structfield>). These statistics are always + flushed at the end of a transaction. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index ddd331e2c81..fd6ab0db16f 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -124,6 +124,8 @@ * ---------- */ +/* minimum interval non-forced stats flushes.*/ +#define PGSTAT_MIN_INTERVAL 1000 /* how long until to block flushing pending stats updates */ #define PGSTAT_MAX_INTERVAL 60000 /* when to call pgstat_report_stat() again, even when idle */ @@ -204,6 +206,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2171,6 +2174,16 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_all_timeouts_initialized()) + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index 9507778415d..073e08c7892 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2801,6 +2801,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f938cc65a3a..8bd37a25b38 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -688,6 +688,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/backend/utils/misc/timeout.c b/src/backend/utils/misc/timeout.c index ddba5dc607c..85c4260d1db 100644 --- a/src/backend/utils/misc/timeout.c +++ b/src/backend/utils/misc/timeout.c @@ -828,3 +828,9 @@ get_timeout_finish_time(TimeoutId id) { return all_timeouts[id].fin_time; } + +bool +get_all_timeouts_initialized(void) +{ + return all_timeouts_initialized; +} diff --git a/src/include/pgstat.h b/src/include/pgstat.h index b011a315679..90237c70829 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -34,9 +34,6 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" -/* Minimum interval non-forced stats flushes */ -#define PGSTAT_MIN_INTERVAL 1000 - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -548,7 +545,7 @@ extern void pgstat_force_next_flush(void); do { \ if (IsUnderPostmaster && !pgstat_pending_anytime) \ { \ - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); \ + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); \ pgstat_pending_anytime = true; \ } \ } while (0) @@ -831,6 +828,7 @@ extern PGDLLIMPORT bool pgstat_pending_anytime; extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index 9c90670d9b8..9b5d2a90387 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, diff --git a/src/include/utils/timeout.h b/src/include/utils/timeout.h index 10723bb664c..fe7327de209 100644 --- a/src/include/utils/timeout.h +++ b/src/include/utils/timeout.h @@ -93,5 +93,6 @@ extern bool get_timeout_active(TimeoutId id); extern bool get_timeout_indicator(TimeoutId id, bool reset_indicator); extern TimestampTz get_timeout_start_time(TimeoutId id); extern TimestampTz get_timeout_finish_time(TimeoutId id); +extern bool get_all_timeouts_initialized(void); #endif /* TIMEOUT_H */ diff --git a/src/test/modules/test_custom_stats/t/001_custom_stats.pl b/src/test/modules/test_custom_stats/t/001_custom_stats.pl index 6ba4022418f..920443487c0 100644 --- a/src/test/modules/test_custom_stats/t/001_custom_stats.pl +++ b/src/test/modules/test_custom_stats/t/001_custom_stats.pl @@ -164,11 +164,12 @@ $node->safe_psql('postgres', q(select test_custom_stats_fixed_reset())); $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); my $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; SET LOCAL stats_fetch_consistency = none; -- Accumulate stats select test_custom_stats_fixed_anytime_update() from generate_series(1, 2); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select 'fixed_anytime:'||numcalls from test_custom_stats_fixed_report(); @@ -184,12 +185,13 @@ like($result, qr/^fixed_anytime:2/m, $node->safe_psql('postgres', q(select pg_stat_force_next_flush())); $anytime_test = q[ + SET stats_flush_interval = '1s'; BEGIN; SET LOCAL stats_fetch_consistency = none; -- Accumulate stats select test_custom_stats_var_anytime_update('entry2'); select test_custom_stats_var_anytime_update('entry2'); - -- Wait (has to be greater than PGSTAT_MIN_INTERVAL) + -- Wait (has to be greater than stats_flush_interval) select pg_sleep(1.5); -- Check select 'var_anytime:'||calls from test_custom_stats_var_report('entry2'); -- 2.34.1 --2MEBAGW8+kohXisi Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v11-0004-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v5 2/4] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 32 +++++++++++++++++++ src/backend/access/transam/xlog.c | 4 +-- src/backend/utils/activity/pgstat.c | 16 +++++++++- src/backend/utils/activity/pgstat_backend.c | 4 +-- src/backend/utils/activity/pgstat_io.c | 2 +- src/backend/utils/activity/pgstat_slru.c | 2 +- src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/pgstat.h | 1 + src/include/utils/guc_hooks.h | 1 + 10 files changed, 66 insertions(+), 7 deletions(-) 45.9% doc/src/sgml/ 6.5% src/backend/access/transam/ 31.8% src/backend/utils/activity/ 12.3% src/backend/utils/misc/ 3.2% src/include/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 5560b95ee60..3136816a933 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8834,6 +8834,38 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which non-transactional statistics are made visible + during running transactions. Non-transactional statistics include, for + example, WAL activity and I/O operations. + They become visible at that interval in monitoring views such as + <link linkend="monitoring-pg-stat-io-view"> <structname>pg_stat_io</structname></link> + and <link linkend="monitoring-pg-stat-wal-view"> <structname>pg_stat_wal</structname></link> + during running transactions. + If this value is specified without units, it is taken as milliseconds. + The default is 10 seconds (<literal>10s</literal>), which is probably + about the smallest value you would want in practice for long running + transactions. + </para> + <note> + <para> + This parameter does not affect transactional statistics such as + <structname>pg_stat_all_tables</structname> columns (like + <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + <structfield>n_tup_del</structfield>), which are always flushed at transaction + boundaries to maintain consistency. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 9503aea5b4d..31523dea923 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -1087,7 +1087,7 @@ XLogInsertRecord(XLogRecData *rdata, /* Schedule next anytime stats update timeout */ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); /* Required for the flush of pending stats WAL data */ pgstat_report_fixed = true; @@ -2073,7 +2073,7 @@ AdvanceXLInsertBuffer(XLogRecPtr upto, TimeLineID tli, bool opportunistic) /* Schedule next anytime stats update timeout */ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, - PGSTAT_MIN_INTERVAL); + pgstat_flush_interval); /* * Required for the flush of pending stats WAL data, per diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index 2c9454677e9..dd174129403 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -203,6 +203,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -1304,7 +1305,7 @@ pgstat_prep_pending_entry(PgStat_Kind kind, Oid dboid, uint64 objid, bool *creat /* Schedule next anytime stats update timeout */ if (kind_info->flush_mode == FLUSH_ANYTIME && IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); } return entry_ref; @@ -2172,6 +2173,19 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) + { + disable_timeout(ANYTIME_STATS_UPDATE_TIMEOUT, false); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); + } +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c index 9dcb24db975..f5b8c7b039c 100644 --- a/src/backend/utils/activity/pgstat_backend.c +++ b/src/backend/utils/activity/pgstat_backend.c @@ -69,7 +69,7 @@ pgstat_count_backend_io_op_time(IOObject io_object, IOContext io_context, /* Schedule next anytime stats update timeout */ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); backend_has_iostats = true; pgstat_report_fixed = true; @@ -89,7 +89,7 @@ pgstat_count_backend_io_op(IOObject io_object, IOContext io_context, /* Schedule next anytime stats update timeout */ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); backend_has_iostats = true; pgstat_report_fixed = true; diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c index 53dbf2a514b..b69a1e26f7d 100644 --- a/src/backend/utils/activity/pgstat_io.c +++ b/src/backend/utils/activity/pgstat_io.c @@ -82,7 +82,7 @@ pgstat_count_io_op(IOObject io_object, IOContext io_context, IOOp io_op, /* Schedule next anytime stats update timeout */ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); have_iostats = true; pgstat_report_fixed = true; diff --git a/src/backend/utils/activity/pgstat_slru.c b/src/backend/utils/activity/pgstat_slru.c index 1d16cde1889..36231ee874b 100644 --- a/src/backend/utils/activity/pgstat_slru.c +++ b/src/backend/utils/activity/pgstat_slru.c @@ -226,7 +226,7 @@ get_slru_entry(int slru_idx) /* Schedule next anytime stats update timeout */ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); have_slrustats = true; pgstat_report_fixed = true; diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index f0260e6e412..3bb43362e51 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2782,6 +2782,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index c4f92fcdac8..6ce5a250170 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -669,6 +669,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 1651f16f966..e0f222695bf 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -816,6 +816,7 @@ extern PgStat_WalStats *pgstat_fetch_stat_wal(void); extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index b6ecb0e769f..3a2ae6c41cd 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, -- 2.34.1 --WLtTrIUEfQDmERxy Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v5-0003-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v6 3/5] Add GUC to specify non-transactional statistics flush interval @ 2026-01-28 07:53 Bertrand Drouvot <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bertrand Drouvot @ 2026-01-28 07:53 UTC (permalink / raw) Adding pgstat_flush_interval, a new GUC to set the interval between flushes of non-transactional statistics. --- doc/src/sgml/config.sgml | 34 +++++++++++++++++++ src/backend/utils/activity/pgstat.c | 16 +++++++++ src/backend/utils/misc/guc_parameters.dat | 10 ++++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/pgstat.h | 6 ++-- src/include/utils/guc_hooks.h | 1 + 6 files changed, 64 insertions(+), 4 deletions(-) 59.1% doc/src/sgml/ 14.2% src/backend/utils/activity/ 14.5% src/backend/utils/misc/ 12.0% src/include/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index f1af1505cf3..20666679f90 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8871,6 +8871,40 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-stats-flush-interval" xreflabel="stats_flush_interval"> + <term><varname>stats_flush_interval</varname> (<type>integer</type>) + <indexterm> + <primary><varname>stats_flush_interval</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Sets the interval at which statistics that can be updated while a + transaction is still running are made visible. These include, for example, + WAL activity and I/O operations. + Such statistics are refreshed at the specified interval and can be observed + during active transactions in monitoring views such as + <link linkend="monitoring-pg-stat-io-view"><structname>pg_stat_io</structname></link> + and + <link linkend="monitoring-pg-stat-wal-view"><structname>pg_stat_wal</structname></link>. + Other statistics are only made visible at transaction end and are not + affected by this setting. + If the value is specified without a unit, milliseconds are assumed. + The default is 10 seconds (<literal>10s</literal>), which is generally + the smallest practical value for long-running transactions. + </para> + <note> + <para> + This parameter does not affect statistics that are only reported at + transaction end, such as the columns of <structname>pg_stat_all_tables</structname> + (for example, <structfield>n_tup_ins</structfield>, <structfield>n_tup_upd</structfield>, + and <structfield>n_tup_del</structfield>). These statistics are always + flushed at the end of a transaction. + </para> + </note> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index 411b65aae3e..79eb59b5625 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -123,6 +123,8 @@ * ---------- */ +/* minimum interval non-forced stats flushes.*/ +#define PGSTAT_MIN_INTERVAL 1000 /* how long until to block flushing pending stats updates */ #define PGSTAT_MAX_INTERVAL 60000 /* when to call pgstat_report_stat() again, even when idle */ @@ -203,6 +205,7 @@ static inline bool pgstat_is_kind_valid(PgStat_Kind kind); bool pgstat_track_counts = false; int pgstat_fetch_consistency = PGSTAT_FETCH_CONSISTENCY_CACHE; +int pgstat_flush_interval = 10000; /* ---------- @@ -2164,6 +2167,19 @@ assign_stats_fetch_consistency(int newval, void *extra) force_stats_snapshot_clear = true; } +/* + * GUC assign_hook for stats_flush_interval. + */ +void +assign_stats_flush_interval(int newval, void *extra) +{ + if (get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) + { + disable_timeout(ANYTIME_STATS_UPDATE_TIMEOUT, false); + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, newval); + } +} + /* * Flushes only FLUSH_ANYTIME stats using non-blocking locks. Transactional * stats (FLUSH_AT_TXN_BOUNDARY) remain pending until transaction boundary. diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat index c1f1603cd39..fc0e4259b36 100644 --- a/src/backend/utils/misc/guc_parameters.dat +++ b/src/backend/utils/misc/guc_parameters.dat @@ -2789,6 +2789,16 @@ assign_hook => 'assign_stats_fetch_consistency', }, +{ name => 'stats_flush_interval', type => 'int', context => 'PGC_USERSET', group => 'STATS_CUMULATIVE', + short_desc => 'Sets the interval between flushes of non-transactional statistics.', + flags => 'GUC_UNIT_MS', + variable => 'pgstat_flush_interval', + boot_val => '10000', + min => '1000', + max => 'INT_MAX', + assign_hook => 'assign_stats_flush_interval' +}, + { name => 'subtransaction_buffers', type => 'int', context => 'PGC_POSTMASTER', group => 'RESOURCES_MEM', short_desc => 'Sets the size of the dedicated buffer pool used for the subtransaction cache.', long_desc => '0 means use a fraction of "shared_buffers".', diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 1ae594af843..3f998a0bea0 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -673,6 +673,7 @@ #track_wal_io_timing = off #track_functions = none # none, pl, all #stats_fetch_consistency = cache # cache, none, snapshot +#stats_flush_interval = 10s # in milliseconds # - Monitoring - diff --git a/src/include/pgstat.h b/src/include/pgstat.h index b340a680614..ef856dbf55b 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -35,9 +35,6 @@ /* Default directory to store temporary statistics data in */ #define PG_STAT_TMP_DIR "pg_stat_tmp" -/* Minimum interval non-forced stats flushes */ -#define PGSTAT_MIN_INTERVAL 1000 - /* Values for track_functions GUC variable --- order is significant! */ typedef enum TrackFunctionsLevel { @@ -548,7 +545,7 @@ extern void pgstat_force_next_flush(void); #define pgstat_schedule_anytime_update() \ do { \ if (IsUnderPostmaster && !get_timeout_active(ANYTIME_STATS_UPDATE_TIMEOUT)) \ - enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, PGSTAT_MIN_INTERVAL); \ + enable_timeout_after(ANYTIME_STATS_UPDATE_TIMEOUT, pgstat_flush_interval); \ } while (0) extern void pgstat_reset_counters(void); @@ -828,6 +825,7 @@ extern PgStat_WalStats *pgstat_fetch_stat_wal(void); extern PGDLLIMPORT bool pgstat_track_counts; extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +extern PGDLLIMPORT int pgstat_flush_interval; /* diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index b6ecb0e769f..3a2ae6c41cd 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -132,6 +132,7 @@ extern bool check_session_authorization(char **newval, void **extra, GucSource s extern void assign_session_authorization(const char *newval, void *extra); extern void assign_session_replication_role(int newval, void *extra); extern void assign_stats_fetch_consistency(int newval, void *extra); +extern void assign_stats_flush_interval(int newval, void *extra); extern bool check_ssl(bool *newval, void **extra, GucSource source); extern bool check_stage_log_stats(bool *newval, void **extra, GucSource source); extern bool check_standard_conforming_strings(bool *newval, void **extra, -- 2.34.1 --DxqFthphbM+ha9Dy Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v6-0004-Remove-useless-calls-to-flush-some-stats.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-01-28 07:53 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-09-05 12:29 [PATCH v7 4/4] Change policy of XLog read-buffer allocation Kyotaro Horiguchi <[email protected]> 2026-01-28 07:53 [PATCH v8 3/5] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v10 3/5] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v4 2/4] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v7 3/5] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v9 3/5] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v11 3/5] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v5 2/4] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]> 2026-01-28 07:53 [PATCH v6 3/5] Add GUC to specify non-transactional statistics flush interval Bertrand Drouvot <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox