<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.84 2010/08/17 04:37:20 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.85 2010/08/21 10:59:17 mha Exp $ -->

<chapter id="monitoring">

<title>Monitoring Database Activity</title>

is a subsystem that supports collection and reporting of information about

server activity. Presently, the collector can count accesses to tables

and indexes in both disk-block and individual-row terms. It also tracks

the total number of rows in each table, andthe last vacuum and analyze times

for each table. It can also count calls to user-defined functions and

the total time spent in each one.

the total number of rows in each table, andinformation about vacuum and

analyze actionsfor each table. It can also count calls to user-defined

functions andthe total time spent in each one.

</para>

<para>

the last time the table was vacuumed manually,

the last time it was vacuumed by the autovacuum daemon,

the last time it was analyzed manually,

and the last time it was analyzed by the autovacuum daemon.

the last time it was analyzed by the autovacuum daemon,

number of times it has been vacuumed manually,

number of times it has been vacuumed by the autovacuum daemon,

number of times it has been analyzed manually,

and the number of times it has been analyzed by the autovacuum daemon.

</entry>

</row>

<entry>Similar to <structname>pg_stat_all_tables</>, but counts actions

taken so far within the current transaction (which are <emphasis>not</>

yet included in <structname>pg_stat_all_tables</> and related views).

The columns for numbers of live and dead rows andlast-vacuum and

last-analyzetimes are not present in this view.</entry>

The columns for numbers of live and dead rows and vacuum and

analyzeactions are not present in this view.</entry>

</row>

<row>

</entry>

</row>

<row>

<entry><literal><function>pg_stat_get_vacuum_count</function>(<type>oid</type>)</literal></entry>

<entry><type>bigint</type></entry>

<entry>

The number of times this table has been vacuumed manually

</entry>

</row>

<row>

<entry><literal><function>pg_stat_get_autovacuum_count</function>(<type>oid</type>)</literal></entry>

<entry><type>bigint</type></entry>

<entry>

The number of times this table has been vacuumed by the autovacuum daemon

</entry>

</row>

<row>

<entry><literal><function>pg_stat_get_analyze_count</function>(<type>oid</type>)</literal></entry>

<entry><type>bigint</type></entry>

<entry>

The number of times this table has been analyzed manually

</entry>

</row>

<row>

<entry><literal><function>pg_stat_get_autoanalyze_count</function>(<type>oid</type>)</literal></entry>

<entry><type>bigint</type></entry>

<entry>

The number of times this table has been analyzed by the autovacuum daemon

</entry>

</row>

<row>

<entry><literal><function>pg_stat_get_xact_numscans</function>(<type>oid</type>)</literal></entry>

<entry><type>bigint</type></entry>

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_last_autoanalyze_time(C.oid)as last_autoanalyze,

pg_stat_get_vacuum_count(C.oid)AS vacuum_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

FROM pg_class CLEFT JOIN

pg_index IONC.oid=I.indrelid

LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)

result->autovac_vacuum_timestamp=0;

result->analyze_timestamp=0;

result->autovac_analyze_timestamp=0;

result->vacuum_count=0;

result->autovac_vacuum_count=0;

result->analyze_count=0;

result->autovac_analyze_count=0;

}

returnresult;

tabentry->n_dead_tuples=0;

if (msg->m_autovacuum)

{

tabentry->autovac_vacuum_timestamp=msg->m_vacuumtime;

tabentry->autovac_vacuum_count++;

}

{

tabentry->vacuum_timestamp=msg->m_vacuumtime;

tabentry->vacuum_count++;

}

}

tabentry->changes_since_analyze=0;

if (msg->m_autovacuum)

{

tabentry->autovac_analyze_timestamp=msg->m_analyzetime;

tabentry->autovac_analyze_count++;

}

{

tabentry->analyze_timestamp=msg->m_analyzetime;

tabentry->analyze_count++;

}

}

externDatumpg_stat_get_last_autovacuum_time(PG_FUNCTION_ARGS);

externDatumpg_stat_get_last_analyze_time(PG_FUNCTION_ARGS);

externDatumpg_stat_get_last_autoanalyze_time(PG_FUNCTION_ARGS);

externDatumpg_stat_get_vacuum_count(PG_FUNCTION_ARGS);

externDatumpg_stat_get_autovacuum_count(PG_FUNCTION_ARGS);

externDatumpg_stat_get_analyze_count(PG_FUNCTION_ARGS);

externDatumpg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS);

externDatumpg_stat_get_function_calls(PG_FUNCTION_ARGS);

externDatumpg_stat_get_function_time(PG_FUNCTION_ARGS);

PG_RETURN_TIMESTAMPTZ(result);

}

pg_stat_get_vacuum_count(PG_FUNCTION_ARGS)

{

Oidrelid=PG_GETARG_OID(0);

int64result;

PgStat_StatTabEntry*tabentry;

if ((tabentry=pgstat_fetch_stat_tabentry(relid))==NULL)

result=0;

result= (int64) (tabentry->vacuum_count);

PG_RETURN_INT64(result);

}

pg_stat_get_autovacuum_count(PG_FUNCTION_ARGS)

{

Oidrelid=PG_GETARG_OID(0);

int64result;

PgStat_StatTabEntry*tabentry;

if ((tabentry=pgstat_fetch_stat_tabentry(relid))==NULL)

result=0;

result= (int64) (tabentry->autovac_vacuum_count);

PG_RETURN_INT64(result);

}

pg_stat_get_analyze_count(PG_FUNCTION_ARGS)

{

Oidrelid=PG_GETARG_OID(0);

int64result;

PgStat_StatTabEntry*tabentry;

if ((tabentry=pgstat_fetch_stat_tabentry(relid))==NULL)

result=0;

result= (int64) (tabentry->analyze_count);

PG_RETURN_INT64(result);

}

pg_stat_get_autoanalyze_count(PG_FUNCTION_ARGS)

{

Oidrelid=PG_GETARG_OID(0);

int64result;

PgStat_StatTabEntry*tabentry;

if ((tabentry=pgstat_fetch_stat_tabentry(relid))==NULL)

result=0;

result= (int64) (tabentry->autovac_analyze_count);

PG_RETURN_INT64(result);

}

pg_stat_get_function_calls(PG_FUNCTION_ARGS)

{

DESCR("statistics: last manual analyze time for a table");

DATA(insertOID=2784 (pg_stat_get_last_autoanalyze_timePGNSPPGUID12100ffftfs101184"26"_null__null__null__null_pg_stat_get_last_autoanalyze_time_null__null__null_ ));

DESCR("statistics: last auto analyze time for a table");

DATA(insertOID=3054 (pg_stat_get_vacuum_countPGNSPPGUID12100ffftfs1020"26"_null__null__null__null_pg_stat_get_vacuum_count_null__null__null_ ));

DESCR("statistics: number of manual vacuums for a table");

DATA(insertOID=3055 (pg_stat_get_autovacuum_countPGNSPPGUID12100ffftfs1020"26"_null__null__null__null_pg_stat_get_autovacuum_count_null__null__null_ ));

DESCR("statistics: number of auto vacuums for a table");

DATA(insertOID=3056 (pg_stat_get_analyze_countPGNSPPGUID12100ffftfs1020"26"_null__null__null__null_pg_stat_get_analyze_count_null__null__null_ ));

DESCR("statistics: number of manual analyzes for a table");

DATA(insertOID=3057 (pg_stat_get_autoanalyze_countPGNSPPGUID12100ffftfs1020"26"_null__null__null__null_pg_stat_get_autoanalyze_count_null__null__null_ ));

DESCR("statistics: number of auto analyzes for a table");

DATA(insertOID=1936 (pg_stat_get_backend_idsetPGNSPPGUID1211000ffftts0023""_null__null__null__null_pg_stat_get_backend_idset_null__null__null_ ));

DESCR("statistics: currently active backend IDs");

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_ ));

PgStat_Counterblocks_hit;

TimestampTzvacuum_timestamp;/* user initiated vacuum */

PgStat_Countervacuum_count;

TimestampTzautovac_vacuum_timestamp;/* autovacuum initiated */

PgStat_Counterautovac_vacuum_count;

TimestampTzanalyze_timestamp;/* user initiated */

PgStat_Counteranalyze_count;

TimestampTzautovac_analyze_timestamp;/* autovacuum initiated */

PgStat_Counterautovac_analyze_count;

}PgStat_StatTabEntry;