Skip to content

Commit 46aa77c

Browse files
tglsfdctglsfdc
committedAug 8, 2010
Add stats functions and views to provide access to a transaction's own
statistics counts. These numbers are being accumulated but haven't yet been transmitted to the collector (and won't be, until the transaction ends). For some purposes, though, it's handy to be able to look at them. Joel Jacobson, reviewed by Itagaki Takahiro
1 parent 83f5491 commit 46aa77c

File tree

8 files changed

+525
-67
lines changed

8 files changed

+525
-67
lines changed
 

‎doc/src/sgml/monitoring.sgml

Lines changed: 147 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.81 2010/07/29 19:34:40 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.82 2010/08/08 16:27:03 tgl Exp $ -->
22

33
<chapter id="monitoring">
44
<title>Monitoring Database Activity</title>
@@ -124,8 +124,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
124124

125125
<para>
126126
<productname>PostgreSQL</productname> also supports reporting of the exact
127-
command currently being executed by other server processes. This is an
128-
facility independent of the collector process.
127+
command currently being executed by other server processes. This
128+
facility is independent of the collector process.
129129
</para>
130130

131131
<sect2 id="monitoring-stats-setup">
@@ -165,8 +165,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
165165
</para>
166166

167167
<para>
168-
The statistics collector communicates with the backends needing
169-
information (including autovacuum) through temporary files.
168+
The statistics collector transmits the collected
169+
information to backends (including autovacuum) through temporary files.
170170
These files are stored in the <filename>pg_stat_tmp</filename> subdirectory.
171171
When the postmaster shuts down, a permanent copy of the statistics
172172
data is stored in the <filename>global</filename> subdirectory. For increased
@@ -219,6 +219,16 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
219219
statistical information will cause a new snapshot to be fetched.
220220
</para>
221221

222+
<para>
223+
A transaction can also see its own statistics (as yet untransmitted to the
224+
collector) in the views <structname>pg_stat_xact_all_tables</>,
225+
<structname>pg_stat_xact_sys_tables</>,
226+
<structname>pg_stat_xact_user_tables</>, and
227+
<structname>pg_stat_xact_user_functions</>, or via these views' underlying
228+
functions. These numbers do not act as stated above; instead they update
229+
continuously throughout the transaction.
230+
</para>
231+
222232
<table id="monitoring-stats-views-table">
223233
<title>Standard Statistics Views</title>
224234

@@ -299,6 +309,27 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
299309
tables are shown.</entry>
300310
</row>
301311

312+
<row>
313+
<entry><structname>pg_stat_xact_all_tables</></entry>
314+
<entry>Similar to <structname>pg_stat_all_tables</>, but counts actions
315+
taken so far within the current transaction (which are <emphasis>not</>
316+
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>
319+
</row>
320+
321+
<row>
322+
<entry><structname>pg_stat_xact_sys_tables</></entry>
323+
<entry>Same as <structname>pg_stat_xact_all_tables</>, except that only
324+
system tables are shown.</entry>
325+
</row>
326+
327+
<row>
328+
<entry><structname>pg_stat_xact_user_tables</></entry>
329+
<entry>Same as <structname>pg_stat_xact_all_tables</>, except that only
330+
user tables are shown.</entry>
331+
</row>
332+
302333
<row>
303334
<entry><structname>pg_stat_all_indexes</></entry>
304335
<entry>For each index in the current database,
@@ -395,6 +426,13 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
395426
</entry>
396427
</row>
397428

429+
<row>
430+
<entry><structname>pg_stat_xact_user_functions</></entry>
431+
<entry>Similar to <structname>pg_stat_user_functions</>, but counts only
432+
calls during the current transaction (which are <emphasis>not</>
433+
yet included in <structname>pg_stat_user_functions</>).</entry>
434+
</row>
435+
398436
</tbody>
399437
</tgroup>
400438
</table>
@@ -405,7 +443,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
405443
</para>
406444

407445
<para>
408-
Beginning in <productname>PostgreSQL</productname> 8.1, indexes can be
446+
Indexes can be
409447
used either directly or via <quote>bitmap scans</>. In a bitmap scan
410448
the output of several indexes can be combined via AND or OR rules;
411449
so it is difficult to associate individual heap row fetches
@@ -680,6 +718,82 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
680718
</entry>
681719
</row>
682720

721+
<row>
722+
<entry><literal><function>pg_stat_get_xact_numscans</function>(<type>oid</type>)</literal></entry>
723+
<entry><type>bigint</type></entry>
724+
<entry>
725+
Number of sequential scans done when argument is a table,
726+
or number of index scans done when argument is an index, in the current transaction
727+
</entry>
728+
</row>
729+
730+
<row>
731+
<entry><literal><function>pg_stat_get_xact_tuples_returned</function>(<type>oid</type>)</literal></entry>
732+
<entry><type>bigint</type></entry>
733+
<entry>
734+
Number of rows read by sequential scans when argument is a table,
735+
or number of index entries returned when argument is an index, in the current transaction
736+
</entry>
737+
</row>
738+
739+
<row>
740+
<entry><literal><function>pg_stat_get_xact_tuples_fetched</function>(<type>oid</type>)</literal></entry>
741+
<entry><type>bigint</type></entry>
742+
<entry>
743+
Number of table rows fetched by bitmap scans when argument is a table,
744+
or table rows fetched by simple index scans using the index
745+
when argument is an index, in the current transaction
746+
</entry>
747+
</row>
748+
749+
<row>
750+
<entry><literal><function>pg_stat_get_xact_tuples_inserted</function>(<type>oid</type>)</literal></entry>
751+
<entry><type>bigint</type></entry>
752+
<entry>
753+
Number of rows inserted into table, in the current transaction
754+
</entry>
755+
</row>
756+
757+
<row>
758+
<entry><literal><function>pg_stat_get_xact_tuples_updated</function>(<type>oid</type>)</literal></entry>
759+
<entry><type>bigint</type></entry>
760+
<entry>
761+
Number of rows updated in table (includes HOT updates), in the current transaction
762+
</entry>
763+
</row>
764+
765+
<row>
766+
<entry><literal><function>pg_stat_get_xact_tuples_deleted</function>(<type>oid</type>)</literal></entry>
767+
<entry><type>bigint</type></entry>
768+
<entry>
769+
Number of rows deleted from table, in the current transaction
770+
</entry>
771+
</row>
772+
773+
<row>
774+
<entry><literal><function>pg_stat_get_xact_tuples_hot_updated</function>(<type>oid</type>)</literal></entry>
775+
<entry><type>bigint</type></entry>
776+
<entry>
777+
Number of rows HOT-updated in table, in the current transaction
778+
</entry>
779+
</row>
780+
781+
<row>
782+
<entry><literal><function>pg_stat_get_xact_blocks_fetched</function>(<type>oid</type>)</literal></entry>
783+
<entry><type>bigint</type></entry>
784+
<entry>
785+
Number of disk block fetch requests for table or index, in the current transaction
786+
</entry>
787+
</row>
788+
789+
<row>
790+
<entry><literal><function>pg_stat_get_xact_blocks_hit</function>(<type>oid</type>)</literal></entry>
791+
<entry><type>bigint</type></entry>
792+
<entry>
793+
Number of disk block requests found in cache for table or index, in the current transaction
794+
</entry>
795+
</row>
796+
683797
<row>
684798
<!-- See also the entry for this in func.sgml -->
685799
<entry><literal><function>pg_backend_pid</function>()</literal></entry>
@@ -726,6 +840,33 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
726840
</entry>
727841
</row>
728842

843+
<row>
844+
<entry><literal><function>pg_stat_get_xact_function_calls</function>(<type>oid</type>)</literal></entry>
845+
<entry><type>bigint</type></entry>
846+
<entry>
847+
Number of times the function has been called, in the current transaction.
848+
</entry>
849+
</row>
850+
851+
<row>
852+
<entry><literal><function>pg_stat_get_xact_function_time</function>(<type>oid</type>)</literal></entry>
853+
<entry><type>bigint</type></entry>
854+
<entry>
855+
Total wall clock time spent in the function, in microseconds, in the
856+
current transaction. Includes the time spent in functions called by
857+
this one.
858+
</entry>
859+
</row>
860+
861+
<row>
862+
<entry><literal><function>pg_stat_get_xact_function_self_time</function>(<type>oid</type>)</literal></entry>
863+
<entry><type>bigint</type></entry>
864+
<entry>
865+
Time spent in only this function, in the current transaction. Time
866+
spent in called functions is excluded.
867+
</entry>
868+
</row>
869+
729870
<row>
730871
<entry><literal><function>pg_stat_get_backend_idset</function>()</literal></entry>
731872
<entry><type>setof integer</type></entry>

‎src/backend/catalog/system_views.sql

Lines changed: 43 additions & 1 deletion
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.66 2010/04/26 14:22:37 momjian Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.67 2010/08/08 16:27:03 tgl Exp $
77
*/
88

99
CREATE VIEW pg_roles AS
@@ -208,16 +208,46 @@ CREATE VIEW pg_stat_all_tables AS
208208
WHERE C.relkind IN ('r', 't')
209209
GROUP BY C.oid, N.nspname, C.relname;
210210

211+
CREATE VIEW pg_stat_xact_all_tables AS
212+
SELECT
213+
C.oid AS relid,
214+
N.nspname AS schemaname,
215+
C.relname AS relname,
216+
pg_stat_get_xact_numscans(C.oid) AS seq_scan,
217+
pg_stat_get_xact_tuples_returned(C.oid) AS seq_tup_read,
218+
sum(pg_stat_get_xact_numscans(I.indexrelid))::bigint AS idx_scan,
219+
sum(pg_stat_get_xact_tuples_fetched(I.indexrelid))::bigint +
220+
pg_stat_get_xact_tuples_fetched(C.oid) AS idx_tup_fetch,
221+
pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins,
222+
pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd,
223+
pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del,
224+
pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd
225+
FROM pg_class C LEFT JOIN
226+
pg_index I ON C.oid = I.indrelid
227+
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
228+
WHERE C.relkind IN ('r', 't')
229+
GROUP BY C.oid, N.nspname, C.relname;
230+
211231
CREATE VIEW pg_stat_sys_tables AS
212232
SELECT * FROM pg_stat_all_tables
213233
WHERE schemaname IN ('pg_catalog', 'information_schema') OR
214234
schemaname ~ '^pg_toast';
215235

236+
CREATE VIEW pg_stat_xact_sys_tables AS
237+
SELECT * FROM pg_stat_xact_all_tables
238+
WHERE schemaname IN ('pg_catalog', 'information_schema') OR
239+
schemaname ~ '^pg_toast';
240+
216241
CREATE VIEW pg_stat_user_tables AS
217242
SELECT * FROM pg_stat_all_tables
218243
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
219244
schemaname !~ '^pg_toast';
220245

246+
CREATE VIEW pg_stat_xact_user_tables AS
247+
SELECT * FROM pg_stat_xact_all_tables
248+
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
249+
schemaname !~ '^pg_toast';
250+
221251
CREATE VIEW pg_statio_all_tables AS
222252
SELECT
223253
C.oid AS relid,
@@ -375,6 +405,18 @@ CREATE VIEW pg_stat_user_functions AS
375405
WHERE P.prolang != 12 -- fast check to eliminate built-in functions
376406
AND pg_stat_get_function_calls(P.oid) IS NOT NULL;
377407

408+
CREATE VIEW pg_stat_xact_user_functions AS
409+
SELECT
410+
P.oid AS funcid,
411+
N.nspname AS schemaname,
412+
P.proname AS funcname,
413+
pg_stat_get_xact_function_calls(P.oid) AS calls,
414+
pg_stat_get_xact_function_time(P.oid) / 1000 AS total_time,
415+
pg_stat_get_xact_function_self_time(P.oid) / 1000 AS self_time
416+
FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
417+
WHERE P.prolang != 12 -- fast check to eliminate built-in functions
418+
AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;
419+
378420
CREATE VIEW pg_stat_bgwriter AS
379421
SELECT
380422
pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,

‎src/backend/postmaster/pgstat.c

Lines changed: 44 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.204 2010/07/06 19:18:57 momjian Exp $
16+
* $PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.205 2010/08/08 16:27:03 tgl Exp $
1717
* ----------
1818
*/
1919
#include "postgres.h"
@@ -1402,6 +1402,23 @@ pgstat_init_function_usage(FunctionCallInfoData *fcinfo,
14021402
INSTR_TIME_SET_CURRENT(fcu->f_start);
14031403
}
14041404

1405+
/*
1406+
* find_funcstat_entry - find any existing PgStat_BackendFunctionEntry entry
1407+
* for specified function
1408+
*
1409+
* If no entry, return NULL, don't create a new one
1410+
*/
1411+
PgStat_BackendFunctionEntry *
1412+
find_funcstat_entry(Oid func_id)
1413+
{
1414+
if (pgStatFunctions == NULL)
1415+
return NULL;
1416+
1417+
return (PgStat_BackendFunctionEntry *) hash_search(pgStatFunctions,
1418+
(void *) &func_id,
1419+
HASH_FIND, NULL);
1420+
}
1421+
14051422
/*
14061423
* Calculate function call usage and update stat counters.
14071424
* Called by the executor after invoking a function.
@@ -1560,6 +1577,32 @@ get_tabstat_entry(Oid rel_id, bool isshared)
15601577
return entry;
15611578
}
15621579

1580+
/*
1581+
* find_tabstat_entry - find any existing PgStat_TableStatus entry for rel
1582+
*
1583+
* If no entry, return NULL, don't create a new one
1584+
*/
1585+
PgStat_TableStatus *
1586+
find_tabstat_entry(Oid rel_id)
1587+
{
1588+
PgStat_TableStatus *entry;
1589+
TabStatusArray *tsa;
1590+
int i;
1591+
1592+
for (tsa = pgStatTabList; tsa != NULL; tsa = tsa->tsa_next)
1593+
{
1594+
for (i = 0; i < tsa->tsa_used; i++)
1595+
{
1596+
entry = &tsa->tsa_entries[i];
1597+
if (entry->t_id == rel_id)
1598+
return entry;
1599+
}
1600+
}
1601+
1602+
/* Not present */
1603+
return NULL;
1604+
}
1605+
15631606
/*
15641607
* get_tabstat_stack_level - add a new (sub)transaction stack entry if needed
15651608
*/

0 commit comments

Comments
 (0)