On Fri, Mar 17, 2023 at 3:22 PM Peter Geoghegan <p...@bowt.ie> wrote: > I think that this is pretty close to being committable already.
Attached revision has some small tweaks by me. Going to commit this revised version tomorrow morning. Changes: * No more dedicated struct to carry around the type of an update. We just use two boolean arguments to the pgstats function instead. The struct didn't seem to be adding much, and it was distracting to track the information this way within heap_update(). * Small adjustments to the documentation. Nearby related items were tweaked slightly to make everything fit together a bit better. For example, the description of n_tup_hot_upd is revised to make it obvious that n_tup_hot_upd counts row updates that can never get counted under the new n_tup_newpage_upd counter. -- Peter Geoghegan
From 23d768e87e95e421e583e2f0dc06bd36534081a6 Mon Sep 17 00:00:00 2001 From: Peter Geoghegan <pg@bowt.ie> Date: Wed, 22 Mar 2023 11:58:18 -0700 Subject: [PATCH v2] Count the number of new page updates in pgstats. Bump catalog and stats format versions. Corey Huinker, with some tweaks by me. Author: Corey Huinker <corey.huinker@gmail.com> Reviewed-By: Peter Geoghegan <pg@bowt.ie> Reviewed-By: Andres Freund <andres@anarazel.de> Discussion: https://postgr.es/m/CADkLM=ded21M9iZ36hHm-vj2rE2d=zcKpUQMds__Xm2pxLfHKA@mail.gmail.com --- src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.dat | 10 ++++++++ src/include/pgstat.h | 10 ++++---- src/backend/access/heap/heapam.c | 2 +- src/backend/catalog/system_views.sql | 4 +++- src/backend/utils/activity/pgstat_relation.c | 12 ++++++++-- src/backend/utils/adt/pgstatfuncs.c | 18 ++++++++++++++ doc/src/sgml/monitoring.sgml | 25 ++++++++++++++++---- src/test/regress/expected/rules.out | 12 +++++++--- 9 files changed, 78 insertions(+), 17 deletions(-) diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index e94528a7c..0c0915885 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202303181 +#define CATALOG_VERSION_NO 202303221 #endif diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 5cf87aeb2..7c358cff1 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5360,6 +5360,11 @@ proname => 'pg_stat_get_tuples_hot_updated', provolatile => 's', proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', prosrc => 'pg_stat_get_tuples_hot_updated' }, +{ oid => '8614', + descr => 'statistics: number of tuples updated onto a new page', + proname => 'pg_stat_get_tuples_newpage_updated', provolatile => 's', + proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', + prosrc => 'pg_stat_get_tuples_newpage_updated' }, { oid => '2878', descr => 'statistics: number of live tuples', proname => 'pg_stat_get_live_tuples', provolatile => 's', proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', @@ -5823,6 +5828,11 @@ proname => 'pg_stat_get_xact_tuples_hot_updated', provolatile => 'v', proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', prosrc => 'pg_stat_get_xact_tuples_hot_updated' }, +{ oid => '8615', + descr => 'statistics: number of tuples updated onto a new page in current transaction', + proname => 'pg_stat_get_xact_tuples_newpage_updated', provolatile => 'v', + proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', + prosrc => 'pg_stat_get_xact_tuples_newpage_updated' }, { oid => '3044', descr => 'statistics: number of blocks fetched in current transaction', proname => 'pg_stat_get_xact_blocks_fetched', provolatile => 'v', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 1e418b682..46d053422 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -151,8 +151,8 @@ typedef struct PgStat_BackendSubEntry * the index AM, while tuples_fetched is the number of tuples successfully * fetched by heap_fetch under the control of simple indexscans for this index. * - * tuples_inserted/updated/deleted/hot_updated count attempted actions, - * regardless of whether the transaction committed. delta_live_tuples, + * tuples_inserted/updated/deleted/hot_updated/newpage_updated count attempted + * actions, regardless of whether the transaction committed. delta_live_tuples, * delta_dead_tuples, and changed_tuples are set depending on commit or abort. * Note that delta_live_tuples and delta_dead_tuples can be negative! * ---------- @@ -168,6 +168,7 @@ typedef struct PgStat_TableCounts PgStat_Counter t_tuples_updated; PgStat_Counter t_tuples_deleted; PgStat_Counter t_tuples_hot_updated; + PgStat_Counter t_tuples_newpage_updated; bool t_truncdropped; PgStat_Counter t_delta_live_tuples; @@ -234,7 +235,7 @@ typedef struct PgStat_TableXactStatus * ------------------------------------------------------------ */ -#define PGSTAT_FILE_FORMAT_ID 0x01A5BCAA +#define PGSTAT_FILE_FORMAT_ID 0x01A5BCAB typedef struct PgStat_ArchiverStats { @@ -401,6 +402,7 @@ typedef struct PgStat_StatTabEntry PgStat_Counter tuples_updated; PgStat_Counter tuples_deleted; PgStat_Counter tuples_hot_updated; + PgStat_Counter tuples_newpage_updated; PgStat_Counter live_tuples; PgStat_Counter dead_tuples; @@ -616,7 +618,7 @@ extern void pgstat_report_analyze(Relation rel, } while (0) extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n); -extern void pgstat_count_heap_update(Relation rel, bool hot); +extern void pgstat_count_heap_update(Relation rel, bool hot, bool newpage); extern void pgstat_count_heap_delete(Relation rel); extern void pgstat_count_truncate(Relation rel); extern void pgstat_update_heap_dead_tuples(Relation rel, int delta); diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index cf4b917eb..8abc101c8 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -3803,7 +3803,7 @@ l2: if (have_tuple_lock) UnlockTupleTuplock(relation, &(oldtup.t_self), *lockmode); - pgstat_count_heap_update(relation, use_hot_update); + pgstat_count_heap_update(relation, use_hot_update, newbuf != buffer); /* * If heaptup is a private copy, release it. Don't forget to copy t_self diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 34ca0e739..8ea159dbd 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -665,6 +665,7 @@ CREATE VIEW pg_stat_all_tables AS pg_stat_get_tuples_updated(C.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(C.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd, + pg_stat_get_tuples_newpage_updated(C.oid) AS n_tup_newpage_upd, pg_stat_get_live_tuples(C.oid) AS n_live_tup, pg_stat_get_dead_tuples(C.oid) AS n_dead_tup, pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze, @@ -696,7 +697,8 @@ CREATE VIEW pg_stat_xact_all_tables AS pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins, pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd, pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del, - pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd + pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd, + pg_stat_get_xact_tuples_newpage_updated(C.oid) AS n_tup_newpage_upd FROM pg_class C LEFT JOIN pg_index I ON C.oid = I.indrelid LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index f793ac151..b57643379 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -373,8 +373,10 @@ pgstat_count_heap_insert(Relation rel, PgStat_Counter n) * count a tuple update */ void -pgstat_count_heap_update(Relation rel, bool hot) +pgstat_count_heap_update(Relation rel, bool hot, bool newpage) { + Assert(!(hot && newpage)); + if (pgstat_should_count_relation(rel)) { PgStat_TableStatus *pgstat_info = rel->pgstat_info; @@ -382,9 +384,14 @@ pgstat_count_heap_update(Relation rel, bool hot) ensure_tabstat_xact_level(pgstat_info); pgstat_info->trans->tuples_updated++; - /* t_tuples_hot_updated is nontransactional, so just advance it */ + /* + * t_tuples_hot_updated and t_tuples_newpage_updated counters are + * nontransactional, so just advance them + */ if (hot) pgstat_info->t_counts.t_tuples_hot_updated++; + else if (newpage) + pgstat_info->t_counts.t_tuples_newpage_updated++; } } @@ -804,6 +811,7 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait) tabentry->tuples_updated += lstats->t_counts.t_tuples_updated; tabentry->tuples_deleted += lstats->t_counts.t_tuples_deleted; tabentry->tuples_hot_updated += lstats->t_counts.t_tuples_hot_updated; + tabentry->tuples_newpage_updated += lstats->t_counts.t_tuples_newpage_updated; /* * If table was truncated/dropped, first reset the live/dead counters. diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 35c6d4655..56119737c 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -92,6 +92,9 @@ PG_STAT_GET_RELENTRY_INT64(tuples_fetched) /* pg_stat_get_tuples_hot_updated */ PG_STAT_GET_RELENTRY_INT64(tuples_hot_updated) +/* pg_stat_get_tuples_newpage_updated */ +PG_STAT_GET_RELENTRY_INT64(tuples_newpage_updated) + /* pg_stat_get_tuples_inserted */ PG_STAT_GET_RELENTRY_INT64(tuples_inserted) @@ -1618,6 +1621,21 @@ pg_stat_get_xact_tuples_hot_updated(PG_FUNCTION_ARGS) PG_RETURN_INT64(result); } +Datum +pg_stat_get_xact_tuples_newpage_updated(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int64 result; + PgStat_TableStatus *tabentry; + + if ((tabentry = find_tabstat_entry(relid)) == NULL) + result = 0; + else + result = (int64) (tabentry->t_counts.t_tuples_newpage_updated); + + PG_RETURN_INT64(result); +} + Datum pg_stat_get_xact_blocks_fetched(PG_FUNCTION_ARGS) { diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 2ae24127a..329c68ff8 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -4789,7 +4789,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i <structfield>n_tup_ins</structfield> <type>bigint</type> </para> <para> - Number of rows inserted + Total number of rows inserted </para></entry> </row> @@ -4798,7 +4798,9 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i <structfield>n_tup_upd</structfield> <type>bigint</type> </para> <para> - Number of rows updated (includes <link linkend="storage-hot">HOT updated rows</link>) + Total number of rows updated (this includes row updates also + counted by <structfield>n_tup_hot_upd</structfield> and + <structfield>n_tup_newpage_upd</structfield>) </para></entry> </row> @@ -4807,7 +4809,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i <structfield>n_tup_del</structfield> <type>bigint</type> </para> <para> - Number of rows deleted + Total number of rows deleted </para></entry> </row> @@ -4816,8 +4818,21 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i <structfield>n_tup_hot_upd</structfield> <type>bigint</type> </para> <para> - Number of rows HOT updated (i.e., with no separate index - update required) + Number of rows <link linkend="storage-hot">HOT updated</link> + (i.e., successor version fits on same page as original, no + successor versions required in indexes) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>n_tup_newpage_upd</structfield> <type>bigint</type> + </para> + <para> + Number of rows updated where the successor version goes onto a + new page, leaving behind an original version whose + <link linkend="storage-tuple-layout"><structfield>t_ctid</structfield> + field</link> links to a different page </para></entry> </row> diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index e953d1f51..996d22b7d 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1789,6 +1789,7 @@ pg_stat_all_tables| SELECT c.oid AS relid, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, + pg_stat_get_tuples_newpage_updated(c.oid) AS n_tup_newpage_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze, @@ -2146,6 +2147,7 @@ pg_stat_sys_tables| SELECT relid, n_tup_upd, n_tup_del, n_tup_hot_upd, + n_tup_newpage_upd, n_live_tup, n_dead_tup, n_mod_since_analyze, @@ -2193,6 +2195,7 @@ pg_stat_user_tables| SELECT relid, n_tup_upd, n_tup_del, n_tup_hot_upd, + n_tup_newpage_upd, n_live_tup, n_dead_tup, n_mod_since_analyze, @@ -2244,7 +2247,8 @@ pg_stat_xact_all_tables| SELECT c.oid AS relid, pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del, - pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd + pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd, + pg_stat_get_xact_tuples_newpage_updated(c.oid) AS n_tup_newpage_upd FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) @@ -2260,7 +2264,8 @@ pg_stat_xact_sys_tables| SELECT relid, n_tup_ins, n_tup_upd, n_tup_del, - n_tup_hot_upd + n_tup_hot_upd, + n_tup_newpage_upd FROM pg_stat_xact_all_tables WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); pg_stat_xact_user_functions| SELECT p.oid AS funcid, @@ -2282,7 +2287,8 @@ pg_stat_xact_user_tables| SELECT relid, n_tup_ins, n_tup_upd, n_tup_del, - n_tup_hot_upd + n_tup_hot_upd, + n_tup_newpage_upd FROM pg_stat_xact_all_tables WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_statio_all_indexes| SELECT c.oid AS relid, -- 2.39.2
