On Wed, Jun 5, 2024 at 7:19 PM Andrey M. Borodin <x4...@yandex-team.ru> wrote: > > > > > On 4 Jun 2024, at 00:26, Masahiko Sawada <sawada.m...@gmail.com> wrote: > > Thank you! Vacuum enhancement is a really good step forward, and this small > change would help a lot of observability tools. > > > > On 4 Jun 2024, at 00:49, Peter Geoghegan <p...@bowt.ie> wrote: > > > > Can we rename this to num_dead_item_ids (or something similar) in > > passing? > > I do not insist, but many tools will have to adapt to this change [0,1]. > However, most of tools will have to deal with removed max_dead_tuples anyway > [2], so this is not that big problem.
True, this incompatibility would not be a big problem. num_dead_item_ids seems good to me. I've updated the patch that incorporated the comment from Álvaro[1]. Regards, [1] https://www.postgresql.org/message-id/202406041535.pmyty3ci4pfd%40alvherre.pgsql -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
From 24838307afc52dfc00317579ad88a59ba5c00192 Mon Sep 17 00:00:00 2001 From: Masahiko Sawada <sawada.mshk@gmail.com> Date: Tue, 4 Jun 2024 06:17:25 +0900 Subject: [PATCH v2] Reintroduce dead tuple counter in pg_stat_progress_vacuum. MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Commit 667e65aac3 changed both num_dead_tuples and max_dead_tuples columns to dead_tuple_bytes and max_dead_tuple_bytes columns, respectively. But as per discussion, the number of dead tuples collected still can provide meaningful insights for users. This change reintroduce the column for the count of dead tuples, renamed as num_dead_item_ids to avoid confusion with the number of dead tuples removed by VACUUM, which includes dead heap-only tuples, but excludes any pre-existing LP_DEAD items left behind by opportunistic pruning. XXX: bump catalog version. Reviewed-by: Peter Geoghegan, Álvaro Herrera, Andrey Borodin Discussion: https://postgr.es/m/CAD21AoBL5sJE9TRWPyv%2Bw7k5Ee5QAJqDJEDJBUdAaCzGWAdvZw%40mail.gmail.com --- doc/src/sgml/monitoring.sgml | 9 +++++++++ src/backend/access/heap/vacuumlazy.c | 12 +++++++++--- src/backend/catalog/system_views.sql | 3 ++- src/include/commands/progress.h | 5 +++-- src/test/regress/expected/rules.out | 5 +++-- 5 files changed, 26 insertions(+), 8 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 053da8d6e4..b2ad9b446f 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -6268,6 +6268,15 @@ FROM pg_stat_get_backend_idset() AS backendid; </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>num_dead_item_ids</structfield> <type>bigint</type> + </para> + <para> + Number of dead item identifiers collected since the last index vacuum cycle. + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>indexes_total</structfield> <type>bigint</type> diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 8145ea8fc3..ef1df35afa 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -2883,13 +2883,19 @@ dead_items_add(LVRelState *vacrel, BlockNumber blkno, OffsetNumber *offsets, int num_offsets) { TidStore *dead_items = vacrel->dead_items; + const int prog_index[2] = { + PROGRESS_VACUUM_NUM_DEAD_ITEM_IDS, + PROGRESS_VACUUM_DEAD_TUPLE_BYTES + }; + int64 prog_val[2]; TidStoreSetBlockOffsets(dead_items, blkno, offsets, num_offsets); vacrel->dead_items_info->num_items += num_offsets; - /* update the memory usage report */ - pgstat_progress_update_param(PROGRESS_VACUUM_DEAD_TUPLE_BYTES, - TidStoreMemoryUsage(dead_items)); + /* update the progress information */ + prog_val[0] = vacrel->dead_items_info->num_items; + prog_val[1] = TidStoreMemoryUsage(dead_items); + pgstat_progress_update_multi_param(2, prog_index, prog_val); } /* diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 53047cab5f..efb29adeb3 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1221,7 +1221,8 @@ CREATE VIEW pg_stat_progress_vacuum AS S.param2 AS heap_blks_total, S.param3 AS heap_blks_scanned, S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_count, S.param6 AS max_dead_tuple_bytes, S.param7 AS dead_tuple_bytes, - S.param8 AS indexes_total, S.param9 AS indexes_processed + S.param8 AS num_dead_item_ids, S.param9 AS indexes_total, + S.param10 AS indexes_processed FROM pg_stat_get_progress_info('VACUUM') AS S LEFT JOIN pg_database D ON S.datid = D.oid; diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h index 82a8fe6bd1..5616d64523 100644 --- a/src/include/commands/progress.h +++ b/src/include/commands/progress.h @@ -25,8 +25,9 @@ #define PROGRESS_VACUUM_NUM_INDEX_VACUUMS 4 #define PROGRESS_VACUUM_MAX_DEAD_TUPLE_BYTES 5 #define PROGRESS_VACUUM_DEAD_TUPLE_BYTES 6 -#define PROGRESS_VACUUM_INDEXES_TOTAL 7 -#define PROGRESS_VACUUM_INDEXES_PROCESSED 8 +#define PROGRESS_VACUUM_NUM_DEAD_ITEM_IDS 7 +#define PROGRESS_VACUUM_INDEXES_TOTAL 8 +#define PROGRESS_VACUUM_INDEXES_PROCESSED 9 /* Phases of vacuum (as advertised via PROGRESS_VACUUM_PHASE) */ #define PROGRESS_VACUUM_PHASE_SCAN_HEAP 1 diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index ef658ad740..13178e2b3d 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2052,8 +2052,9 @@ pg_stat_progress_vacuum| SELECT s.pid, s.param5 AS index_vacuum_count, s.param6 AS max_dead_tuple_bytes, s.param7 AS dead_tuple_bytes, - s.param8 AS indexes_total, - s.param9 AS indexes_processed + s.param8 AS num_dead_item_ids, + s.param9 AS indexes_total, + s.param10 AS indexes_processed FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20) LEFT JOIN pg_database d ON ((s.datid = d.oid))); pg_stat_recovery_prefetch| SELECT stats_reset, -- 2.39.3
