Re: [PATCH] Unremovable tuple monitoring (was: Addition of some trivial auto vacuum logging) - Mailing list pgsql-hackers

On 04/10/2011, at 11:26 PM, Robert Haas wrote:

> On Mon, Oct 3, 2011 at 9:17 AM, Royce Ausburn <royce.ml@inomial.com> wrote:
>> - I'm not sure if I'm supposed to update CATALOG_VERSION_NO in catalog.h.  In this patch I have.
>
> Generally that is left to the committer, as the correct value depends
> on the value at the time of commit, not the time you submit the patch;
> and including it in the patch tends to result in failing hunks, since
> the value changes fairly frequently.

>> - The VACUUM FULL implementation in cluster.c doesn't do any stats updating similar to vacuumlazy.c, so I haven't
don'tanything there… (is this right?  A vacuum full may also encounter unremovable tuples, right?) 
>
> We've occasionally heard grumblings about making cluster do more stats
> updating, but your patch should just go along with whatever's being
> done now in similar cases.

I think I get this stats stuff now.  Unless someone here thinks it's too hard for a new postgres dev's 2nd patch, I
couldtake a stab.  I might take a look at it tonight to get a feel for how hard, and what stats we could collect.  I'll
starta new thread for discussion. 

Attached is v2 of my patch that doesn't update CATALOG_VERSION_NO.  I've also fixed the name of an argument to
pgstat_report_vacuumwhich I don't think was particularly good, and I've replace the word "tuple" with "row" in some
docsI added for consistency. 

I'm not sure what my next step should be.  I've added this patch to the open commit fest -- is that all for now until
thecommit fest begins review? 

--Royce



diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index a19e3f0..8692580 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -328,7 +328,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       belonging to the table), number of live rows fetched by index
       scans, numbers of row insertions, updates, and deletions,
       number of row updates that were HOT (i.e., no separate index update),
-      numbers of live and dead rows,
+      numbers of live and dead rows,
+      the number of dead rows not removed in the last vacuum,
       the last time the table was non-<option>FULL</> vacuumed manually,
       the last time it was vacuumed by the autovacuum daemon,
       the last time it was analyzed manually,
@@ -764,6 +765,14 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
        Number of dead rows in table
       </entry>
      </row>
+
+     <row>
+      <entry><literal><function>pg_stat_get_unremovable_tuples</function>(<type>oid</type>)</literal></entry>
+      <entry><type>bigint</type></entry>
+      <entry>
+       Number of dead rows not removed in the table's last vacuum
+      </entry>
+     </row>

      <row>
       <entry><literal><function>pg_stat_get_blocks_fetched</function>(<type>oid</type>)</literal></entry>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 2253ca8..9c18dc7 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -353,6 +353,7 @@ CREATE VIEW pg_stat_all_tables AS
             pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_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_unremovable_tuples(C.oid) AS n_unremovable_tup,
             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/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index cf8337b..140fe92 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -91,6 +91,7 @@ typedef struct LVRelStats
     double        scanned_tuples; /* counts only tuples on scanned pages */
     double        old_rel_tuples; /* previous value of pg_class.reltuples */
     double        new_rel_tuples; /* new estimated total # of tuples */
+    double        unremovable_tuples; /* count of dead tuples not yet removable */
     BlockNumber pages_removed;
     double        tuples_deleted;
     BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */
@@ -245,7 +246,8 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
     /* report results to the stats collector, too */
     pgstat_report_vacuum(RelationGetRelid(onerel),
                          onerel->rd_rel->relisshared,
-                         new_rel_tuples);
+                         new_rel_tuples,
+                         vacrelstats->unremovable_tuples);

     /* and log the action if appropriate */
     if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0)
@@ -829,6 +831,7 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
     /* save stats for use later */
     vacrelstats->scanned_tuples = num_tuples;
     vacrelstats->tuples_deleted = tups_vacuumed;
+    vacrelstats->unremovable_tuples = nkeep;

     /* now we can compute the new value for pg_class.reltuples */
     vacrelstats->new_rel_tuples = vac_estimate_reltuples(onerel, false,
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 9132db7..d974a96 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -1251,7 +1251,7 @@ pgstat_report_autovac(Oid dboid)
  * ---------
  */
 void
-pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter tuples)
+pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter tuples, PgStat_Counter unremovable_tuples)
 {
     PgStat_MsgVacuum msg;

@@ -1264,6 +1264,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter tuples)
     msg.m_autovacuum = IsAutoVacuumWorkerProcess();
     msg.m_vacuumtime = GetCurrentTimestamp();
     msg.m_tuples = tuples;
+    msg.m_unremovable_tuples = unremovable_tuples;
     pgstat_send(&msg, sizeof(msg));
 }

@@ -4202,6 +4203,7 @@ pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len)
     tabentry = pgstat_get_tab_entry(dbentry, msg->m_tableoid, true);

     tabentry->n_live_tuples = msg->m_tuples;
+    tabentry->n_unremovable_tuples = msg->m_unremovable_tuples;
     /* Resetting dead_tuples to 0 is an approximation ... */
     tabentry->n_dead_tuples = 0;

diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 7792b33..fb60fc5 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -33,6 +33,7 @@ extern Datum pg_stat_get_tuples_deleted(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_tuples_hot_updated(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_live_tuples(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_dead_tuples(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_unremovable_tuples(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_blocks_hit(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS);
@@ -256,6 +257,22 @@ pg_stat_get_dead_tuples(PG_FUNCTION_ARGS)
     PG_RETURN_INT64(result);
 }

+Datum
+pg_stat_get_unremovable_tuples(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_unremovable_tuples);
+
+    PG_RETURN_INT64(result);
+}
+
+

 Datum
 pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 96f43fe..69f6415 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2526,6 +2526,8 @@ DATA(insert OID = 2878 (  pg_stat_get_live_tuples    PGNSP PGUID 12 1 0 0 0 f f f t
 DESCR("statistics: number of live tuples");
 DATA(insert OID = 2879 (  pg_stat_get_dead_tuples    PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_
_null__null_ pg_stat_get_dead_tuples _null_ _null_ _null_ )); 
 DESCR("statistics: number of dead tuples");
+DATA(insert OID = 3122 (  pg_stat_get_unremovable_tuples    PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_
_null__null_ _null_ pg_stat_get_unremovable_tuples _null_ _null_ _null_ )); 
+DESCR("statistics: number of dead tuples not yet removable");
 DATA(insert OID = 1934 (  pg_stat_get_blocks_fetched    PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_
_null__null_ pg_stat_get_blocks_fetched _null_ _null_ _null_ )); 
 DESCR("statistics: number of blocks fetched");
 DATA(insert OID = 1935 (  pg_stat_get_blocks_hit        PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_
_null__null_ pg_stat_get_blocks_hit _null_ _null_ _null_ )); 
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 20c4d43..e1b082e 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -326,6 +326,7 @@ typedef struct PgStat_MsgVacuum
     bool        m_autovacuum;
     TimestampTz m_vacuumtime;
     PgStat_Counter m_tuples;
+    PgStat_Counter m_unremovable_tuples;
 } PgStat_MsgVacuum;


@@ -539,6 +540,7 @@ typedef struct PgStat_StatTabEntry

     PgStat_Counter n_live_tuples;
     PgStat_Counter n_dead_tuples;
+    PgStat_Counter n_unremovable_tuples;
     PgStat_Counter changes_since_analyze;

     PgStat_Counter blocks_fetched;
@@ -706,7 +708,7 @@ extern void pgstat_reset_single_counter(Oid objectid, PgStat_Single_Reset_Type t

 extern void pgstat_report_autovac(Oid dboid);
 extern void pgstat_report_vacuum(Oid tableoid, bool shared,
-                     PgStat_Counter tuples);
+                     PgStat_Counter tuples, PgStat_Counter unremovable_tuples);
 extern void pgstat_report_analyze(Relation rel,
                       PgStat_Counter livetuples, PgStat_Counter deadtuples);