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

Commit76cbf7e

Browse files
committed
pg_stat_statements: Track I/O timing for temporary file blocks
This commit adds two new columns to pg_stat_statements, calledtemp_blk_read_time and temp_blk_write_time. Those columns respectivelyshow the time spent to read and write temporary file blocks on disk,whose tracking has been added inefb0ef9. This information isavailable when track_io_timing is enabled, like blk_read_time andblk_write_time.pg_stat_statements is updated to version to 1.10 as an effect of thenewly-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 SawadaReviewed-by: Georgios Kokolatos, Melanie Plageman, Julien Rouhaud,Ranier VilelaDiscussion:https://postgr.es/m/CAD21AoAJgotTeP83p6HiAGDhs_9Fw9pZ2J=_tYTsiO5Ob-V5GQ@mail.gmail.com
1 parenta6baa4b commit76cbf7e

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;
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+
DROPVIEW pg_stat_statements;
12+
DROPFUNCTION pg_stat_statements(boolean);
13+
14+
/* Now redefine*/
15+
CREATEFUNCTIONpg_stat_statements(IN showtextboolean,
16+
OUT useridoid,
17+
OUT dbidoid,
18+
OUT toplevel bool,
19+
OUT queryidbigint,
20+
OUT querytext,
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_bytesnumeric
51+
)
52+
RETURNS SETOF record
53+
AS'MODULE_PATHNAME','pg_stat_statements_1_10'
54+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
55+
56+
CREATEVIEWpg_stat_statementsAS
57+
SELECT*FROM pg_stat_statements(true);
58+
59+
GRANTSELECTON 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
#definePGSS_TEXT_FILEPG_STAT_TMP_DIR "/pgss_query_texts.stat"
8484

8585
/* Magic number identifying the stats file format */
86-
staticconstuint32PGSS_FILE_HEADER=0x20201227;
86+
staticconstuint32PGSS_FILE_HEADER=0x20220408;
8787

8888
/* PostgreSQL major version number, changes in which invalidate all entries */
8989
staticconstuint32PGSS_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
typedefenumpgssStoreKind
@@ -178,8 +179,11 @@ typedef struct Counters
178179
int64local_blks_written;/* # of local disk blocks written */
179180
int64temp_blks_read;/* # of temp blocks read */
180181
int64temp_blks_written;/* # of temp blocks written */
181-
doubleblk_read_time;/* time spent reading, in msec */
182-
doubleblk_write_time;/* time spent writing, in msec */
182+
doubleblk_read_time;/* time spent reading blocks, in msec */
183+
doubleblk_write_time;/* time spent writing blocks, in msec */
184+
doubletemp_blk_read_time;/* time spent reading temp blocks, in msec */
185+
doubletemp_blk_write_time;/* time spent writing temp blocks, in
186+
* msec */
183187
doubleusage;/* usage factor */
184188
int64wal_records;/* # of WAL records generated */
185189
int64wal_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
#definePG_STAT_STATEMENTS_COLS_V1_323
14181425
#definePG_STAT_STATEMENTS_COLS_V1_832
14191426
#definePG_STAT_STATEMENTS_COLS_V1_933
1420-
#definePG_STAT_STATEMENTS_COLS33/* maximum of above */
1427+
#definePG_STAT_STATEMENTS_COLS_V1_1035
1428+
#definePG_STAT_STATEMENTS_COLS35/* 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+
boolshowtext=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+
casePG_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
charbuf[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);
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_statementsUPDATE TO'1.9';
42+
SELECT pg_get_functiondef('pg_stat_statements_info'::regproc);
43+
\d pg_stat_statements
44+
SELECTcount(*)>0AS has_dataFROM pg_stat_statements;
45+
46+
-- New functions and views for pg_stat_statements in 1.10
47+
AlTER EXTENSION pg_stat_statementsUPDATE TO'1.10';
48+
\d pg_stat_statements
49+
SELECTcount(*)>0AS has_dataFROM 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 readingdata fileblocks, 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 writingdata fileblocks, 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)
[8]ページ先頭
©2009-2025 Movatter.jp