1 <!-- $PostgreSQL: pgsql/doc/src/sgml/wal.sgml,v 1.51 2007/12/16 14:05:12 momjian Exp $ -->
4 <title>Reliability and the Write-Ahead Log</title>
7 This chapter explains how the Write-Ahead Log is used to obtain
8 efficient, reliable operation.
11 <sect1 id="wal-reliability">
12 <title>Reliability</title>
15 Reliability is an important property of any serious database
16 system, and <productname>PostgreSQL</> does everything possible to
17 guarantee reliable operation. One aspect of reliable operation is
18 that all data recorded by a committed transaction should be stored
19 in a nonvolatile area that is safe from power loss, operating
20 system failure, and hardware failure (except failure of the
21 nonvolatile area itself, of course). Successfully writing the data
22 to the computer's permanent storage (disk drive or equivalent)
23 ordinarily meets this requirement. In fact, even if a computer is
24 fatally damaged, if the disk drives survive they can be moved to
25 another computer with similar hardware and all committed
26 transactions will remain intact.
30 While forcing data periodically to the disk platters might seem like
31 a simple operation, it is not. Because disk drives are dramatically
32 slower than main memory and CPUs, several layers of caching exist
33 between the computer's main memory and the disk platters.
34 First, there is the operating system's buffer cache, which caches
35 frequently requested disk blocks and combines disk writes. Fortunately,
36 all operating systems give applications a way to force writes from
37 the buffer cache to disk, and <productname>PostgreSQL</> uses those
38 features. (See the <xref linkend="guc-wal-sync-method"> parameter
39 to adjust how this is done.)
43 Next, there might be a cache in the disk drive controller; this is
44 particularly common on <acronym>RAID</> controller cards. Some of
45 these caches are <firstterm>write-through</>, meaning writes are passed
46 along to the drive as soon as they arrive. Others are
47 <firstterm>write-back</>, meaning data is passed on to the drive at
48 some later time. Such caches can be a reliability hazard because the
49 memory in the disk controller cache is volatile, and will lose its
50 contents in a power failure. Better controller cards have
51 <firstterm>battery-backed</> caches, meaning the card has a battery that
52 maintains power to the cache in case of system power loss. After power
53 is restored the data will be written to the disk drives.
57 And finally, most disk drives have caches. Some are write-through
58 while some are write-back, and the
59 same concerns about data loss exist for write-back drive caches as
60 exist for disk controller caches. Consumer-grade IDE and SATA drives are
61 particularly likely to have write-back caches that will not survive a
62 power failure. To check write caching on <productname>Linux</> use
63 <command>hdparm -I</>; it is enabled if there is a <literal>*</> next
64 to <literal>Write cache</>. <command>hdparm -W</> to turn off
65 write caching. On <productname>FreeBSD</> use
66 <application>atacontrol</>. (For SCSI disks use <ulink
67 url="http://sg.torque.net/sg/sdparm.html"><application>sdparm</></ulink>
68 to turn off <literal>WCE</>.) On <productname>Windows</> if
69 <varname>wal_sync_method</> is <literal>open_datasync</> (the default),
70 write caching is disabled by unchecking <literal>My
71 Computer\Open\{select disk
72 drive}\Properties\Hardware\Properties\Policies\Enable write caching on
73 the disk</>. Also on Windows, <literal>fsync</> and
74 <literal>fsync_writethrough</> never do write caching.
78 When the operating system sends a write request to the disk hardware,
79 there is little it can do to make sure the data has arrived at a truly
80 non-volatile storage area. Rather, it is the
81 administrator's responsibility to be sure that all storage components
82 ensure data integrity. Avoid disk controllers that have non-battery-backed
83 write caches. At the drive level, disable write-back caching if the
84 drive cannot guarantee the data will be written before shutdown.
88 Another risk of data loss is posed by the disk platter write
89 operations themselves. Disk platters are divided into sectors,
90 commonly 512 bytes each. Every physical read or write operation
91 processes a whole sector.
92 When a write request arrives at the drive, it might be for 512 bytes,
93 1024 bytes, or 8192 bytes, and the process of writing could fail due
94 to power loss at any time, meaning some of the 512-byte sectors were
95 written, and others were not. To guard against such failures,
96 <productname>PostgreSQL</> periodically writes full page images to
97 permanent storage <emphasis>before</> modifying the actual page on
98 disk. By doing this, during crash recovery <productname>PostgreSQL</> can
99 restore partially-written pages. If you have a battery-backed disk
100 controller or file-system software that prevents partial page writes
101 (e.g., ReiserFS 4), you can turn off this page imaging by using the
102 <xref linkend="guc-full-page-writes"> parameter.
106 <sect1 id="wal-intro">
107 <title>Write-Ahead Logging (<acronym>WAL</acronym>)</title>
109 <indexterm zone="wal">
110 <primary>WAL</primary>
114 <primary>transaction log</primary>
119 <firstterm>Write-Ahead Logging</firstterm> (<acronym>WAL</acronym>)
120 is a standard method for ensuring data integrity. A detailed
121 description can be found in most (if not all) books about
122 transaction processing. Briefly, <acronym>WAL</acronym>'s central
123 concept is that changes to data files (where tables and indexes
124 reside) must be written only after those changes have been logged,
125 that is, after log records describing the changes have been flushed
126 to permanent storage. If we follow this procedure, we do not need
127 to flush data pages to disk on every transaction commit, because we
128 know that in the event of a crash we will be able to recover the
129 database using the log: any changes that have not been applied to
130 the data pages can be redone from the log records. (This is
131 roll-forward recovery, also known as REDO.)
135 Using <acronym>WAL</acronym> results in a
136 significantly reduced number of disk writes, because only the log
137 file needs to be flushed to disk to guarantee that a transaction is
138 committed, rather than every data file changed by the transaction.
139 The log file is written sequentially,
140 and so the cost of syncing the log is much less than the cost of
141 flushing the data pages. This is especially true for servers
142 handling many small transactions touching different parts of the data
143 store. Furthermore, when the server is processing many small concurrent
144 transactions, one <function>fsync</function> of the log file may
145 suffice to commit many transactions.
149 <acronym>WAL</acronym> also makes it possible to support on-line
150 backup and point-in-time recovery, as described in <xref
151 linkend="continuous-archiving">. By archiving the WAL data we can support
152 reverting to any time instant covered by the available WAL data:
153 we simply install a prior physical backup of the database, and
154 replay the WAL log just as far as the desired time. What's more,
155 the physical backup doesn't have to be an instantaneous snapshot
156 of the database state — if it is made over some period of time,
157 then replaying the WAL log for that period will fix any internal
162 <sect1 id="wal-async-commit">
163 <title>Asynchronous Commit</title>
166 <primary>synchronous commit</primary>
170 <primary>asynchronous commit</primary>
174 <firstterm>Asynchronous commit</> is an option that allows transactions
175 to complete more quickly, at the cost that the most recent transactions may
176 be lost if the database should crash. In many applications this is an
177 acceptable trade-off.
181 As described in the previous section, transaction commit is normally
182 <firstterm>synchronous</>: the server waits for the transaction's
183 <acronym>WAL</acronym> records to be flushed to permanent storage
184 before returning a success indication to the client. The client is
185 therefore guaranteed that a transaction reported to be committed will
186 be preserved, even in the event of a server crash immediately after.
187 However, for short transactions this delay is a major component of the
188 total transaction time. Selecting asynchronous commit mode means that
189 the server returns success as soon as the transaction is logically
190 completed, before the <acronym>WAL</acronym> records it generated have
191 actually made their way to disk. This can provide a significant boost
192 in throughput for small transactions.
196 Asynchronous commit introduces the risk of data loss. There is a short
197 time window between the report of transaction completion to the client
198 and the time that the transaction is truly committed (that is, it is
199 guaranteed not to be lost if the server crashes). Thus asynchronous
200 commit should not be used if the client will take external actions
201 relying on the assumption that the transaction will be remembered.
202 As an example, a bank would certainly not use asynchronous commit for
203 a transaction recording an ATM's dispensing of cash. But in many
204 scenarios, such as event logging, there is no need for a strong
205 guarantee of this kind.
209 The risk that is taken by using asynchronous commit is of data loss,
210 not data corruption. If the database should crash, it will recover
211 by replaying <acronym>WAL</acronym> up to the last record that was
212 flushed. The database will therefore be restored to a self-consistent
213 state, but any transactions that were not yet flushed to disk will
214 not be reflected in that state. The net effect is therefore loss of
215 the last few transactions. Because the transactions are replayed in
216 commit order, no inconsistency can be introduced — for example,
217 if transaction B made changes relying on the effects of a previous
218 transaction A, it is not possible for A's effects to be lost while B's
219 effects are preserved.
223 The user can select the commit mode of each transaction, so that
224 it is possible to have both synchronous and asynchronous commit
225 transactions running concurrently. This allows flexible trade-offs
226 between performance and certainty of transaction durability.
227 The commit mode is controlled by the user-settable parameter
228 <xref linkend="guc-synchronous-commit">, which can be changed in any of
229 the ways that a configuration parameter can be set. The mode used for
230 any one transaction depends on the value of
231 <varname>synchronous_commit</varname> when transaction commit begins.
235 Certain utility commands, for instance <command>DROP TABLE</>, are
236 forced to commit synchronously regardless of the setting of
237 <varname>synchronous_commit</varname>. This is to ensure consistency
238 between the server's file system and the logical state of the database.
239 The commands supporting two-phase commit, such as <command>PREPARE
240 TRANSACTION</>, are also always synchronous.
244 If the database crashes during the risk window between an
245 asynchronous commit and the writing of the transaction's
246 <acronym>WAL</acronym> records,
247 then changes made during that transaction <emphasis>will</> be lost.
249 risk window is limited because a background process (the <quote>WAL
250 writer</>) flushes unwritten <acronym>WAL</acronym> records to disk
251 every <xref linkend="guc-wal-writer-delay"> milliseconds.
252 The actual maximum duration of the risk window is three times
253 <varname>wal_writer_delay</varname> because the WAL writer is
254 designed to favor writing whole pages at a time during busy periods.
259 An immediate-mode shutdown is equivalent to a server crash, and will
260 therefore cause loss of any unflushed asynchronous commits.
265 Asynchronous commit provides behavior different from setting
266 <xref linkend="guc-fsync"> = off.
267 <varname>fsync</varname> is a server-wide
268 setting that will alter the behavior of all transactions. It disables
269 all logic within <productname>PostgreSQL</> that attempts to synchronize
270 writes to different portions of the database, and therefore a system
271 crash (that is, a hardware or operating system crash, not a failure of
272 <productname>PostgreSQL</> itself) could result in arbitrarily bad
273 corruption of the database state. In many scenarios, asynchronous
274 commit provides most of the performance improvement that could be
275 obtained by turning off <varname>fsync</varname>, but without the risk
280 <xref linkend="guc-commit-delay"> also sounds very similar to
281 asynchronous commit, but it is actually a synchronous commit method
282 (in fact, <varname>commit_delay</varname> is ignored during an
283 asynchronous commit). <varname>commit_delay</varname> causes a delay
284 just before a synchronous commit attempts to flush
285 <acronym>WAL</acronym> to disk, in the hope that a single flush
286 executed by one such transaction can also serve other transactions
287 committing at about the same time. Setting <varname>commit_delay</varname>
288 can only help when there are many concurrently committing transactions,
289 and it is difficult to tune it to a value that actually helps rather
290 than hurting throughput.
295 <sect1 id="wal-configuration">
296 <title><acronym>WAL</acronym> Configuration</title>
299 There are several <acronym>WAL</>-related configuration parameters that
300 affect database performance. This section explains their use.
301 Consult <xref linkend="runtime-config"> for general information about
302 setting server configuration parameters.
306 <firstterm>Checkpoints</firstterm><indexterm><primary>checkpoint</></>
307 are points in the sequence of transactions at which it is guaranteed
308 that the data files have been updated with all information written before
309 the checkpoint. At checkpoint time, all dirty data pages are flushed to
310 disk and a special checkpoint record is written to the log file.
311 In the event of a crash, the crash recovery procedure looks at the latest
312 checkpoint record to determine the point in the log (known as the redo
313 record) from which it should start the REDO operation. Any changes made to
314 data files before that point are known to be already on disk. Hence, after
315 a checkpoint has been made, any log segments preceding the one containing
316 the redo record are no longer needed and can be recycled or removed. (When
317 <acronym>WAL</acronym> archiving is being done, the log segments must be
318 archived before being recycled or removed.)
322 The server's background writer process will automatically perform
323 a checkpoint every so often. A checkpoint is created every <xref
324 linkend="guc-checkpoint-segments"> log segments, or every <xref
325 linkend="guc-checkpoint-timeout"> seconds, whichever comes first.
326 The default settings are 3 segments and 300 seconds respectively.
327 It is also possible to force a checkpoint by using the SQL command
328 <command>CHECKPOINT</command>.
332 Reducing <varname>checkpoint_segments</varname> and/or
333 <varname>checkpoint_timeout</varname> causes checkpoints to be done
334 more often. This allows faster after-crash recovery (since less work
335 will need to be redone). However, one must balance this against the
336 increased cost of flushing dirty data pages more often. If
337 <xref linkend="guc-full-page-writes"> is set (as is the default), there is
338 another factor to consider. To ensure data page consistency,
339 the first modification of a data page after each checkpoint results in
340 logging the entire page content. In that case,
341 a smaller checkpoint interval increases the volume of output to the WAL log,
342 partially negating the goal of using a smaller interval,
343 and in any case causing more disk I/O.
347 Checkpoints are fairly expensive, first because they require writing
348 out all currently dirty buffers, and second because they result in
349 extra subsequent WAL traffic as discussed above. It is therefore
350 wise to set the checkpointing parameters high enough that checkpoints
351 don't happen too often. As a simple sanity check on your checkpointing
352 parameters, you can set the <xref linkend="guc-checkpoint-warning">
353 parameter. If checkpoints happen closer together than
354 <varname>checkpoint_warning</> seconds,
355 a message will be output to the server log recommending increasing
356 <varname>checkpoint_segments</varname>. Occasional appearance of such
357 a message is not cause for alarm, but if it appears often then the
358 checkpoint control parameters should be increased. Bulk operations such
359 as large <command>COPY</> transfers might cause a number of such warnings
360 to appear if you have not set <varname>checkpoint_segments</> high
365 To avoid flooding the I/O system with a burst of page writes,
366 writing dirty buffers during a checkpoint is spread over a period of time.
367 That period is controlled by
368 <xref linkend="guc-checkpoint-completion-target">, which is
369 given as a fraction of the checkpoint interval.
370 The I/O rate is adjusted so that the checkpoint finishes when the
371 given fraction of <varname>checkpoint_segments</varname> WAL segments
372 have been consumed since checkpoint start, or the given fraction of
373 <varname>checkpoint_timeout</varname> seconds have elapsed,
374 whichever is sooner. With the default value of 0.5,
375 <productname>PostgreSQL</> can be expected to complete each checkpoint
376 in about half the time before the next checkpoint starts. On a system
377 that's very close to maximum I/O throughput during normal operation,
378 you might want to increase <varname>checkpoint_completion_target</varname>
379 to reduce the I/O load from checkpoints. The disadvantage of this is that
380 prolonging checkpoints affects recovery time, because more WAL segments
381 will need to be kept around for possible use in recovery. Although
382 <varname>checkpoint_completion_target</varname> can be set as high as 1.0,
383 it is best to keep it less than that (perhaps 0.9 at most) since
384 checkpoints include some other activities besides writing dirty buffers.
385 A setting of 1.0 is quite likely to result in checkpoints not being
386 completed on time, which would result in performance loss due to
387 unexpected variation in the number of WAL segments needed.
391 There will always be at least one WAL segment file, and will normally
392 not be more than (2 + <varname>checkpoint_completion_target</varname>) * <varname>checkpoint_segments</varname> + 1
393 files. Each segment file is normally 16 MB (though this size can be
394 altered when building the server). You can use this to estimate space
395 requirements for <acronym>WAL</acronym>.
396 Ordinarily, when old log segment files are no longer needed, they
397 are recycled (renamed to become the next segments in the numbered
398 sequence). If, due to a short-term peak of log output rate, there
399 are more than 3 * <varname>checkpoint_segments</varname> + 1
400 segment files, the unneeded segment files will be deleted instead
401 of recycled until the system gets back under this limit.
405 There are two commonly used internal <acronym>WAL</acronym> functions:
406 <function>LogInsert</function> and <function>LogFlush</function>.
407 <function>LogInsert</function> is used to place a new record into
408 the <acronym>WAL</acronym> buffers in shared memory. If there is no
409 space for the new record, <function>LogInsert</function> will have
410 to write (move to kernel cache) a few filled <acronym>WAL</acronym>
411 buffers. This is undesirable because <function>LogInsert</function>
412 is used on every database low level modification (for example, row
413 insertion) at a time when an exclusive lock is held on affected
414 data pages, so the operation needs to be as fast as possible. What
415 is worse, writing <acronym>WAL</acronym> buffers might also force the
416 creation of a new log segment, which takes even more
417 time. Normally, <acronym>WAL</acronym> buffers should be written
418 and flushed by a <function>LogFlush</function> request, which is
419 made, for the most part, at transaction commit time to ensure that
420 transaction records are flushed to permanent storage. On systems
421 with high log output, <function>LogFlush</function> requests might
422 not occur often enough to prevent <function>LogInsert</function>
423 from having to do writes. On such systems
424 one should increase the number of <acronym>WAL</acronym> buffers by
425 modifying the configuration parameter <xref
426 linkend="guc-wal-buffers">. The default number of <acronym>WAL</acronym>
427 buffers is 8. Increasing this value will
428 correspondingly increase shared memory usage. When
429 <xref linkend="guc-full-page-writes"> is set and the system is very busy,
430 setting this value higher will help smooth response times during the
431 period immediately following each checkpoint.
435 The <xref linkend="guc-commit-delay"> parameter defines for how many
436 microseconds the server process will sleep after writing a commit
437 record to the log with <function>LogInsert</function> but before
438 performing a <function>LogFlush</function>. This delay allows other
439 server processes to add their commit records to the log so as to have all
440 of them flushed with a single log sync. No sleep will occur if
441 <xref linkend="guc-fsync">
442 is not enabled, nor if fewer than <xref linkend="guc-commit-siblings">
443 other sessions are currently in active transactions; this avoids
444 sleeping when it's unlikely that any other session will commit soon.
445 Note that on most platforms, the resolution of a sleep request is
446 ten milliseconds, so that any nonzero <varname>commit_delay</varname>
447 setting between 1 and 10000 microseconds would have the same effect.
448 Good values for these parameters are not yet clear; experimentation
453 The <xref linkend="guc-wal-sync-method"> parameter determines how
454 <productname>PostgreSQL</productname> will ask the kernel to force
455 <acronym>WAL</acronym> updates out to disk.
456 All the options should be the same as far as reliability goes,
457 but it's quite platform-specific which one will be the fastest.
458 Note that this parameter is irrelevant if <varname>fsync</varname>
463 Enabling the <xref linkend="guc-wal-debug"> configuration parameter
464 (provided that <productname>PostgreSQL</productname> has been
465 compiled with support for it) will result in each
466 <function>LogInsert</function> and <function>LogFlush</function>
467 <acronym>WAL</acronym> call being logged to the server log. This
468 option might be replaced by a more general mechanism in the future.
472 <sect1 id="wal-internals">
473 <title>WAL Internals</title>
476 <acronym>WAL</acronym> is automatically enabled; no action is
477 required from the administrator except ensuring that the
478 disk-space requirements for the <acronym>WAL</acronym> logs are met,
479 and that any necessary tuning is done (see <xref
480 linkend="wal-configuration">).
484 <acronym>WAL</acronym> logs are stored in the directory
485 <filename>pg_xlog</filename> under the data directory, as a set of
486 segment files, normally each 16 MB in size. Each segment is divided into
487 pages, normally 8 kB each. The log record headers are described in
488 <filename>access/xlog.h</filename>; the record content is dependent
489 on the type of event that is being logged. Segment files are given
490 ever-increasing numbers as names, starting at
491 <filename>000000010000000000000000</filename>. The numbers do not wrap, at
492 present, but it should take a very very long time to exhaust the
493 available stock of numbers.
497 It is of advantage if the log is located on another disk than the
498 main database files. This can be achieved by moving the directory
499 <filename>pg_xlog</filename> to another location (while the server
500 is shut down, of course) and creating a symbolic link from the
501 original location in the main data directory to the new location.
505 The aim of <acronym>WAL</acronym>, to ensure that the log is
506 written before database records are altered, can be subverted by
507 disk drives<indexterm><primary>disk drive</></> that falsely report a
508 successful write to the kernel,
509 when in fact they have only cached the data and not yet stored it
510 on the disk. A power failure in such a situation might still lead to
511 irrecoverable data corruption. Administrators should try to ensure
512 that disks holding <productname>PostgreSQL</productname>'s
513 <acronym>WAL</acronym> log files do not make such false reports.
517 After a checkpoint has been made and the log flushed, the
518 checkpoint's position is saved in the file
519 <filename>pg_control</filename>. Therefore, when recovery is to be
520 done, the server first reads <filename>pg_control</filename> and
521 then the checkpoint record; then it performs the REDO operation by
522 scanning forward from the log position indicated in the checkpoint
523 record. Because the entire content of data pages is saved in the
524 log on the first page modification after a checkpoint, all pages
525 changed since the checkpoint will be restored to a consistent
530 To deal with the case where <filename>pg_control</filename> is
531 corrupted, we should support the possibility of scanning existing log
532 segments in reverse order — newest to oldest — in order to find the
533 latest checkpoint. This has not been implemented yet.
534 <filename>pg_control</filename> is small enough (less than one disk page)
535 that it is not subject to partial-write problems, and as of this writing
536 there have been no reports of database failures due solely to inability
537 to read <filename>pg_control</filename> itself. So while it is
538 theoretically a weak spot, <filename>pg_control</filename> does not
539 seem to be a problem in practice.