<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.81 2010/07/29 19:34:40 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.82 2010/08/08 16:27:03 tgl Exp $ -->

<chapter id="monitoring">

<title>Monitoring Database Activity</title>

<para>

<productname>PostgreSQL</productname> also supports reporting of the exact

command currently being executed by other server processes. This is an

facility independent of the collector process.

command currently being executed by other server processes. This

facilityisindependent of the collector process.

</para>

<sect2 id="monitoring-stats-setup">

</para>

<para>

The statistics collectorcommunicates withthebackends needing

information (including autovacuum) through temporary files.

The statistics collectortransmitsthecollected

informationto backends(including autovacuum) through temporary files.

These files are stored in the <filename>pg_stat_tmp</filename> subdirectory.

When the postmaster shuts down, a permanent copy of the statistics

data is stored in the <filename>global</filename> subdirectory. For increased

statistical information will cause a new snapshot to be fetched.

</para>

<para>

A transaction can also see its own statistics (as yet untransmitted to the

collector) in the views <structname>pg_stat_xact_all_tables</>,

<structname>pg_stat_xact_sys_tables</>,

<structname>pg_stat_xact_user_tables</>, and

<structname>pg_stat_xact_user_functions</>, or via these views' underlying

functions. These numbers do not act as stated above; instead they update

continuously throughout the transaction.

</para>

<table id="monitoring-stats-views-table">

<title>Standard Statistics Views</title>

tables are shown.</entry>

</row>

<row>

<entry><structname>pg_stat_xact_all_tables</></entry>

<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 and last-vacuum and

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

</row>

<row>

<entry><structname>pg_stat_xact_sys_tables</></entry>

<entry>Same as <structname>pg_stat_xact_all_tables</>, except that only

system tables are shown.</entry>

</row>

<row>

<entry><structname>pg_stat_xact_user_tables</></entry>

<entry>Same as <structname>pg_stat_xact_all_tables</>, except that only

user tables are shown.</entry>

</row>

<row>

<entry><structname>pg_stat_all_indexes</></entry>

<entry>For each index in the current database,

</entry>

</row>

<row>

<entry><structname>pg_stat_xact_user_functions</></entry>

<entry>Similar to <structname>pg_stat_user_functions</>, but counts only

calls during the current transaction (which are <emphasis>not</>

yet included in <structname>pg_stat_user_functions</>).</entry>

</row>

</tbody>

</tgroup>

</table>

</para>

<para>

Beginning in <productname>PostgreSQL</productname> 8.1, indexes can be

Indexes can be

used either directly or via <quote>bitmap scans</>. In a bitmap scan

the output of several indexes can be combined via AND or OR rules;

so it is difficult to associate individual heap row fetches

</entry>

</row>

<row>

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

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

<entry>

Number of sequential scans done when argument is a table,

or number of index scans done when argument is an index, in the current transaction

</entry>

</row>

<row>

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

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

<entry>

Number of rows read by sequential scans when argument is a table,

or number of index entries returned when argument is an index, in the current transaction

</entry>

</row>

<row>

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

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

<entry>

Number of table rows fetched by bitmap scans when argument is a table,

or table rows fetched by simple index scans using the index

when argument is an index, in the current transaction

</entry>

</row>

<row>

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

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

<entry>

Number of rows inserted into table, in the current transaction

</entry>

</row>

<row>

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

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

<entry>

Number of rows updated in table (includes HOT updates), in the current transaction

</entry>

</row>

<row>

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

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

<entry>

Number of rows deleted from table, in the current transaction

</entry>

</row>

<row>

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

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

<entry>

Number of rows HOT-updated in table, in the current transaction

</entry>

</row>

<row>

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

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

<entry>

Number of disk block fetch requests for table or index, in the current transaction

</entry>

</row>

<row>

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

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

<entry>

Number of disk block requests found in cache for table or index, in the current transaction

</entry>

</row>

<row>

<!-- See also the entry for this in func.sgml -->

<entry><literal><function>pg_backend_pid</function>()</literal></entry>

</entry>

</row>

<row>

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

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

<entry>

Number of times the function has been called, in the current transaction.

</entry>

</row>

<row>

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

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

<entry>

Total wall clock time spent in the function, in microseconds, in the

current transaction. Includes the time spent in functions called by

this one.

</entry>

</row>

<row>

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

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

<entry>

Time spent in only this function, in the current transaction. Time

spent in called functions is excluded.

</entry>

</row>

<row>

<entry><literal><function>pg_stat_get_backend_idset</function>()</literal></entry>

<entry><type>setof integer</type></entry>

WHEREC.relkindIN ('r','t')

GROUP BYC.oid,N.nspname,C.relname;

C.oidAS relid,

N.nspnameAS schemaname,

C.relnameAS relname,

pg_stat_get_xact_numscans(C.oid)AS seq_scan,

pg_stat_get_xact_tuples_returned(C.oid)AS seq_tup_read,

sum(pg_stat_get_xact_numscans(I.indexrelid))::bigintAS idx_scan,

sum(pg_stat_get_xact_tuples_fetched(I.indexrelid))::bigint+

pg_stat_get_xact_tuples_fetched(C.oid)AS idx_tup_fetch,

pg_stat_get_xact_tuples_inserted(C.oid)AS n_tup_ins,

pg_stat_get_xact_tuples_updated(C.oid)AS n_tup_upd,

pg_stat_get_xact_tuples_deleted(C.oid)AS n_tup_del,

pg_stat_get_xact_tuples_hot_updated(C.oid)AS n_tup_hot_upd

FROM pg_class CLEFT JOIN

pg_index IONC.oid=I.indrelid

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

WHEREC.relkindIN ('r','t')

GROUP BYC.oid,N.nspname,C.relname;

SELECT*FROM pg_stat_all_tables

WHERE schemanameIN ('pg_catalog','information_schema')OR

schemaname ~'^pg_toast';

SELECT*FROM pg_stat_xact_all_tables

WHERE schemanameIN ('pg_catalog','information_schema')OR

schemaname ~'^pg_toast';

SELECT*FROM pg_stat_all_tables

WHERE schemaname NOTIN ('pg_catalog','information_schema')AND

schemaname !~'^pg_toast';

SELECT*FROM pg_stat_xact_all_tables

WHERE schemaname NOTIN ('pg_catalog','information_schema')AND

schemaname !~'^pg_toast';

C.oidAS relid,

WHEREP.prolang!=12-- fast check to eliminate built-in functions

AND pg_stat_get_function_calls(P.oid)IS NOT NULL;

P.oidAS funcid,

N.nspnameAS schemaname,

P.pronameAS funcname,

pg_stat_get_xact_function_calls(P.oid)AS calls,

pg_stat_get_xact_function_time(P.oid)/1000AS total_time,

pg_stat_get_xact_function_self_time(P.oid)/1000AS self_time

FROM pg_proc PLEFT JOIN pg_namespace NON (N.oid=P.pronamespace)

WHEREP.prolang!=12-- fast check to eliminate built-in functions

AND pg_stat_get_xact_function_calls(P.oid)IS NOT NULL;

pg_stat_get_bgwriter_timed_checkpoints()AS checkpoints_timed,

INSTR_TIME_SET_CURRENT(fcu->f_start);

}

find_funcstat_entry(Oidfunc_id)

{

if (pgStatFunctions==NULL)

returnNULL;

return (PgStat_BackendFunctionEntry*)hash_search(pgStatFunctions,

(void*)&func_id,

HASH_FIND,NULL);

}

returnentry;

}

find_tabstat_entry(Oidrel_id)

{

PgStat_TableStatus*entry;

TabStatusArray*tsa;

inti;

for (tsa=pgStatTabList;tsa!=NULL;tsa=tsa->tsa_next)

{

for (i=0;i<tsa->tsa_used;i++)

{

entry=&tsa->tsa_entries[i];

if (entry->t_id==rel_id)

returnentry;

}

}

returnNULL;

}