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

Commitbc33474

Browse files
committed
Track temporary file count and size in pg_stat_database
Add counters for number and size of temporary files usedfor spill-to-disk queries for each database to thepg_stat_database view.Tomas Vondra, review by Magnus Hagander
1 parent9d35116 commitbc33474

File tree

9 files changed

+149
-24
lines changed

9 files changed

+149
-24
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -283,7 +283,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
283283
read requests avoided by finding the block already in buffer cache),
284284
number of rows returned, fetched, inserted, updated and deleted, the
285285
total number of queries canceled due to conflict with recovery (on
286-
standby servers), and time of last statistics reset.
286+
standby servers), number and size of temporary files used, and time
287+
of last statistics reset.
287288
</entry>
288289
</row>
289290

@@ -886,6 +887,28 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
886887
</entry>
887888
</row>
888889

890+
<row>
891+
<entry><literal><function>pg_stat_get_db_temp_bytes</function>(<type>oid</type>)</literal></entry>
892+
<entry><type>bigint</type></entry>
893+
<entry>
894+
Amount of data written to temporary files by queries in the database.
895+
All temporary files are counted, regardless of why the temporary file
896+
was created (sorting or hash), and regardless of the
897+
<xref linkend="guc-log-temp-files"> setting.
898+
</entry>
899+
</row>
900+
901+
<row>
902+
<entry><literal><function>pg_stat_get_db_temp_files</function>(<type>oid</type>)</literal></entry>
903+
<entry><type>bigint</type></entry>
904+
<entry>
905+
Number of temporary files written by queries in the database. All temporary
906+
files are counted, regardless of why the temporary file was created
907+
(sorting or hash) or file size, and regardless of the
908+
<xref linkend="guc-log-temp-files"> setting.
909+
</entry>
910+
</row>
911+
889912
<row>
890913
<entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry>
891914
<entry><type>bigint</type></entry>

‎src/backend/catalog/system_views.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -576,6 +576,8 @@ CREATE VIEW pg_stat_database AS
576576
pg_stat_get_db_tuples_updated(D.oid)AS tup_updated,
577577
pg_stat_get_db_tuples_deleted(D.oid)AS tup_deleted,
578578
pg_stat_get_db_conflict_all(D.oid)AS conflicts,
579+
pg_stat_get_db_temp_files(D.oid)AS temp_files,
580+
pg_stat_get_db_temp_bytes(D.oid)AS temp_bytes,
579581
pg_stat_get_db_stat_reset_time(D.oid)AS stats_reset
580582
FROM pg_database D;
581583

‎src/backend/postmaster/pgstat.c

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -286,6 +286,7 @@ static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
286286
staticvoidpgstat_recv_funcstat(PgStat_MsgFuncstat*msg,intlen);
287287
staticvoidpgstat_recv_funcpurge(PgStat_MsgFuncpurge*msg,intlen);
288288
staticvoidpgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict*msg,intlen);
289+
staticvoidpgstat_recv_tempfile(PgStat_MsgTempFile*msg,intlen);
289290

290291

291292
/* ------------------------------------------------------------
@@ -1339,6 +1340,29 @@ pgstat_report_recovery_conflict(int reason)
13391340
pgstat_send(&msg,sizeof(msg));
13401341
}
13411342

1343+
1344+
/* --------
1345+
* pgstat_report_tempfile() -
1346+
*
1347+
*Tell the collector about a temporary file.
1348+
* --------
1349+
*/
1350+
void
1351+
pgstat_report_tempfile(size_tfilesize)
1352+
{
1353+
PgStat_MsgTempFilemsg;
1354+
1355+
if (pgStatSock==PGINVALID_SOCKET|| !pgstat_track_counts)
1356+
return;
1357+
1358+
pgstat_setheader(&msg.m_hdr,PGSTAT_MTYPE_TEMPFILE);
1359+
msg.m_databaseid=MyDatabaseId;
1360+
msg.m_filesize=filesize;
1361+
pgstat_send(&msg,sizeof(msg));
1362+
}
1363+
1364+
;
1365+
13421366
/* ----------
13431367
* pgstat_ping() -
13441368
*
@@ -3218,6 +3242,10 @@ PgstatCollectorMain(int argc, char *argv[])
32183242
pgstat_recv_recoveryconflict((PgStat_MsgRecoveryConflict*)&msg,len);
32193243
break;
32203244

3245+
casePGSTAT_MTYPE_TEMPFILE:
3246+
pgstat_recv_tempfile((PgStat_MsgTempFile*)&msg,len);
3247+
break;
3248+
32213249
default:
32223250
break;
32233251
}
@@ -3299,6 +3327,8 @@ pgstat_get_db_entry(Oid databaseid, bool create)
32993327
result->n_conflict_snapshot=0;
33003328
result->n_conflict_bufferpin=0;
33013329
result->n_conflict_startup_deadlock=0;
3330+
result->n_temp_files=0;
3331+
result->n_temp_bytes=0;
33023332

33033333
result->stat_reset_timestamp=GetCurrentTimestamp();
33043334

@@ -4210,6 +4240,8 @@ pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int len)
42104240
dbentry->n_tuples_updated=0;
42114241
dbentry->n_tuples_deleted=0;
42124242
dbentry->last_autovac_time=0;
4243+
dbentry->n_temp_bytes=0;
4244+
dbentry->n_temp_files=0;
42134245

42144246
dbentry->stat_reset_timestamp=GetCurrentTimestamp();
42154247

@@ -4435,6 +4467,24 @@ pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len)
44354467
}
44364468
}
44374469

4470+
/* ----------
4471+
* pgstat_recv_tempfile() -
4472+
*
4473+
*Process as PGSTAT_MTYPE_TEMPFILE message.
4474+
* ----------
4475+
*/
4476+
staticvoid
4477+
pgstat_recv_tempfile(PgStat_MsgTempFile*msg,intlen)
4478+
{
4479+
PgStat_StatDBEntry*dbentry;
4480+
4481+
dbentry=pgstat_get_db_entry(msg->m_databaseid, true);
4482+
4483+
dbentry->n_temp_bytes+=msg->m_filesize;
4484+
dbentry->n_temp_files+=1;
4485+
4486+
}
4487+
44384488
/* ----------
44394489
* pgstat_recv_funcstat() -
44404490
*

‎src/backend/storage/file/fd.c

Lines changed: 16 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -1088,6 +1088,9 @@ FileClose(File file)
10881088
*/
10891089
if (vfdP->fdstate&FD_TEMPORARY)
10901090
{
1091+
structstatfilestats;
1092+
intstat_errno;
1093+
10911094
/*
10921095
* If we get an error, as could happen within the ereport/elog calls,
10931096
* we'll come right back here during transaction abort. Reset the
@@ -1101,23 +1104,22 @@ FileClose(File file)
11011104
temporary_files_size-=vfdP->fileSize;
11021105
vfdP->fileSize=0;
11031106

1104-
if (log_temp_files >=0)
1105-
{
1106-
structstatfilestats;
1107-
intstat_errno;
1107+
/* first try the stat() */
1108+
if (stat(vfdP->fileName,&filestats))
1109+
stat_errno=errno;
1110+
else
1111+
stat_errno=0;
11081112

1109-
/* first try the stat() */
1110-
if (stat(vfdP->fileName,&filestats))
1111-
stat_errno=errno;
1112-
else
1113-
stat_errno=0;
1113+
/* in any case do the unlink */
1114+
if (unlink(vfdP->fileName))
1115+
elog(LOG,"could not unlink file \"%s\": %m",vfdP->fileName);
11141116

1115-
/* in any case do the unlink */
1116-
if (unlink(vfdP->fileName))
1117-
elog(LOG,"could not unlink file \"%s\": %m",vfdP->fileName);
1117+
/* and last report the stat results */
1118+
if (stat_errno==0)
1119+
{
1120+
pgstat_report_tempfile(filestats.st_size);
11181121

1119-
/* and last report the stat results */
1120-
if (stat_errno==0)
1122+
if (log_temp_files >=0)
11211123
{
11221124
if ((filestats.st_size /1024) >=log_temp_files)
11231125
ereport(LOG,
@@ -1131,12 +1133,6 @@ FileClose(File file)
11311133
elog(LOG,"could not stat file \"%s\": %m",vfdP->fileName);
11321134
}
11331135
}
1134-
else
1135-
{
1136-
/* easy case, just do the unlink */
1137-
if (unlink(vfdP->fileName))
1138-
elog(LOG,"could not unlink file \"%s\": %m",vfdP->fileName);
1139-
}
11401136
}
11411137

11421138
/* Unregister it from the resource owner */

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

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -79,6 +79,8 @@ extern Datum pg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS);
7979
externDatumpg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS);
8080
externDatumpg_stat_get_db_conflict_all(PG_FUNCTION_ARGS);
8181
externDatumpg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS);
82+
externDatumpg_stat_get_db_temp_files(PG_FUNCTION_ARGS);
83+
externDatumpg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);
8284

8385
externDatumpg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
8486
externDatumpg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
@@ -1213,6 +1215,37 @@ pg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS)
12131215
PG_RETURN_TIMESTAMPTZ(result);
12141216
}
12151217

1218+
Datum
1219+
pg_stat_get_db_temp_files(PG_FUNCTION_ARGS)
1220+
{
1221+
Oiddbid=PG_GETARG_OID(0);
1222+
int64result;
1223+
PgStat_StatDBEntry*dbentry;
1224+
1225+
if ((dbentry=pgstat_fetch_stat_dbentry(dbid))==NULL)
1226+
result=0;
1227+
else
1228+
result=dbentry->n_temp_files;
1229+
1230+
PG_RETURN_INT64(result);
1231+
}
1232+
1233+
1234+
Datum
1235+
pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS)
1236+
{
1237+
Oiddbid=PG_GETARG_OID(0);
1238+
int64result;
1239+
PgStat_StatDBEntry*dbentry;
1240+
1241+
if ((dbentry=pgstat_fetch_stat_dbentry(dbid))==NULL)
1242+
result=0;
1243+
else
1244+
result=dbentry->n_temp_bytes;
1245+
1246+
PG_RETURN_INT64(result);
1247+
}
1248+
12161249
Datum
12171250
pg_stat_get_db_conflict_tablespace(PG_FUNCTION_ARGS)
12181251
{

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201201192
56+
#defineCATALOG_VERSION_NO201201261
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2636,6 +2636,10 @@ DATA(insert OID = 3070 ( pg_stat_get_db_conflict_all PGNSP PGUID 12 1 0 0 0 f f
26362636
DESCR("statistics: recovery conflicts in database");
26372637
DATA(insertOID=3074 (pg_stat_get_db_stat_reset_timePGNSPPGUID121000ffftfs101184"26"_null__null__null__null_pg_stat_get_db_stat_reset_time_null__null__null_ ));
26382638
DESCR("statistics: last reset for a database");
2639+
DATA(insertOID=3150 (pg_stat_get_db_temp_filesPGNSPPGUID121000ffftfs1020"26"_null__null__null__null_pg_stat_get_db_temp_files_null__null__null_ ));
2640+
DESCR("statistics: number of temporary files written");
2641+
DATA(insertOID=3151 (pg_stat_get_db_temp_bytesPGNSPPGUID121000ffftfs1020"26"_null__null__null__null_pg_stat_get_db_temp_bytes_null__null__null_ ));
2642+
DESCR("statistics: number of bytes in temporary files written");
26392643
DATA(insertOID=2769 (pg_stat_get_bgwriter_timed_checkpointsPGNSPPGUID121000ffftfs0020""_null__null__null__null_pg_stat_get_bgwriter_timed_checkpoints_null__null__null_ ));
26402644
DESCR("statistics: number of timed checkpoints started by the bgwriter");
26412645
DATA(insertOID=2770 (pg_stat_get_bgwriter_requested_checkpointsPGNSPPGUID121000ffftfs0020""_null__null__null__null_pg_stat_get_bgwriter_requested_checkpoints_null__null__null_ ));

‎src/include/pgstat.h

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -47,7 +47,8 @@ typedef enum StatMsgType
4747
PGSTAT_MTYPE_BGWRITER,
4848
PGSTAT_MTYPE_FUNCSTAT,
4949
PGSTAT_MTYPE_FUNCPURGE,
50-
PGSTAT_MTYPE_RECOVERYCONFLICT
50+
PGSTAT_MTYPE_RECOVERYCONFLICT,
51+
PGSTAT_MTYPE_TEMPFILE
5152
}StatMsgType;
5253

5354
/* ----------
@@ -376,6 +377,18 @@ typedef struct PgStat_MsgRecoveryConflict
376377
intm_reason;
377378
}PgStat_MsgRecoveryConflict;
378379

380+
/* ----------
381+
* PgStat_MsgTempFileSent by the backend upon creating a temp file
382+
* ----------
383+
*/
384+
typedefstructPgStat_MsgTempFile
385+
{
386+
PgStat_MsgHdrm_hdr;
387+
388+
Oidm_databaseid;
389+
size_tm_filesize;
390+
}PgStat_MsgTempFile;
391+
379392
/* ----------
380393
* PgStat_FunctionCountsThe actual per-function counts kept by a backend
381394
*
@@ -507,6 +520,9 @@ typedef struct PgStat_StatDBEntry
507520
PgStat_Countern_conflict_snapshot;
508521
PgStat_Countern_conflict_bufferpin;
509522
PgStat_Countern_conflict_startup_deadlock;
523+
PgStat_Countern_temp_files;
524+
PgStat_Countern_temp_bytes;
525+
510526
TimestampTzstat_reset_timestamp;
511527

512528

@@ -735,6 +751,7 @@ extern void pgstat_initialize(void);
735751
externvoidpgstat_bestart(void);
736752

737753
externvoidpgstat_report_activity(BackendStatestate,constchar*cmd_str);
754+
externvoidpgstat_report_tempfile(size_tfilesize);
738755
externvoidpgstat_report_appname(constchar*appname);
739756
externvoidpgstat_report_xact_timestamp(TimestampTztstamp);
740757
externvoidpgstat_report_waiting(boolwaiting);

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1296,7 +1296,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
12961296
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"]));
12971297
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_tuples_hot_updated(c.oid) AS n_tup_hot_upd, 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, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count 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;
12981298
pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
1299-
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, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d;
1299+
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, pg_stat_get_db_conflict_all(d.oid) AS conflicts,pg_stat_get_db_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes,pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d;
13001300
pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock FROM pg_database d;
13011301
pg_stat_replication | SELECT s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid));
13021302
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, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text));

0 commit comments

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