Re: [HACKERS] More stats about skipped vacuums - Mailing list pgsql-hackers
| From | Kyotaro HORIGUCHI |
|---|---|
| Subject | Re: [HACKERS] More stats about skipped vacuums |
| Date | |
| Msg-id | 20171116.193402.26276109.horiguchi.kyotaro@lab.ntt.co.jp Whole thread Raw |
| In response to | Re: [HACKERS] More stats about skipped vacuums (Michael Paquier <michael.paquier@gmail.com>) |
| Responses |
Re: [HACKERS] More stats about skipped vacuums
(Michael Paquier <michael.paquier@gmail.com>)
|
| List | pgsql-hackers |
Thank you for reviewing this.
At Wed, 15 Nov 2017 16:13:01 +0900, Michael Paquier <michael.paquier@gmail.com> wrote in
<CAB7nPqQm_WCKuUf5RD0CzeMuMO907ZPKP7mBh-3t2zSJ9jn+PA@mail.gmail.com>
> pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze,
> + pg_stat_get_vacuum_necessity(C.oid) AS vacuum_required,
> pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
> pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
> pg_stat_get_last_analyze_time(C.oid) as last_analyze,
> pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
> pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
> Please use spaces instead of tabs. Indentation is not consistent.
Done. Thank you for pointing. (whitespace-mode showed me some
similar inconsistencies at the other places in the file...)
> + case PGSTAT_VACUUM_CANCELED:
> + phase = tabentry->vacuum_last_phase;
> + /* number of elements of phasestr above */
> + if (phase >= 0 && phase <= 7)
> + result = psprintf("%s while %s",
> + status == PGSTAT_VACUUM_CANCELED ?
> + "canceled" : "error",
> + phasestr[phase]);
> Such complication is not necessary. The phase parameter is updated by
> individual calls of pgstat_progress_update_param(), so the information
> showed here overlaps with the existing information in the "phase"
> field.
The "phase" is pg_stat_progress_vacuum's? If "complexy" means
phasestr[phase], the "phase" cannot be overlap with
last_vacuum_status since pg_stat_progress_vacuum's entry has
already gone when someone looks into pg_stat_all_tables and see a
failed vacuum status. Could you give a bit specific comment?
> @@ -210,7 +361,6 @@ pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS)
> PG_RETURN_INT64(result);
> }
>
> -
> Datum
> pg_stat_get_blocks_hit(PG_FUNCTION_ARGS)
> Noise diff.
Removed.
> Thinking about trying to et something into core by the end of the
> commit fest, this patch presents multiple concepts at once which could
> be split into separate patches for simplicity:
> 1) Additional data fields to help in debugging completed vacuums.
> 2) Tracking of interrupted vacuum jobs in progress table.
> 3) Get state of vacuum job on error.
>
> However, progress reports are here to allow users to do decisions
> based on the activity of how things are working. This patch proposes
> to add multiple new fields:
> - oldest Xmin.
> - number of index scans.
> - number of pages truncated.
> - number of pages that should have been truncated, but are not truncated.
> Among all this information, as Sawada-san has already mentioned
> upthread, the more index scans the less dead tuples you can store at
> once, so autovacuum_work_mem ought to be increases. This is useful for
> tuning and should be documented properly if reported to give
> indications about vacuum behavior. The rest though, could indicate how
> aggressive autovacuum is able to remove tail blocks and do its work.
> But what really matters for users to decide if autovacuum should be
> more aggressive is tracking the number of dead tuples, something which
> is already evaluated.
Hmm. I tend to agree. Such numbers are better to be shown as
average of the last n vacuums or maximum. I decided to show
last_vacuum_index_scan only and I think that someone can record
it continuously to elsewhere if wants.
> Tracking the number of failed vacuum attempts is also something
> helpful to understand how much the job is able to complete. As there
> is already tracking vacuum jobs that have completed, it could be
> possible, instead of logging activity when a vacuum job has failed, to
> track the number of *begun* jobs on a relation. Then it is possible to
> guess how many have failed by taking the difference between those that
> completed properly. Having counters per failure types could also be a
> possibility.
Maybe pg_stat_all_tables is not the place to hold such many kinds
of vacuum specific information. pg_stat_vacuum_all_tables or
something like?
> For this commit fest, I would suggest a patch that simply adds
> tracking for the number of index scans done, with documentation to
> give recommendations about parameter tuning. i am switching the patch
> as "waiting on author".
Ok, the patch has been split into the following four parts. (Not
split by function, but by the kind of information to add.)
The first one is that.
0001. Adds pg_stat_all_tables.last_vacuum_index_scans. Documentation is added.
0002. Adds pg_stat_all_tables.vacuum_required. And primitive documentation.
0003. Adds pg_stat_all_tables.last_vacuum_status/autovacuum_fail_count plus primitive documentation.
0004. truncation information stuff.
One concern on pg_stat_all_tables view is the number of
predefined functions it uses. Currently 20 functions and this
patch adds more seven. I feel it's better that at least the
functions this patch adds are merged into one function..
--
Kyotaro Horiguchi
NTT Open Source Software Center
From f0132151fddddb6f8439b82465ba31e64bc3b8ad Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyotaro@lab.ntt.co.jp>
Date: Thu, 16 Nov 2017 15:27:53 +0900
Subject: [PATCH 1/4] Show index scans of the last vacuum in pg_stat_all_tables
This number is already shown in the autovacuum completion log or the
result of VACUUM VERBOSE, but the number is useful to see whether
maintenance_work_mem is large enough so this patch adds the number in
pg_stat_all_tables view.
---doc/src/sgml/config.sgml | 9 +++++++++doc/src/sgml/monitoring.sgml | 5
+++++src/backend/catalog/system_views.sql| 1 +src/backend/commands/vacuumlazy.c | 3
++-src/backend/postmaster/pgstat.c | 6 +++++-src/backend/utils/adt/pgstatfuncs.c | 14
++++++++++++++src/include/catalog/pg_proc.h | 2 ++src/include/pgstat.h | 5
++++-src/test/regress/expected/rules.out | 3 +++9 files changed, 45 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index fc1752f..41f0858 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1496,6 +1496,15 @@ include_dir 'conf.d' performance for vacuuming and for restoring database dumps.
</para> <para>
+ Vacuuming scans all index pages to remove index entries that pointed
+ the removed tuples. In order to finish vacuuming by as few index
+ scans as possible, the removed tuples are remembered in working
+ memory. If this setting is not large enough, vacuuming runs
+ additional index scans to vacate the memory and it might cause a
+ performance problem. That behavior can be monitored
+ in <xref linkend="pg-stat-all-tables-view">.
+ </para>
+ <para> Note that when autovacuum runs, up to <xref linkend="guc-autovacuum-max-workers"> times
thismemory may be allocated, so be careful not to set the default value
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 6f82033..71823c5 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2576,6 +2576,11 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i (not
counting<command>VACUUM FULL</command>)</entry> </row> <row>
+ <entry><structfield>last_vacuum_index_scans</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry>Number of splitted index scans performed during the last vacuum on this table</entry>
+ </row>
+ <row> <entry><structfield>autovacuum_count</structfield></entry> <entry><type>bigint</type></entry>
<entry>Numberof times this table has been vacuumed by the autovacuum
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 394aea8..cf6621d 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -528,6 +528,7 @@ CREATE VIEW pg_stat_all_tables AS pg_stat_get_last_analyze_time(C.oid) as last_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze, pg_stat_get_vacuum_count(C.oid) AS
vacuum_count,
+ pg_stat_get_last_vacuum_index_scans(C.oid) AS last_vacuum_index_scans,
pg_stat_get_autovacuum_count(C.oid)AS autovacuum_count, pg_stat_get_analyze_count(C.oid) AS analyze_count,
pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 6587db7..c482c8e 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -342,7 +342,8 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared,
new_live_tuples,
- vacrelstats->new_dead_tuples);
+ vacrelstats->new_dead_tuples,
+ vacrelstats->num_index_scans); pgstat_progress_end_command(); /* and log the action if
appropriate*/
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 5c256ff..5f3fdf6 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -1403,7 +1403,8 @@ pgstat_report_autovac(Oid dboid) */voidpgstat_report_vacuum(Oid tableoid, bool shared,
- PgStat_Counter livetuples, PgStat_Counter deadtuples)
+ PgStat_Counter livetuples, PgStat_Counter deadtuples,
+ PgStat_Counter num_index_scans){ PgStat_MsgVacuum msg;
@@ -1417,6 +1418,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared, msg.m_vacuumtime = GetCurrentTimestamp();
msg.m_live_tuples= livetuples; msg.m_dead_tuples = deadtuples;
+ msg.m_num_index_scans = num_index_scans; pgstat_send(&msg, sizeof(msg));}
@@ -4585,6 +4587,7 @@ pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create)
result->n_live_tuples= 0; result->n_dead_tuples = 0; result->changes_since_analyze = 0;
+ result->n_index_scans = 0; result->blocks_fetched = 0; result->blocks_hit = 0;
result->vacuum_timestamp= 0;
@@ -5981,6 +5984,7 @@ pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len) tabentry->n_live_tuples =
msg->m_live_tuples; tabentry->n_dead_tuples = msg->m_dead_tuples;
+ tabentry->n_index_scans = msg->m_num_index_scans; if (msg->m_autovacuum) {
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 8d9e7c1..2956356 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -194,6 +194,20 @@ pg_stat_get_mod_since_analyze(PG_FUNCTION_ARGS) PG_RETURN_INT64(result);}
+Datum
+pg_stat_get_last_vacuum_index_scans(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int32 result;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ result = 0;
+ else
+ result = (int32) (tabentry->n_index_scans);
+
+ PG_RETURN_INT32(result);
+}Datumpg_stat_get_blocks_fetched(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 0330c04..f3b606b 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2887,6 +2887,8 @@ DATA(insert OID = 3317 ( pg_stat_get_wal_receiver PGNSP PGUID 12 1 0 0 0 f f
fDESCR("statistics:information about WAL receiver");DATA(insert OID = 6118 ( pg_stat_get_subscription PGNSP PGUID
121 0 0 0 f f f f f f s r 1 0 2249 "26" "{26,26,26,23,3220,1184,1184,3220,1184}" "{i,o,o,o,o,o,o,o,o}"
"{subid,subid,relid,pid,received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time}"_null_
_null_pg_stat_get_subscription _null_ _null_ _null_ ));DESCR("statistics: information about subscription");
+DATA(insert OID = 3281 ( pg_stat_get_last_vacuum_index_scans PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 23 "26"
_null__null_ _null_ _null_ _null_ pg_stat_get_last_vacuum_index_scans _null_ _null_ _null_ ));
+DESCR("statistics: number of index scans in the last vacuum");DATA(insert OID = 2026 ( pg_backend_pid
PGNSPPGUID 12 1 0 0 0 f f f f t f s r 0 0 23 "" _null_ _null_ _null_ _null_ _null_ pg_backend_pid _null_ _null_ _null_
));DESCR("statistics:current backend PID");DATA(insert OID = 1937 ( pg_stat_get_backend_pid PGNSP PGUID 12 1 0
00 f f f f t f s r 1 0 23 "23" _null_ _null_ _null_ _null_ _null_ pg_stat_get_backend_pid _null_ _null_ _null_ ));
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 089b7c3..3ab5f4a 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -369,6 +369,7 @@ typedef struct PgStat_MsgVacuum TimestampTz m_vacuumtime; PgStat_Counter m_live_tuples;
PgStat_Counterm_dead_tuples;
+ PgStat_Counter m_num_index_scans;} PgStat_MsgVacuum;
@@ -629,6 +630,7 @@ typedef struct PgStat_StatTabEntry PgStat_Counter n_live_tuples; PgStat_Counter
n_dead_tuples; PgStat_Counter changes_since_analyze;
+ PgStat_Counter n_index_scans; PgStat_Counter blocks_fetched; PgStat_Counter blocks_hit;
@@ -1165,7 +1167,8 @@ extern void pgstat_reset_single_counter(Oid objectid, PgStat_Single_Reset_Type textern void
pgstat_report_autovac(Oiddboid);extern void pgstat_report_vacuum(Oid tableoid, bool shared,
- PgStat_Counter livetuples, PgStat_Counter deadtuples);
+ PgStat_Counter livetuples, PgStat_Counter deadtuples,
+ PgStat_Counter num_index_scans);extern void pgstat_report_analyze(Relation rel,
PgStat_Counter livetuples, PgStat_Counter deadtuples, bool resetcounter);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index f1c1b44..c334d20 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1764,6 +1764,7 @@ pg_stat_all_tables| SELECT c.oid AS relid, pg_stat_get_last_analyze_time(c.oid) AS
last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS
vacuum_count,
+ pg_stat_get_last_vacuum_index_scans(c.oid) AS last_vacuum_index_scans, pg_stat_get_autovacuum_count(c.oid) AS
autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS
autoanalyze_count
@@ -1911,6 +1912,7 @@ pg_stat_sys_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.last_analyze,
pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count,
+ pg_stat_all_tables.last_vacuum_index_scans, pg_stat_all_tables.autovacuum_count,
pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count
@@ -1954,6 +1956,7 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.last_analyze,
pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count,
+ pg_stat_all_tables.last_vacuum_index_scans, pg_stat_all_tables.autovacuum_count,
pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count
--
2.9.2
From 9619edd924c13337843f3fde221096b389701012 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyotaro@lab.ntt.co.jp>
Date: Thu, 16 Nov 2017 16:18:54 +0900
Subject: [PATCH 2/4] Add vacuum_required to pg_stat_all_tables
If vacuum of a table has been failed for a long time for some reasons,
it is hard for uses to distinguish between that the server judged
vacuuming of the table is not required and that rquired but failed.
This offers convenient way to check that as the first step of trouble
shooting.
---doc/src/sgml/config.sgml | 5 +-doc/src/sgml/maintenance.sgml | 4
+-doc/src/sgml/monitoring.sgml | 5 ++src/backend/catalog/system_views.sql | 1
+src/backend/commands/cluster.c | 2 +-src/backend/commands/vacuum.c | 69
++++++++++++++++++---src/backend/commands/vacuumlazy.c | 14 +----src/backend/postmaster/autovacuum.c | 115
+++++++++++++++++++++++++++++++++++src/backend/utils/adt/pgstatfuncs.c | 9 +++src/include/catalog/pg_proc.h |
2 +src/include/commands/vacuum.h | 3 +-src/include/postmaster/autovacuum.h | 1
+src/test/regress/expected/rules.out | 3 +13 files changed, 210 insertions(+), 23 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 41f0858..7262ffb 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -6579,7 +6579,10 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; <para>
<command>VACUUM</command>performs an aggressive scan if the table's
<structname>pg_class</structname>.<structfield>relfrozenxid</structfield>field has reached
- the age specified by this setting. An aggressive scan differs from
+ the age specified by this setting. It is indicated
+ as <quote>aggressive</quote> in vacuum_required
+ of <xref linkend="pg-stat-all-tables-view">. An aggressive scan
+ differs from a regular <command>VACUUM</command> in that it visits every page that might contain
unfrozenXIDs or MXIDs, not just those that might contain dead tuples. The default is 150 million transactions.
Althoughusers can
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 1a37905..d045b09 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -514,7 +514,9 @@ <varname>autovacuum_freeze_max_age</varname> wouldn't make sense because an anti-wraparound
autovacuumwould be triggered at that point anyway, and the 0.95 multiplier leaves some breathing room to run a
manual
- <command>VACUUM</command> before that happens. As a rule of thumb,
+ <command>VACUUM</command> before that happens. It is indicated
+ as <quote>close to freeze-limit xid</quote> in vacuum_required
+ of <xref linkend="pg-stat-all-tables-view">. As a rule of thumb, <command>vacuum_freeze_table_age</command>
shouldbe set to a value somewhat below <varname>autovacuum_freeze_max_age</varname>, leaving enough gap so that a
regularlyscheduled <command>VACUUM</command> or an autovacuum triggered by
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 71823c5..e8a8f77 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2547,6 +2547,11 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
<entry>Estimatednumber of rows modified since this table was last analyzed</entry> </row> <row>
+ <entry><structfield>vacuum_required</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry>Vacuum requirement status. "partial", "aggressive", "required", "not requried" or "close to freeze-limit
xid".</entry>
+ </row>
+ <row> <entry><structfield>last_vacuum</structfield></entry> <entry><type>timestamp with time
zone</type></entry> <entry>Last time at which this table was manually vacuumed
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index cf6621d..97bafb8 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -523,6 +523,7 @@ CREATE VIEW pg_stat_all_tables AS 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,
+ pg_stat_get_vacuum_necessity(C.oid) AS vacuum_required, pg_stat_get_last_vacuum_time(C.oid) as
last_vacuum, pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
pg_stat_get_last_analyze_time(C.oid)as last_analyze,
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 48f1e6e..403b76d 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -850,7 +850,7 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose, */
vacuum_set_xid_limits(OldHeap,0, 0, 0, 0, &OldestXmin, &FreezeXid, NULL, &MultiXactCutoff,
- NULL);
+ NULL, NULL, NULL); /* * FreezeXid will become the table's new relfrozenxid, and that
mustn'tgo
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index cbd6e9b..f51dcdb 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -585,6 +585,10 @@ get_all_vacuum_rels(void) * Xmax. * - mxactFullScanLimit is a value against which a table's
relminmxidvalue is * compared to produce a full-table vacuum, as with xidFullScanLimit.
+ * - aggressive is set if it is not NULL and set true if the table needs
+ * aggressive scan.
+ * - close_to_wrap_around_limit is set if it is not NULL and set true if it is
+ * in anti-anti-wraparound window. * * xidFullScanLimit and mxactFullScanLimit can be passed as NULL if caller is *
notinterested.
@@ -599,9 +603,11 @@ vacuum_set_xid_limits(Relation rel, TransactionId *freezeLimit,
TransactionId *xidFullScanLimit, MultiXactId *multiXactCutoff,
- MultiXactId *mxactFullScanLimit)
+ MultiXactId *mxactFullScanLimit,
+ bool *aggressive, bool *close_to_wrap_around_limit){ int freezemin;
+ int freezemax; int mxid_freezemin; int effective_multixact_freeze_max_age;
TransactionId limit;
@@ -701,11 +707,13 @@ vacuum_set_xid_limits(Relation rel, *multiXactCutoff = mxactLimit;
- if (xidFullScanLimit != NULL)
+ if (xidFullScanLimit != NULL || aggressive != NULL) { int freezetable;
+ bool maybe_anti_wrapround = false;
- Assert(mxactFullScanLimit != NULL);
+ /* these two output should be requested together */
+ Assert(xidFullScanLimit == NULL || mxactFullScanLimit != NULL); /* * Determine the table freeze
ageto use: as specified by the caller,
@@ -717,7 +725,14 @@ vacuum_set_xid_limits(Relation rel, freezetable = freeze_table_age; if (freezetable
<0) freezetable = vacuum_freeze_table_age;
- freezetable = Min(freezetable, autovacuum_freeze_max_age * 0.95);
+
+ freezemax = autovacuum_freeze_max_age * 0.95;
+ if (freezemax < freezetable)
+ {
+ /* We may be in anti-anti-warparound window */
+ freezetable = freezemax;
+ maybe_anti_wrapround = true;
+ } Assert(freezetable >= 0); /*
@@ -728,7 +743,8 @@ vacuum_set_xid_limits(Relation rel, if (!TransactionIdIsNormal(limit)) limit =
FirstNormalTransactionId;
- *xidFullScanLimit = limit;
+ if (xidFullScanLimit)
+ *xidFullScanLimit = limit; /* * Similar to the above, determine the table freeze age to use
for
@@ -741,10 +757,20 @@ vacuum_set_xid_limits(Relation rel, freezetable = multixact_freeze_table_age; if
(freezetable< 0) freezetable = vacuum_multixact_freeze_table_age;
- freezetable = Min(freezetable,
- effective_multixact_freeze_max_age * 0.95);
+
+ freezemax = effective_multixact_freeze_max_age * 0.95;
+ if (freezemax < freezetable)
+ {
+ /* We may be in anti-anti-warparound window */
+ freezetable = freezemax;
+ maybe_anti_wrapround = true;
+ } Assert(freezetable >= 0);
+ /* We may be in anti-anti-warparound window */
+ if (effective_multixact_freeze_max_age * 0.95 < freezetable)
+ maybe_anti_wrapround = true;
+ /* * Compute MultiXact limit causing a full-table vacuum, being careful * to generate a valid
MultiXactvalue.
@@ -753,11 +779,38 @@ vacuum_set_xid_limits(Relation rel, if (mxactLimit < FirstMultiXactId)
mxactLimit= FirstMultiXactId;
- *mxactFullScanLimit = mxactLimit;
+ if (mxactFullScanLimit)
+ *mxactFullScanLimit = mxactLimit;
+
+ /*
+ * We request an aggressive scan if the table's frozen Xid is now
+ * older than or equal to the requested Xid full-table scan limit; or
+ * if the table's minimum MultiXactId is older than or equal to the
+ * requested mxid full-table scan limit.
+ */
+ if (aggressive)
+ {
+ *aggressive =
+ TransactionIdPrecedesOrEquals(rel->rd_rel->relfrozenxid,
+ limit);
+ *aggressive |=
+ MultiXactIdPrecedesOrEquals(rel->rd_rel->relminmxid,
+ mxactLimit);
+
+ /* set close_to_wrap_around_limit if requested */
+ if (close_to_wrap_around_limit)
+ *close_to_wrap_around_limit =
+ (*aggressive && maybe_anti_wrapround);
+ }
+ else
+ {
+ Assert (!close_to_wrap_around_limit);
+ } } else { Assert(mxactFullScanLimit == NULL);
+ Assert(aggressive == NULL); }}
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index c482c8e..4274043 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -227,18 +227,10 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
params->multixact_freeze_min_age, params->multixact_freeze_table_age,
&OldestXmin,&FreezeLimit, &xidFullScanLimit,
- &MultiXactCutoff, &mxactFullScanLimit);
+ &MultiXactCutoff, &mxactFullScanLimit,
+ &aggressive, NULL);
- /*
- * We request an aggressive scan if the table's frozen Xid is now older
- * than or equal to the requested Xid full-table scan limit; or if the
- * table's minimum MultiXactId is older than or equal to the requested
- * mxid full-table scan limit; or if DISABLE_PAGE_SKIPPING was specified.
- */
- aggressive = TransactionIdPrecedesOrEquals(onerel->rd_rel->relfrozenxid,
- xidFullScanLimit);
- aggressive |= MultiXactIdPrecedesOrEquals(onerel->rd_rel->relminmxid,
- mxactFullScanLimit);
+ /* force aggressive scan if DISABLE_PAGE_SKIPPING was specified */ if (options & VACOPT_DISABLE_PAGE_SKIPPING)
aggressive = true;
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 48765bb..abbf660 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -831,6 +831,121 @@ shutdown:}/*
+ * Returns status string of auto vacuum on the relation
+ */
+char *
+AutoVacuumRequirement(Oid reloid)
+{
+ Relation classRel;
+ Relation rel;
+ TupleDesc pg_class_desc;
+ HeapTuple tuple;
+ Form_pg_class classForm;
+ AutoVacOpts *relopts;
+ PgStat_StatTabEntry *tabentry;
+ PgStat_StatDBEntry *shared;
+ PgStat_StatDBEntry *dbentry;
+ int effective_multixact_freeze_max_age;
+ bool dovacuum;
+ bool doanalyze;
+ bool wraparound;
+ bool aggressive;
+ bool xid_calculated = false;
+ bool in_anti_wa_window = false;
+ char *ret = "not requried";
+
+ /* Compute the multixact age for which freezing is urgent. */
+ effective_multixact_freeze_max_age = MultiXactMemberFreezeThreshold();
+
+ /* Fetch the pgclass entry for this relation */
+ tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(reloid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u", reloid);
+ classForm = (Form_pg_class) GETSTRUCT(tuple);
+
+ /* extract relopts for autovacuum */
+ classRel = heap_open(RelationRelationId, AccessShareLock);
+ pg_class_desc = RelationGetDescr(classRel);
+ relopts = extract_autovac_opts(tuple, pg_class_desc);
+ heap_close(classRel, AccessShareLock);
+
+ /* Fetch the pgstat shared entry and entry for this database */
+ shared = pgstat_fetch_stat_dbentry(InvalidOid);
+ dbentry = pgstat_fetch_stat_dbentry(MyDatabaseId);
+
+ /* Fetch the pgstat entry for this table */
+ tabentry = get_pgstat_tabentry_relid(reloid, classForm->relisshared,
+ shared, dbentry);
+
+ /*
+ * Check if the relation needs vacuum. This function is intended to
+ * suggest aggresive vacuum for the last 5% window in
+ * autovacuum_freeze_max_age so the variable wraparound is ignored
+ * here. See vacuum_set_xid_limits for details.
+ */
+ relation_needs_vacanalyze(reloid, relopts, classForm, tabentry,
+ effective_multixact_freeze_max_age,
+ &dovacuum, &doanalyze, &wraparound);
+ ReleaseSysCache(tuple);
+
+ /* get further information if needed */
+ rel = NULL;
+
+ /* don't get stuck with lock */
+ if (ConditionalLockRelationOid(reloid, AccessShareLock))
+ rel = try_relation_open(reloid, NoLock);
+
+ if (rel)
+ {
+ TransactionId OldestXmin, FreezeLimit;
+ MultiXactId MultiXactCutoff;
+
+ vacuum_set_xid_limits(rel,
+ vacuum_freeze_min_age,
+ vacuum_freeze_table_age,
+ vacuum_multixact_freeze_min_age,
+ vacuum_multixact_freeze_table_age,
+ &OldestXmin, &FreezeLimit, NULL,
+ &MultiXactCutoff, NULL,
+ &aggressive, &in_anti_wa_window);
+
+ xid_calculated = true;
+ relation_close(rel, AccessShareLock);
+ }
+
+ /* choose the proper message according to the calculation above */
+ if (xid_calculated)
+ {
+ if (dovacuum)
+ {
+ /* we don't care anti-wraparound if autovacuum is on */
+ if (aggressive)
+ ret = "aggressive";
+ else
+ ret = "partial";
+ }
+ else if (in_anti_wa_window)
+ ret = "close to freeze-limit xid";
+ /* otherwise just "not requried" */
+ }
+ else
+ {
+ /*
+ * failed to compute xid limits. show less-grained messages. We can
+ * use just "required" in the autovacuum case is enough to distinguish
+ * from full-grained messages, but we require additional words in the
+ * case where autovacuum is turned off.
+ */
+ if (dovacuum)
+ ret = "required";
+ else
+ ret = "not required (lock not acquired)";
+ }
+
+ return ret;
+}
+
+/* * Determine the time to sleep, based on the database list. * * The "canlaunch" parameter indicates whether we can
starta worker right now,
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 2956356..ab80794 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -23,6 +23,7 @@#include "pgstat.h"#include "postmaster/bgworker_internals.h"#include "postmaster/postmaster.h"
+#include "postmaster/autovacuum.h"#include "storage/proc.h"#include "storage/procarray.h"#include "utils/acl.h"
@@ -195,6 +196,14 @@ pg_stat_get_mod_since_analyze(PG_FUNCTION_ARGS)}Datum
+pg_stat_get_vacuum_necessity(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+
+ PG_RETURN_TEXT_P(cstring_to_text(AutoVacuumRequirement(relid)));
+}
+
+Datumpg_stat_get_last_vacuum_index_scans(PG_FUNCTION_ARGS){ Oid relid = PG_GETARG_OID(0);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f3b606b..6b84c9a 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2887,6 +2887,8 @@ DATA(insert OID = 3317 ( pg_stat_get_wal_receiver PGNSP PGUID 12 1 0 0 0 f f
fDESCR("statistics:information about WAL receiver");DATA(insert OID = 6118 ( pg_stat_get_subscription PGNSP PGUID
121 0 0 0 f f f f f f s r 1 0 2249 "26" "{26,26,26,23,3220,1184,1184,3220,1184}" "{i,o,o,o,o,o,o,o,o}"
"{subid,subid,relid,pid,received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time}"_null_
_null_pg_stat_get_subscription _null_ _null_ _null_ ));DESCR("statistics: information about subscription");
+DATA(insert OID = 2579 ( pg_stat_get_vacuum_necessity PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 25 "26" _null_
_null__null_ _null_ _null_ pg_stat_get_vacuum_necessity _null_ _null_ _null_ ));
+DESCR("statistics: true if needs vacuum");DATA(insert OID = 3281 ( pg_stat_get_last_vacuum_index_scans PGNSP PGUID
121 0 0 0 f f f f t f s r 1 0 23 "26" _null_ _null_ _null_ _null_ _null_ pg_stat_get_last_vacuum_index_scans _null_
_null__null_ ));DESCR("statistics: number of index scans in the last vacuum");DATA(insert OID = 2026 ( pg_backend_pid
PGNSP PGUID 12 1 0 0 0 f f f f t f s r 0 0 23 "" _null_ _null_ _null_ _null_ _null_ pg_backend_pid _null_
_null__null_ ));
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 60586b2..84bec74 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -182,7 +182,8 @@ extern void vacuum_set_xid_limits(Relation rel, TransactionId *freezeLimit,
TransactionId *xidFullScanLimit, MultiXactId *multiXactCutoff,
- MultiXactId *mxactFullScanLimit);
+ MultiXactId *mxactFullScanLimit,
+ bool *aggressive, bool *in_wa_window);extern void vac_update_datfrozenxid(void);extern void
vacuum_delay_point(void);
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index 3469915..848a322 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -49,6 +49,7 @@ extern int Log_autovacuum_min_duration;extern bool AutoVacuumingActive(void);extern bool
IsAutoVacuumLauncherProcess(void);externbool IsAutoVacuumWorkerProcess(void);
+extern char *AutoVacuumRequirement(Oid reloid);#define IsAnyAutoVacuumProcess() \ (IsAutoVacuumLauncherProcess() ||
IsAutoVacuumWorkerProcess())
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index c334d20..2144269 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1759,6 +1759,7 @@ pg_stat_all_tables| SELECT c.oid AS relid, 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,
+ pg_stat_get_vacuum_necessity(c.oid) AS vacuum_required, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum,
pg_stat_get_last_autovacuum_time(c.oid)AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze,
@@ -1907,6 +1908,7 @@ pg_stat_sys_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.n_live_tup,
pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.n_mod_since_analyze,
+ pg_stat_all_tables.vacuum_required, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum,
pg_stat_all_tables.last_analyze,
@@ -1951,6 +1953,7 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.n_live_tup,
pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.n_mod_since_analyze,
+ pg_stat_all_tables.vacuum_required, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum,
pg_stat_all_tables.last_analyze,
--
2.9.2
From 8c76950d665cabd095b5eed34ad25854eb2dd5a0 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyotaro@lab.ntt.co.jp>
Date: Thu, 16 Nov 2017 17:05:21 +0900
Subject: [PATCH 3/4] Add vacuum execution status in pg_stat_all_tables
The main objective of this patch is showing how vacuuming is
failing. It is sometimes very hard to diagnose since autovacuum stops
silently in most cases. This patch leaves the reason for vacuum
failure in pg_stat_all_tables and how many times it is continuing to
fail.
---doc/src/sgml/monitoring.sgml | 10 +++++src/backend/catalog/system_views.sql | 2
+src/backend/commands/vacuum.c | 33 ++++++++++++++src/backend/commands/vacuumlazy.c | 72
++++++++++++++++++++++++++++++-src/backend/postmaster/pgstat.c | 62
+++++++++++++++++++++------src/backend/utils/adt/pgstatfuncs.c | 83
++++++++++++++++++++++++++++++++++++src/include/catalog/pg_proc.h | 4 ++src/include/commands/vacuum.h |
1+src/include/pgstat.h | 25 ++++++++++-src/test/regress/expected/rules.out | 6 +++10 files changed,
283insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index e8a8f77..e2bf2d2 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2586,6 +2586,16 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
<entry>Numberof splitted index scans performed during the last vacuum on this table</entry> </row> <row>
+ <entry><structfield>last_vacuum_status</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry>The status of last autovacuum.</entry>
+ </row>
+ <row>
+ <entry><structfield>autovacuum_fail_count</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry>The number of continuously failed vacuum trials. Cleared to zero if completed.</entry>
+ </row>
+ <row> <entry><structfield>autovacuum_count</structfield></entry> <entry><type>bigint</type></entry>
<entry>Numberof times this table has been vacuumed by the autovacuum
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 97bafb8..cd0ea69 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -530,6 +530,8 @@ CREATE VIEW pg_stat_all_tables AS pg_stat_get_last_autoanalyze_time(C.oid) as
last_autoanalyze, pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
pg_stat_get_last_vacuum_index_scans(C.oid)AS last_vacuum_index_scans,
+ pg_stat_get_last_vacuum_status(C.oid) AS last_vacuum_status,
+ pg_stat_get_autovacuum_fail_count(C.oid) AS autovacuum_fail_count,
pg_stat_get_autovacuum_count(C.oid)AS autovacuum_count, pg_stat_get_analyze_count(C.oid) AS analyze_count,
pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index f51dcdb..ac7c2ac 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -35,6 +35,7 @@#include "catalog/pg_inherits_fn.h"#include "catalog/pg_namespace.h"#include "commands/cluster.h"
+#include "commands/progress.h"#include "commands/vacuum.h"#include "miscadmin.h"#include "nodes/makefuncs.h"
@@ -367,6 +368,9 @@ vacuum(int options, List *relations, VacuumParams *params, } PG_CATCH(); {
+ /* report the final status of this vacuum */
+ lazy_vacuum_cancel_handler();
+ in_vacuum = false; VacuumCostActive = false; PG_RE_THROW();
@@ -1463,6 +1467,8 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) if (!onerel)
{
+ pgstat_report_vacuum(relid, false, 0, 0, 0,
+ PGSTAT_VACUUM_SKIP_LOCK_FAILED, 0, 0); PopActiveSnapshot();
CommitTransactionCommand(); return false;
@@ -1494,6 +1500,11 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params)
(errmsg("skipping\"%s\" --- only table or database owner can vacuum it",
RelationGetRelationName(onerel)))); relation_close(onerel, lmode);
+
+ pgstat_report_vacuum(RelationGetRelid(onerel),
+ onerel->rd_rel->relisshared,
+ 0, 0, 0, PGSTAT_VACUUM_SKIP_NONTARGET, 0, 0);
+ PopActiveSnapshot(); CommitTransactionCommand(); return false;
@@ -1511,6 +1522,12 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params)
(errmsg("skipping\"%s\" --- cannot vacuum non-tables or special system tables",
RelationGetRelationName(onerel)))); relation_close(onerel, lmode);
+
+ pgstat_report_vacuum(RelationGetRelid(onerel),
+ onerel->rd_rel->relisshared,
+ 0, 0, 0,
+ PGSTAT_VACUUM_SKIP_NONTARGET, 0, 0);
+ PopActiveSnapshot(); CommitTransactionCommand(); return false;
@@ -1526,6 +1543,12 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) if
(RELATION_IS_OTHER_TEMP(onerel)) { relation_close(onerel, lmode);
+
+ pgstat_report_vacuum(RelationGetRelid(onerel),
+ onerel->rd_rel->relisshared,
+ 0, 0, 0,
+ PGSTAT_VACUUM_SKIP_NONTARGET, 0, 0);
+ PopActiveSnapshot(); CommitTransactionCommand(); return false;
@@ -1539,6 +1562,12 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) if
(onerel->rd_rel->relkind== RELKIND_PARTITIONED_TABLE) { relation_close(onerel, lmode);
+
+ pgstat_report_vacuum(RelationGetRelid(onerel),
+ onerel->rd_rel->relisshared,
+ 0, 0, 0,
+ PGSTAT_VACUUM_SKIP_NONTARGET, 0, 0);
+ PopActiveSnapshot(); CommitTransactionCommand(); /* It's OK to proceed with ANALYZE on this
table*/
@@ -1584,6 +1613,8 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) */ if
(options& VACOPT_FULL) {
+ bool isshared = onerel->rd_rel->relisshared;
+ /* close relation before vacuuming, but hold lock until commit */ relation_close(onerel, NoLock);
onerel = NULL;
@@ -1591,6 +1622,8 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) /* VACUUM
FULLis now a variant of CLUSTER; see cluster.c */ cluster_rel(relid, InvalidOid, false,
(options& VACOPT_VERBOSE) != 0);
+ pgstat_report_vacuum(relid, isshared, 0, 0, 0,
+ PGSTAT_VACUUM_FULL_FINISHED, 0, 0); } else lazy_vacuum_rel(onerel, options,
params,vac_strategy);
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 4274043..af38962 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -105,6 +105,8 @@typedef struct LVRelStats{
+ Oid reloid; /* oid of the target relation */
+ bool shared; /* is shared relation? */ /* hasindex = true means two-pass strategy; false
meansone-pass */ bool hasindex; /* Overall statistics about rel */
@@ -138,6 +140,7 @@ static int elevel = -1;static TransactionId OldestXmin;static TransactionId FreezeLimit;static
MultiXactIdMultiXactCutoff;
+static LVRelStats *current_lvstats;static BufferAccessStrategy vac_strategy;
@@ -216,6 +219,7 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params, else elevel =
DEBUG2;
+ current_lvstats = NULL; pgstat_progress_start_command(PROGRESS_COMMAND_VACUUM,
RelationGetRelid(onerel));
@@ -236,12 +240,20 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params, vacrelstats = (LVRelStats
*)palloc0(sizeof(LVRelStats));
+ vacrelstats->reloid = RelationGetRelid(onerel);
+ vacrelstats->shared = onerel->rd_rel->relisshared; vacrelstats->old_rel_pages = onerel->rd_rel->relpages;
vacrelstats->old_rel_tuples= onerel->rd_rel->reltuples; vacrelstats->num_index_scans = 0;
vacrelstats->pages_removed= 0; vacrelstats->lock_waiter_detected = false;
+ /*
+ * Register current vacrelstats so that final status can be reported on
+ * interrupts
+ */
+ current_lvstats = vacrelstats;
+ /* Open all indexes of the relation */ vac_open_indexes(onerel, RowExclusiveLock, &nindexes, &Irel);
vacrelstats->hasindex= (nindexes > 0);
@@ -331,11 +343,19 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params, if (new_live_tuples < 0)
new_live_tuples = 0; /* just in case */
- pgstat_report_vacuum(RelationGetRelid(onerel),
+ /* vacuum successfully finished. nothing to do on exit */
+ current_lvstats = NULL;
+
+ pgstat_report_vacuum(vacrelstats->reloid, onerel->rd_rel->relisshared,
new_live_tuples, vacrelstats->new_dead_tuples,
- vacrelstats->num_index_scans);
+ vacrelstats->num_index_scans,
+ aggressive ?
+ PGSTAT_VACUUM_AGGRESSIVE_FINISHED :
+ PGSTAT_VACUUM_FINISHED,
+ 0, 0);
+ pgstat_progress_end_command(); /* and log the action if appropriate */
@@ -2198,3 +2218,51 @@ heap_page_is_all_visible(Relation rel, Buffer buf, return all_visible;}
+
+/*
+ * lazy_vacuum_cancel_handler - report interrupted vacuum status
+ */
+void
+lazy_vacuum_cancel_handler(void)
+{
+ LVRelStats *stats = current_lvstats;
+ LocalPgBackendStatus *local_beentry;
+ PgBackendStatus *beentry;
+ int phase;
+ int err;
+
+ current_lvstats = NULL;
+
+ /* we have nothing to report */
+ if (!stats)
+ return;
+
+ /* get vacuum progress stored in backend status */
+ local_beentry = pgstat_fetch_stat_local_beentry(MyBackendId);
+ if (!local_beentry)
+ return;
+
+ beentry = &local_beentry->backendStatus;
+
+ Assert (beentry && beentry->st_progress_command == PROGRESS_COMMAND_VACUUM);
+
+ phase = beentry->st_progress_param[PROGRESS_VACUUM_PHASE];
+
+ /* we can reach here both on interrupt and error */
+ if (geterrcode() == ERRCODE_QUERY_CANCELED)
+ err = PGSTAT_VACUUM_CANCELED;
+ else
+ err = PGSTAT_VACUUM_ERROR;
+
+ /*
+ * vacuum has been canceled, report stats numbers without normalization
+ * here. (But currently they are not used.)
+ */
+ pgstat_report_vacuum(stats->reloid,
+ stats->shared,
+ stats->new_rel_tuples,
+ stats->new_dead_tuples,
+ stats->num_index_scans,
+ err,
+ phase, geterrcode());
+}
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 5f3fdf6..540c580 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -1404,7 +1404,9 @@ pgstat_report_autovac(Oid dboid)voidpgstat_report_vacuum(Oid tableoid, bool shared,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
- PgStat_Counter num_index_scans)
+ PgStat_Counter num_index_scans,
+ PgStat_Counter status, PgStat_Counter last_phase,
+ PgStat_Counter errcode){ PgStat_MsgVacuum msg;
@@ -1419,6 +1421,9 @@ pgstat_report_vacuum(Oid tableoid, bool shared, msg.m_live_tuples = livetuples;
msg.m_dead_tuples= deadtuples; msg.m_num_index_scans = num_index_scans;
+ msg.m_vacuum_status = status;
+ msg.m_vacuum_last_phase = last_phase;
+ msg.m_vacuum_errcode = errcode; pgstat_send(&msg, sizeof(msg));}
@@ -4598,6 +4603,11 @@ pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create)
result->analyze_count= 0; result->autovac_analyze_timestamp = 0; result->autovac_analyze_count = 0;
+
+ result->vacuum_status = 0;
+ result->vacuum_last_phase = 0;
+ result->vacuum_errcode = 0;
+ result->vacuum_failcount = 0; } return result;
@@ -5982,19 +5992,47 @@ pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len) tabentry = pgstat_get_tab_entry(dbentry,
msg->m_tableoid,true);
- tabentry->n_live_tuples = msg->m_live_tuples;
- tabentry->n_dead_tuples = msg->m_dead_tuples;
- tabentry->n_index_scans = msg->m_num_index_scans;
+ tabentry->vacuum_status = msg->m_vacuum_status;
+ tabentry->vacuum_last_phase = msg->m_vacuum_last_phase;
+ tabentry->vacuum_errcode = msg->m_vacuum_errcode;
- if (msg->m_autovacuum)
- {
- tabentry->autovac_vacuum_timestamp = msg->m_vacuumtime;
- tabentry->autovac_vacuum_count++;
- }
- else
+ /*
+ * We store the numbers only when the vacuum has been completed. They
+ * might be usable to find how much the stopped vacuum processed but we
+ * choose not to show them rather than show bogus numbers.
+ */
+ switch ((StatVacuumStatus)msg->m_vacuum_status) {
- tabentry->vacuum_timestamp = msg->m_vacuumtime;
- tabentry->vacuum_count++;
+ case PGSTAT_VACUUM_FINISHED:
+ case PGSTAT_VACUUM_FULL_FINISHED:
+ case PGSTAT_VACUUM_AGGRESSIVE_FINISHED:
+ tabentry->n_live_tuples = msg->m_live_tuples;
+ tabentry->n_dead_tuples = msg->m_dead_tuples;
+ tabentry->n_index_scans = msg->m_num_index_scans;
+ tabentry->vacuum_failcount = 0;
+
+ if (msg->m_autovacuum)
+ {
+ tabentry->autovac_vacuum_timestamp = msg->m_vacuumtime;
+ tabentry->autovac_vacuum_count++;
+ }
+ else
+ {
+ tabentry->vacuum_timestamp = msg->m_vacuumtime;
+ tabentry->vacuum_count++;
+ }
+ break;
+
+ case PGSTAT_VACUUM_ERROR:
+ case PGSTAT_VACUUM_CANCELED:
+ case PGSTAT_VACUUM_SKIP_LOCK_FAILED:
+ tabentry->vacuum_failcount++;
+ break;
+
+ case PGSTAT_VACUUM_SKIP_NONTARGET:
+ default:
+ /* don't increment failure count for non-target tables */
+ break; }}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index ab80794..bc5d370 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -219,6 +219,89 @@ pg_stat_get_last_vacuum_index_scans(PG_FUNCTION_ARGS)}Datum
+pg_stat_get_last_vacuum_status(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ char *result = "unknown";
+ PgStat_StatTabEntry *tabentry;
+
+ /*
+ * status string. this must be synced with the strings shown by the
+ * statistics view "pg_stat_progress_vacuum"
+ */
+ static char *phasestr[] =
+ {"initialization",
+ "scanning heap",
+ "vacuuming indexes",
+ "vacuuming heap",
+ "cleaning up indexes",
+ "trucating heap",
+ "performing final cleanup"};
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) != NULL)
+ {
+ int phase;
+ StatVacuumStatus status;
+
+ status = tabentry->vacuum_status;
+ switch (status)
+ {
+ case PGSTAT_VACUUM_FINISHED:
+ result = "completed";
+ break;
+ case PGSTAT_VACUUM_ERROR:
+ case PGSTAT_VACUUM_CANCELED:
+ phase = tabentry->vacuum_last_phase;
+ /* number of elements of phasestr above */
+ if (phase >= 0 && phase <= 7)
+ result = psprintf("%s while %s",
+ status == PGSTAT_VACUUM_CANCELED ?
+ "canceled" : "error",
+ phasestr[phase]);
+ else
+ result = psprintf("unknown vacuum phase: %d", phase);
+ break;
+ case PGSTAT_VACUUM_SKIP_LOCK_FAILED:
+ result = "skipped - lock unavailable";
+ break;
+
+ case PGSTAT_VACUUM_AGGRESSIVE_FINISHED:
+ result = "aggressive vacuum completed";
+ break;
+
+ case PGSTAT_VACUUM_FULL_FINISHED:
+ result = "vacuum full completed";
+ break;
+
+ case PGSTAT_VACUUM_SKIP_NONTARGET:
+ result = "unvacuumable";
+ break;
+
+ default:
+ result = "unknown status";
+ break;
+ }
+ }
+
+ PG_RETURN_TEXT_P(cstring_to_text(result));
+}
+
+Datum
+pg_stat_get_autovacuum_fail_count(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ result = 0;
+ else
+ result = (int32) (tabentry->vacuum_failcount);
+
+ PG_RETURN_INT32(result);
+}
+
+Datumpg_stat_get_blocks_fetched(PG_FUNCTION_ARGS){ Oid relid = PG_GETARG_OID(0);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 6b84c9a..a51e321 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2891,6 +2891,10 @@ DATA(insert OID = 2579 ( pg_stat_get_vacuum_necessity PGNSP PGUID 12 1 0 0 0
fDESCR("statistics:true if needs vacuum");DATA(insert OID = 3281 ( pg_stat_get_last_vacuum_index_scans PGNSP PGUID
121 0 0 0 f f f f t f s r 1 0 23 "26" _null_ _null_ _null_ _null_ _null_ pg_stat_get_last_vacuum_index_scans _null_
_null__null_ ));DESCR("statistics: number of index scans in the last vacuum");
+DATA(insert OID = 3420 ( pg_stat_get_last_vacuum_status PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 25 "26" _null_
_null__null_ _null_ _null_ pg_stat_get_last_vacuum_status _null_ _null_ _null_ ));
+DESCR("statistics: ending status of the last vacuum");
+DATA(insert OID = 3421 ( pg_stat_get_autovacuum_fail_count PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 23 "26"
_null__null_ _null_ _null_ _null_ pg_stat_get_autovacuum_fail_count _null_ _null_ _null_ ));
+DESCR("statistics: number of successively failed vacuum trials");DATA(insert OID = 2026 ( pg_backend_pid
PGNSP PGUID 12 1 0 0 0 f f f f t f s r 0 0 23 "" _null_ _null_ _null_ _null_ _null_ pg_backend_pid _null_ _null_
_null_));DESCR("statistics: current backend PID");DATA(insert OID = 1937 ( pg_stat_get_backend_pid PGNSP PGUID
121 0 0 0 f f f f t f s r 1 0 23 "23" _null_ _null_ _null_ _null_ _null_ pg_stat_get_backend_pid _null_ _null_ _null_
));
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 84bec74..da3107a 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -190,6 +190,7 @@ extern void vacuum_delay_point(void);/* in commands/vacuumlazy.c */extern void
lazy_vacuum_rel(Relationonerel, int options, VacuumParams *params, BufferAccessStrategy bstrategy);
+extern void lazy_vacuum_cancel_handler(void);/* in commands/analyze.c */extern void analyze_rel(Oid relid, RangeVar
*relation,int options,
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 3ab5f4a..62c2369 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -67,6 +67,20 @@ typedef enum StatMsgType PGSTAT_MTYPE_DEADLOCK} StatMsgType;
+/*
+ * The exit status stored in vacuum report.
+ */
+typedef enum StatVacuumStatus
+{
+ PGSTAT_VACUUM_FINISHED,
+ PGSTAT_VACUUM_CANCELED,
+ PGSTAT_VACUUM_ERROR,
+ PGSTAT_VACUUM_SKIP_LOCK_FAILED,
+ PGSTAT_VACUUM_SKIP_NONTARGET,
+ PGSTAT_VACUUM_AGGRESSIVE_FINISHED,
+ PGSTAT_VACUUM_FULL_FINISHED
+} StatVacuumStatus;
+/* ---------- * The data type used for counters. * ----------
@@ -370,6 +384,9 @@ typedef struct PgStat_MsgVacuum PgStat_Counter m_live_tuples; PgStat_Counter m_dead_tuples;
PgStat_Counter m_num_index_scans;
+ PgStat_Counter m_vacuum_status;
+ PgStat_Counter m_vacuum_last_phase;
+ PgStat_Counter m_vacuum_errcode;} PgStat_MsgVacuum;
@@ -643,6 +660,10 @@ typedef struct PgStat_StatTabEntry PgStat_Counter analyze_count; TimestampTz
autovac_analyze_timestamp; /* autovacuum initiated */ PgStat_Counter autovac_analyze_count;
+ PgStat_Counter vacuum_status;
+ PgStat_Counter vacuum_last_phase;
+ PgStat_Counter vacuum_errcode;
+ PgStat_Counter vacuum_failcount;} PgStat_StatTabEntry;
@@ -1168,7 +1189,9 @@ extern void pgstat_reset_single_counter(Oid objectid, PgStat_Single_Reset_Type textern void
pgstat_report_autovac(Oiddboid);extern void pgstat_report_vacuum(Oid tableoid, bool shared,
PgStat_Counterlivetuples, PgStat_Counter deadtuples,
- PgStat_Counter num_index_scans);
+ PgStat_Counter num_index_scans,
+ PgStat_Counter status, PgStat_Counter last_phase,
+ PgStat_Counter errcode);extern void pgstat_report_analyze(Relation rel,
PgStat_Counterlivetuples, PgStat_Counter deadtuples, bool resetcounter);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2144269..f0a8416 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1766,6 +1766,8 @@ pg_stat_all_tables| SELECT c.oid AS relid, pg_stat_get_last_autoanalyze_time(c.oid) AS
last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_last_vacuum_index_scans(c.oid) AS
last_vacuum_index_scans,
+ pg_stat_get_last_vacuum_status(c.oid) AS last_vacuum_status,
+ pg_stat_get_autovacuum_fail_count(c.oid) AS autovacuum_fail_count, pg_stat_get_autovacuum_count(c.oid) AS
autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS
autoanalyze_count
@@ -1915,6 +1917,8 @@ pg_stat_sys_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.last_autoanalyze,
pg_stat_all_tables.vacuum_count, pg_stat_all_tables.last_vacuum_index_scans,
+ pg_stat_all_tables.last_vacuum_status,
+ pg_stat_all_tables.autovacuum_fail_count, pg_stat_all_tables.autovacuum_count,
pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count
@@ -1960,6 +1964,8 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.last_autoanalyze,
pg_stat_all_tables.vacuum_count, pg_stat_all_tables.last_vacuum_index_scans,
+ pg_stat_all_tables.last_vacuum_status,
+ pg_stat_all_tables.autovacuum_fail_count, pg_stat_all_tables.autovacuum_count,
pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count
--
2.9.2
From 6052a2e9c0c01e53fa083f9e63e1cee610ae09a0 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horiguchi.kyotaro@lab.ntt.co.jp>
Date: Thu, 16 Nov 2017 17:47:16 +0900
Subject: [PATCH 4/4] Add truncation information and oldestxmin topg_stat_all_tables
This patch adds truncated and tried-but-not-truncated pages in the
last vacuum. This is intended to use to find uncertain failure of
truncation or unwanted aggressive trancation.
This also adds oldestxmin, find whether a long transaction hindered
vacuuming or not.
---doc/src/sgml/monitoring.sgml | 15 ++++++++++++src/backend/catalog/system_views.sql | 3
+++src/backend/commands/vacuum.c | 25 +++++++++++---------src/backend/commands/vacuumlazy.c | 15
++++++++++++src/backend/postmaster/pgstat.c | 12 ++++++++++src/backend/utils/adt/pgstatfuncs.c | 45
++++++++++++++++++++++++++++++++++++src/include/catalog/pg_proc.h | 6 +++++src/include/pgstat.h
| 9 ++++++++src/test/regress/expected/rules.out | 9 ++++++++9 files changed, 128 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index e2bf2d2..d496fe8 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2581,11 +2581,26 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i (not
counting<command>VACUUM FULL</command>)</entry> </row> <row>
+ <entry><structfield>last_vacuum_truncated</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Number actually truncated pages during the last vacuum on this table</entry>
+ </row>
+ <row>
+ <entry><structfield>last_vacuum_untruncated</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Number tried but actually not truncated pages during the last vacuum on this table</entry>
+ </row>
+ <row> <entry><structfield>last_vacuum_index_scans</structfield></entry>
<entry><type>integer</type></entry> <entry>Number of splitted index scans performed during the last vacuum on this
table</entry> </row> <row>
+ <entry><structfield>last_vacuum_oldext_xmin</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry>The oldest xmin used by the last vacuum on this table</entry>
+ </row>
+ <row> <entry><structfield>last_vacuum_status</structfield></entry> <entry><type>text</type></entry>
<entry>Thestatus of last autovacuum.</entry>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index cd0ea69..0eb3a76 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -529,7 +529,10 @@ CREATE VIEW pg_stat_all_tables AS pg_stat_get_last_analyze_time(C.oid) as last_analyze,
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze, pg_stat_get_vacuum_count(C.oid) AS
vacuum_count,
+ pg_stat_get_last_vacuum_truncated(C.oid) AS last_vacuum_truncated,
+ pg_stat_get_last_vacuum_untruncated(C.oid) AS last_vacuum_untruncated,
pg_stat_get_last_vacuum_index_scans(C.oid)AS last_vacuum_index_scans,
+ pg_stat_get_last_vacuum_oldest_xmin(C.oid) AS last_vacuum_oldest_xmin,
pg_stat_get_last_vacuum_status(C.oid)AS last_vacuum_status, pg_stat_get_autovacuum_fail_count(C.oid) AS
autovacuum_fail_count, pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index ac7c2ac..a0c5a12 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1467,8 +1467,9 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) if (!onerel)
{
- pgstat_report_vacuum(relid, false, 0, 0, 0,
- PGSTAT_VACUUM_SKIP_LOCK_FAILED, 0, 0);
+ pgstat_report_vacuum(relid, false,
+ 0, 0, 0, 0, 0, PGSTAT_VACUUM_SKIP_LOCK_FAILED,
+ InvalidTransactionId, 0, 0); PopActiveSnapshot();
CommitTransactionCommand(); return false;
@@ -1503,7 +1504,8 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params)
pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared,
- 0, 0, 0, PGSTAT_VACUUM_SKIP_NONTARGET, 0, 0);
+ 0, 0, 0, 0, 0, PGSTAT_VACUUM_SKIP_NONTARGET,
+ InvalidTransactionId, 0, 0); PopActiveSnapshot();
CommitTransactionCommand();
@@ -1525,8 +1527,8 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params)
pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared,
- 0, 0, 0,
- PGSTAT_VACUUM_SKIP_NONTARGET, 0, 0);
+ 0, 0, 0, 0, 0, PGSTAT_VACUUM_SKIP_NONTARGET,
+ InvalidTransactionId, 0, 0); PopActiveSnapshot();
CommitTransactionCommand();
@@ -1546,8 +1548,8 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params)
pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared,
- 0, 0, 0,
- PGSTAT_VACUUM_SKIP_NONTARGET, 0, 0);
+ 0, 0, 0, 0, 0, PGSTAT_VACUUM_SKIP_NONTARGET,
+ InvalidTransactionId, 0, 0); PopActiveSnapshot();
CommitTransactionCommand();
@@ -1565,8 +1567,8 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params)
pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared,
- 0, 0, 0,
- PGSTAT_VACUUM_SKIP_NONTARGET, 0, 0);
+ 0, 0, 0, 0, 0, PGSTAT_VACUUM_SKIP_NONTARGET,
+ InvalidTransactionId, 0, 0); PopActiveSnapshot();
CommitTransactionCommand();
@@ -1622,8 +1624,9 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) /* VACUUM
FULLis now a variant of CLUSTER; see cluster.c */ cluster_rel(relid, InvalidOid, false,
(options& VACOPT_VERBOSE) != 0);
- pgstat_report_vacuum(relid, isshared, 0, 0, 0,
- PGSTAT_VACUUM_FULL_FINISHED, 0, 0);
+ pgstat_report_vacuum(relid, isshared, 0, 0, 0, 0, 0,
+ PGSTAT_VACUUM_FULL_FINISHED,
+ InvalidTransactionId, 0, 0); } else lazy_vacuum_rel(onerel, options, params,
vac_strategy);
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index af38962..fcd1e3e 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -121,6 +121,7 @@ typedef struct LVRelStats double new_rel_tuples; /* new estimated total # of tuples */
double new_dead_tuples; /* new estimated total # of dead tuples */ BlockNumber pages_removed;
+ BlockNumber pages_not_removed; double tuples_deleted; BlockNumber nonempty_pages; /* actually, last
nonemptypage + 1 */ /* List of TIDs of tuples we intend to delete */
@@ -246,6 +247,7 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params, vacrelstats->old_rel_tuples
=onerel->rd_rel->reltuples; vacrelstats->num_index_scans = 0; vacrelstats->pages_removed = 0;
+ vacrelstats->pages_not_removed = 0; vacrelstats->lock_waiter_detected = false; /*
@@ -284,8 +286,15 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params, * Optionally truncate the
relation. */ if (should_attempt_truncation(vacrelstats))
+ { lazy_truncate_heap(onerel, vacrelstats);
+ /* just paranoia */
+ if (vacrelstats->rel_pages >= vacrelstats->nonempty_pages)
+ vacrelstats->pages_not_removed +=
+ vacrelstats->rel_pages - vacrelstats->nonempty_pages;
+ }
+ /* Report that we are now doing final cleanup */ pgstat_progress_update_param(PROGRESS_VACUUM_PHASE,
PROGRESS_VACUUM_PHASE_FINAL_CLEANUP);
@@ -350,7 +359,10 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
onerel->rd_rel->relisshared, new_live_tuples,
vacrelstats->new_dead_tuples,
+ vacrelstats->pages_removed,
+ vacrelstats->pages_not_removed, vacrelstats->num_index_scans,
+ OldestXmin, aggressive ?
PGSTAT_VACUUM_AGGRESSIVE_FINISHED: PGSTAT_VACUUM_FINISHED,
@@ -2262,7 +2274,10 @@ lazy_vacuum_cancel_handler(void) stats->shared,
stats->new_rel_tuples, stats->new_dead_tuples,
+ stats->pages_removed,
+ stats->pages_not_removed, stats->num_index_scans,
+ OldestXmin, err, phase, geterrcode());}
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 540c580..2d3a6ae 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -1404,7 +1404,10 @@ pgstat_report_autovac(Oid dboid)voidpgstat_report_vacuum(Oid tableoid, bool shared,
PgStat_Counter livetuples, PgStat_Counter deadtuples,
+ PgStat_Counter pages_removed,
+ PgStat_Counter pages_not_removed, PgStat_Counter num_index_scans,
+ TransactionId oldestxmin, PgStat_Counter status, PgStat_Counter
last_phase, PgStat_Counter errcode){
@@ -1420,7 +1423,10 @@ pgstat_report_vacuum(Oid tableoid, bool shared, msg.m_vacuumtime = GetCurrentTimestamp();
msg.m_live_tuples= livetuples; msg.m_dead_tuples = deadtuples;
+ msg.m_pages_removed = pages_removed;
+ msg.m_pages_not_removed = pages_not_removed; msg.m_num_index_scans = num_index_scans;
+ msg.m_oldest_xmin = oldestxmin; msg.m_vacuum_status = status; msg.m_vacuum_last_phase = last_phase;
msg.m_vacuum_errcode= errcode;
@@ -4592,7 +4598,10 @@ pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create)
result->n_live_tuples= 0; result->n_dead_tuples = 0; result->changes_since_analyze = 0;
+ result->n_pages_removed = 0;
+ result->n_pages_not_removed = 0; result->n_index_scans = 0;
+ result->oldest_xmin = InvalidTransactionId; result->blocks_fetched = 0; result->blocks_hit = 0;
result->vacuum_timestamp = 0;
@@ -6008,7 +6017,10 @@ pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len) case PGSTAT_VACUUM_AGGRESSIVE_FINISHED:
tabentry->n_live_tuples = msg->m_live_tuples; tabentry->n_dead_tuples = msg->m_dead_tuples;
+ tabentry->n_pages_removed = msg->m_pages_removed;
+ tabentry->n_pages_not_removed = msg->m_pages_not_removed; tabentry->n_index_scans =
msg->m_num_index_scans;
+ tabentry->oldest_xmin = msg->m_oldest_xmin; tabentry->vacuum_failcount = 0; if
(msg->m_autovacuum)
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index bc5d370..769a196 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -204,6 +204,36 @@ pg_stat_get_vacuum_necessity(PG_FUNCTION_ARGS)}Datum
+pg_stat_get_last_vacuum_truncated(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (tabentry->n_pages_removed);
+
+ PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_last_vacuum_untruncated(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int64 result;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ result = 0;
+ else
+ result = (int64) (tabentry->n_pages_not_removed);
+
+ PG_RETURN_INT64(result);
+}
+
+Datumpg_stat_get_last_vacuum_index_scans(PG_FUNCTION_ARGS){ Oid relid = PG_GETARG_OID(0);
@@ -219,6 +249,21 @@ pg_stat_get_last_vacuum_index_scans(PG_FUNCTION_ARGS)}Datum
+pg_stat_get_last_vacuum_oldest_xmin(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ TransactionId result;
+ PgStat_StatTabEntry *tabentry;
+
+ if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+ result = InvalidTransactionId;
+ else
+ result = (int32) (tabentry->oldest_xmin);
+
+ return TransactionIdGetDatum(result);
+}
+
+Datumpg_stat_get_last_vacuum_status(PG_FUNCTION_ARGS){ Oid relid = PG_GETARG_OID(0);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index a51e321..a3623dd 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2889,6 +2889,12 @@ DATA(insert OID = 6118 ( pg_stat_get_subscription PGNSP PGUID 12 1 0 0 0 f f
fDESCR("statistics:information about subscription");DATA(insert OID = 2579 ( pg_stat_get_vacuum_necessity PGNSP
PGUID12 1 0 0 0 f f f f t f s r 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_stat_get_vacuum_necessity _null_
_null__null_ ));DESCR("statistics: true if needs vacuum");
+DATA(insert OID = 3422 ( pg_stat_get_last_vacuum_untruncated PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 20 "26"
_null__null_ _null_ _null_ _null_ pg_stat_get_last_vacuum_untruncated _null_ _null_ _null_ ));
+DESCR("statistics: pages left untruncated in the last vacuum");
+DATA(insert OID = 3423 ( pg_stat_get_last_vacuum_truncated PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 20 "26"
_null__null_ _null_ _null_ _null_ pg_stat_get_last_vacuum_truncated _null_ _null_ _null_ ));
+DESCR("statistics: pages truncated in the last vacuum");
+DATA(insert OID = 3424 ( pg_stat_get_last_vacuum_oldest_xmin PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 28 "26"
_null__null_ _null_ _null_ _null_ pg_stat_get_last_vacuum_oldest_xmin _null_ _null_ _null_ ));
+DESCR("statistics: The oldest xmin used in the last vacuum");DATA(insert OID = 3281 (
pg_stat_get_last_vacuum_index_scans PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 23 "26" _null_ _null_ _null_ _null_
_null_pg_stat_get_last_vacuum_index_scans _null_ _null_ _null_ ));DESCR("statistics: number of index scans in the last
vacuum");DATA(insertOID = 3420 ( pg_stat_get_last_vacuum_status PGNSP PGUID 12 1 0 0 0 f f f f t f s r 1 0 25 "26"
_null__null_ _null_ _null_ _null_ pg_stat_get_last_vacuum_status _null_ _null_ _null_ ));
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 62c2369..5b8bf7e 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -383,7 +383,10 @@ typedef struct PgStat_MsgVacuum TimestampTz m_vacuumtime; PgStat_Counter m_live_tuples;
PgStat_Counterm_dead_tuples;
+ PgStat_Counter m_pages_removed;
+ PgStat_Counter m_pages_not_removed; PgStat_Counter m_num_index_scans;
+ TransactionId m_oldest_xmin; PgStat_Counter m_vacuum_status; PgStat_Counter m_vacuum_last_phase;
PgStat_Counterm_vacuum_errcode;
@@ -647,7 +650,10 @@ typedef struct PgStat_StatTabEntry PgStat_Counter n_live_tuples; PgStat_Counter
n_dead_tuples; PgStat_Counter changes_since_analyze;
+ PgStat_Counter n_pages_removed;
+ PgStat_Counter n_pages_not_removed; PgStat_Counter n_index_scans;
+ TransactionId oldest_xmin; PgStat_Counter blocks_fetched; PgStat_Counter blocks_hit;
@@ -1189,7 +1195,10 @@ extern void pgstat_reset_single_counter(Oid objectid, PgStat_Single_Reset_Type textern void
pgstat_report_autovac(Oiddboid);extern void pgstat_report_vacuum(Oid tableoid, bool shared,
PgStat_Counterlivetuples, PgStat_Counter deadtuples,
+ PgStat_Counter pages_removed,
+ PgStat_Counter pages_not_removed, PgStat_Counter num_index_scans,
+ TransactionId oldextxmin, PgStat_Counter status, PgStat_Counter last_phase,
PgStat_Counter errcode);extern void pgstat_report_analyze(Relation rel,
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index f0a8416..fb1ea49 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1765,7 +1765,10 @@ pg_stat_all_tables| SELECT c.oid AS relid, pg_stat_get_last_analyze_time(c.oid) AS
last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS
vacuum_count,
+ pg_stat_get_last_vacuum_truncated(c.oid) AS last_vacuum_truncated,
+ pg_stat_get_last_vacuum_untruncated(c.oid) AS last_vacuum_untruncated,
pg_stat_get_last_vacuum_index_scans(c.oid)AS last_vacuum_index_scans,
+ pg_stat_get_last_vacuum_oldest_xmin(c.oid) AS last_vacuum_oldest_xmin, pg_stat_get_last_vacuum_status(c.oid) AS
last_vacuum_status, pg_stat_get_autovacuum_fail_count(c.oid) AS autovacuum_fail_count,
pg_stat_get_autovacuum_count(c.oid)AS autovacuum_count,
@@ -1916,7 +1919,10 @@ pg_stat_sys_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.last_analyze,
pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count,
+ pg_stat_all_tables.last_vacuum_truncated,
+ pg_stat_all_tables.last_vacuum_untruncated, pg_stat_all_tables.last_vacuum_index_scans,
+ pg_stat_all_tables.last_vacuum_oldest_xmin, pg_stat_all_tables.last_vacuum_status,
pg_stat_all_tables.autovacuum_fail_count, pg_stat_all_tables.autovacuum_count,
@@ -1963,7 +1969,10 @@ pg_stat_user_tables| SELECT pg_stat_all_tables.relid, pg_stat_all_tables.last_analyze,
pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count,
+ pg_stat_all_tables.last_vacuum_truncated,
+ pg_stat_all_tables.last_vacuum_untruncated, pg_stat_all_tables.last_vacuum_index_scans,
+ pg_stat_all_tables.last_vacuum_oldest_xmin, pg_stat_all_tables.last_vacuum_status,
pg_stat_all_tables.autovacuum_fail_count, pg_stat_all_tables.autovacuum_count,
--
2.9.2
pgsql-hackers by date: