Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit946045f

Browse files
committed
Add vacuum and analyze counters to pg_stat_*_tables views.
1 parentefe2e9a commit946045f

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, andthe 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, andinformation about vacuum and
121+
analyze actionsfor each table. It can also count calls to user-defined
122+
functions andthe 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 andlast-vacuum and
318-
last-analyzetimes are not present in this view.</entry>
321+
The columns for numbers of live and dead rows and vacuum and
322+
analyzeactions 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
CREATEVIEWpg_rolesAS
@@ -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 CLEFT JOIN
206210
pg_index IONC.oid=I.indrelid
207211
LEFT JOIN pg_namespace NON (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
returnresult;
@@ -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
externDatumpg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS);
3939
externDatumpg_stat_get_last_analyze_time(PG_FUNCTION_ARGS);
4040
externDatumpg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);
41+
externDatumpg_stat_get_vacuum_count(PG_FUNCTION_ARGS);
42+
externDatumpg_stat_get_autovacuum_count(PG_FUNCTION_ARGS);
43+
externDatumpg_stat_get_analyze_count(PG_FUNCTION_ARGS);
44+
externDatumpg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS);
4145

4246
externDatumpg_stat_get_function_calls(PG_FUNCTION_ARGS);
4347
externDatumpg_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+
Oidrelid=PG_GETARG_OID(0);
357+
int64result;
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+
Oidrelid=PG_GETARG_OID(0);
372+
int64result;
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+
Oidrelid=PG_GETARG_OID(0);
387+
int64result;
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+
Oidrelid=PG_GETARG_OID(0);
402+
int64result;
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-
#defineCATALOG_VERSION_NO201008131
56+
#defineCATALOG_VERSION_NO201008211
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(insertOID=2784 (pg_stat_get_last_autoanalyze_timePGNSPPGUID12100ffftfs101184"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(insertOID=3054 (pg_stat_get_vacuum_countPGNSPPGUID12100ffftfs1020"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(insertOID=3055 (pg_stat_get_autovacuum_countPGNSPPGUID12100ffftfs1020"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(insertOID=3056 (pg_stat_get_analyze_countPGNSPPGUID12100ffftfs1020"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(insertOID=3057 (pg_stat_get_autoanalyze_countPGNSPPGUID12100ffftfs1020"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(insertOID=1936 (pg_stat_get_backend_idsetPGNSPPGUID1211000ffftts0023""_null__null__null__null_pg_stat_get_backend_idset_null__null__null_ ));
30333041
DESCR("statistics: currently active backend IDs");
30343042
DATA(insertOID=2022 (pg_stat_get_activityPGNSPPGUID1211000ffffts102249"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
#ifndefPGSTAT_H
@@ -525,9 +525,13 @@ typedef struct PgStat_StatTabEntry
525525
PgStat_Counterblocks_hit;
526526

527527
TimestampTzvacuum_timestamp;/* user initiated vacuum */
528+
PgStat_Countervacuum_count;
528529
TimestampTzautovac_vacuum_timestamp;/* autovacuum initiated */
530+
PgStat_Counterautovac_vacuum_count;
529531
TimestampTzanalyze_timestamp;/* user initiated */
532+
PgStat_Counteranalyze_count;
530533
TimestampTzautovac_analyze_timestamp;/* autovacuum initiated */
534+
PgStat_Counterautovac_analyze_count;
531535
}PgStat_StatTabEntry;
532536

533537

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp