4
4
<title>26.3.�WAL Configuration</title>
5
5
<link rel="stylesheet" href="stylesheet.css" type="text/css">
6
6
<link rev="made" href="pgsql-docs@postgresql.org">
7
<meta name="generator" content="DocBook XSL Stylesheets V1.69.1">
8
<link rel="start" href="index.html" title="PostgreSQL 8.1.3 Documentation">
7
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
8
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
9
9
<link rel="up" href="wal.html" title="Chapter�26.�Reliability and the Write-Ahead Log">
10
10
<link rel="prev" href="wal-intro.html" title="26.2.�Write-Ahead Logging (WAL)">
11
11
<link rel="next" href="wal-internals.html" title="26.4.�WAL Internals">
12
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
13
14
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
14
15
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
15
<a name="wal-configuration"></a>26.3.�<span class="acronym">WAL</span> Configuration</h2></div></div></div>
16
<p> There are several <span class="acronym">WAL</span>-related configuration parameters that
16
<a name="wal-configuration"></a>26.3.�<acronym class="acronym">WAL</acronym> Configuration</h2></div></div></div>
17
<p> There are several <acronym class="acronym">WAL</acronym>-related configuration parameters that
17
18
affect database performance. This section explains their use.
18
19
Consult <a href="runtime-config.html" title="Chapter�17.�Server Configuration">Chapter�17, <i>Server Configuration</i></a> for general information about
19
20
setting server configuration parameters.
21
<p> <em class="firstterm">Checkpoints</em><a name="id621052"></a>
22
<p> <em class="firstterm">Checkpoints</em><a name="id673773"></a>
22
23
are points in the sequence of transactions at which it is guaranteed
23
24
that the data files have been updated with all information written before
24
25
the checkpoint. At checkpoint time, all dirty data pages are flushed to
29
30
data files before that point are known to be already on disk. Hence, after
30
31
a checkpoint has been made, any log segments preceding the one containing
31
32
the redo record are no longer needed and can be recycled or removed. (When
32
<span class="acronym">WAL</span> archiving is being done, the log segments must be
33
<acronym class="acronym">WAL</acronym> archiving is being done, the log segments must be
33
34
archived before being recycled or removed.)
35
36
<p> The server's background writer process will automatically perform
71
72
not be more than 2 * <code class="varname">checkpoint_segments</code> + 1
72
73
files. Each segment file is normally 16 MB (though this size can be
73
74
altered when building the server). You can use this to estimate space
74
requirements for <span class="acronym">WAL</span>.
75
requirements for <acronym class="acronym">WAL</acronym>.
75
76
Ordinarily, when old log segment files are no longer needed, they
76
77
are recycled (renamed to become the next segments in the numbered
77
78
sequence). If, due to a short-term peak of log output rate, there
79
80
segment files, the unneeded segment files will be deleted instead
80
81
of recycled until the system gets back under this limit.
82
<p> There are two commonly used internal <span class="acronym">WAL</span> functions:
83
<p> There are two commonly used internal <acronym class="acronym">WAL</acronym> functions:
83
84
<code class="function">LogInsert</code> and <code class="function">LogFlush</code>.
84
85
<code class="function">LogInsert</code> is used to place a new record into
85
the <span class="acronym">WAL</span> buffers in shared memory. If there is no
86
the <acronym class="acronym">WAL</acronym> buffers in shared memory. If there is no
86
87
space for the new record, <code class="function">LogInsert</code> will have
87
to write (move to kernel cache) a few filled <span class="acronym">WAL</span>
88
to write (move to kernel cache) a few filled <acronym class="acronym">WAL</acronym>
88
89
buffers. This is undesirable because <code class="function">LogInsert</code>
89
90
is used on every database low level modification (for example, row
90
91
insertion) at a time when an exclusive lock is held on affected
91
92
data pages, so the operation needs to be as fast as possible. What
92
is worse, writing <span class="acronym">WAL</span> buffers may also force the
93
is worse, writing <acronym class="acronym">WAL</acronym> buffers may also force the
93
94
creation of a new log segment, which takes even more
94
time. Normally, <span class="acronym">WAL</span> buffers should be written
95
time. Normally, <acronym class="acronym">WAL</acronym> buffers should be written
95
96
and flushed by a <code class="function">LogFlush</code> request, which is
96
97
made, for the most part, at transaction commit time to ensure that
97
98
transaction records are flushed to permanent storage. On systems
98
99
with high log output, <code class="function">LogFlush</code> requests may
99
100
not occur often enough to prevent <code class="function">LogInsert</code>
100
101
from having to do writes. On such systems
101
one should increase the number of <span class="acronym">WAL</span> buffers by
102
modifying the configuration parameter <a href="runtime-config-wal.html#guc-wal-buffers">wal_buffers</a>. The default number of <span class="acronym">WAL</span>
102
one should increase the number of <acronym class="acronym">WAL</acronym> buffers by
103
modifying the configuration parameter <a href="runtime-config-wal.html#guc-wal-buffers">wal_buffers</a>. The default number of <acronym class="acronym">WAL</acronym>
103
104
buffers is 8. Increasing this value will
104
105
correspondingly increase shared memory usage. When
105
106
<a href="runtime-config-wal.html#guc-full-page-writes">full_page_writes</a> is set and the system is very busy,
125
126
<p> The <a href="runtime-config-wal.html#guc-wal-sync-method">wal_sync_method</a> parameter determines how
126
127
<span class="productname">PostgreSQL</span> will ask the kernel to force
127
<span class="acronym">WAL</span> updates out to disk.
128
<acronym class="acronym">WAL</acronym> updates out to disk.
128
129
All the options should be the same as far as reliability goes,
129
130
but it's quite platform-specific which one will be the fastest.
130
131
Note that this parameter is irrelevant if <code class="varname">fsync</code>
134
135
(provided that <span class="productname">PostgreSQL</span> has been
135
136
compiled with support for it) will result in each
136
137
<code class="function">LogInsert</code> and <code class="function">LogFlush</code>
137
<span class="acronym">WAL</span> call being logged to the server log. This
138
<acronym class="acronym">WAL</acronym> call being logged to the server log. This
138
139
option may be replaced by a more general mechanism in the future.