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

Commit3134d88

Browse files
committed
Add new buffers_backend_fsync field to pg_stat_bgwriter.
This new field counts the number of times that a backend which writes abuffer out to the OS must also fsync() it. This happens when thebgwriter fsync request queue is full, and is generally detrimental toperformance, so it's good to know when it's happening. Along the way,log a new message at level DEBUG1 whenever we fail to hand off an fsync,so that the problem can also be seen in examination of log files(if the logging level is cranked up high enough).Greg Smith, with minor tweaks by me.
1 parent8d70ed8 commit3134d88

File tree

10 files changed

+34
-8
lines changed

10 files changed

+34
-8
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -264,8 +264,10 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
264264
and the number of times the background writer stopped a cleaning scan
265265
because it had written too many buffers. Also includes
266266
statistics about the shared buffer pool, including buffers written
267-
by backends (that is, not by the background writer) and total buffers
268-
allocated.
267+
by backends (that is, not by the background writer), how many times
268+
those backends had to execute their own fsync calls (normally the
269+
background writer handles those even when the backend does its own
270+
write), and total buffers allocated.
269271
</entry>
270272
</row>
271273

‎src/backend/catalog/system_views.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -537,6 +537,7 @@ CREATE VIEW pg_stat_bgwriter AS
537537
pg_stat_get_bgwriter_buf_written_clean()AS buffers_clean,
538538
pg_stat_get_bgwriter_maxwritten_clean()AS maxwritten_clean,
539539
pg_stat_get_buf_written_backend()AS buffers_backend,
540+
pg_stat_get_buf_fsync_backend()AS buffers_backend_fsync,
540541
pg_stat_get_buf_alloc()AS buffers_alloc;
541542

542543
CREATEVIEWpg_user_mappingsAS

‎src/backend/postmaster/bgwriter.c

Lines changed: 12 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -102,13 +102,15 @@
102102
*
103103
* num_backend_writes is used to count the number of buffer writes performed
104104
* by non-bgwriter processes. This counter should be wide enough that it
105-
* can't overflow during a single bgwriter cycle.
105+
* can't overflow during a single bgwriter cycle. num_backend_fsync
106+
* counts the subset of those writes that also had to do their own fsync,
107+
* because the background writer failed to absorb their request.
106108
*
107109
* The requests array holds fsync requests sent by backends and not yet
108110
* absorbed by the bgwriter.
109111
*
110-
* Unlike the checkpoint fields, num_backend_writes and the requests
111-
* fields are protected by BgWriterCommLock.
112+
* Unlike the checkpoint fields, num_backend_writes, num_backend_fsync, and
113+
*the requestsfields are protected by BgWriterCommLock.
112114
*----------
113115
*/
114116
typedefstruct
@@ -132,6 +134,7 @@ typedef struct
132134
intckpt_flags;/* checkpoint flags, as defined in xlog.h */
133135

134136
uint32num_backend_writes;/* counts non-bgwriter buffer writes */
137+
uint32num_backend_fsync;/* counts non-bgwriter fsync calls */
135138

136139
intnum_requests;/* current # of requests */
137140
intmax_requests;/* allocated array size */
@@ -1084,12 +1087,14 @@ ForwardFsyncRequest(RelFileNodeBackend rnode, ForkNumber forknum,
10841087

10851088
LWLockAcquire(BgWriterCommLock,LW_EXCLUSIVE);
10861089

1087-
/*we count non-bgwriter writeseven when the request queue overflows */
1090+
/*Count all backend writesregardless of if they fit in the queue */
10881091
BgWriterShmem->num_backend_writes++;
10891092

10901093
if (BgWriterShmem->bgwriter_pid==0||
10911094
BgWriterShmem->num_requests >=BgWriterShmem->max_requests)
10921095
{
1096+
/* Also count the subset where backends have to do their own fsync */
1097+
BgWriterShmem->num_backend_fsync++;
10931098
LWLockRelease(BgWriterCommLock);
10941099
return false;
10951100
}
@@ -1137,7 +1142,10 @@ AbsorbFsyncRequests(void)
11371142

11381143
/* Transfer write count into pending pgstats message */
11391144
BgWriterStats.m_buf_written_backend+=BgWriterShmem->num_backend_writes;
1145+
BgWriterStats.m_buf_fsync_backend+=BgWriterShmem->num_backend_fsync;
1146+
11401147
BgWriterShmem->num_backend_writes=0;
1148+
BgWriterShmem->num_backend_fsync=0;
11411149

11421150
n=BgWriterShmem->num_requests;
11431151
if (n>0)

‎src/backend/postmaster/pgstat.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4188,6 +4188,7 @@ pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
41884188
globalStats.buf_written_clean+=msg->m_buf_written_clean;
41894189
globalStats.maxwritten_clean+=msg->m_maxwritten_clean;
41904190
globalStats.buf_written_backend+=msg->m_buf_written_backend;
4191+
globalStats.buf_fsync_backend+=msg->m_buf_fsync_backend;
41914192
globalStats.buf_alloc+=msg->m_buf_alloc;
41924193
}
41934194

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1224,6 +1224,9 @@ register_dirty_segment(SMgrRelation reln, ForkNumber forknum, MdfdVec *seg)
12241224
if (ForwardFsyncRequest(reln->smgr_rnode,forknum,seg->mdfd_segno))
12251225
return;/* passed it off successfully */
12261226

1227+
ereport(DEBUG1,
1228+
(errmsg("could not forward fsync request because request queue is full")));
1229+
12271230
if (FileSync(seg->mdfd_vfd)<0)
12281231
ereport(ERROR,
12291232
(errcode_for_file_access(),

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,7 @@ extern Datum pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS);
7878
externDatumpg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS);
7979
externDatumpg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS);
8080
externDatumpg_stat_get_buf_written_backend(PG_FUNCTION_ARGS);
81+
externDatumpg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS);
8182
externDatumpg_stat_get_buf_alloc(PG_FUNCTION_ARGS);
8283

8384
externDatumpg_stat_get_xact_numscans(PG_FUNCTION_ARGS);
@@ -1164,6 +1165,12 @@ pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS)
11641165
PG_RETURN_INT64(pgstat_fetch_global()->buf_written_backend);
11651166
}
11661167

1168+
Datum
1169+
pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS)
1170+
{
1171+
PG_RETURN_INT64(pgstat_fetch_global()->buf_fsync_backend);
1172+
}
1173+
11671174
Datum
11681175
pg_stat_get_buf_alloc(PG_FUNCTION_ARGS)
11691176
{

‎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_NO201011091
56+
#defineCATALOG_VERSION_NO201011151
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3117,6 +3117,8 @@ DATA(insert OID = 2773 ( pg_stat_get_bgwriter_maxwritten_clean PGNSP PGUID 12 1
31173117
DESCR("statistics: number of times the bgwriter stopped processing when it had written too many buffers while cleaning");
31183118
DATA(insertOID=2775 (pg_stat_get_buf_written_backendPGNSPPGUID12100ffftfs0020""_null__null__null__null_pg_stat_get_buf_written_backend_null__null__null_ ));
31193119
DESCR("statistics: number of buffers written by backends");
3120+
DATA(insertOID=3063 (pg_stat_get_buf_fsync_backendPGNSPPGUID12100ffftfs0020""_null__null__null__null_pg_stat_get_buf_fsync_backend_null__null__null_ ));
3121+
DESCR("statistics: number of backend buffer writes that did their own fsync");
31203122
DATA(insertOID=2859 (pg_stat_get_buf_allocPGNSPPGUID12100ffftfs0020""_null__null__null__null_pg_stat_get_buf_alloc_null__null__null_ ));
31213123
DESCR("statistics: number of buffer allocations");
31223124

‎src/include/pgstat.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -360,6 +360,7 @@ typedef struct PgStat_MsgBgWriter
360360
PgStat_Counterm_buf_written_clean;
361361
PgStat_Counterm_maxwritten_clean;
362362
PgStat_Counterm_buf_written_backend;
363+
PgStat_Counterm_buf_fsync_backend;
363364
PgStat_Counterm_buf_alloc;
364365
}PgStat_MsgBgWriter;
365366

@@ -562,6 +563,7 @@ typedef struct PgStat_GlobalStats
562563
PgStat_Counterbuf_written_clean;
563564
PgStat_Countermaxwritten_clean;
564565
PgStat_Counterbuf_written_backend;
566+
PgStat_Counterbuf_fsync_backend;
565567
PgStat_Counterbuf_alloc;
566568
}PgStat_GlobalStats;
567569

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1293,7 +1293,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
12931293
pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_port, s.backend_start, s.xact_start, s.query_start, s.waiting, s.current_query FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, application_name, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
12941294
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"]));
12951295
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;
1296-
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_alloc() AS buffers_alloc;
1296+
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;
12971297
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 FROM pg_database d;
12981298
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));
12991299
pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text));

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp