1 <!-- $PostgreSQL: pgsql/doc/src/sgml/wal.sgml,v 1.49 2007/12/10 22:08:36 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</> write caching is
69 disabled by unchecking <literal>My Computer\Open\{select disk
70 drive}\Properties\Hardware\Properties\Policies\Enable write caching on
75 When the operating system sends a write request to the disk hardware,
76 there is little it can do to make sure the data has arrived at a truly
77 non-volatile storage area. Rather, it is the
78 administrator's responsibility to be sure that all storage components
79 ensure data integrity. Avoid disk controllers that have non-battery-backed
80 write caches. At the drive level, disable write-back caching if the
81 drive cannot guarantee the data will be written before shutdown.
85 Another risk of data loss is posed by the disk platter write
86 operations themselves. Disk platters are divided into sectors,
87 commonly 512 bytes each. Every physical read or write operation
88 processes a whole sector.
89 When a write request arrives at the drive, it might be for 512 bytes,
90 1024 bytes, or 8192 bytes, and the process of writing could fail due
91 to power loss at any time, meaning some of the 512-byte sectors were
92 written, and others were not. To guard against such failures,
93 <productname>PostgreSQL</> periodically writes full page images to
94 permanent storage <emphasis>before</> modifying the actual page on
95 disk. By doing this, during crash recovery <productname>PostgreSQL</> can
96 restore partially-written pages. If you have a battery-backed disk
97 controller or file-system software that prevents partial page writes
98 (e.g., ReiserFS 4), you can turn off this page imaging by using the
99 <xref linkend="guc-full-page-writes"> parameter.
103 <sect1 id="wal-intro">
104 <title>Write-Ahead Logging (<acronym>WAL</acronym>)</title>
106 <indexterm zone="wal">
107 <primary>WAL</primary>
111 <primary>transaction log</primary>
116 <firstterm>Write-Ahead Logging</firstterm> (<acronym>WAL</acronym>)
117 is a standard method for ensuring data integrity. A detailed
118 description can be found in most (if not all) books about
119 transaction processing. Briefly, <acronym>WAL</acronym>'s central
120 concept is that changes to data files (where tables and indexes
121 reside) must be written only after those changes have been logged,
122 that is, after log records describing the changes have been flushed
123 to permanent storage. If we follow this procedure, we do not need
124 to flush data pages to disk on every transaction commit, because we
125 know that in the event of a crash we will be able to recover the
126 database using the log: any changes that have not been applied to
127 the data pages can be redone from the log records. (This is
128 roll-forward recovery, also known as REDO.)
132 Using <acronym>WAL</acronym> results in a
133 significantly reduced number of disk writes, because only the log
134 file needs to be flushed to disk to guarantee that a transaction is
135 committed, rather than every data file changed by the transaction.
136 The log file is written sequentially,
137 and so the cost of syncing the log is much less than the cost of
138 flushing the data pages. This is especially true for servers
139 handling many small transactions touching different parts of the data
140 store. Furthermore, when the server is processing many small concurrent
141 transactions, one <function>fsync</function> of the log file may
142 suffice to commit many transactions.
146 <acronym>WAL</acronym> also makes it possible to support on-line
147 backup and point-in-time recovery, as described in <xref
148 linkend="continuous-archiving">. By archiving the WAL data we can support
149 reverting to any time instant covered by the available WAL data:
150 we simply install a prior physical backup of the database, and
151 replay the WAL log just as far as the desired time. What's more,
152 the physical backup doesn't have to be an instantaneous snapshot
153 of the database state — if it is made over some period of time,
154 then replaying the WAL log for that period will fix any internal
159 <sect1 id="wal-async-commit">
160 <title>Asynchronous Commit</title>
163 <primary>synchronous commit</primary>
167 <primary>asynchronous commit</primary>
171 <firstterm>Asynchronous commit</> is an option that allows transactions
172 to complete more quickly, at the cost that the most recent transactions may
173 be lost if the database should crash. In many applications this is an
174 acceptable trade-off.
178 As described in the previous section, transaction commit is normally
179 <firstterm>synchronous</>: the server waits for the transaction's
180 <acronym>WAL</acronym> records to be flushed to permanent storage
181 before returning a success indication to the client. The client is
182 therefore guaranteed that a transaction reported to be committed will
183 be preserved, even in the event of a server crash immediately after.
184 However, for short transactions this delay is a major component of the
185 total transaction time. Selecting asynchronous commit mode means that
186 the server returns success as soon as the transaction is logically
187 completed, before the <acronym>WAL</acronym> records it generated have
188 actually made their way to disk. This can provide a significant boost
189 in throughput for small transactions.
193 Asynchronous commit introduces the risk of data loss. There is a short
194 time window between the report of transaction completion to the client
195 and the time that the transaction is truly committed (that is, it is
196 guaranteed not to be lost if the server crashes). Thus asynchronous
197 commit should not be used if the client will take external actions
198 relying on the assumption that the transaction will be remembered.
199 As an example, a bank would certainly not use asynchronous commit for
200 a transaction recording an ATM's dispensing of cash. But in many
201 scenarios, such as event logging, there is no need for a strong
202 guarantee of this kind.
206 The risk that is taken by using asynchronous commit is of data loss,
207 not data corruption. If the database should crash, it will recover
208 by replaying <acronym>WAL</acronym> up to the last record that was
209 flushed. The database will therefore be restored to a self-consistent
210 state, but any transactions that were not yet flushed to disk will
211 not be reflected in that state. The net effect is therefore loss of
212 the last few transactions. Because the transactions are replayed in
213 commit order, no inconsistency can be introduced — for example,
214 if transaction B made changes relying on the effects of a previous
215 transaction A, it is not possible for A's effects to be lost while B's
216 effects are preserved.
220 The user can select the commit mode of each transaction, so that
221 it is possible to have both synchronous and asynchronous commit
222 transactions running concurrently. This allows flexible trade-offs
223 between performance and certainty of transaction durability.
224 The commit mode is controlled by the user-settable parameter
225 <xref linkend="guc-synchronous-commit">, which can be changed in any of
226 the ways that a configuration parameter can be set. The mode used for
227 any one transaction depends on the value of
228 <varname>synchronous_commit</varname> when transaction commit begins.
232 Certain utility commands, for instance <command>DROP TABLE</>, are
233 forced to commit synchronously regardless of the setting of
234 <varname>synchronous_commit</varname>. This is to ensure consistency
235 between the server's file system and the logical state of the database.
236 The commands supporting two-phase commit, such as <command>PREPARE
237 TRANSACTION</>, are also always synchronous.
241 If the database crashes during the risk window between an
242 asynchronous commit and the writing of the transaction's
243 <acronym>WAL</acronym> records,
244 then changes made during that transaction <emphasis>will</> be lost.
246 risk window is limited because a background process (the <quote>WAL
247 writer</>) flushes unwritten <acronym>WAL</acronym> records to disk
248 every <xref linkend="guc-wal-writer-delay"> milliseconds.
249 The actual maximum duration of the risk window is three times
250 <varname>wal_writer_delay</varname> because the WAL writer is
251 designed to favor writing whole pages at a time during busy periods.
256 An immediate-mode shutdown is equivalent to a server crash, and will
257 therefore cause loss of any unflushed asynchronous commits.
262 Asynchronous commit provides behavior different from setting
263 <xref linkend="guc-fsync"> = off.
264 <varname>fsync</varname> is a server-wide
265 setting that will alter the behavior of all transactions. It disables
266 all logic within <productname>PostgreSQL</> that attempts to synchronize
267 writes to different portions of the database, and therefore a system
268 crash (that is, a hardware or operating system crash, not a failure of
269 <productname>PostgreSQL</> itself) could result in arbitrarily bad
270 corruption of the database state. In many scenarios, asynchronous
271 commit provides most of the performance improvement that could be
272 obtained by turning off <varname>fsync</varname>, but without the risk
277 <xref linkend="guc-commit-delay"> also sounds very similar to
278 asynchronous commit, but it is actually a synchronous commit method
279 (in fact, <varname>commit_delay</varname> is ignored during an
280 asynchronous commit). <varname>commit_delay</varname> causes a delay
281 just before a synchronous commit attempts to flush
282 <acronym>WAL</acronym> to disk, in the hope that a single flush
283 executed by one such transaction can also serve other transactions
284 committing at about the same time. Setting <varname>commit_delay</varname>
285 can only help when there are many concurrently committing transactions,
286 and it is difficult to tune it to a value that actually helps rather
287 than hurting throughput.
292 <sect1 id="wal-configuration">
293 <title><acronym>WAL</acronym> Configuration</title>
296 There are several <acronym>WAL</>-related configuration parameters that
297 affect database performance. This section explains their use.
298 Consult <xref linkend="runtime-config"> for general information about
299 setting server configuration parameters.
303 <firstterm>Checkpoints</firstterm><indexterm><primary>checkpoint</></>
304 are points in the sequence of transactions at which it is guaranteed
305 that the data files have been updated with all information written before
306 the checkpoint. At checkpoint time, all dirty data pages are flushed to
307 disk and a special checkpoint record is written to the log file.
308 In the event of a crash, the crash recovery procedure looks at the latest
309 checkpoint record to determine the point in the log (known as the redo
310 record) from which it should start the REDO operation. Any changes made to
311 data files before that point are known to be already on disk. Hence, after
312 a checkpoint has been made, any log segments preceding the one containing
313 the redo record are no longer needed and can be recycled or removed. (When
314 <acronym>WAL</acronym> archiving is being done, the log segments must be
315 archived before being recycled or removed.)
319 The server's background writer process will automatically perform
320 a checkpoint every so often. A checkpoint is created every <xref
321 linkend="guc-checkpoint-segments"> log segments, or every <xref
322 linkend="guc-checkpoint-timeout"> seconds, whichever comes first.
323 The default settings are 3 segments and 300 seconds respectively.
324 It is also possible to force a checkpoint by using the SQL command
325 <command>CHECKPOINT</command>.
329 Reducing <varname>checkpoint_segments</varname> and/or
330 <varname>checkpoint_timeout</varname> causes checkpoints to be done
331 more often. This allows faster after-crash recovery (since less work
332 will need to be redone). However, one must balance this against the
333 increased cost of flushing dirty data pages more often. If
334 <xref linkend="guc-full-page-writes"> is set (as is the default), there is
335 another factor to consider. To ensure data page consistency,
336 the first modification of a data page after each checkpoint results in
337 logging the entire page content. In that case,
338 a smaller checkpoint interval increases the volume of output to the WAL log,
339 partially negating the goal of using a smaller interval,
340 and in any case causing more disk I/O.
344 Checkpoints are fairly expensive, first because they require writing
345 out all currently dirty buffers, and second because they result in
346 extra subsequent WAL traffic as discussed above. It is therefore
347 wise to set the checkpointing parameters high enough that checkpoints
348 don't happen too often. As a simple sanity check on your checkpointing
349 parameters, you can set the <xref linkend="guc-checkpoint-warning">
350 parameter. If checkpoints happen closer together than
351 <varname>checkpoint_warning</> seconds,
352 a message will be output to the server log recommending increasing
353 <varname>checkpoint_segments</varname>. Occasional appearance of such
354 a message is not cause for alarm, but if it appears often then the
355 checkpoint control parameters should be increased. Bulk operations such
356 as large <command>COPY</> transfers might cause a number of such warnings
357 to appear if you have not set <varname>checkpoint_segments</> high
362 To avoid flooding the I/O system with a burst of page writes,
363 writing dirty buffers during a checkpoint is spread over a period of time.
364 That period is controlled by
365 <xref linkend="guc-checkpoint-completion-target">, which is
366 given as a fraction of the checkpoint interval.
367 The I/O rate is adjusted so that the checkpoint finishes when the
368 given fraction of <varname>checkpoint_segments</varname> WAL segments
369 have been consumed since checkpoint start, or the given fraction of
370 <varname>checkpoint_timeout</varname> seconds have elapsed,
371 whichever is sooner. With the default value of 0.5,
372 <productname>PostgreSQL</> can be expected to complete each checkpoint
373 in about half the time before the next checkpoint starts. On a system
374 that's very close to maximum I/O throughput during normal operation,
375 you might want to increase <varname>checkpoint_completion_target</varname>
376 to reduce the I/O load from checkpoints. The disadvantage of this is that
377 prolonging checkpoints affects recovery time, because more WAL segments
378 will need to be kept around for possible use in recovery. Although
379 <varname>checkpoint_completion_target</varname> can be set as high as 1.0,
380 it is best to keep it less than that (perhaps 0.9 at most) since
381 checkpoints include some other activities besides writing dirty buffers.
382 A setting of 1.0 is quite likely to result in checkpoints not being
383 completed on time, which would result in performance loss due to
384 unexpected variation in the number of WAL segments needed.
388 There will always be at least one WAL segment file, and will normally
389 not be more than (2 + <varname>checkpoint_completion_target</varname>) * <varname>checkpoint_segments</varname> + 1
390 files. Each segment file is normally 16 MB (though this size can be
391 altered when building the server). You can use this to estimate space
392 requirements for <acronym>WAL</acronym>.
393 Ordinarily, when old log segment files are no longer needed, they
394 are recycled (renamed to become the next segments in the numbered
395 sequence). If, due to a short-term peak of log output rate, there
396 are more than 3 * <varname>checkpoint_segments</varname> + 1
397 segment files, the unneeded segment files will be deleted instead
398 of recycled until the system gets back under this limit.
402 There are two commonly used internal <acronym>WAL</acronym> functions:
403 <function>LogInsert</function> and <function>LogFlush</function>.
404 <function>LogInsert</function> is used to place a new record into
405 the <acronym>WAL</acronym> buffers in shared memory. If there is no
406 space for the new record, <function>LogInsert</function> will have
407 to write (move to kernel cache) a few filled <acronym>WAL</acronym>
408 buffers. This is undesirable because <function>LogInsert</function>
409 is used on every database low level modification (for example, row
410 insertion) at a time when an exclusive lock is held on affected
411 data pages, so the operation needs to be as fast as possible. What
412 is worse, writing <acronym>WAL</acronym> buffers might also force the
413 creation of a new log segment, which takes even more
414 time. Normally, <acronym>WAL</acronym> buffers should be written
415 and flushed by a <function>LogFlush</function> request, which is
416 made, for the most part, at transaction commit time to ensure that
417 transaction records are flushed to permanent storage. On systems
418 with high log output, <function>LogFlush</function> requests might
419 not occur often enough to prevent <function>LogInsert</function>
420 from having to do writes. On such systems
421 one should increase the number of <acronym>WAL</acronym> buffers by
422 modifying the configuration parameter <xref
423 linkend="guc-wal-buffers">. The default number of <acronym>WAL</acronym>
424 buffers is 8. Increasing this value will
425 correspondingly increase shared memory usage. When
426 <xref linkend="guc-full-page-writes"> is set and the system is very busy,
427 setting this value higher will help smooth response times during the
428 period immediately following each checkpoint.
432 The <xref linkend="guc-commit-delay"> parameter defines for how many
433 microseconds the server process will sleep after writing a commit
434 record to the log with <function>LogInsert</function> but before
435 performing a <function>LogFlush</function>. This delay allows other
436 server processes to add their commit records to the log so as to have all
437 of them flushed with a single log sync. No sleep will occur if
438 <xref linkend="guc-fsync">
439 is not enabled, nor if fewer than <xref linkend="guc-commit-siblings">
440 other sessions are currently in active transactions; this avoids
441 sleeping when it's unlikely that any other session will commit soon.
442 Note that on most platforms, the resolution of a sleep request is
443 ten milliseconds, so that any nonzero <varname>commit_delay</varname>
444 setting between 1 and 10000 microseconds would have the same effect.
445 Good values for these parameters are not yet clear; experimentation
450 The <xref linkend="guc-wal-sync-method"> parameter determines how
451 <productname>PostgreSQL</productname> will ask the kernel to force
452 <acronym>WAL</acronym> updates out to disk.
453 All the options should be the same as far as reliability goes,
454 but it's quite platform-specific which one will be the fastest.
455 Note that this parameter is irrelevant if <varname>fsync</varname>
460 Enabling the <xref linkend="guc-wal-debug"> configuration parameter
461 (provided that <productname>PostgreSQL</productname> has been
462 compiled with support for it) will result in each
463 <function>LogInsert</function> and <function>LogFlush</function>
464 <acronym>WAL</acronym> call being logged to the server log. This
465 option might be replaced by a more general mechanism in the future.
469 <sect1 id="wal-internals">
470 <title>WAL Internals</title>
473 <acronym>WAL</acronym> is automatically enabled; no action is
474 required from the administrator except ensuring that the
475 disk-space requirements for the <acronym>WAL</acronym> logs are met,
476 and that any necessary tuning is done (see <xref
477 linkend="wal-configuration">).
481 <acronym>WAL</acronym> logs are stored in the directory
482 <filename>pg_xlog</filename> under the data directory, as a set of
483 segment files, normally each 16 MB in size. Each segment is divided into
484 pages, normally 8 kB each. The log record headers are described in
485 <filename>access/xlog.h</filename>; the record content is dependent
486 on the type of event that is being logged. Segment files are given
487 ever-increasing numbers as names, starting at
488 <filename>000000010000000000000000</filename>. The numbers do not wrap, at
489 present, but it should take a very very long time to exhaust the
490 available stock of numbers.
494 It is of advantage if the log is located on another disk than the
495 main database files. This can be achieved by moving the directory
496 <filename>pg_xlog</filename> to another location (while the server
497 is shut down, of course) and creating a symbolic link from the
498 original location in the main data directory to the new location.
502 The aim of <acronym>WAL</acronym>, to ensure that the log is
503 written before database records are altered, can be subverted by
504 disk drives<indexterm><primary>disk drive</></> that falsely report a
505 successful write to the kernel,
506 when in fact they have only cached the data and not yet stored it
507 on the disk. A power failure in such a situation might still lead to
508 irrecoverable data corruption. Administrators should try to ensure
509 that disks holding <productname>PostgreSQL</productname>'s
510 <acronym>WAL</acronym> log files do not make such false reports.
514 After a checkpoint has been made and the log flushed, the
515 checkpoint's position is saved in the file
516 <filename>pg_control</filename>. Therefore, when recovery is to be
517 done, the server first reads <filename>pg_control</filename> and
518 then the checkpoint record; then it performs the REDO operation by
519 scanning forward from the log position indicated in the checkpoint
520 record. Because the entire content of data pages is saved in the
521 log on the first page modification after a checkpoint, all pages
522 changed since the checkpoint will be restored to a consistent
527 To deal with the case where <filename>pg_control</filename> is
528 corrupted, we should support the possibility of scanning existing log
529 segments in reverse order — newest to oldest — in order to find the
530 latest checkpoint. This has not been implemented yet.
531 <filename>pg_control</filename> is small enough (less than one disk page)
532 that it is not subject to partial-write problems, and as of this writing
533 there have been no reports of database failures due solely to inability
534 to read <filename>pg_control</filename> itself. So while it is
535 theoretically a weak spot, <filename>pg_control</filename> does not
536 seem to be a problem in practice.