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

Commit51d7741

Browse files
committed
Add new columns for tuple statistics on a database level to
pg_stat_database.
1 parentc4fdfb8 commit51d7741

File tree

8 files changed

+172
-12
lines changed

8 files changed

+172
-12
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 44 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.46 2007/02/07 23:11:29 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.47 2007/03/16 17:57:35 mha Exp $ -->
22

33
<chapter id="monitoring">
44
<title>Monitoring Database Activity</title>
@@ -265,8 +265,9 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
265265
<entry>One row per database, showing database OID, database name,
266266
number of active server processes connected to that database,
267267
number of transactions committed and rolled back in that database,
268-
total disk blocks read, and total buffer hits (i.e., block
269-
read requests avoided by finding the block already in buffer cache).
268+
total disk blocks read, total buffer hits (i.e., block
269+
read requests avoided by finding the block already in buffer cache),
270+
number of rows returned, inserted, updated and deleted.
270271
</entry>
271272
</row>
272273

@@ -502,6 +503,46 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
502503
</entry>
503504
</row>
504505

506+
<row>
507+
<entry><literal><function>pg_stat_get_db_tuples_returned</function>(<type>oid</type>)</literal></entry>
508+
<entry><type>bigint</type></entry>
509+
<entry>
510+
Number of tuples returned for database
511+
</entry>
512+
</row>
513+
514+
<row>
515+
<entry><literal><function>pg_stat_get_db_tuples_fetched</function>(<type>oid</type>)</literal></entry>
516+
<entry><type>bigint</type></entry>
517+
<entry>
518+
Number of tuples fetched for database
519+
</entry>
520+
</row>
521+
522+
<row>
523+
<entry><literal><function>pg_stat_get_db_tuples_inserted</function>(<type>oid</type>)</literal></entry>
524+
<entry><type>bigint</type></entry>
525+
<entry>
526+
Number of tuples inserted in database
527+
</entry>
528+
</row>
529+
530+
<row>
531+
<entry><literal><function>pg_stat_get_db_tuples_updated</function>(<type>oid</type>)</literal></entry>
532+
<entry><type>bigint</type></entry>
533+
<entry>
534+
Number of tuples updated in database
535+
</entry>
536+
</row>
537+
538+
<row>
539+
<entry><literal><function>pg_stat_get_db_tuples_deleted</function>(<type>oid</type>)</literal></entry>
540+
<entry><type>bigint</type></entry>
541+
<entry>
542+
Number of tuples deleted in database
543+
</entry>
544+
</row>
545+
505546
<row>
506547
<entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry>
507548
<entry><type>bigint</type></entry>

‎src/backend/catalog/system_views.sql

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2007, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.35 2007/01/05 22:19:25 momjian Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.36 2007/03/16 17:57:36 mha Exp $
77
*/
88

99
CREATEVIEWpg_rolesAS
@@ -357,5 +357,10 @@ CREATE VIEW pg_stat_database AS
357357
pg_stat_get_db_xact_rollback(D.oid)AS xact_rollback,
358358
pg_stat_get_db_blocks_fetched(D.oid)-
359359
pg_stat_get_db_blocks_hit(D.oid)AS blks_read,
360-
pg_stat_get_db_blocks_hit(D.oid)AS blks_hit
360+
pg_stat_get_db_blocks_hit(D.oid)AS blks_hit,
361+
pg_stat_get_db_tuples_returned(D.oid)AS tup_returned,
362+
pg_stat_get_db_tuples_fetched(D.oid)AS tup_fetched,
363+
pg_stat_get_db_tuples_inserted(D.oid)AS tup_inserted,
364+
pg_stat_get_db_tuples_updated(D.oid)AS tup_updated,
365+
pg_stat_get_db_tuples_deleted(D.oid)AS tup_deleted
361366
FROM pg_database D;

‎src/backend/postmaster/pgstat.c

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
*Copyright (c) 2001-2007, PostgreSQL Global Development Group
1515
*
16-
*$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.148 2007/03/01 20:06:56 tgl Exp $
16+
*$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.149 2007/03/16 17:57:36 mha Exp $
1717
* ----------
1818
*/
1919
#include"postgres.h"
@@ -1970,6 +1970,11 @@ pgstat_get_db_entry(Oid databaseid, bool create)
19701970
result->n_xact_rollback=0;
19711971
result->n_blocks_fetched=0;
19721972
result->n_blocks_hit=0;
1973+
result->n_tuples_returned=0;
1974+
result->n_tuples_fetched=0;
1975+
result->n_tuples_inserted=0;
1976+
result->n_tuples_updated=0;
1977+
result->n_tuples_deleted=0;
19731978
result->last_autovac_time=0;
19741979

19751980
memset(&hash_ctl,0,sizeof(hash_ctl));
@@ -2413,6 +2418,15 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
24132418
tabentry->blocks_hit+=tabmsg[i].t_blocks_hit;
24142419
}
24152420

2421+
/*
2422+
* Add table stats to the database entry.
2423+
*/
2424+
dbentry->n_tuples_returned+=tabmsg[i].t_tuples_returned;
2425+
dbentry->n_tuples_fetched+=tabmsg[i].t_tuples_fetched;
2426+
dbentry->n_tuples_inserted+=tabmsg[i].t_tuples_inserted;
2427+
dbentry->n_tuples_updated+=tabmsg[i].t_tuples_updated;
2428+
dbentry->n_tuples_deleted+=tabmsg[i].t_tuples_deleted;
2429+
24162430
/*
24172431
* And add the block IO to the database entry.
24182432
*/

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

Lines changed: 86 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.39 2007/02/27 23:48:08 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.40 2007/03/16 17:57:36 mha Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -55,6 +55,11 @@ extern Datum pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS);
5555
externDatumpg_stat_get_db_xact_rollback(PG_FUNCTION_ARGS);
5656
externDatumpg_stat_get_db_blocks_fetched(PG_FUNCTION_ARGS);
5757
externDatumpg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS);
58+
externDatumpg_stat_get_db_tuples_returned(PG_FUNCTION_ARGS);
59+
externDatumpg_stat_get_db_tuples_fetched(PG_FUNCTION_ARGS);
60+
externDatumpg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS);
61+
externDatumpg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS);
62+
externDatumpg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS);
5863

5964
externDatumpg_stat_clear_snapshot(PG_FUNCTION_ARGS);
6065
externDatumpg_stat_reset(PG_FUNCTION_ARGS);
@@ -672,6 +677,86 @@ pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS)
672677
}
673678

674679

680+
Datum
681+
pg_stat_get_db_tuples_returned(PG_FUNCTION_ARGS)
682+
{
683+
Oiddbid=PG_GETARG_OID(0);
684+
int64result;
685+
PgStat_StatDBEntry*dbentry;
686+
687+
if ((dbentry=pgstat_fetch_stat_dbentry(dbid))==NULL)
688+
result=0;
689+
else
690+
result= (int64) (dbentry->n_tuples_returned);
691+
692+
PG_RETURN_INT64(result);
693+
}
694+
695+
696+
Datum
697+
pg_stat_get_db_tuples_fetched(PG_FUNCTION_ARGS)
698+
{
699+
Oiddbid=PG_GETARG_OID(0);
700+
int64result;
701+
PgStat_StatDBEntry*dbentry;
702+
703+
if ((dbentry=pgstat_fetch_stat_dbentry(dbid))==NULL)
704+
result=0;
705+
else
706+
result= (int64) (dbentry->n_tuples_fetched);
707+
708+
PG_RETURN_INT64(result);
709+
}
710+
711+
712+
Datum
713+
pg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS)
714+
{
715+
Oiddbid=PG_GETARG_OID(0);
716+
int64result;
717+
PgStat_StatDBEntry*dbentry;
718+
719+
if ((dbentry=pgstat_fetch_stat_dbentry(dbid))==NULL)
720+
result=0;
721+
else
722+
result= (int64) (dbentry->n_tuples_inserted);
723+
724+
PG_RETURN_INT64(result);
725+
}
726+
727+
728+
Datum
729+
pg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS)
730+
{
731+
Oiddbid=PG_GETARG_OID(0);
732+
int64result;
733+
PgStat_StatDBEntry*dbentry;
734+
735+
if ((dbentry=pgstat_fetch_stat_dbentry(dbid))==NULL)
736+
result=0;
737+
else
738+
result= (int64) (dbentry->n_tuples_updated);
739+
740+
PG_RETURN_INT64(result);
741+
}
742+
743+
744+
Datum
745+
pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS)
746+
{
747+
Oiddbid=PG_GETARG_OID(0);
748+
int64result;
749+
PgStat_StatDBEntry*dbentry;
750+
751+
if ((dbentry=pgstat_fetch_stat_dbentry(dbid))==NULL)
752+
result=0;
753+
else
754+
result= (int64) (dbentry->n_tuples_deleted);
755+
756+
PG_RETURN_INT64(result);
757+
}
758+
759+
675760
/* Discard the active statistics snapshot */
676761
Datum
677762
pg_stat_clear_snapshot(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-2007, 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.389 2007/03/02 00:48:44 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.390 2007/03/16 17:57:36 mha Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200703011
56+
#defineCATALOG_VERSION_NO200703161
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, 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.447 2007/03/03 19:52:46 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.448 2007/03/16 17:57:36 mha Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2974,6 +2974,16 @@ DATA(insert OID = 1944 ( pg_stat_get_db_blocks_fetched PGNSP PGUID 12 1 0 f f t
29742974
DESCR("Statistics: Blocks fetched for database");
29752975
DATA(insertOID=1945 (pg_stat_get_db_blocks_hitPGNSPPGUID1210fftfs120"26"_null__null__null_pg_stat_get_db_blocks_hit-_null_ ));
29762976
DESCR("Statistics: Blocks found in cache for database");
2977+
DATA(insertOID=2758 (pg_stat_get_db_tuples_returnedPGNSPPGUID1210fftfs120"26"_null__null__null_pg_stat_get_db_tuples_returned-_null_ ));
2978+
DESCR("Statistics: Tuples returned for database");
2979+
DATA(insertOID=2759 (pg_stat_get_db_tuples_fetchedPGNSPPGUID1210fftfs120"26"_null__null__null_pg_stat_get_db_tuples_fetched-_null_ ));
2980+
DESCR("Statistics: Tuples fetched for database");
2981+
DATA(insertOID=2760 (pg_stat_get_db_tuples_insertedPGNSPPGUID1210fftfs120"26"_null__null__null_pg_stat_get_db_tuples_inserted-_null_ ));
2982+
DESCR("Statistics: Tuples inserted in database");
2983+
DATA(insertOID=2761 (pg_stat_get_db_tuples_updatedPGNSPPGUID1210fftfs120"26"_null__null__null_pg_stat_get_db_tuples_updated-_null_ ));
2984+
DESCR("Statistics: Tuples updated in database");
2985+
DATA(insertOID=2762 (pg_stat_get_db_tuples_deletedPGNSPPGUID1210fftfs120"26"_null__null__null_pg_stat_get_db_tuples_deleted-_null_ ));
2986+
DESCR("Statistics: Tuples deleted in database");
29772987
DATA(insertOID=2230 (pg_stat_clear_snapshotPGNSPPGUID1210ffffv02278""_null__null__null_pg_stat_clear_snapshot-_null_ ));
29782988
DESCR("Statistics: Discard current transaction's statistics snapshot");
29792989
DATA(insertOID=2274 (pg_stat_resetPGNSPPGUID1210ffffv02278""_null__null__null_pg_stat_reset-_null_ ));

‎src/include/pgstat.h

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55
*
66
*Copyright (c) 2001-2007, PostgreSQL Global Development Group
77
*
8-
*$PostgreSQL: pgsql/src/include/pgstat.h,v 1.54 2007/02/09 16:12:19 tgl Exp $
8+
*$PostgreSQL: pgsql/src/include/pgstat.h,v 1.55 2007/03/16 17:57:36 mha Exp $
99
* ----------
1010
*/
1111
#ifndefPGSTAT_H
@@ -251,6 +251,11 @@ typedef struct PgStat_StatDBEntry
251251
PgStat_Countern_xact_rollback;
252252
PgStat_Countern_blocks_fetched;
253253
PgStat_Countern_blocks_hit;
254+
PgStat_Countern_tuples_returned;
255+
PgStat_Countern_tuples_fetched;
256+
PgStat_Countern_tuples_inserted;
257+
PgStat_Countern_tuples_updated;
258+
PgStat_Countern_tuples_deleted;
254259
TimestampTzlast_autovac_time;
255260

256261
/*

‎src/test/regress/expected/rules.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1292,7 +1292,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
12921292
pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS current_query, pg_stat_get_backend_waiting(s.backendid) AS waiting, pg_stat_get_backend_txn_start(s.backendid) AS txn_start, pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS backend_start, pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid) AS client_port FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.oid));
12931293
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
12941294
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, 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 FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
1295-
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit FROM pg_database d;
1295+
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted FROM pg_database d;
12961296
pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE (pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::"name", 'pg_toast'::"name", 'information_schema'::"name"]));
12971297
pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE (pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::"name", 'pg_toast'::"name", 'information_schema'::"name"]));
12981298
pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE (pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::"name", 'pg_toast'::"name", 'information_schema'::"name"]));

0 commit comments

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