Skip to content

Commit bc33474

Browse files
mhagandermhagander
committed
Track temporary file count and size in pg_stat_database
Add counters for number and size of temporary files used for spill-to-disk queries for each database to the pg_stat_database view. Tomas Vondra, review by Magnus Hagander
1 parent 9d35116 commit bc33474

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
static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
287287
static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
288288
static void pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len);
289+
static void pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len);
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_t filesize)
1352+
{
1353+
PgStat_MsgTempFile msg;
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+
case PGSTAT_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+
static void
4477+
pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len)
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+
struct stat filestats;
1092+
int stat_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-
struct stat filestats;
1107-
int stat_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
extern Datum pg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS);
8080
extern Datum pg_stat_get_db_conflict_all(PG_FUNCTION_ARGS);
8181
extern Datum pg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS);
82+
extern Datum pg_stat_get_db_temp_files(PG_FUNCTION_ARGS);
83+
extern Datum pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);
8284

8385
extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
8486
extern Datum pg_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+
Oid dbid = PG_GETARG_OID(0);
1222+
int64 result;
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+
Oid dbid = PG_GETARG_OID(0);
1238+
int64 result;
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-
#define CATALOG_VERSION_NO 201201192
56+
#define CATALOG_VERSION_NO 201201261
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(insert OID = 3074 ( pg_stat_get_db_stat_reset_time PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 1184 "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(insert OID = 3150 ( pg_stat_get_db_temp_files PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_temp_files _null_ _null_ _null_ ));
2640+
DESCR("statistics: number of temporary files written");
2641+
DATA(insert OID = 3151 ( pg_stat_get_db_temp_bytes PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "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(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 0 f f f t f s 0 0 20 "" _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(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP PGUID 12 1 0 0 0 f f f t f s 0 0 20 "" _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
int m_reason;
377378
} PgStat_MsgRecoveryConflict;
378379

380+
/* ----------
381+
* PgStat_MsgTempFile Sent by the backend upon creating a temp file
382+
* ----------
383+
*/
384+
typedef struct PgStat_MsgTempFile
385+
{
386+
PgStat_MsgHdr m_hdr;
387+
388+
Oid m_databaseid;
389+
size_t m_filesize;
390+
} PgStat_MsgTempFile;
391+
379392
/* ----------
380393
* PgStat_FunctionCounts The actual per-function counts kept by a backend
381394
*
@@ -507,6 +520,9 @@ typedef struct PgStat_StatDBEntry
507520
PgStat_Counter n_conflict_snapshot;
508521
PgStat_Counter n_conflict_bufferpin;
509522
PgStat_Counter n_conflict_startup_deadlock;
523+
PgStat_Counter n_temp_files;
524+
PgStat_Counter n_temp_bytes;
525+
510526
TimestampTz stat_reset_timestamp;
511527

512528

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

737753
extern void pgstat_report_activity(BackendState state, const char *cmd_str);
754+
extern void pgstat_report_tempfile(size_t filesize);
738755
extern void pgstat_report_appname(const char *appname);
739756
extern void pgstat_report_xact_timestamp(TimestampTz tstamp);
740757
extern void pgstat_report_waiting(bool waiting);

‎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)