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

Commitb736aef

Browse files
committed
Publish checkpoint timing information to pg_stat_bgwriter.
Greg Smith, Peter Geoghegan, and Robert Haas
1 parenta75b080 commitb736aef

File tree

10 files changed

+81
-26
lines changed

10 files changed

+81
-26
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -767,6 +767,28 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
767767
This value can also be returned by directly calling
768768
the <function>pg_stat_get_bgwriter_requested_checkpoints</function> function.</entry>
769769
</row>
770+
<row>
771+
<entry>checkpoint_write_time</entry>
772+
<entry><type>bigint</type></entry>
773+
<entry>
774+
Total amount of time that has been spent in the portion of
775+
checkpoint processing where files are written to disk, in milliseconds.
776+
This value can also be returned by directly calling the
777+
<function>pg_stat_get_checkpoint_write_time</function>
778+
function.
779+
</entry>
780+
</row>
781+
<row>
782+
<entry>checkpoint_sync_time</entry>
783+
<entry><type>bigint</type></entry>
784+
<entry>
785+
Total amount of time that has been spent in the portion of
786+
checkpoint processing where files are synchronized to disk, in
787+
milliseconds. This value can also be returned by directly calling
788+
the <function>pg_stat_get_checkpoint_sync_time</function>
789+
function.
790+
</entry>
791+
</row>
770792
<row>
771793
<entry>buffers_checkpoint</entry>
772794
<entry><type>bigint</type></entry>

‎src/backend/access/transam/xlog.c

Lines changed: 21 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -7511,9 +7511,6 @@ LogCheckpointEnd(bool restartpoint)
75117511

75127512
CheckpointStats.ckpt_end_t=GetCurrentTimestamp();
75137513

7514-
TimestampDifference(CheckpointStats.ckpt_start_t,
7515-
CheckpointStats.ckpt_end_t,
7516-
&total_secs,&total_usecs);
75177514

75187515
TimestampDifference(CheckpointStats.ckpt_write_t,
75197516
CheckpointStats.ckpt_sync_t,
@@ -7523,6 +7520,23 @@ LogCheckpointEnd(bool restartpoint)
75237520
CheckpointStats.ckpt_sync_end_t,
75247521
&sync_secs,&sync_usecs);
75257522

7523+
/* Record checkpoint timing summary data. */
7524+
BgWriterStats.m_checkpoint_write_time+=
7525+
write_secs*1000+write_usecs /1000;
7526+
BgWriterStats.m_checkpoint_sync_time+=
7527+
sync_secs*1000+sync_usecs /1000;
7528+
7529+
/*
7530+
* All of the published timing statistics are accounted for. Only
7531+
* continue if a log message is to be written.
7532+
*/
7533+
if (!log_checkpoints)
7534+
return;
7535+
7536+
TimestampDifference(CheckpointStats.ckpt_start_t,
7537+
CheckpointStats.ckpt_end_t,
7538+
&total_secs,&total_usecs);
7539+
75267540
/*
75277541
* Timing values returned from CheckpointStats are in microseconds.
75287542
* Convert to the second plus microsecond form that TimestampDifference
@@ -7971,9 +7985,8 @@ CreateCheckPoint(int flags)
79717985
if (!RecoveryInProgress())
79727986
TruncateSUBTRANS(GetOldestXmin(true, false));
79737987

7974-
/* All real work is done, but log before releasing lock. */
7975-
if (log_checkpoints)
7976-
LogCheckpointEnd(false);
7988+
/* Real work is done, but log and update stats before releasing lock. */
7989+
LogCheckpointEnd(false);
79777990

79787991
TRACE_POSTGRESQL_CHECKPOINT_DONE(CheckpointStats.ckpt_bufs_written,
79797992
NBuffers,
@@ -8237,9 +8250,8 @@ CreateRestartPoint(int flags)
82378250
if (EnableHotStandby)
82388251
TruncateSUBTRANS(GetOldestXmin(true, false));
82398252

8240-
/* All real work is done, but log before releasing lock. */
8241-
if (log_checkpoints)
8242-
LogCheckpointEnd(true);
8253+
/* Real work is done, but log and update before releasing lock. */
8254+
LogCheckpointEnd(true);
82438255

82448256
xtime=GetLatestXTime();
82458257
ereport((log_checkpoints ?LOG :DEBUG2),

‎src/backend/catalog/system_views.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -648,6 +648,8 @@ CREATE VIEW pg_stat_bgwriter AS
648648
SELECT
649649
pg_stat_get_bgwriter_timed_checkpoints()AS checkpoints_timed,
650650
pg_stat_get_bgwriter_requested_checkpoints()AS checkpoints_req,
651+
pg_stat_get_checkpoint_write_time()AS checkpoint_write_time,
652+
pg_stat_get_checkpoint_sync_time()AS checkopint_sync_time,
651653
pg_stat_get_bgwriter_buf_written_checkpoints()AS buffers_checkpoint,
652654
pg_stat_get_bgwriter_buf_written_clean()AS buffers_clean,
653655
pg_stat_get_bgwriter_maxwritten_clean()AS maxwritten_clean,

‎src/backend/postmaster/pgstat.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4455,6 +4455,8 @@ pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
44554455
{
44564456
globalStats.timed_checkpoints+=msg->m_timed_checkpoints;
44574457
globalStats.requested_checkpoints+=msg->m_requested_checkpoints;
4458+
globalStats.checkpoint_write_time+=msg->m_checkpoint_write_time;
4459+
globalStats.checkpoint_sync_time+=msg->m_checkpoint_sync_time;
44584460
globalStats.buf_written_checkpoints+=msg->m_buf_written_checkpoints;
44594461
globalStats.buf_written_clean+=msg->m_buf_written_clean;
44604462
globalStats.maxwritten_clean+=msg->m_maxwritten_clean;

‎src/backend/storage/smgr/md.c

Lines changed: 10 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1094,27 +1094,22 @@ mdsync(void)
10941094
entry->tag.segno* ((BlockNumber)RELSEG_SIZE),
10951095
false,EXTENSION_RETURN_NULL);
10961096

1097-
if (log_checkpoints)
1098-
INSTR_TIME_SET_CURRENT(sync_start);
1099-
else
1100-
INSTR_TIME_SET_ZERO(sync_start);
1097+
INSTR_TIME_SET_CURRENT(sync_start);
11011098

11021099
if (seg!=NULL&&
11031100
FileSync(seg->mdfd_vfd) >=0)
11041101
{
1105-
if (log_checkpoints&& (!INSTR_TIME_IS_ZERO(sync_start)))
1106-
{
1107-
INSTR_TIME_SET_CURRENT(sync_end);
1108-
sync_diff=sync_end;
1109-
INSTR_TIME_SUBTRACT(sync_diff,sync_start);
1110-
elapsed=INSTR_TIME_GET_MICROSEC(sync_diff);
1111-
if (elapsed>longest)
1112-
longest=elapsed;
1113-
total_elapsed+=elapsed;
1114-
processed++;
1102+
INSTR_TIME_SET_CURRENT(sync_end);
1103+
sync_diff=sync_end;
1104+
INSTR_TIME_SUBTRACT(sync_diff,sync_start);
1105+
elapsed=INSTR_TIME_GET_MICROSEC(sync_diff);
1106+
if (elapsed>longest)
1107+
longest=elapsed;
1108+
total_elapsed+=elapsed;
1109+
processed++;
1110+
if (log_checkpoints)
11151111
elog(DEBUG1,"checkpoint sync: number=%d file=%s time=%.3f msec",
11161112
processed,FilePathName(seg->mdfd_vfd), (double)elapsed /1000);
1117-
}
11181113

11191114
break;/* success; break out of retry loop */
11201115
}

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

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -87,6 +87,8 @@ extern Datum pg_stat_get_db_block_time_write(PG_FUNCTION_ARGS);
8787

8888
externDatumpg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
8989
externDatumpg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
90+
externDatumpg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS);
91+
externDatumpg_stat_get_checkpoint_sync_time(PG_FUNCTION_ARGS);
9092
externDatumpg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS);
9193
externDatumpg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS);
9294
externDatumpg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS);
@@ -1419,6 +1421,18 @@ pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS)
14191421
PG_RETURN_INT64(pgstat_fetch_global()->maxwritten_clean);
14201422
}
14211423

1424+
Datum
1425+
pg_stat_get_checkpoint_write_time(PG_FUNCTION_ARGS)
1426+
{
1427+
PG_RETURN_INT64(pgstat_fetch_global()->checkpoint_write_time);
1428+
}
1429+
1430+
Datum
1431+
pg_stat_get_checkpoint_sync_time(PG_FUNCTION_ARGS)
1432+
{
1433+
PG_RETURN_INT64(pgstat_fetch_global()->checkpoint_sync_time);
1434+
}
1435+
14221436
Datum
14231437
pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS)
14241438
{

‎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_NO201204051
56+
#defineCATALOG_VERSION_NO201204052
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2678,6 +2678,10 @@ DATA(insert OID = 2773 ( pg_stat_get_bgwriter_maxwritten_clean PGNSP PGUID 12 1
26782678
DESCR("statistics: number of times the bgwriter stopped processing when it had written too many buffers while cleaning");
26792679
DATA(insertOID=3075 (pg_stat_get_bgwriter_stat_reset_timePGNSPPGUID121000fffftfs001184""_null__null__null__null_pg_stat_get_bgwriter_stat_reset_time_null__null__null_ ));
26802680
DESCR("statistics: last reset for the bgwriter");
2681+
DATA(insertOID=3160 (pg_stat_get_checkpoint_write_timePGNSPPGUID121000fffftfs0020""_null__null__null__null_pg_stat_get_checkpoint_write_time_null__null__null_ ));
2682+
DESCR("statistics: total amount of checkpoint time spent writing buffers to disk");
2683+
DATA(insertOID=3161 (pg_stat_get_checkpoint_sync_timePGNSPPGUID121000fffftfs0020""_null__null__null__null_pg_stat_get_checkpoint_sync_time_null__null__null_ ));
2684+
DESCR("statistics: total amount of checkpoint time spent synchronizing buffers to disk");
26812685
DATA(insertOID=2775 (pg_stat_get_buf_written_backendPGNSPPGUID121000fffftfs0020""_null__null__null__null_pg_stat_get_buf_written_backend_null__null__null_ ));
26822686
DESCR("statistics: number of buffers written by backends");
26832687
DATA(insertOID=3063 (pg_stat_get_buf_fsync_backendPGNSPPGUID121000fffftfs0020""_null__null__null__null_pg_stat_get_buf_fsync_backend_null__null__null_ ));

‎src/include/pgstat.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -366,6 +366,8 @@ typedef struct PgStat_MsgBgWriter
366366
PgStat_Counterm_buf_written_backend;
367367
PgStat_Counterm_buf_fsync_backend;
368368
PgStat_Counterm_buf_alloc;
369+
PgStat_Counterm_checkpoint_write_time;
370+
PgStat_Counterm_checkpoint_sync_time;
369371
}PgStat_MsgBgWriter;
370372

371373
/* ----------
@@ -612,6 +614,8 @@ typedef struct PgStat_GlobalStats
612614
TimestampTzstats_timestamp;/* time of stats file update */
613615
PgStat_Countertimed_checkpoints;
614616
PgStat_Counterrequested_checkpoints;
617+
PgStat_Countercheckpoint_write_time;/* times in milliseconds */
618+
PgStat_Countercheckpoint_sync_time;
615619
PgStat_Counterbuf_written_checkpoints;
616620
PgStat_Counterbuf_written_clean;
617621
PgStat_Countermaxwritten_clean;

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1295,7 +1295,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
12951295
pg_stat_activity | SELECT s.datid, d.datname, s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, s.xact_start, s.query_start, s.state_change, s.waiting, s.state, s.query FROM pg_database d, 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 WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
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;
1298-
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;
1298+
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_checkpoint_write_time() AS checkpoint_write_time, pg_stat_get_checkpoint_sync_time() AS checkopint_sync_time,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;
12991299
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_deadlocks(d.oid) AS deadlocks, (pg_stat_get_db_block_time_read(d.oid) / 1000) AS block_read_time, (pg_stat_get_db_block_time_write(d.oid) / 1000) AS block_write_time, 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));

0 commit comments

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