Skip to content

Commit 8964dbd

Browse files
ItGackyItGacky
committed
Add buffer access counters to pg_stat_statements.
This uses the same infrastructure with EXPLAIN BUFFERS to support {shared|local}_blks_{hit|read|written} andtemp_blks_{read|written} columns in the pg_stat_statements view. The dumped file format also updated. Thanks to Robert Haas for the review.
1 parent b1ee6da commit 8964dbd

File tree

3 files changed

+169
-55
lines changed

3 files changed

+169
-55
lines changed

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 66 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
* Copyright (c) 2008-2010, PostgreSQL Global Development Group
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/contrib/pg_stat_statements/pg_stat_statements.c,v 1.11 2010/01/02 16:57:32 momjian Exp $
17+
* $PostgreSQL: pgsql/contrib/pg_stat_statements/pg_stat_statements.c,v 1.12 2010/01/08 00:38:19 itagaki Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -26,6 +26,7 @@
2626
#include "catalog/pg_type.h"
2727
#include "executor/executor.h"
2828
#include "executor/instrument.h"
29+
#include "funcapi.h"
2930
#include "mb/pg_wchar.h"
3031
#include "miscadmin.h"
3132
#include "pgstat.h"
@@ -44,7 +45,7 @@ PG_MODULE_MAGIC;
4445
#define PGSS_DUMP_FILE "global/pg_stat_statements.stat"
4546

4647
/* This constant defines the magic number in the stats file header */
47-
static const uint32 PGSS_FILE_HEADER = 0x20081202;
48+
static const uint32 PGSS_FILE_HEADER = 0x20100108;
4849

4950
/* XXX: Should USAGE_EXEC reflect execution time and/or buffer usage? */
5051
#define USAGE_EXEC(duration) (1.0)
@@ -75,10 +76,18 @@ typedef struct pgssHashKey
7576
*/
7677
typedef struct Counters
7778
{
78-
int64 calls; /* # of times executed */
79-
double total_time; /* total execution time in seconds */
80-
int64 rows; /* total # of retrieved or affected rows */
81-
double usage; /* usage factor */
79+
int64 calls; /* # of times executed */
80+
double total_time; /* total execution time in seconds */
81+
int64 rows; /* total # of retrieved or affected rows */
82+
int64 shared_blks_hit; /* # of shared buffer hits */
83+
int64 shared_blks_read; /* # of shared disk blocks read */
84+
int64 shared_blks_written;/* # of shared disk blocks written */
85+
int64 local_blks_hit; /* # of local buffer hits */
86+
int64 local_blks_read; /* # of local disk blocks read */
87+
int64 local_blks_written; /* # of local disk blocks written */
88+
int64 temp_blks_read; /* # of temp blocks read */
89+
int64 temp_blks_written; /* # of temp blocks written */
90+
double usage; /* usage factor */
8291
} Counters;
8392

8493
/*
@@ -129,7 +138,8 @@ typedef enum
129138
PGSS_TRACK_ALL /* all statements, including nested ones */
130139
} PGSSTrackLevel;
131140

132-
static const struct config_enum_entry track_options[] = {
141+
static const struct config_enum_entry track_options[] =
142+
{
133143
{"none", PGSS_TRACK_NONE, false},
134144
{"top", PGSS_TRACK_TOP, false},
135145
{"all", PGSS_TRACK_ALL, false},
@@ -169,7 +179,8 @@ static void pgss_ProcessUtility(Node *parsetree,
169179
DestReceiver *dest, char *completionTag);
170180
static uint32 pgss_hash_fn(const void *key, Size keysize);
171181
static int pgss_match_fn(const void *key1, const void *key2, Size keysize);
172-
static void pgss_store(const char *query, double total_time, uint64 rows);
182+
static void pgss_store(const char *query, double total_time, uint64 rows,
183+
const BufferUsage *bufusage);
173184
static Size pgss_memsize(void);
174185
static pgssEntry *entry_alloc(pgssHashKey *key);
175186
static void entry_dealloc(void);
@@ -558,7 +569,8 @@ pgss_ExecutorEnd(QueryDesc *queryDesc)
558569

559570
pgss_store(queryDesc->sourceText,
560571
queryDesc->totaltime->total,
561-
queryDesc->estate->es_processed);
572+
queryDesc->estate->es_processed,
573+
&queryDesc->totaltime->bufusage);
562574
}
563575

564576
if (prev_ExecutorEnd)
@@ -580,7 +592,9 @@ pgss_ProcessUtility(Node *parsetree, const char *queryString,
580592
instr_time start;
581593
instr_time duration;
582594
uint64 rows = 0;
595+
BufferUsage bufusage;
583596

597+
bufusage = pgBufferUsage;
584598
INSTR_TIME_SET_CURRENT(start);
585599

586600
nested_level++;
@@ -609,7 +623,26 @@ pgss_ProcessUtility(Node *parsetree, const char *queryString,
609623
sscanf(completionTag, "COPY " UINT64_FORMAT, &rows) != 1)
610624
rows = 0;
611625

612-
pgss_store(queryString, INSTR_TIME_GET_DOUBLE(duration), rows);
626+
/* calc differences of buffer counters. */
627+
bufusage.shared_blks_hit =
628+
pgBufferUsage.shared_blks_hit - bufusage.shared_blks_hit;
629+
bufusage.shared_blks_read =
630+
pgBufferUsage.shared_blks_read - bufusage.shared_blks_read;
631+
bufusage.shared_blks_written =
632+
pgBufferUsage.shared_blks_written - bufusage.shared_blks_written;
633+
bufusage.local_blks_hit =
634+
pgBufferUsage.local_blks_hit - bufusage.local_blks_hit;
635+
bufusage.local_blks_read =
636+
pgBufferUsage.local_blks_read - bufusage.local_blks_read;
637+
bufusage.local_blks_written =
638+
pgBufferUsage.local_blks_written - bufusage.local_blks_written;
639+
bufusage.temp_blks_read =
640+
pgBufferUsage.temp_blks_read - bufusage.temp_blks_read;
641+
bufusage.temp_blks_written =
642+
pgBufferUsage.temp_blks_written - bufusage.temp_blks_written;
643+
644+
pgss_store(queryString, INSTR_TIME_GET_DOUBLE(duration), rows,
645+
&bufusage);
613646
}
614647
else
615648
{
@@ -660,7 +693,8 @@ pgss_match_fn(const void *key1, const void *key2, Size keysize)
660693
* Store some statistics for a statement.
661694
*/
662695
static void
663-
pgss_store(const char *query, double total_time, uint64 rows)
696+
pgss_store(const char *query, double total_time, uint64 rows,
697+
const BufferUsage *bufusage)
664698
{
665699
pgssHashKey key;
666700
double usage;
@@ -706,6 +740,14 @@ pgss_store(const char *query, double total_time, uint64 rows)
706740
e->counters.calls += 1;
707741
e->counters.total_time += total_time;
708742
e->counters.rows += rows;
743+
e->counters.shared_blks_hit += bufusage->shared_blks_hit;
744+
e->counters.shared_blks_read += bufusage->shared_blks_read;
745+
e->counters.shared_blks_written += bufusage->shared_blks_written;
746+
e->counters.local_blks_hit += bufusage->local_blks_hit;
747+
e->counters.local_blks_read += bufusage->local_blks_read;
748+
e->counters.local_blks_written += bufusage->local_blks_written;
749+
e->counters.temp_blks_read += bufusage->temp_blks_read;
750+
e->counters.temp_blks_written += bufusage->temp_blks_written;
709751
e->counters.usage += usage;
710752
SpinLockRelease(&e->mutex);
711753
}
@@ -727,7 +769,7 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
727769
PG_RETURN_VOID();
728770
}
729771

730-
#define PG_STAT_STATEMENTS_COLS 6
772+
#define PG_STAT_STATEMENTS_COLS 14
731773

732774
/*
733775
* Retrieve statement statistics.
@@ -761,23 +803,13 @@ pg_stat_statements(PG_FUNCTION_ARGS)
761803
errmsg("materialize mode required, but it is not " \
762804
"allowed in this context")));
763805

806+
/* Build a tuple descriptor for our result type */
807+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
808+
elog(ERROR, "return type must be a row type");
809+
764810
per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
765811
oldcontext = MemoryContextSwitchTo(per_query_ctx);
766812

767-
tupdesc = CreateTemplateTupleDesc(PG_STAT_STATEMENTS_COLS, false);
768-
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "userid",
769-
OIDOID, -1, 0);
770-
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "dbid",
771-
OIDOID, -1, 0);
772-
TupleDescInitEntry(tupdesc, (AttrNumber) 3, "query",
773-
TEXTOID, -1, 0);
774-
TupleDescInitEntry(tupdesc, (AttrNumber) 4, "calls",
775-
INT8OID, -1, 0);
776-
TupleDescInitEntry(tupdesc, (AttrNumber) 5, "total_time",
777-
FLOAT8OID, -1, 0);
778-
TupleDescInitEntry(tupdesc, (AttrNumber) 6, "rows",
779-
INT8OID, -1, 0);
780-
781813
tupstore = tuplestore_begin_heap(true, false, work_mem);
782814
rsinfo->returnMode = SFRM_Materialize;
783815
rsinfo->setResult = tupstore;
@@ -829,6 +861,14 @@ pg_stat_statements(PG_FUNCTION_ARGS)
829861
values[i++] = Int64GetDatumFast(tmp.calls);
830862
values[i++] = Float8GetDatumFast(tmp.total_time);
831863
values[i++] = Int64GetDatumFast(tmp.rows);
864+
values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
865+
values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
866+
values[i++] = Int64GetDatumFast(tmp.shared_blks_written);
867+
values[i++] = Int64GetDatumFast(tmp.local_blks_hit);
868+
values[i++] = Int64GetDatumFast(tmp.local_blks_read);
869+
values[i++] = Int64GetDatumFast(tmp.local_blks_written);
870+
values[i++] = Int64GetDatumFast(tmp.temp_blks_read);
871+
values[i++] = Int64GetDatumFast(tmp.temp_blks_written);
832872

833873
Assert(i == PG_STAT_STATEMENTS_COLS);
834874

‎contrib/pg_stat_statements/pg_stat_statements.sql.in

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* $PostgreSQL: pgsql/contrib/pg_stat_statements/pg_stat_statements.sql.in,v 1.1 2009/01/04 22:19:59 tgl Exp $ */
1+
/* $PostgreSQL: pgsql/contrib/pg_stat_statements/pg_stat_statements.sql.in,v 1.2 2010/01/08 00:38:19 itagaki Exp $ */
22

33
-- Adjust this setting to control where the objects get created.
44
SET search_path = public;
@@ -15,7 +15,15 @@ CREATE FUNCTION pg_stat_statements(
1515
OUT query text,
1616
OUT calls int8,
1717
OUT total_time float8,
18-
OUT rows int8
18+
OUT rows int8,
19+
OUT shared_blks_hit int8,
20+
OUT shared_blks_read int8,
21+
OUT shared_blks_written int8,
22+
OUT local_blks_hit int8,
23+
OUT local_blks_read int8,
24+
OUT local_blks_written int8,
25+
OUT temp_blks_read int8,
26+
OUT temp_blks_written int8
1927
)
2028
RETURNS SETOF record
2129
AS 'MODULE_PATHNAME'

‎doc/src/sgml/pgstatstatements.sgml

Lines changed: 93 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/pgstatstatements.sgml,v 1.5 2009/12/15 20:04:49 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/pgstatstatements.sgml,v 1.6 2010/01/08 00:38:20 itagaki Exp $ -->
22

33
<sect1 id="pgstatstatements">
44
<title>pg_stat_statements</title>
@@ -85,6 +85,62 @@
8585
<entry>Total number of rows retrieved or affected by the statement</entry>
8686
</row>
8787

88+
<row>
89+
<entry><structfield>shared_blks_hit</structfield></entry>
90+
<entry><type>bigint</type></entry>
91+
<entry></entry>
92+
<entry>Total number of shared blocks hits by the statement</entry>
93+
</row>
94+
95+
<row>
96+
<entry><structfield>shared_blks_read</structfield></entry>
97+
<entry><type>bigint</type></entry>
98+
<entry></entry>
99+
<entry>Total number of shared blocks reads by the statement</entry>
100+
</row>
101+
102+
<row>
103+
<entry><structfield>shared_blks_written</structfield></entry>
104+
<entry><type>bigint</type></entry>
105+
<entry></entry>
106+
<entry>Total number of shared blocks writes by the statement</entry>
107+
</row>
108+
109+
<row>
110+
<entry><structfield>local_blks_hit</structfield></entry>
111+
<entry><type>bigint</type></entry>
112+
<entry></entry>
113+
<entry>Total number of local blocks hits by the statement</entry>
114+
</row>
115+
116+
<row>
117+
<entry><structfield>local_blks_read</structfield></entry>
118+
<entry><type>bigint</type></entry>
119+
<entry></entry>
120+
<entry>Total number of local blocks reads by the statement</entry>
121+
</row>
122+
123+
<row>
124+
<entry><structfield>local_blks_written</structfield></entry>
125+
<entry><type>bigint</type></entry>
126+
<entry></entry>
127+
<entry>Total number of local blocks writes by the statement</entry>
128+
</row>
129+
130+
<row>
131+
<entry><structfield>temp_blks_read</structfield></entry>
132+
<entry><type>bigint</type></entry>
133+
<entry></entry>
134+
<entry>Total number of temp blocks reads by the statement</entry>
135+
</row>
136+
137+
<row>
138+
<entry><structfield>temp_blks_written</structfield></entry>
139+
<entry><type>bigint</type></entry>
140+
<entry></entry>
141+
<entry>Total number of temp blocks writes by the statement</entry>
142+
</row>
143+
88144
</tbody>
89145
</tgroup>
90146
</table>
@@ -239,35 +295,45 @@ pg_stat_statements.track = all
239295
<title>Sample output</title>
240296

241297
<programlisting>
242-
$ pgbench -i bench
243-
244-
postgres=# SELECT pg_stat_statements_reset();
298+
bench=# SELECT pg_stat_statements_reset();
245299

300+
$ pgbench -i bench
246301
$ pgbench -c10 -t300 -M prepared bench
247302

248-
postgres=# \x
249-
postgres=# SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 3;
250-
-[ RECORD 1 ]------------------------------------------------------------
251-
userid | 10
252-
dbid | 63781
253-
query | UPDATE branches SET bbalance = bbalance + $1 WHERE bid = $2;
254-
calls | 3000
255-
total_time | 20.716706
256-
rows | 3000
257-
-[ RECORD 2 ]------------------------------------------------------------
258-
userid | 10
259-
dbid | 63781
260-
query | UPDATE tellers SET tbalance = tbalance + $1 WHERE tid = $2;
261-
calls | 3000
262-
total_time | 17.1107649999999
263-
rows | 3000
264-
-[ RECORD 3 ]------------------------------------------------------------
265-
userid | 10
266-
dbid | 63781
267-
query | UPDATE accounts SET abalance = abalance + $1 WHERE aid = $2;
268-
calls | 3000
269-
total_time | 0.645601
270-
rows | 3000
303+
bench=# \x
304+
bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
305+
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
306+
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
307+
-[ RECORD 1 ]---------------------------------------------------------------------
308+
query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
309+
calls | 3000
310+
total_time | 9.60900100000002
311+
rows | 2836
312+
hit_percent | 99.9778970000200936
313+
-[ RECORD 2 ]---------------------------------------------------------------------
314+
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
315+
calls | 3000
316+
total_time | 8.015156
317+
rows | 2990
318+
hit_percent | 99.9731126579631345
319+
-[ RECORD 3 ]---------------------------------------------------------------------
320+
query | copy pgbench_accounts from stdin
321+
calls | 1
322+
total_time | 0.310624
323+
rows | 100000
324+
hit_percent | 0.30395136778115501520
325+
-[ RECORD 4 ]---------------------------------------------------------------------
326+
query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2;
327+
calls | 3000
328+
total_time | 0.271741999999997
329+
rows | 3000
330+
hit_percent | 93.7968855088209426
331+
-[ RECORD 5 ]---------------------------------------------------------------------
332+
query | alter table pgbench_accounts add primary key (aid)
333+
calls | 1
334+
total_time | 0.08142
335+
rows | 0
336+
hit_percent | 34.4947735191637631
271337
</programlisting>
272338
</sect2>
273339

0 commit comments

Comments
 (0)