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

Commit867e2c9

Browse files
committed
Implement "distributed" checkpoints in which the checkpoint I/O is spread
over a fairly long period of time, rather than being spat out in a burst.This happens only for background checkpoints carried out by the bgwriter;other cases, such as a shutdown checkpoint, are still done at full speed.Remove the "all buffers" scan in the bgwriter, and associated statsinfrastructure, since this seems no longer very useful when the checkpointitself is properly throttled.Original patch by Itagaki Takahiro, reworked by Heikki Linnakangas,and some minor API editorialization by me.
1 parentb09c248 commit867e2c9

File tree

22 files changed

+748
-451
lines changed

22 files changed

+748
-451
lines changed

‎doc/src/sgml/config.sgml‎

Lines changed: 23 additions & 59 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.128 2007/06/22 16:15:23 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.129 2007/06/28 00:02:37 tgl Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -1168,21 +1168,17 @@ SET ENABLE_SEQSCAN TO OFF;
11681168

11691169
<para>
11701170
Beginning in <productname>PostgreSQL</> 8.0, there is a separate server
1171-
process called the <firstterm>background writer</>, whosesolefunction
1171+
process called the <firstterm>background writer</>, whose function
11721172
is to issue writes of <quote>dirty</> shared buffers. The intent is
11731173
that server processes handling user queries should seldom or never have
11741174
to wait for a write to occur, because the background writer will do it.
1175-
This arrangement also reduces the performance penalty associated with
1176-
checkpoints. The background writer will continuously trickle out dirty
1177-
pages to disk, so that only a few pages will need to be forced out when
1178-
checkpoint time arrives, instead of the storm of dirty-buffer writes that
1179-
formerly occurred at each checkpoint. However there is a net overall
1175+
However there is a net overall
11801176
increase in I/O load, because where a repeatedly-dirtied page might
11811177
before have been written only once per checkpoint interval, the
11821178
background writer might write it several times in the same interval.
11831179
In most situations a continuous low load is preferable to periodic
1184-
spikes, but the parameters discussed in this subsection can be used to tune
1185-
the behavior for local needs.
1180+
spikes, but the parameters discussed in this subsection can be used to
1181+
tunethe behavior for local needs.
11861182
</para>
11871183

11881184
<variablelist>
@@ -1242,62 +1238,14 @@ SET ENABLE_SEQSCAN TO OFF;
12421238
</para>
12431239
</listitem>
12441240
</varlistentry>
1245-
1246-
<varlistentry id="guc-bgwriter-all-percent" xreflabel="bgwriter_all_percent">
1247-
<term><varname>bgwriter_all_percent</varname> (<type>floating point</type>)</term>
1248-
<indexterm>
1249-
<primary><varname>bgwriter_all_percent</> configuration parameter</primary>
1250-
</indexterm>
1251-
<listitem>
1252-
<para>
1253-
To reduce the amount of work that will be needed at checkpoint time,
1254-
the background writer also does a circular scan through the entire
1255-
buffer pool, writing buffers that are found to be dirty.
1256-
In each round, it examines up to
1257-
<varname>bgwriter_all_percent</> of the buffers for this purpose.
1258-
The default value is 0.333 (0.333% of the total number
1259-
of shared buffers). With the default <varname>bgwriter_delay</>
1260-
setting, this will allow the entire shared buffer pool to be scanned
1261-
about once per minute.
1262-
This parameter can only be set in the <filename>postgresql.conf</>
1263-
file or on the server command line.
1264-
</para>
1265-
</listitem>
1266-
</varlistentry>
1267-
1268-
<varlistentry id="guc-bgwriter-all-maxpages" xreflabel="bgwriter_all_maxpages">
1269-
<term><varname>bgwriter_all_maxpages</varname> (<type>integer</type>)</term>
1270-
<indexterm>
1271-
<primary><varname>bgwriter_all_maxpages</> configuration parameter</primary>
1272-
</indexterm>
1273-
<listitem>
1274-
<para>
1275-
In each round, no more than this many buffers will be written
1276-
as a result of the scan of the entire buffer pool. (If this
1277-
limit is reached, the scan stops, and resumes at the next buffer
1278-
during the next round.)
1279-
The default value is five buffers.
1280-
This parameter can only be set in the <filename>postgresql.conf</>
1281-
file or on the server command line.
1282-
</para>
1283-
</listitem>
1284-
</varlistentry>
12851241
</variablelist>
12861242

12871243
<para>
1288-
Smaller values of <varname>bgwriter_all_percent</varname> and
1289-
<varname>bgwriter_all_maxpages</varname> reduce the extra I/O load
1290-
caused by the background writer, but leave more work to be done
1291-
at checkpoint time. To reduce load spikes at checkpoints,
1292-
increase these two values.
1293-
Similarly, smaller values of <varname>bgwriter_lru_percent</varname> and
1244+
Smaller values of <varname>bgwriter_lru_percent</varname> and
12941245
<varname>bgwriter_lru_maxpages</varname> reduce the extra I/O load
12951246
caused by the background writer, but make it more likely that server
12961247
processes will have to issue writes for themselves, delaying interactive
12971248
queries.
1298-
To disable background writing entirely,
1299-
set both <varname>maxpages</varname> values and/or both
1300-
<varname>percent</varname> values to zero.
13011249
</para>
13021250
</sect2>
13031251
</sect1>
@@ -1307,7 +1255,7 @@ SET ENABLE_SEQSCAN TO OFF;
13071255

13081256
<para>
13091257
See also <xref linkend="wal-configuration"> for details on WAL
1310-
tuning.
1258+
and checkpointtuning.
13111259
</para>
13121260

13131261
<sect2 id="runtime-config-wal-settings">
@@ -1565,6 +1513,22 @@ SET ENABLE_SEQSCAN TO OFF;
15651513
</listitem>
15661514
</varlistentry>
15671515

1516+
<varlistentry id="guc-checkpoint-completion-target" xreflabel="checkpoint_completion_target">
1517+
<term><varname>checkpoint_completion_target</varname> (<type>floating point</type>)</term>
1518+
<indexterm>
1519+
<primary><varname>checkpoint_completion_target</> configuration parameter</primary>
1520+
</indexterm>
1521+
<listitem>
1522+
<para>
1523+
Specifies the target length of checkpoints, as a fraction of
1524+
the checkpoint interval. The default is 0.5.
1525+
1526+
This parameter can only be set in the <filename>postgresql.conf</>
1527+
file or on the server command line.
1528+
</para>
1529+
</listitem>
1530+
</varlistentry>
1531+
15681532
<varlistentry id="guc-checkpoint-warning" xreflabel="checkpoint_warning">
15691533
<term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
15701534
<indexterm>

‎doc/src/sgml/monitoring.sgml‎

Lines changed: 9 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.50 2007/04/27 20:08:43 neilc Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.51 2007/06/28 00:02:37 tgl Exp $ -->
22

33
<chapter id="monitoring">
44
<title>Monitoring Database Activity</title>
@@ -251,9 +251,9 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
251251
<entry><structname>pg_stat_bgwriter</></entry>
252252
<entry>One row only, showing cluster-wide statistics from the
253253
background writer: number of scheduled checkpoints, requested
254-
checkpoints, buffers written by checkpoints, lru-scansandall-scans,
255-
and the number of times the bgwriteraborted around because it had
256-
written too many buffers during lru-scans and all-scans.
254+
checkpoints, buffers written by checkpointsandcleaningscans,
255+
and the number of times the bgwriterstopped acleaning scan
256+
because it hadwritten too many buffers.
257257
</entry>
258258
</row>
259259

@@ -777,43 +777,24 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
777777
</row>
778778

779779
<row>
780-
<entry><literal><function>pg_stat_get_bgwriter_buf_written_lru</function>()</literal></entry>
780+
<entry><literal><function>pg_stat_get_bgwriter_buf_written_clean</function>()</literal></entry>
781781
<entry><type>bigint</type></entry>
782782
<entry>
783-
The number of buffers written by the bgwriterwhen performing a
784-
LRU scan of the buffer cache
783+
The number of buffers written by the bgwriterfor routine cleaning of
784+
dirty pages
785785
</entry>
786786
</row>
787787

788788
<row>
789-
<entry><literal><function>pg_stat_get_bgwriter_buf_written_all</function>()</literal></entry>
789+
<entry><literal><function>pg_stat_get_bgwriter_maxwritten_clean</function>()</literal></entry>
790790
<entry><type>bigint</type></entry>
791791
<entry>
792-
The number of buffers written by the bgwriter when performing a
793-
scan of all the buffer cache
794-
</entry>
795-
</row>
796-
797-
<row>
798-
<entry><literal><function>pg_stat_get_bgwriter_maxwritten_lru</function>()</literal></entry>
799-
<entry><type>bigint</type></entry>
800-
<entry>
801-
The number of times the bgwriter has stopped its LRU round because
792+
The number of times the bgwriter has stopped its cleaning scan because
802793
it has written more buffers than specified in the
803794
<varname>bgwriter_lru_maxpages</varname> parameter
804795
</entry>
805796
</row>
806797

807-
<row>
808-
<entry><literal><function>pg_stat_get_bgwriter_maxwritten_all</function>()</literal></entry>
809-
<entry><type>bigint</type></entry>
810-
<entry>
811-
The number of times the bgwriter has stopped its all-buffer round
812-
because it has written more buffers than specified in the
813-
<varname>bgwriter_all_maxpages</varname> parameter
814-
</entry>
815-
</row>
816-
817798
<row>
818799
<entry><literal><function>pg_stat_clear_snapshot</function>()</literal></entry>
819800
<entry><type>void</type></entry>

‎doc/src/sgml/wal.sgml‎

Lines changed: 30 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/wal.sgml,v 1.43 2007/01/31 20:56:19 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/wal.sgml,v 1.44 2007/06/28 00:02:37 tgl Exp $ -->
22

33
<chapter id="wal">
44
<title>Reliability and the Write-Ahead Log</title>
@@ -217,15 +217,41 @@
217217
</para>
218218

219219
<para>
220-
There will be at least one WAL segment file, and will normally
221-
not be more than 2 * <varname>checkpoint_segments</varname> + 1
220+
To avoid flooding the I/O system with a burst of page writes,
221+
writing dirty buffers during a checkpoint is spread over a period of time.
222+
That period is controlled by
223+
<xref linkend="guc-checkpoint-completion-target">, which is
224+
given as a fraction of the checkpoint interval.
225+
The I/O rate is adjusted so that the checkpoint finishes when the
226+
given fraction of <varname>checkpoint_segments</varname> WAL segments
227+
have been consumed since checkpoint start, or the given fraction of
228+
<varname>checkpoint_timeout</varname> seconds have elapsed,
229+
whichever is sooner. With the default value of 0.5,
230+
<productname>PostgreSQL</> can be expected to complete each checkpoint
231+
in about half the time before the next checkpoint starts. On a system
232+
that's very close to maximum I/O throughput during normal operation,
233+
you might want to increase <varname>checkpoint_completion_target</varname>
234+
to reduce the I/O load from checkpoints. The disadvantage of this is that
235+
prolonging checkpoints affects recovery time, because more WAL segments
236+
will need to be kept around for possible use in recovery. Although
237+
<varname>checkpoint_completion_target</varname> can be set as high as 1.0,
238+
it is best to keep it less than that (perhaps 0.9 at most) since
239+
checkpoints include some other activities besides writing dirty buffers.
240+
A setting of 1.0 is quite likely to result in checkpoints not being
241+
completed on time, which would result in performance loss due to
242+
unexpected variation in the number of WAL segments needed.
243+
</para>
244+
245+
<para>
246+
There will always be at least one WAL segment file, and will normally
247+
not be more than (2 + <varname>checkpoint_completion_target</varname>) * <varname>checkpoint_segments</varname> + 1
222248
files. Each segment file is normally 16 MB (though this size can be
223249
altered when building the server). You can use this to estimate space
224250
requirements for <acronym>WAL</acronym>.
225251
Ordinarily, when old log segment files are no longer needed, they
226252
are recycled (renamed to become the next segments in the numbered
227253
sequence). If, due to a short-term peak of log output rate, there
228-
are more than2 * <varname>checkpoint_segments</varname> + 1
254+
are more than3 * <varname>checkpoint_segments</varname> + 1
229255
segment files, the unneeded segment files will be deleted instead
230256
of recycled until the system gets back under this limit.
231257
</para>

0 commit comments

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