8000 pg_stat_statements: Track I/O timing for temporary file blocks · postgres/postgres@76cbf7e · GitHub
[go: up one dir, main page]
Skip to content

Commit 76cbf7e

Browse files
michaelpqmichaelpq
committed
pg_stat_statements: Track I/O timing for temporary file blocks
This commit adds two new columns to pg_stat_statements, called temp_blk_read_time and temp_blk_write_time. Those columns respectively show the time sp 8000 ent to read and write temporary file blocks on disk, whose tracking has been added in efb0ef9. This information is available when track_io_timing is enabled, like blk_read_time and blk_write_time. pg_stat_statements is updated to version to 1.10 as an effect of the newly-added columns. Tests for the upgrade path 1.9->1.10 are added. PGSS_FILE_HEADER is bumped for the new stats file format. Author: Masahiko Sawada Reviewed-by: Georgios Kokolatos, Melanie Plageman, Julien Rouhaud, Ranier Vilela Discussion: https://postgr.es/m/CAD21AoAJgotTeP83p6HiAGDhs_9Fw9pZ2J=_tYTsiO5Ob-V5GQ@mail.gmail.com
1 parent a6baa4b commit 76cbf7e

File tree

7 files changed

+237
-9
lines changed

7 files changed

+237
-9
lines changed

‎contrib/pg_stat_statements/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,8 @@ OBJS = \
66
pg_stat_statements.o
77

88
EXTENSION = pg_stat_statements
9-
DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.8--1.9.sql \
9+
DATA = pg_stat_statements--1.4.sql \
10+
pg_stat_statements--1.9--1.10.sql pg_stat_statements--1.8--1.9.sql \
1011
pg_stat_statements--1.7--1.8.sql pg_stat_statements--1.6--1.7.sql \
1112
pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.4--1.5.sql \
1213
pg_stat_statements--1.3--1.4.sql pg_stat_statements--1.2--1.3.sql \

‎contrib/pg_stat_statements/expected/oldextversions.out

Lines changed: 106 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -136,4 +136,110 @@ SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
136136

137137
(1 row)
138138

139+
-- New function pg_stat_statement_info, and new function
140+
-- and view for pg_stat_statements introduced in 1.9
141+
AlTER EXTENSION pg_stat_statements UPDATE TO '1.9';
142+
SELECT pg_get_functiondef('pg_stat_statements_info'::regproc);
143+
pg_get_functiondef
144+
-------------------------------------------------------------------------------------------------------------------------
145+
CREATE OR REPLACE FUNCTION public.pg_stat_statements_info(OUT dealloc bigint, OUT stats_reset timestamp with time zone)+
146+
RETURNS record +
147+
LANGUAGE c +
148+
PARALLEL SAFE STRICT +
149+
AS '$libdir/pg_stat_statements', $function$pg_stat_statements_info$function$ +
150+
151+
(1 row)
152+
153+
\d pg_stat_statements
154+
View "public.pg_stat_statements"
155+
Column | Type | Collation | Nullable | Default
156+
---------------------+------------------+-----------+----------+---------
157+
userid | oid | | |
158+
dbid | oid | | |
159+
toplevel | boolean | | |
160+
queryid | bigint | | |
161+
query | text | | |
162+
plans | bigint | | |
163+
total_plan_time | double precision | | |
164+
min_plan_time | double precision | | |
165+
max_plan_time | double precision | | |
166+
mean_plan_time | double precision | | |
167+
stddev_plan_time | double precision | | |
168+
calls | bigint | | |
169+
total_exec_time | double precision | | |
170+
min_exec_time | double precision | | |
171+
max_exec_time | double precision | | |
172+
mean_exec_time | double precision | | |
173+
stddev_exec_time | double precision | | |
174+
rows | bigint | | |
175+
shared_blks_hit | bigint | | |
176+
shared_blks_read | bigint | | |
177+
shared_blks_dirtied | bigint | | |
178+
shared_blks_written | bigint | | |
179+
local_blks_hit | bigint | | |
180+
local_blks_read | bigint | | |
181+
local_blks_dirtied | bigint | | |
182+
local_blks_written | bigint | | |
183+
temp_blks_read | bigint | | |
184+
temp_blks_written | bigint | | |
185+
blk_read_time | double precision | | |
186+
blk_write_time | double precision | | |
187+
wal_records | bigint | | |
188+
wal_fpi | bigint | | |
189+
wal_bytes | numeric | | |
190+
191+
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
192+
has_data
193+
----------
194+
t
195+
(1 row)
196+
197+
-- New functions and views for pg_stat_statements in 1.10
198+
AlTER EXTENSION pg_stat_statements UPDATE TO '1.10';
199+
\d pg_stat_statements
200+
View "public.pg_stat_statements"
201+
Column | Type | Collation | Nullable | Default
202+
---------------------+------------------+-----------+----------+---------
203+
userid | oid | | |
204+
dbid | oid | | |
205+
toplevel | boolean | | |
206+
queryid | bigint | | |
207+
query | text | | |
208+
plans | bigint | | |
209+
total_plan_time | double precision | | |
210+
min_plan_time | double precision | | |
211+
max_plan_time | double precision | | |
212+
mean_plan_time | double precision | | |
213+
stddev_plan_time | double precision | | |
214+
calls | bigint | | |
215+
total_exec_time | double precision | | |
216+
min_exec_time | double precision | | |
217+
max_exec_time | double precision | | |
218+
mean_exec_time | double precision | | |
219+
stddev_exec_time | double precision | | |
220+
rows | bigint | | |
221+
shared_blks_hit | bigint | | |
222+
shared_blks_read | bigint | | |
223+
shared_blks_dirtied | bigint | | |
224+
shared_blks_written | bigint | | |
225+
local_blks_hit | bigint | | |
226+
local_blks_read | bigint | | |
227+
local_blks_dirtied | bigint | | |
228+
local_blks_written | bigint | | |
229+
temp_blks_read | bigint | | |
230+
temp_blks_written | bigint | | |
231+
blk_read_time | double precision | | |
232+
blk_write_time | double precision | | |
233+
temp_blk_read_time | double precision | | |
234+
temp_blk_write_time | double precision | | |
235+
wal_records | bigint | | |
236+
wal_fpi | bigint | | |
237+
wal_bytes | numeric | | |
238+
239+
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
240+
has_data
241+
----------
242+
t
243+
(1 row)
244+
139245
DROP EXTENSION pg_stat_statements;

‎contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
/* contrib/pg_stat_statements/pg_stat_statements--1.9--1.10.sql */
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.10'" to load this file. \quit
5+
6+
/* First we have to remove them from the extension */
7+
ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
8+
ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean);
9+
10+
/* Then we can drop them */
11+
DROP VIEW pg_stat_statements;
12+
DROP FUNCTION pg_stat_statements(boolean);
13+
14+
/* Now redefine */
15+
CREATE FUNCTION pg_stat_statements(IN showtext boolean,
16+
OUT userid oid,
17+
OUT dbid oid,
18+
OUT toplevel bool,
19+
OUT queryid bigint,
20+
OUT query text,
21+
OUT plans int8,
22+
OUT total_plan_time float8,
23+
OUT min_plan_time float8,
24+
OUT max_plan_time float8,
25+
OUT mean_plan_time float8,
26+
OUT stddev_plan_time float8,
27+
OUT calls int8,
28+
OUT total_exec_time float8,
29+
OUT min_exec_time float8,
30+
OUT max_exec_time float8,
31+
OUT mean_exec_time float8,
32+
OUT stddev_exec_time float8,
33+
OUT rows int8,
34+
OUT shared_blks_hit int8,
35+
OUT shared_blks_read int8,
36+
OUT shared_blks_dirtied int8,
37+
OUT shared_blks_written int8,
38+
OUT local_blks_hit int8,
39+
OUT local_blks_read int8,
40+
OUT local_blks_dirtied int8,
41+
OUT local_blks_written int8,
42+
OUT temp_blks_read int8,
43+
OUT temp_blks_written int8,
44+
OUT blk_read_time float8,
45+
OUT blk_write_time float8,
46+
OUT temp_blk_read_time float8,
47+
OUT temp_blk_write_time float8,
48+
OUT wal_records int8,
49+
OUT wal_fpi int8,
50+
OUT wal_bytes numeric
51+
)
52+
RETURNS SETOF record
53+
AS 'MODULE_PATHNAME', 'pg_stat_statements_1_10'
54+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
55+
56+
CREATE VIEW pg_stat_statements AS
57+
SELECT * FROM pg_stat_statements(true);
58+
59+
GRANT SELECT ON pg_stat_statements TO PUBLIC;

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 33 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -83,7 +83,7 @@ PG_MODULE_MAGIC;
8383
#define PGSS_TEXT_FILE PG_STAT_TMP_DIR "/pgss_query_texts.stat"
8484

8585
/* Magic number identifying the stats file format */
86-
static const uint32 PGSS_FILE_HEADER = 0x20201227;
86+
static const uint32 PGSS_FILE_HEADER = 0x20220408;
8787

8888
/* PostgreSQL major version number, changes in which invalidate all entries */
8989
static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100;
@@ -116,7 +116,8 @@ typedef enum pgssVersion
116116
PGSS_V1_2,
117117
PGSS_V1_3,
118118
PGSS_V1_8,
119-
PGSS_V1_9
119+
PGSS_V1_9,
120+
PGSS_V1_10
120121
} pgssVersion;
121122

122123
typedef enum pgssStoreKind
@@ -178,8 +179,11 @@ typedef struct Counters
178179
int64 local_blks_written; /* # of local disk blocks written */
179180
int64 temp_blks_read; /* # of temp blocks read */
180181
int64 temp_blks_written; /* # of temp blocks written */
181-
double blk_read_time; /* time spent reading, in msec */
182-
double blk_write_time; /* time spent writing, in msec */
182+
double blk_read_time; /* time spent reading blocks, in msec */
183+
double blk_write_time; /* time spent writing blocks, in msec */
184+
double temp_blk_read_time; /* time spent reading temp blocks, in msec */
185+
double temp_blk_write_time; /* time spent writing temp blocks, in
186+
* msec */
183187
double usage; /* usage factor */
184188
int64 wal_records; /* # of WAL records generated */
185189
int64 wal_fpi; /* # of WAL full page images generated */
@@ -297,6 +301,7 @@ PG_FUNCTION_INFO_V1(pg_stat_statements_1_2);
297301
PG_FUNCTION_INFO_V1(pg_stat_statements_1_3);
298302
PG_FUNCTION_INFO_V1(pg_stat_statements_1_8);
299303
PG_FUNCTION_INFO_V1(pg_stat_statements_1_9);
304+
PG_FUNCTION_INFO_V1(pg_stat_statements_1_10);
300305
PG_FUNCTION_INFO_V1(pg_stat_statements);
301306
PG_FUNCTION_INFO_V1(pg_stat_statements_info);
302307

@@ -1364,6 +1369,8 @@ pgss_store(const char *query, uint64 queryId,
13641369
e->counters.temp_blks_written += bufusage->temp_blks_written;
13651370
e->counters.blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_read_time);
13661371
e->counters.blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_write_time);
1372+
e->counters.temp_blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage->temp_blk_read_time);
1373+
e->counters.temp_blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage->temp_blk_write_time);
13671374
e->counters.usage += USAGE_EXEC(total_time);
13681375
e->counters.wal_records += walusage->wal_records;
13691376
e->counters.wal_fpi += walusage->wal_fpi;
@@ -1417,7 +1424,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
14171424
#define PG_STAT_STATEMENTS_COLS_V1_3 23
14181425
#define PG_STAT_STATEMENTS_COLS_V1_8 32
14191426
#define PG_STAT_STATEMENTS_COLS_V1_9 33
1420-
#define PG_STAT_STATEMENTS_COLS 33 /* maximum of above */
1427+
#define PG_STAT_STATEMENTS_COLS_V1_10 35
1428+
#define PG_STAT_STATEMENTS_COLS 35 /* maximum of above */
14211429

14221430
/*
14231431
* Retrieve statement statistics.
@@ -1429,6 +1437,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
14291437
* expected API version is identified by embedding it in the C name of the
14301438
* function. Unfortunately we weren't bright enough to do that for 1.1.
14311439
*/
1440+
Datum
1441+
pg_stat_statements_1_10(PG_FUNCTION_ARGS)
1442+
{
1443+
bool showtext = PG_GETARG_BOOL(0);
1444+
1445+
pg_stat_statements_internal(fcinfo, PGSS_V1_10, showtext);
1446+
1447+
return (Datum) 0;
1448+
}
1449+
14321450
Datum
14331451
pg_stat_statements_1_9(PG_FUNCTION_ARGS)
14341452
{
@@ -1542,6 +1560,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
15421560
if (api_version != PGSS_V1_9)
15431561
elog(ERROR, "incorrect number of output arguments");
15441562
break;
1563+
case PG_STAT_STATEMENTS_COLS_V1_10:
1564+
if (api_version != PGSS_V1_10)
1565+
elog(ERROR, "incorrect number of output arguments");
1566+
break;
15451567
default:
15461568
elog(ERROR, "incorrect number of output arguments");
15471569
}
@@ -1742,6 +1764,11 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
17421764
values[i++] = Float8GetDatumFast(tmp.blk_read_time);
17431765
values[i++] = Float8GetDatumFast(tmp.blk_write_time);
17441766
}
1767+
if (api_version >= PGSS_V1_10)
1768+
{
1769+
values[i++] = Float8GetDatumFast(tmp.temp_blk_read_time);
1770+
values[i++] = Float8GetDatumFast(tmp.temp_blk_write_time);
1771+
}
17451772
if (api_version >= PGSS_V1_8)
17461773
{
17471774
char buf[256];
@@ -1766,6 +1793,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
17661793
api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
17671794
api_version == PGSS_V1_8 ? PG_STAT_STATEMENTS_COLS_V1_8 :
17681795
api_version == PGSS_V1_9 ? PG_STAT_STATEMENTS_COLS_V1_9 :
1796+
api_version == PGSS_V1_10 ? PG_STAT_STATEMENTS_COLS_V1_10 :
17691797
-1 /* fail if you forget to update this assert */ ));
17701798

17711799
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);

‎contrib/pg_stat_statements/pg_stat_statements.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pg_stat_statements extension
22
comment = 'track planning and execution statistics of all SQL statements executed'
3-
default_version = '1.9'
3+
default_version = '1.10'
44
module_pathname = '$libdir/pg_stat_statements'
55
relocatable = true

‎contrib/pg_stat_statements/sql/oldextversions.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,4 +36,16 @@ AlTER EXTENSION pg_stat_statements UPDATE TO '1.8';
3636
\d pg_stat_statements
3737
SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
3838

39+
-- New function pg_stat_statement_info, and new function
40+
-- and view for pg_stat_statements introduced in 1.9
41+
AlTER EXTENSION pg_stat_statements UPDATE TO '1.9';
42+
SELECT pg_get_functiondef('pg_stat_statements_info'::regproc);
43+
\d pg_stat_statements
44+
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
45+
46+
-- New functions and views for pg_stat_statements in 1.10
47+
AlTER EXTENSION pg_stat_statements UPDATE TO '1.10';
48+
\d pg_stat_statements
49+
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
50+
3951
DROP EXTENSION pg_stat_statements;

‎doc/src/sgml/pgstatstatements.sgml

Lines changed: 24 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -338,7 +338,7 @@
338338
<structfield>blk_read_time</structfield> <type>double precision</type>
339339
</para>
340340
<para>
341-
Total time the statement spent reading blocks, in milliseconds
341+
Total time the statement spent reading data file blocks, in milliseconds
342342
(if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
343343
</para></entry>
344344
</row>
@@ -348,11 +348,33 @@
348348
<structfield>blk_write_time</structfield> <type>double precision</type>
349349
</para>
350350
<para>
351-
Total time the statement spent writing blocks, in milliseconds
351+
Total time the statement spent writing data file blocks, in milliseconds
352352
(if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
353353
</para></entry>
354354
</row>
355355

356+
<row>
357+
<entry role="catalog_table_entry"><para role="column_definition">
358+
<structfield>temp_blk_read_time</structfield> <type>double precision</type>
359+
</para>
360+
<para>
361+
Total time the statement spent reading temporary file blocks, in
362+
milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
363+
otherwise zero)
364+
</para></entry>
365+
</row>
366+
367+
<row>
368+
<entry role="catalog_table_entry"><para role="column_definition">
369+
<structfield>temp_blk_write_time</structfield> <type>double precision</type>
370+
</para>
371+
<para>
372+
Total time the statement spent writing temporary file blocks, in
373+
milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
374+
otherwise zero)
375+
</para></entry>
376+
</row>
377+
356378
<row>
357379
<entry role="catalog_table_entry"><para role="column_definition">
358380
<structfield>wal_records</structfield> <type>bigint</type>

0 commit comments

Comments
 (0)
0