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

Commit1f219cf

Browse files
committed
Add last-vacuum/analyze-time columns to the stats collector, both manual and
issued by autovacuum. Add accessor functions to them, and use those in thepg_stat_*_tables system views.Catalog version bumped due to changes in the pgstat views and the pgstat file.Patch from Larry Rosenman, minor improvements by me.
1 parent09518fb commit1f219cf

File tree

8 files changed

+171
-12
lines changed

8 files changed

+171
-12
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 36 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.31 2006/03/10 19:10:48 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.32 2006/05/19 19:08:26 alvherre Exp $ -->
22

33
<chapter id="monitoring">
44
<title>Monitoring Database Activity</title>
@@ -250,7 +250,9 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
250250
<row>
251251
<entry><structname>pg_stat_all_tables</></entry>
252252
<entry>For each table in the current database (including TOAST tables),
253-
the table OID, schema and table name, number of sequential
253+
the table OID, schema and table name, the last time the table was
254+
vacuumed by the user and the autovacuum daemon, the last time the table
255+
was analyzed by the user and the autovacuum daemon, number of sequential
254256
scans initiated, number of live rows fetched by sequential
255257
scans, number of index scans initiated (over all indexes
256258
belonging to the table), number of live rows fetched by index
@@ -543,6 +545,38 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
543545
</entry>
544546
</row>
545547

548+
<row>
549+
<entry><literal><function>pg_stat_get_last_vacuum_time</function>(<type>oid</type>)</literal></entry>
550+
<entry><type>timestamptz</type></entry>
551+
<entry>
552+
Time of the last vacuum initiated by the user
553+
</entry>
554+
</row>
555+
556+
<row>
557+
<entry><literal><function>pg_stat_get_last_autovacuum_time</function>(<type>oid</type>)</literal></entry>
558+
<entry><type>timestamptz</type></entry>
559+
<entry>
560+
Time of the last vacuum initiated by the autovacuum daemon
561+
</entry>
562+
</row>
563+
564+
<row>
565+
<entry><literal><function>pg_stat_get_last_analyze_time</function>(<type>oid</type>)</literal></entry>
566+
<entry><type>timestamptz</type></entry>
567+
<entry>
568+
Time of the last analyze initiated by the user
569+
</entry>
570+
</row>
571+
572+
<row>
573+
<entry><literal><function>pg_stat_get_last_autoanalyze_time</function>(<type>oid</type>)</literal></entry>
574+
<entry><type>timestamptz</type></entry>
575+
<entry>
576+
Time of the last analyze initiated by the autovacuum daemon
577+
</entry>
578+
</row>
579+
546580
<row>
547581
<entry><literal><function>pg_stat_get_backend_idset</function>()</literal></entry>
548582
<entry><type>setof integer</type></entry>

‎src/backend/catalog/system_views.sql

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2006, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.26 2006/03/05 15:58:23 momjian Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.27 2006/05/19 19:08:26 alvherre Exp $
77
*/
88

99
CREATEVIEWpg_rolesAS
@@ -193,6 +193,10 @@ CREATE VIEW pg_stat_all_tables AS
193193
C.oidAS relid,
194194
N.nspnameAS schemaname,
195195
C.relnameAS relname,
196+
pg_stat_get_last_vacuum_time(C.oid)as last_vacuum,
197+
pg_stat_get_last_autovacuum_time(C.oid)as last_autovacuum,
198+
pg_stat_get_last_analyze_time(C.oid)as last_analyze,
199+
pg_stat_get_last_autoanalyze_time(C.oid)as last_autoanalyze,
196200
pg_stat_get_numscans(C.oid)AS seq_scan,
197201
pg_stat_get_tuples_returned(C.oid)AS seq_tup_read,
198202
sum(pg_stat_get_numscans(I.indexrelid))::bigintAS idx_scan,

‎src/backend/postmaster/pgstat.c

Lines changed: 23 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
*Copyright (c) 2001-2006, PostgreSQL Global Development Group
1515
*
16-
*$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.125 2006/05/1915:15:37 alvherre Exp $
16+
*$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.126 2006/05/1919:08:26 alvherre Exp $
1717
* ----------
1818
*/
1919
#include"postgres.h"
@@ -739,6 +739,8 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
739739
msg.m_databaseid=shared ?InvalidOid :MyDatabaseId;
740740
msg.m_tableoid=tableoid;
741741
msg.m_analyze=analyze;
742+
msg.m_autovacuum=IsAutoVacuumProcess();/* is this autovacuum? */
743+
msg.m_vacuumtime=GetCurrentTimestamp();
742744
msg.m_tuples=tuples;
743745
pgstat_send(&msg,sizeof(msg));
744746
}
@@ -762,6 +764,8 @@ pgstat_report_analyze(Oid tableoid, bool shared, PgStat_Counter livetuples,
762764
pgstat_setheader(&msg.m_hdr,PGSTAT_MTYPE_ANALYZE);
763765
msg.m_databaseid=shared ?InvalidOid :MyDatabaseId;
764766
msg.m_tableoid=tableoid;
767+
msg.m_autovacuum=IsAutoVacuumProcess();/* is this autovacuum? */
768+
msg.m_analyzetime=GetCurrentTimestamp();
765769
msg.m_live_tuples=livetuples;
766770
msg.m_dead_tuples=deadtuples;
767771
pgstat_send(&msg,sizeof(msg));
@@ -2887,10 +2891,20 @@ pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len)
28872891
if (tabentry==NULL)
28882892
return;
28892893

2894+
if (msg->m_autovacuum)
2895+
tabentry->autovac_vacuum_timestamp=msg->m_vacuumtime;
2896+
else
2897+
tabentry->vacuum_timestamp=msg->m_vacuumtime;
28902898
tabentry->n_live_tuples=msg->m_tuples;
28912899
tabentry->n_dead_tuples=0;
28922900
if (msg->m_analyze)
2901+
{
28932902
tabentry->last_anl_tuples=msg->m_tuples;
2903+
if (msg->m_autovacuum)
2904+
tabentry->autovac_analyze_timestamp=msg->m_vacuumtime;
2905+
else
2906+
tabentry->analyze_timestamp=msg->m_vacuumtime;
2907+
}
28942908
}
28952909

28962910
/* ----------
@@ -2919,6 +2933,10 @@ pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len)
29192933
if (tabentry==NULL)
29202934
return;
29212935

2936+
if (msg->m_autovacuum)
2937+
tabentry->autovac_analyze_timestamp=msg->m_analyzetime;
2938+
else
2939+
tabentry->analyze_timestamp=msg->m_analyzetime;
29222940
tabentry->n_live_tuples=msg->m_live_tuples;
29232941
tabentry->n_dead_tuples=msg->m_dead_tuples;
29242942
tabentry->last_anl_tuples=msg->m_live_tuples+msg->m_dead_tuples;
@@ -3005,6 +3023,10 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
30053023
tabentry->n_dead_tuples=tabmsg[i].t_tuples_updated+
30063024
tabmsg[i].t_tuples_deleted;
30073025
tabentry->last_anl_tuples=0;
3026+
tabentry->vacuum_timestamp=0;
3027+
tabentry->autovac_vacuum_timestamp=0;
3028+
tabentry->analyze_timestamp=0;
3029+
tabentry->autovac_analyze_timestamp=0;
30083030

30093031
tabentry->blocks_fetched=tabmsg[i].t_blocks_fetched;
30103032
tabentry->blocks_hit=tabmsg[i].t_blocks_hit;

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

Lines changed: 84 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.28 2006/05/1915:15:37 alvherre Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.29 2006/05/1919:08:26 alvherre Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -34,6 +34,10 @@ extern Datum pg_stat_get_tuples_updated(PG_FUNCTION_ARGS);
3434
externDatumpg_stat_get_tuples_deleted(PG_FUNCTION_ARGS);
3535
externDatumpg_stat_get_blocks_fetched(PG_FUNCTION_ARGS);
3636
externDatumpg_stat_get_blocks_hit(PG_FUNCTION_ARGS);
37+
externDatumpg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS);
38+
externDatumpg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS);
39+
externDatumpg_stat_get_last_analyze_time(PG_FUNCTION_ARGS);
40+
externDatumpg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);
3741

3842
externDatumpg_stat_get_backend_idset(PG_FUNCTION_ARGS);
3943
externDatumpg_backend_pid(PG_FUNCTION_ARGS);
@@ -197,6 +201,85 @@ pg_stat_get_blocks_hit(PG_FUNCTION_ARGS)
197201
PG_RETURN_INT64(result);
198202
}
199203

204+
Datum
205+
pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS)
206+
{
207+
PgStat_StatTabEntry*tabentry;
208+
Oidrelid;
209+
TimestampTzresult;
210+
211+
relid=PG_GETARG_OID(0);
212+
213+
if ((tabentry=pgstat_fetch_stat_tabentry(relid))==NULL)
214+
result=0;
215+
else
216+
result=tabentry->vacuum_timestamp;
217+
218+
if (result==0)
219+
PG_RETURN_NULL();
220+
else
221+
PG_RETURN_TIMESTAMPTZ(result);
222+
}
223+
224+
Datum
225+
pg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS)
226+
{
227+
PgStat_StatTabEntry*tabentry;
228+
Oidrelid;
229+
TimestampTzresult;
230+
231+
relid=PG_GETARG_OID(0);
232+
233+
if ((tabentry=pgstat_fetch_stat_tabentry(relid))==NULL)
234+
result=0;
235+
else
236+
result=tabentry->autovac_vacuum_timestamp;
237+
238+
if (result==0)
239+
PG_RETURN_NULL();
240+
else
241+
PG_RETURN_TIMESTAMPTZ(result);
242+
}
243+
244+
Datum
245+
pg_stat_get_last_analyze_time(PG_FUNCTION_ARGS)
246+
{
247+
PgStat_StatTabEntry*tabentry;
248+
Oidrelid;
249+
TimestampTzresult;
250+
251+
relid=PG_GETARG_OID(0);
252+
253+
if ((tabentry=pgstat_fetch_stat_tabentry(relid))==NULL)
254+
result=0;
255+
else
256+
result=tabentry->analyze_timestamp;
257+
258+
if (result==0)
259+
PG_RETURN_NULL();
260+
else
261+
PG_RETURN_TIMESTAMPTZ(result);
262+
}
263+
264+
Datum
265+
pg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS)
266+
{
267+
PgStat_StatTabEntry*tabentry;
268+
Oidrelid;
269+
TimestampTzresult;
270+
271+
relid=PG_GETARG_OID(0);
272+
273+
if ((tabentry=pgstat_fetch_stat_tabentry(relid))==NULL)
274+
result=0;
275+
else
276+
result=tabentry->autovac_analyze_timestamp;
277+
278+
if (result==0)
279+
PG_RETURN_NULL();
280+
else
281+
PG_RETURN_TIMESTAMPTZ(result);
282+
}
200283

201284
Datum
202285
pg_stat_get_backend_idset(PG_FUNCTION_ARGS)

‎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-2006, 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.332 2006/05/17 16:37:06 teodor Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.333 2006/05/19 19:08:26 alvherre Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200605171
56+
#defineCATALOG_VERSION_NO200605191
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-2006, 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.411 2006/05/10 23:18:39 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.412 2006/05/19 19:08:26 alvherre Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2839,6 +2839,14 @@ DATA(insert OID = 1934 ( pg_stat_get_blocks_fetchedPGNSP PGUID 12 f f t f s 1
28392839
DESCR("Statistics: Number of blocks fetched");
28402840
DATA(insertOID=1935 (pg_stat_get_blocks_hitPGNSPPGUID12fftfs120"26"_null__null__null_pg_stat_get_blocks_hit-_null_ ));
28412841
DESCR("Statistics: Number of blocks found in cache");
2842+
DATA(insertOID=2781 (pg_stat_get_last_vacuum_timePGNSPPGUID12fftfs11184"26"_null__null__null_pg_stat_get_last_vacuum_time-_null_));
2843+
DESCR("Statistics: Last manual vacuum time for a table");
2844+
DATA(insertOID=2782 (pg_stat_get_last_autovacuum_timePGNSPPGUID12fftfs11184"26"_null__null__null_pg_stat_get_last_autovacuum_time-_null_));
2845+
DESCR("Statistics: Last auto vacuum time for a table");
2846+
DATA(insertOID=2783 (pg_stat_get_last_analyze_timePGNSPPGUID12fftfs11184"26"_null__null__null_pg_stat_get_last_analyze_time-_null_));
2847+
DESCR("Statistics: Last manual analyze time for a table");
2848+
DATA(insertOID=2784 (pg_stat_get_last_autoanalyze_timePGNSPPGUID12fftfs11184"26"_null__null__null_pg_stat_get_last_autoanalyze_time-_null_));
2849+
DESCR("Statistics: Last auto analyze time for a table");
28422850
DATA(insertOID=1936 (pg_stat_get_backend_idsetPGNSPPGUID12fftts023""_null__null__null_pg_stat_get_backend_idset-_null_ ));
28432851
DESCR("Statistics: Currently active backend IDs");
28442852
DATA(insertOID=2026 (pg_backend_pidPGNSPPGUID12fftfs023""_null__null__null_pg_backend_pid-_null_ ));

‎src/include/pgstat.h

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55
*
66
*Copyright (c) 2001-2006, PostgreSQL Global Development Group
77
*
8-
*$PostgreSQL: pgsql/src/include/pgstat.h,v 1.44 2006/04/27 00:06:59 momjian Exp $
8+
*$PostgreSQL: pgsql/src/include/pgstat.h,v 1.45 2006/05/19 19:08:26 alvherre Exp $
99
* ----------
1010
*/
1111
#ifndefPGSTAT_H
@@ -161,6 +161,8 @@ typedef struct PgStat_MsgVacuum
161161
Oidm_databaseid;
162162
Oidm_tableoid;
163163
boolm_analyze;
164+
boolm_autovacuum;
165+
TimestampTzm_vacuumtime;
164166
PgStat_Counterm_tuples;
165167
}PgStat_MsgVacuum;
166168

@@ -174,6 +176,8 @@ typedef struct PgStat_MsgAnalyze
174176
PgStat_MsgHdrm_hdr;
175177
Oidm_databaseid;
176178
Oidm_tableoid;
179+
boolm_autovacuum;
180+
TimestampTzm_analyzetime;
177181
PgStat_Counterm_live_tuples;
178182
PgStat_Counterm_dead_tuples;
179183
}PgStat_MsgAnalyze;
@@ -344,6 +348,10 @@ typedef struct PgStat_StatBeEntry
344348
typedefstructPgStat_StatTabEntry
345349
{
346350
Oidtableid;
351+
TimestampTzvacuum_timestamp;/* user initiated vacuum */
352+
TimestampTzautovac_vacuum_timestamp;/* autovacuum initiated */
353+
TimestampTzanalyze_timestamp;/* user initiated */
354+
TimestampTzautovac_analyze_timestamp;/* autovacuum initiated */
347355

348356
PgStat_Counternumscans;
349357

0 commit comments

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