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

Commit6f5c38d

Browse files
committed
Just-in-time background writing strategy. This code avoids re-scanning
buffers that cannot possibly need to be cleaned, and estimates how manybuffers it should try to clean based on moving averages of recent allocationrequests and density of reusable buffers. The patch also adds a couplemore columns to pg_stat_bgwriter to help measure the effectiveness of thebgwriter.Greg Smith, building on his own work and ideas from several other people,in particular a much older patch from Itagaki Takahiro.
1 parent588901d commit6f5c38d

File tree

16 files changed

+437
-95
lines changed

16 files changed

+437
-95
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 25 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.146 2007/09/2403:12:23 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.147 2007/09/25 20:03:37 tgl Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -1205,35 +1205,42 @@ SET ENABLE_SEQSCAN TO OFF;
12051205
</listitem>
12061206
</varlistentry>
12071207

1208-
<varlistentry id="guc-bgwriter-lru-percent" xreflabel="bgwriter_lru_percent">
1209-
<term><varname>bgwriter_lru_percent</varname> (<type>floating point</type>)</term>
1208+
<varlistentry id="guc-bgwriter-lru-maxpages" xreflabel="bgwriter_lru_maxpages">
1209+
<term><varname>bgwriter_lru_maxpages</varname> (<type>integer</type>)</term>
12101210
<indexterm>
1211-
<primary><varname>bgwriter_lru_percent</> configuration parameter</primary>
1211+
<primary><varname>bgwriter_lru_maxpages</> configuration parameter</primary>
12121212
</indexterm>
12131213
<listitem>
12141214
<para>
1215-
To reduce the probability that server processes will need to issue
1216-
their own writes, the background writer tries to write buffers that
1217-
are likely to be recycled soon. In each round, it examines up to
1218-
<varname>bgwriter_lru_percent</> of the buffers that are nearest to
1219-
being recycled, and writes any that are dirty.
1220-
The default value is 1.0 (1% of the total number of shared buffers).
1215+
In each round, no more than this many buffers will be written
1216+
by the background writer. Setting this to zero disables
1217+
background writing (except for checkpoint activity).
1218+
The default value is 100 buffers.
12211219
This parameter can only be set in the <filename>postgresql.conf</>
12221220
file or on the server command line.
12231221
</para>
12241222
</listitem>
12251223
</varlistentry>
12261224

1227-
<varlistentry id="guc-bgwriter-lru-maxpages" xreflabel="bgwriter_lru_maxpages">
1228-
<term><varname>bgwriter_lru_maxpages</varname> (<type>integer</type>)</term>
1225+
<varlistentry id="guc-bgwriter-lru-multiplier" xreflabel="bgwriter_lru_multiplier">
1226+
<term><varname>bgwriter_lru_multiplier</varname> (<type>floating point</type>)</term>
12291227
<indexterm>
1230-
<primary><varname>bgwriter_lru_maxpages</> configuration parameter</primary>
1228+
<primary><varname>bgwriter_lru_multiplier</> configuration parameter</primary>
12311229
</indexterm>
12321230
<listitem>
12331231
<para>
1234-
In each round, no more than this many buffers will be written
1235-
as a result of scanning soon-to-be-recycled buffers.
1236-
The default value is five buffers.
1232+
Unless limited by <varname>bgwriter_lru_maxpages</>, the number
1233+
of dirty buffers written in each round is determined by reference
1234+
to the number of new buffers that have been needed by server
1235+
processes during recent rounds. This number is multiplied by
1236+
<varname>bgwriter_lru_multiplier</> to arrive at the estimate
1237+
of the number of buffers that will be needed during the next round.
1238+
Thus, a setting of 1.0 represents a <quote>just in time</> policy
1239+
of writing exactly the number of buffers predicted to be needed.
1240+
Larger values provide some cushion against spikes in demand,
1241+
while smaller values intentionally leave writes to be done by
1242+
server processes.
1243+
The default is 2.0.
12371244
This parameter can only be set in the <filename>postgresql.conf</>
12381245
file or on the server command line.
12391246
</para>
@@ -1242,8 +1249,8 @@ SET ENABLE_SEQSCAN TO OFF;
12421249
</variablelist>
12431250

12441251
<para>
1245-
Smaller values of <varname>bgwriter_lru_percent</varname> and
1246-
<varname>bgwriter_lru_maxpages</varname> reduce the extra I/O load
1252+
Smaller values of <varname>bgwriter_lru_maxpages</varname> and
1253+
<varname>bgwriter_lru_multiplier</varname> reduce the extra I/O load
12471254
caused by the background writer, but make it more likely that server
12481255
processes will have to issue writes for themselves, delaying interactive
12491256
queries.

‎doc/src/sgml/monitoring.sgml

Lines changed: 22 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.53 2007/09/2403:12:23 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.54 2007/09/25 20:03:37 tgl Exp $ -->
22

33
<chapter id="monitoring">
44
<title>Monitoring Database Activity</title>
@@ -237,7 +237,10 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
237237
background writer: number of scheduled checkpoints, requested
238238
checkpoints, buffers written by checkpoints and cleaning scans,
239239
and the number of times the bgwriter stopped a cleaning scan
240-
because it had written too many buffers.
240+
because it had written too many buffers. Also includes
241+
statistics about the shared buffer pool, including buffers written
242+
by backends (that is, not by the background writer) and total buffers
243+
allocated.
241244
</entry>
242245
</row>
243246

@@ -817,6 +820,23 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
817820
</entry>
818821
</row>
819822

823+
<row>
824+
<entry><literal><function>pg_stat_get_buf_written_backend</function>()</literal></entry>
825+
<entry><type>bigint</type></entry>
826+
<entry>
827+
The number of buffers written by backends because they needed
828+
to allocate a new buffer
829+
</entry>
830+
</row>
831+
832+
<row>
833+
<entry><literal><function>pg_stat_get_buf_alloc</function>()</literal></entry>
834+
<entry><type>bigint</type></entry>
835+
<entry>
836+
The total number of buffer allocations
837+
</entry>
838+
</row>
839+
820840
<row>
821841
<entry><literal><function>pg_stat_clear_snapshot</function>()</literal></entry>
822842
<entry><type>void</type></entry>

‎src/backend/catalog/system_views.sql

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2007, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.45 2007/09/20 17:56:30 tgl Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.46 2007/09/25 20:03:37 tgl Exp $
77
*/
88

99
CREATEVIEWpg_rolesAS
@@ -382,7 +382,9 @@ CREATE VIEW pg_stat_bgwriter AS
382382
pg_stat_get_bgwriter_requested_checkpoints()AS checkpoints_req,
383383
pg_stat_get_bgwriter_buf_written_checkpoints()AS buffers_checkpoint,
384384
pg_stat_get_bgwriter_buf_written_clean()AS buffers_clean,
385-
pg_stat_get_bgwriter_maxwritten_clean()AS maxwritten_clean;
385+
pg_stat_get_bgwriter_maxwritten_clean()AS maxwritten_clean,
386+
pg_stat_get_buf_written_backend()AS buffers_backend,
387+
pg_stat_get_buf_alloc()AS buffers_alloc;
386388

387389
-- Tsearch debug function. Defined here because it'd be pretty unwieldy
388390
-- to put it into pg_proc.h

‎src/backend/postmaster/bgwriter.c

Lines changed: 27 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
*
3838
*
3939
* IDENTIFICATION
40-
* $PostgreSQL: pgsql/src/backend/postmaster/bgwriter.c,v 1.43 2007/09/16 16:33:04 tgl Exp $
40+
* $PostgreSQL: pgsql/src/backend/postmaster/bgwriter.c,v 1.44 2007/09/25 20:03:37 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -98,8 +98,14 @@
9898
* requesting backends since the last checkpoint start. The flags are
9999
* chosen so that OR'ing is the correct way to combine multiple requests.
100100
*
101+
* num_backend_writes is used to count the number of buffer writes performed
102+
* by non-bgwriter processes. This counter should be wide enough that it
103+
* can't overflow during a single bgwriter cycle.
104+
*
101105
* The requests array holds fsync requests sent by backends and not yet
102-
* absorbed by the bgwriter. Unlike the checkpoint fields, the requests
106+
* absorbed by the bgwriter.
107+
*
108+
* Unlike the checkpoint fields, num_backend_writes and the requests
103109
* fields are protected by BgWriterCommLock.
104110
*----------
105111
*/
@@ -122,6 +128,8 @@ typedef struct
122128

123129
intckpt_flags;/* checkpoint flags, as defined in xlog.h */
124130

131+
uint32num_backend_writes;/* counts non-bgwriter buffer writes */
132+
125133
intnum_requests;/* current # of requests */
126134
intmax_requests;/* allocated array size */
127135
BgWriterRequestrequests[1];/* VARIABLE LENGTH ARRAY */
@@ -566,8 +574,7 @@ BgWriterNap(void)
566574
*
567575
* We absorb pending requests after each short sleep.
568576
*/
569-
if ((bgwriter_lru_percent>0.0&&bgwriter_lru_maxpages>0)||
570-
ckpt_active)
577+
if (bgwriter_lru_maxpages>0||ckpt_active)
571578
udelay=BgWriterDelay*1000L;
572579
elseif (XLogArchiveTimeout>0)
573580
udelay=1000000L;/* One second */
@@ -648,12 +655,13 @@ CheckpointWriteDelay(int flags, double progress)
648655
got_SIGHUP= false;
649656
ProcessConfigFile(PGC_SIGHUP);
650657
}
651-
BgBufferSync();
652-
CheckArchiveTimeout();
653-
BgWriterNap();
654658

655659
AbsorbFsyncRequests();
656660
absorb_counter=WRITES_PER_ABSORB;
661+
662+
BgBufferSync();
663+
CheckArchiveTimeout();
664+
BgWriterNap();
657665
}
658666
elseif (--absorb_counter <=0)
659667
{
@@ -963,7 +971,8 @@ RequestCheckpoint(int flags)
963971
* Whenever a backend is compelled to write directly to a relation
964972
* (which should be seldom, if the bgwriter is getting its job done),
965973
* the backend calls this routine to pass over knowledge that the relation
966-
* is dirty and must be fsync'd before next checkpoint.
974+
* is dirty and must be fsync'd before next checkpoint. We also use this
975+
* opportunity to count such writes for statistical purposes.
967976
*
968977
* segno specifies which segment (not block!) of the relation needs to be
969978
* fsync'd. (Since the valid range is much less than BlockNumber, we can
@@ -987,7 +996,13 @@ ForwardFsyncRequest(RelFileNode rnode, BlockNumber segno)
987996
if (!IsUnderPostmaster)
988997
return false;/* probably shouldn't even get here */
989998

999+
Assert(!am_bg_writer);
1000+
9901001
LWLockAcquire(BgWriterCommLock,LW_EXCLUSIVE);
1002+
1003+
/* we count non-bgwriter writes even when the request queue overflows */
1004+
BgWriterShmem->num_backend_writes++;
1005+
9911006
if (BgWriterShmem->bgwriter_pid==0||
9921007
BgWriterShmem->num_requests >=BgWriterShmem->max_requests)
9931008
{
@@ -1035,6 +1050,10 @@ AbsorbFsyncRequests(void)
10351050
*/
10361051
LWLockAcquire(BgWriterCommLock,LW_EXCLUSIVE);
10371052

1053+
/* Transfer write count into pending pgstats message */
1054+
BgWriterStats.m_buf_written_backend+=BgWriterShmem->num_backend_writes;
1055+
BgWriterShmem->num_backend_writes=0;
1056+
10381057
n=BgWriterShmem->num_requests;
10391058
if (n>0)
10401059
{

‎src/backend/postmaster/pgstat.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
*Copyright (c) 2001-2007, PostgreSQL Global Development Group
1515
*
16-
*$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.165 2007/09/2403:12:23 tgl Exp $
16+
*$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.166 2007/09/25 20:03:37 tgl Exp $
1717
* ----------
1818
*/
1919
#include"postgres.h"
@@ -3165,4 +3165,6 @@ pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
31653165
globalStats.buf_written_checkpoints+=msg->m_buf_written_checkpoints;
31663166
globalStats.buf_written_clean+=msg->m_buf_written_clean;
31673167
globalStats.maxwritten_clean+=msg->m_maxwritten_clean;
3168+
globalStats.buf_written_backend+=msg->m_buf_written_backend;
3169+
globalStats.buf_alloc+=msg->m_buf_alloc;
31683170
}

0 commit comments

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