Skip to content

Commit 946045f

Browse files
mhagandermhagander
committed
Add vacuum and analyze counters to pg_stat_*_tables views.
1 parent efe2e9a commit 946045f

File tree

7 files changed

+147
-15
lines changed

7 files changed

+147
-15
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 43 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.84 2010/08/17 04:37:20 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.85 2010/08/21 10:59:17 mha Exp $ -->
22

33
<chapter id="monitoring">
44
<title>Monitoring Database Activity</title>
@@ -117,9 +117,9 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
117117
is a subsystem that supports collection and reporting of information about
118118
server activity. Presently, the collector can count accesses to tables
119119
and indexes in both disk-block and individual-row terms. It also tracks
120-
the total number of rows in each table, and the last vacuum and analyze times
121-
for each table. It can also count calls to user-defined functions and
122-
the total time spent in each one.
120+
the total number of rows in each table, and information about vacuum and
121+
analyze actions for each table. It can also count calls to user-defined
122+
functions and the total time spent in each one.
123123
</para>
124124

125125
<para>
@@ -293,7 +293,11 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
293293
the last time the table was vacuumed manually,
294294
the last time it was vacuumed by the autovacuum daemon,
295295
the last time it was analyzed manually,
296-
and the last time it was analyzed by the autovacuum daemon.
296+
the last time it was analyzed by the autovacuum daemon,
297+
number of times it has been vacuumed manually,
298+
number of times it has been vacuumed by the autovacuum daemon,
299+
number of times it has been analyzed manually,
300+
and the number of times it has been analyzed by the autovacuum daemon.
297301
</entry>
298302
</row>
299303

@@ -314,8 +318,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
314318
<entry>Similar to <structname>pg_stat_all_tables</>, but counts actions
315319
taken so far within the current transaction (which are <emphasis>not</>
316320
yet included in <structname>pg_stat_all_tables</> and related views).
317-
The columns for numbers of live and dead rows and last-vacuum and
318-
last-analyze times are not present in this view.</entry>
321+
The columns for numbers of live and dead rows and vacuum and
322+
analyze actions are not present in this view.</entry>
319323
</row>
320324

321325
<row>
@@ -718,6 +722,38 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
718722
</entry>
719723
</row>
720724

725+
<row>
726+
<entry><literal><function>pg_stat_get_vacuum_count</function>(<type>oid</type>)</literal></entry>
727+
<entry><type>bigint</type></entry>
728+
<entry>
729+
The number of times this table has been vacuumed manually
730+
</entry>
731+
</row>
732+
733+
<row>
734+
<entry><literal><function>pg_stat_get_autovacuum_count</function>(<type>oid</type>)</literal></entry>
735+
<entry><type>bigint</type></entry>
736+
<entry>
737+
The number of times this table has been vacuumed by the autovacuum daemon
738+
</entry>
739+
</row>
740+
741+
<row>
742+
<entry><literal><function>pg_stat_get_analyze_count</function>(<type>oid</type>)</literal></entry>
743+
<entry><type>bigint</type></entry>
744+
<entry>
745+
The number of times this table has been analyzed manually
746+
</entry>
747+
</row>
748+
749+
<row>
750+
<entry><literal><function>pg_stat_get_autoanalyze_count</function>(<type>oid</type>)</literal></entry>
751+
<entry><type>bigint</type></entry>
752+
<entry>
753+
The number of times this table has been analyzed by the autovacuum daemon
754+
</entry>
755+
</row>
756+
721757
<row>
722758
<entry><literal><function>pg_stat_get_xact_numscans</function>(<type>oid</type>)</literal></entry>
723759
<entry><type>bigint</type></entry>

‎src/backend/catalog/system_views.sql

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2010, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.67 2010/08/08 16:27:03 tgl Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.68 2010/08/21 10:59:17 mha Exp $
77
*/
88

99
CREATE VIEW pg_roles AS
@@ -201,7 +201,11 @@ CREATE VIEW pg_stat_all_tables AS
201201
pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
202202
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
203203
pg_stat_get_last_analyze_time(C.oid) as last_analyze,
204-
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze
204+
pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
205+
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
206+
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
207+
pg_stat_get_analyze_count(C.oid) AS analyze_count,
208+
pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
205209
FROM pg_class C LEFT JOIN
206210
pg_index I ON C.oid = I.indrelid
207211
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

‎src/backend/postmaster/pgstat.c

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
* Copyright (c) 2001-2010, PostgreSQL Global Development Group
1515
*
16-
* $PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.205 2010/08/08 16:27:03 tgl Exp $
16+
* $PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.206 2010/08/21 10:59:17 mha Exp $
1717
* ----------
1818
*/
1919
#include "postgres.h"
@@ -3192,6 +3192,10 @@ pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create)
31923192
result->autovac_vacuum_timestamp = 0;
31933193
result->analyze_timestamp = 0;
31943194
result->autovac_analyze_timestamp = 0;
3195+
result->vacuum_count = 0;
3196+
result->autovac_vacuum_count = 0;
3197+
result->analyze_count = 0;
3198+
result->autovac_analyze_count = 0;
31953199
}
31963200

31973201
return result;
@@ -4114,9 +4118,15 @@ pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len)
41144118
tabentry->n_dead_tuples = 0;
41154119

41164120
if (msg->m_autovacuum)
4121+
{
41174122
tabentry->autovac_vacuum_timestamp = msg->m_vacuumtime;
4123+
tabentry->autovac_vacuum_count++;
4124+
}
41184125
else
4126+
{
41194127
tabentry->vacuum_timestamp = msg->m_vacuumtime;
4128+
tabentry->vacuum_count++;
4129+
}
41204130
}
41214131

41224132
/* ----------
@@ -4151,9 +4161,15 @@ pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len)
41514161
tabentry->changes_since_analyze = 0;
41524162

41534163
if (msg->m_autovacuum)
4164+
{
41544165
tabentry->autovac_analyze_timestamp = msg->m_analyzetime;
4166+
tabentry->autovac_analyze_count++;
4167+
}
41554168
else
4169+
{
41564170
tabentry->analyze_timestamp = msg->m_analyzetime;
4171+
tabentry->analyze_count++;
4172+
}
41574173
}
41584174

41594175

‎src/backend/utils/adt/pgstatfuncs.c

Lines changed: 65 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.61 2010/08/08 16:27:04 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.62 2010/08/21 10:59:17 mha Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -38,6 +38,10 @@ extern Datum pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS);
3838
extern Datum pg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS);
3939
extern Datum pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS);
4040
extern Datum pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);
41+
extern Datum pg_stat_get_vacuum_count(PG_FUNCTION_ARGS);
42+
extern Datum pg_stat_get_autovacuum_count(PG_FUNCTION_ARGS);
43+
extern Datum pg_stat_get_analyze_count(PG_FUNCTION_ARGS);
44+
extern Datum pg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS);
4145

4246
extern Datum pg_stat_get_function_calls(PG_FUNCTION_ARGS);
4347
extern Datum pg_stat_get_function_time(PG_FUNCTION_ARGS);
@@ -346,6 +350,66 @@ pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS)
346350
PG_RETURN_TIMESTAMPTZ(result);
347351
}
348352

353+
Datum
354+
pg_stat_get_vacuum_count(PG_FUNCTION_ARGS)
355+
{
356+
Oid relid = PG_GETARG_OID(0);
357+
int64 result;
358+
PgStat_StatTabEntry *tabentry;
359+
360+
if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
361+
result = 0;
362+
else
363+
result = (int64) (tabentry->vacuum_count);
364+
365+
PG_RETURN_INT64(result);
366+
}
367+
368+
Datum
369+
pg_stat_get_autovacuum_count(PG_FUNCTION_ARGS)
370+
{
371+
Oid relid = PG_GETARG_OID(0);
372+
int64 result;
373+
PgStat_StatTabEntry *tabentry;
374+
375+
if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
376+
result = 0;
377+
else
378+
result = (int64) (tabentry->autovac_vacuum_count);
379+
380+
PG_RETURN_INT64(result);
381+
}
382+
383+
Datum
384+
pg_stat_get_analyze_count(PG_FUNCTION_ARGS)
385+
{
386+
Oid relid = PG_GETARG_OID(0);
387+
int64 result;
388+
PgStat_StatTabEntry *tabentry;
389+
390+
if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
391+
result = 0;
392+
else
393+
result = (int64) (tabentry->analyze_count);
394+
395+
PG_RETURN_INT64(result);
396+
}
397+
398+
Datum
399+
pg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS)
400+
{
401+
Oid relid = PG_GETARG_OID(0);
402+
int64 result;
403+
PgStat_StatTabEntry *tabentry;
404+
405+
if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
406+
result = 0;
407+
else
408+
result = (int64) (tabentry->autovac_analyze_count);
409+
410+
PG_RETURN_INT64(result);
411+
}
412+
349413
Datum
350414
pg_stat_get_function_calls(PG_FUNCTION_ARGS)
351415
{

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.595 2010/08/13 18:36:24 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.596 2010/08/21 10:59:17 mha Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201008131
56+
#define CATALOG_VERSION_NO 201008211
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.579 2010/08/13 18:36:25 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.580 2010/08/21 10:59:17 mha Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.pl reads this file and generates .bki
@@ -3029,6 +3029,14 @@ DATA(insert OID = 2783 ( pg_stat_get_last_analyze_time PGNSP PGUID 12 1 0 0 f f
30293029
DESCR("statistics: last manual analyze time for a table");
30303030
DATA(insert OID = 2784 ( pg_stat_get_last_autoanalyze_time PGNSP PGUID 12 1 0 0 f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_ pg_stat_get_last_autoanalyze_time _null_ _null_ _null_ ));
30313031
DESCR("statistics: last auto analyze time for a table");
3032+
DATA(insert OID = 3054 ( pg_stat_get_vacuum_count PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_vacuum_count _null_ _null_ _null_ ));
3033+
DESCR("statistics: number of manual vacuums for a table");
3034+
DATA(insert OID = 3055 ( pg_stat_get_autovacuum_count PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_autovacuum_count _null_ _null_ _null_ ));
3035+
DESCR("statistics: number of auto vacuums for a table");
3036+
DATA(insert OID = 3056 ( pg_stat_get_analyze_count PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_analyze_count _null_ _null_ _null_ ));
3037+
DESCR("statistics: number of manual analyzes for a table");
3038+
DATA(insert OID = 3057 ( pg_stat_get_autoanalyze_count PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_autoanalyze_count _null_ _null_ _null_ ));
3039+
DESCR("statistics: number of auto analyzes for a table");
30323040
DATA(insert OID = 1936 ( pg_stat_get_backend_idset PGNSP PGUID 12 1 100 0 f f f t t s 0 0 23 "" _null_ _null_ _null_ _null_ pg_stat_get_backend_idset _null_ _null_ _null_ ));
30333041
DESCR("statistics: currently active backend IDs");
30343042
DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,16,1184,1184,1184,869,23}" "{i,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,procpid,usesysid,application_name,current_query,waiting,xact_start,query_start,backend_start,client_addr,client_port}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));

‎src/include/pgstat.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55
*
66
* Copyright (c) 2001-2010, PostgreSQL Global Development Group
77
*
8-
* $PostgreSQL: pgsql/src/include/pgstat.h,v 1.90 2010/08/08 16:27:06 tgl Exp $
8+
* $PostgreSQL: pgsql/src/include/pgstat.h,v 1.91 2010/08/21 10:59:17 mha Exp $
99
* ----------
1010
*/
1111
#ifndef PGSTAT_H
@@ -525,9 +525,13 @@ typedef struct PgStat_StatTabEntry
525525
PgStat_Counter blocks_hit;
526526

527527
TimestampTz vacuum_timestamp; /* user initiated vacuum */
528+
PgStat_Counter vacuum_count;
528529
TimestampTz autovac_vacuum_timestamp; /* autovacuum initiated */
530+
PgStat_Counter autovac_vacuum_count;
529531
TimestampTz analyze_timestamp; /* user initiated */
532+
PgStat_Counter analyze_count;
530533
TimestampTz autovac_analyze_timestamp; /* autovacuum initiated */
534+
PgStat_Counter autovac_analyze_count;
531535
} PgStat_StatTabEntry;
532536

533537

0 commit comments

Comments
 (0)