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

Commit6b466bf

Browse files
author
Amit Kapila
committed
Allow pg_stat_statements to track WAL usage statistics.
This commit adds three new columns in pg_stat_statements output todisplay WAL usage statistics added by commitdf3b181.This commit doesn't bump the version of pg_stat_statements as thesame is done for this release in commit17e0328.Author: Kirill Bychik and Julien RouhaudReviewed-by: Julien Rouhaud, Fujii Masao, Dilip Kumar and Amit KapilaDiscussion:https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com
1 parent70de4e9 commit6b466bf

File tree

5 files changed

+145
-4
lines changed

5 files changed

+145
-4
lines changed

‎contrib/pg_stat_statements/expected/pg_stat_statements.out

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -211,6 +211,45 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
211211
UPDATE test SET b = $1 WHERE a > $2 | 1 | 3
212212
(10 rows)
213213

214+
--
215+
-- INSERT, UPDATE, DELETE on test table to validate WAL generation metrics
216+
--
217+
SELECT pg_stat_statements_reset();
218+
pg_stat_statements_reset
219+
--------------------------
220+
221+
(1 row)
222+
223+
-- utility "create table" should not be shown
224+
CREATE TABLE pgss_test (a int, b char(20));
225+
INSERT INTO pgss_test VALUES(generate_series(1, 10), 'aaa');
226+
UPDATE pgss_test SET b = 'bbb' WHERE a > 7;
227+
DELETE FROM pgss_test WHERE a > 9;
228+
-- DROP test table
229+
SET pg_stat_statements.track_utility = TRUE;
230+
DROP TABLE pgss_test;
231+
SET pg_stat_statements.track_utility = FALSE;
232+
-- Check WAL is generated for the above statements
233+
SELECT query, calls, rows,
234+
wal_bytes > 0 as wal_bytes_generated,
235+
wal_records > 0 as wal_records_generated,
236+
wal_records = rows as wal_records_as_rows
237+
FROM pg_stat_statements ORDER BY query COLLATE "C";
238+
query | calls | rows | wal_bytes_generated | wal_records_generated | wal_records_as_rows
239+
-----------------------------------------------------------+-------+------+---------------------+-----------------------+---------------------
240+
DELETE FROM pgss_test WHERE a > $1 | 1 | 1 | t | t | t
241+
DROP TABLE pgss_test | 1 | 0 | t | t | f
242+
INSERT INTO pgss_test VALUES(generate_series($1, $2), $3) | 1 | 10 | t | t | t
243+
SELECT pg_stat_statements_reset() | 1 | 1 | f | f | f
244+
SELECT query, calls, rows, +| 0 | 0 | f | f | t
245+
wal_bytes > $1 as wal_bytes_generated, +| | | | |
246+
wal_records > $2 as wal_records_generated, +| | | | |
247+
wal_records = rows as wal_records_as_rows +| | | | |
248+
FROM pg_stat_statements ORDER BY query COLLATE "C" | | | | |
249+
SET pg_stat_statements.track_utility = FALSE | 1 | 0 | f | f | t
250+
UPDATE pgss_test SET b = $1 WHERE a > $2 | 1 | 3 | t | t | t
251+
(7 rows)
252+
214253
--
215254
-- pg_stat_statements.track = none
216255
--

‎contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -41,7 +41,10 @@ CREATE FUNCTION pg_stat_statements(IN showtext boolean,
4141
OUT temp_blks_read int8,
4242
OUT temp_blks_written int8,
4343
OUT blk_read_time float8,
44-
OUT blk_write_time float8
44+
OUT blk_write_time float8,
45+
OUT wal_records int8,
46+
OUT wal_num_fpw int8,
47+
OUT wal_bytesnumeric
4548
)
4649
RETURNS SETOF record
4750
AS'MODULE_PATHNAME','pg_stat_statements_1_8'

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 52 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -188,6 +188,9 @@ typedef struct Counters
188188
doubleblk_read_time;/* time spent reading, in msec */
189189
doubleblk_write_time;/* time spent writing, in msec */
190190
doubleusage;/* usage factor */
191+
int64wal_records;/* # of WAL records generated */
192+
int64wal_num_fpw;/* # of WAL full page image records generated */
193+
uint64wal_bytes;/* total amount of WAL bytes generated */
191194
}Counters;
192195

193196
/*
@@ -348,6 +351,7 @@ static void pgss_store(const char *query, uint64 queryId,
348351
pgssStoreKindkind,
349352
doubletotal_time,uint64rows,
350353
constBufferUsage*bufusage,
354+
constWalUsage*walusage,
351355
pgssJumbleState*jstate);
352356
staticvoidpg_stat_statements_internal(FunctionCallInfofcinfo,
353357
pgssVersionapi_version,
@@ -891,6 +895,7 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query)
891895
0,
892896
0,
893897
NULL,
898+
NULL,
894899
&jstate);
895900
}
896901

@@ -926,9 +931,17 @@ pgss_planner(Query *parse,
926931
instr_timeduration;
927932
BufferUsagebufusage_start,
928933
bufusage;
934+
WalUsagewalusage_start,
935+
walusage;
929936

930937
/* We need to track buffer usage as the planner can access them. */
931938
bufusage_start=pgBufferUsage;
939+
940+
/*
941+
* Similarly the planner could write some WAL records in some cases
942+
* (e.g. setting a hint bit with those being WAL-logged)
943+
*/
944+
walusage_start=pgWalUsage;
932945
INSTR_TIME_SET_CURRENT(start);
933946

934947
plan_nested_level++;
@@ -954,6 +967,10 @@ pgss_planner(Query *parse,
954967
memset(&bufusage,0,sizeof(BufferUsage));
955968
BufferUsageAccumDiff(&bufusage,&pgBufferUsage,&bufusage_start);
956969

970+
/* calc differences of WAL counters. */
971+
memset(&walusage,0,sizeof(WalUsage));
972+
WalUsageAccumDiff(&walusage,&pgWalUsage,&walusage_start);
973+
957974
pgss_store(query_string,
958975
parse->queryId,
959976
parse->stmt_location,
@@ -962,6 +979,7 @@ pgss_planner(Query *parse,
962979
INSTR_TIME_GET_MILLISEC(duration),
963980
0,
964981
&bufusage,
982+
&walusage,
965983
NULL);
966984
}
967985
else
@@ -1079,6 +1097,7 @@ pgss_ExecutorEnd(QueryDesc *queryDesc)
10791097
queryDesc->totaltime->total*1000.0,/* convert to msec */
10801098
queryDesc->estate->es_processed,
10811099
&queryDesc->totaltime->bufusage,
1100+
&queryDesc->totaltime->walusage,
10821101
NULL);
10831102
}
10841103

@@ -1123,8 +1142,11 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
11231142
uint64rows;
11241143
BufferUsagebufusage_start,
11251144
bufusage;
1145+
WalUsagewalusage_start,
1146+
walusage;
11261147

11271148
bufusage_start=pgBufferUsage;
1149+
walusage_start=pgWalUsage;
11281150
INSTR_TIME_SET_CURRENT(start);
11291151

11301152
exec_nested_level++;
@@ -1154,6 +1176,10 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
11541176
memset(&bufusage,0,sizeof(BufferUsage));
11551177
BufferUsageAccumDiff(&bufusage,&pgBufferUsage,&bufusage_start);
11561178

1179+
/* calc differences of WAL counters. */
1180+
memset(&walusage,0,sizeof(WalUsage));
1181+
WalUsageAccumDiff(&walusage,&pgWalUsage,&walusage_start);
1182+
11571183
pgss_store(queryString,
11581184
0,/* signal that it's a utility stmt */
11591185
pstmt->stmt_location,
@@ -1162,6 +1188,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
11621188
INSTR_TIME_GET_MILLISEC(duration),
11631189
rows,
11641190
&bufusage,
1191+
&walusage,
11651192
NULL);
11661193
}
11671194
else
@@ -1197,7 +1224,8 @@ pgss_hash_string(const char *str, int len)
11971224
*
11981225
* If jstate is not NULL then we're trying to create an entry for which
11991226
* we have no statistics as yet; we just want to record the normalized
1200-
* query string. total_time, rows, bufusage are ignored in this case.
1227+
* query string. total_time, rows, bufusage and walusage are ignored in this
1228+
* case.
12011229
*
12021230
* If kind is PGSS_PLAN or PGSS_EXEC, its value is used as the array position
12031231
* for the arrays in the Counters field.
@@ -1208,6 +1236,7 @@ pgss_store(const char *query, uint64 queryId,
12081236
pgssStoreKindkind,
12091237
doubletotal_time,uint64rows,
12101238
constBufferUsage*bufusage,
1239+
constWalUsage*walusage,
12111240
pgssJumbleState*jstate)
12121241
{
12131242
pgssHashKeykey;
@@ -1402,6 +1431,9 @@ pgss_store(const char *query, uint64 queryId,
14021431
e->counters.blk_read_time+=INSTR_TIME_GET_MILLISEC(bufusage->blk_read_time);
14031432
e->counters.blk_write_time+=INSTR_TIME_GET_MILLISEC(bufusage->blk_write_time);
14041433
e->counters.usage+=USAGE_EXEC(total_time);
1434+
e->counters.wal_records+=walusage->wal_records;
1435+
e->counters.wal_num_fpw+=walusage->wal_num_fpw;
1436+
e->counters.wal_bytes+=walusage->wal_bytes;
14051437

14061438
SpinLockRelease(&e->mutex);
14071439
}
@@ -1449,8 +1481,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
14491481
#definePG_STAT_STATEMENTS_COLS_V1_118
14501482
#definePG_STAT_STATEMENTS_COLS_V1_219
14511483
#definePG_STAT_STATEMENTS_COLS_V1_323
1452-
#definePG_STAT_STATEMENTS_COLS_V1_829
1453-
#definePG_STAT_STATEMENTS_COLS29/* maximum of above */
1484+
#definePG_STAT_STATEMENTS_COLS_V1_832
1485+
#definePG_STAT_STATEMENTS_COLS32/* maximum of above */
14541486

14551487
/*
14561488
* Retrieve statement statistics.
@@ -1786,6 +1818,23 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
17861818
values[i++]=Float8GetDatumFast(tmp.blk_read_time);
17871819
values[i++]=Float8GetDatumFast(tmp.blk_write_time);
17881820
}
1821+
if (api_version >=PGSS_V1_8)
1822+
{
1823+
charbuf[256];
1824+
Datumwal_bytes;
1825+
1826+
values[i++]=Int64GetDatumFast(tmp.wal_records);
1827+
values[i++]=Int64GetDatumFast(tmp.wal_num_fpw);
1828+
1829+
snprintf(buf,sizeofbuf,UINT64_FORMAT,tmp.wal_bytes);
1830+
1831+
/* Convert to numeric. */
1832+
wal_bytes=DirectFunctionCall3(numeric_in,
1833+
CStringGetDatum(buf),
1834+
ObjectIdGetDatum(0),
1835+
Int32GetDatum(-1));
1836+
values[i++]=wal_bytes;
1837+
}
17891838

17901839
Assert(i== (api_version==PGSS_V1_0 ?PG_STAT_STATEMENTS_COLS_V1_0 :
17911840
api_version==PGSS_V1_1 ?PG_STAT_STATEMENTS_COLS_V1_1 :

‎contrib/pg_stat_statements/sql/pg_stat_statements.sql

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -101,6 +101,29 @@ SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
101101

102102
SELECT query, calls, rowsFROM pg_stat_statementsORDER BY query COLLATE"C";
103103

104+
--
105+
-- INSERT, UPDATE, DELETE on test table to validate WAL generation metrics
106+
--
107+
SELECT pg_stat_statements_reset();
108+
109+
-- utility "create table" should not be shown
110+
CREATETABLEpgss_test (aint, bchar(20));
111+
112+
INSERT INTO pgss_testVALUES(generate_series(1,10),'aaa');
113+
UPDATE pgss_testSET b='bbb'WHERE a>7;
114+
DELETEFROM pgss_testWHERE a>9;
115+
-- DROP test table
116+
SETpg_stat_statements.track_utility= TRUE;
117+
DROPTABLE pgss_test;
118+
SETpg_stat_statements.track_utility= FALSE;
119+
120+
-- Check WAL is generated for the above statements
121+
SELECT query, calls, rows,
122+
wal_bytes>0as wal_bytes_generated,
123+
wal_records>0as wal_records_generated,
124+
wal_records= rowsas wal_records_as_rows
125+
FROM pg_stat_statementsORDER BY query COLLATE"C";
126+
104127
--
105128
-- pg_stat_statements.track = none
106129
--

‎doc/src/sgml/pgstatstatements.sgml

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -264,6 +264,33 @@
264264
</entry>
265265
</row>
266266

267+
<row>
268+
<entry><structfield>wal_bytes</structfield></entry>
269+
<entry><type>numeric</type></entry>
270+
<entry></entry>
271+
<entry>
272+
Total amount of WAL bytes generated by the statement
273+
</entry>
274+
</row>
275+
276+
<row>
277+
<entry><structfield>wal_records</structfield></entry>
278+
<entry><type>bigint</type></entry>
279+
<entry></entry>
280+
<entry>
281+
Total count of WAL records generated by the statement
282+
</entry>
283+
</row>
284+
285+
<row>
286+
<entry><structfield>wal_num_fpw</structfield></entry>
287+
<entry><type>bigint</type></entry>
288+
<entry></entry>
289+
<entry>
290+
Total count of WAL full page writes generated by the statement
291+
</entry>
292+
</row>
293+
267294
</tbody>
268295
</tgroup>
269296
</table>

0 commit comments

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