1 <!-- $PostgreSQL: pgsql/doc/src/sgml/wal.sgml,v 1.63 2010/02/27 01:39:46 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 sent
46 to the drive as soon as they arrive. Others are
47 <firstterm>write-back</>, meaning data is sent 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, though <acronym>ATAPI-6</> introduced a drive cache
63 flush command that some file systems use, e.g. <acronym>ZFS</>.
64 Many solid-state drives also have volatile write-back
65 caches, and many do not honor cache flush commands by default.
66 To check write caching on <productname>Linux</> use
67 <command>hdparm -I</>; it is enabled if there is a <literal>*</> next
68 to <literal>Write cache</>; <command>hdparm -W</> to turn off
69 write caching. On <productname>FreeBSD</> use
70 <application>atacontrol</>. (For SCSI disks use <ulink
71 url="http://sg.torque.net/sg/sdparm.html"><application>sdparm</></ulink>
72 to turn off <literal>WCE</>.) On <productname>Solaris</> the disk
73 write cache is controlled by <ulink
74 url="http://www.sun.com/bigadmin/content/submitted/format_utility.jsp"><literal>format
75 -e</></ulink>. (The Solaris <acronym>ZFS</> file system is safe with
76 disk write-cache enabled because it issues its own disk cache flush
77 commands.) On <productname>Windows</> if <varname>wal_sync_method</>
78 is <literal>open_datasync</> (the default), write caching is disabled
79 by unchecking <literal>My Computer\Open\{select disk
80 drive}\Properties\Hardware\Properties\Policies\Enable write caching on
81 the disk</>. Also on Windows, <literal>fsync</> and
82 <literal>fsync_writethrough</> never do write caching.
86 When the operating system sends a write request to the storage hardware,
87 there is little it can do to make sure the data has arrived at a truly
88 non-volatile storage area. Rather, it is the
89 administrator's responsibility to make certain that all storage components
90 ensure data integrity. Avoid disk controllers that have non-battery-backed
91 write caches. At the drive level, disable write-back caching if the
92 drive cannot guarantee the data will be written before shutdown.
93 You can test for reliable I/O subsystem behavior using <ulink
94 url="http://brad.livejournal.com/2116715.html">diskchecker.pl</ulink>.
98 Another risk of data loss is posed by the disk platter write
99 operations themselves. Disk platters are divided into sectors,
100 commonly 512 bytes each. Every physical read or write operation
101 processes a whole sector.
102 When a write request arrives at the drive, it might be for 512 bytes,
103 1024 bytes, or 8192 bytes, and the process of writing could fail due
104 to power loss at any time, meaning some of the 512-byte sectors were
105 written, and others were not. To guard against such failures,
106 <productname>PostgreSQL</> periodically writes full page images to
107 permanent WAL storage <emphasis>before</> modifying the actual page on
108 disk. By doing this, during crash recovery <productname>PostgreSQL</> can
109 restore partially-written pages. If you have a battery-backed disk
110 controller or file-system software that prevents partial page writes
111 (e.g., ZFS), you can turn off this page imaging by turning off the
112 <xref linkend="guc-full-page-writes"> parameter.
116 <sect1 id="wal-intro">
117 <title>Write-Ahead Logging (<acronym>WAL</acronym>)</title>
119 <indexterm zone="wal">
120 <primary>WAL</primary>
124 <primary>transaction log</primary>
129 <firstterm>Write-Ahead Logging</firstterm> (<acronym>WAL</acronym>)
130 is a standard method for ensuring data integrity. A detailed
131 description can be found in most (if not all) books about
132 transaction processing. Briefly, <acronym>WAL</acronym>'s central
133 concept is that changes to data files (where tables and indexes
134 reside) must be written only after those changes have been logged,
135 that is, after log records describing the changes have been flushed
136 to permanent storage. If we follow this procedure, we do not need
137 to flush data pages to disk on every transaction commit, because we
138 know that in the event of a crash we will be able to recover the
139 database using the log: any changes that have not been applied to
140 the data pages can be redone from the log records. (This is
141 roll-forward recovery, also known as REDO.)
146 Because <acronym>WAL</acronym> restores database file
147 contents after a crash, journaled file systems are not necessary for
148 reliable storage of the data files or WAL files. In fact, journaling
149 overhead can reduce performance, especially if journaling
150 causes file system <emphasis>data</emphasis> to be flushed
151 to disk. Fortunately, data flushing during journaling can
152 often be disabled with a file system mount option, e.g.
153 <literal>data=writeback</> on a Linux ext3 file system.
154 Journaled file systems do improve boot speed after a crash.
160 Using <acronym>WAL</acronym> results in a
161 significantly reduced number of disk writes, because only the log
162 file needs to be flushed to disk to guarantee that a transaction is
163 committed, rather than every data file changed by the transaction.
164 The log file is written sequentially,
165 and so the cost of syncing the log is much less than the cost of
166 flushing the data pages. This is especially true for servers
167 handling many small transactions touching different parts of the data
168 store. Furthermore, when the server is processing many small concurrent
169 transactions, one <function>fsync</function> of the log file may
170 suffice to commit many transactions.
174 <acronym>WAL</acronym> also makes it possible to support on-line
175 backup and point-in-time recovery, as described in <xref
176 linkend="continuous-archiving">. By archiving the WAL data we can support
177 reverting to any time instant covered by the available WAL data:
178 we simply install a prior physical backup of the database, and
179 replay the WAL log just as far as the desired time. What's more,
180 the physical backup doesn't have to be an instantaneous snapshot
181 of the database state — if it is made over some period of time,
182 then replaying the WAL log for that period will fix any internal
187 <sect1 id="wal-async-commit">
188 <title>Asynchronous Commit</title>
191 <primary>synchronous commit</primary>
195 <primary>asynchronous commit</primary>
199 <firstterm>Asynchronous commit</> is an option that allows transactions
200 to complete more quickly, at the cost that the most recent transactions may
201 be lost if the database should crash. In many applications this is an
202 acceptable trade-off.
206 As described in the previous section, transaction commit is normally
207 <firstterm>synchronous</>: the server waits for the transaction's
208 <acronym>WAL</acronym> records to be flushed to permanent storage
209 before returning a success indication to the client. The client is
210 therefore guaranteed that a transaction reported to be committed will
211 be preserved, even in the event of a server crash immediately after.
212 However, for short transactions this delay is a major component of the
213 total transaction time. Selecting asynchronous commit mode means that
214 the server returns success as soon as the transaction is logically
215 completed, before the <acronym>WAL</acronym> records it generated have
216 actually made their way to disk. This can provide a significant boost
217 in throughput for small transactions.
221 Asynchronous commit introduces the risk of data loss. There is a short
222 time window between the report of transaction completion to the client
223 and the time that the transaction is truly committed (that is, it is
224 guaranteed not to be lost if the server crashes). Thus asynchronous
225 commit should not be used if the client will take external actions
226 relying on the assumption that the transaction will be remembered.
227 As an example, a bank would certainly not use asynchronous commit for
228 a transaction recording an ATM's dispensing of cash. But in many
229 scenarios, such as event logging, there is no need for a strong
230 guarantee of this kind.
234 The risk that is taken by using asynchronous commit is of data loss,
235 not data corruption. If the database should crash, it will recover
236 by replaying <acronym>WAL</acronym> up to the last record that was
237 flushed. The database will therefore be restored to a self-consistent
238 state, but any transactions that were not yet flushed to disk will
239 not be reflected in that state. The net effect is therefore loss of
240 the last few transactions. Because the transactions are replayed in
241 commit order, no inconsistency can be introduced — for example,
242 if transaction B made changes relying on the effects of a previous
243 transaction A, it is not possible for A's effects to be lost while B's
244 effects are preserved.
248 The user can select the commit mode of each transaction, so that
249 it is possible to have both synchronous and asynchronous commit
250 transactions running concurrently. This allows flexible trade-offs
251 between performance and certainty of transaction durability.
252 The commit mode is controlled by the user-settable parameter
253 <xref linkend="guc-synchronous-commit">, which can be changed in any of
254 the ways that a configuration parameter can be set. The mode used for
255 any one transaction depends on the value of
256 <varname>synchronous_commit</varname> when transaction commit begins.
260 Certain utility commands, for instance <command>DROP TABLE</>, are
261 forced to commit synchronously regardless of the setting of
262 <varname>synchronous_commit</varname>. This is to ensure consistency
263 between the server's file system and the logical state of the database.
264 The commands supporting two-phase commit, such as <command>PREPARE
265 TRANSACTION</>, are also always synchronous.
269 If the database crashes during the risk window between an
270 asynchronous commit and the writing of the transaction's
271 <acronym>WAL</acronym> records,
272 then changes made during that transaction <emphasis>will</> be lost.
274 risk window is limited because a background process (the <quote>WAL
275 writer</>) flushes unwritten <acronym>WAL</acronym> records to disk
276 every <xref linkend="guc-wal-writer-delay"> milliseconds.
277 The actual maximum duration of the risk window is three times
278 <varname>wal_writer_delay</varname> because the WAL writer is
279 designed to favor writing whole pages at a time during busy periods.
284 An immediate-mode shutdown is equivalent to a server crash, and will
285 therefore cause loss of any unflushed asynchronous commits.
290 Asynchronous commit provides behavior different from setting
291 <xref linkend="guc-fsync"> = off.
292 <varname>fsync</varname> is a server-wide
293 setting that will alter the behavior of all transactions. It disables
294 all logic within <productname>PostgreSQL</> that attempts to synchronize
295 writes to different portions of the database, and therefore a system
296 crash (that is, a hardware or operating system crash, not a failure of
297 <productname>PostgreSQL</> itself) could result in arbitrarily bad
298 corruption of the database state. In many scenarios, asynchronous
299 commit provides most of the performance improvement that could be
300 obtained by turning off <varname>fsync</varname>, but without the risk
305 <xref linkend="guc-commit-delay"> also sounds very similar to
306 asynchronous commit, but it is actually a synchronous commit method
307 (in fact, <varname>commit_delay</varname> is ignored during an
308 asynchronous commit). <varname>commit_delay</varname> causes a delay
309 just before a synchronous commit attempts to flush
310 <acronym>WAL</acronym> to disk, in the hope that a single flush
311 executed by one such transaction can also serve other transactions
312 committing at about the same time. Setting <varname>commit_delay</varname>
313 can only help when there are many concurrently committing transactions,
314 and it is difficult to tune it to a value that actually helps rather
315 than hurt throughput.
320 <sect1 id="wal-configuration">
321 <title><acronym>WAL</acronym> Configuration</title>
324 There are several <acronym>WAL</>-related configuration parameters that
325 affect database performance. This section explains their use.
326 Consult <xref linkend="runtime-config"> for general information about
327 setting server configuration parameters.
331 <firstterm>Checkpoints</firstterm><indexterm><primary>checkpoint</></>
332 are points in the sequence of transactions at which it is guaranteed
333 that the heap and index data files have been updated with all information written before
334 the checkpoint. At checkpoint time, all dirty data pages are flushed to
335 disk and a special checkpoint record is written to the log file.
336 (The changes were previously flushed to the <acronym>WAL</acronym> files.)
337 In the event of a crash, the crash recovery procedure looks at the latest
338 checkpoint record to determine the point in the log (known as the redo
339 record) from which it should start the REDO operation. Any changes made to
340 data files before that point are guaranteed to be already on disk. Hence, after
341 a checkpoint, log segments preceding the one containing
342 the redo record are no longer needed and can be recycled or removed. (When
343 <acronym>WAL</acronym> archiving is being done, the log segments must be
344 archived before being recycled or removed.)
348 The checkpoint requirement of flushing all dirty data pages to disk
349 can cause a significant I/O load. For this reason, checkpoint
350 activity is throttled so I/O begins at checkpoint start and completes
351 before the next checkpoint starts; this minimizes performance
352 degradation during checkpoints.
356 The server's background writer process automatically performs
357 a checkpoint every so often. A checkpoint is created every <xref
358 linkend="guc-checkpoint-segments"> log segments, or every <xref
359 linkend="guc-checkpoint-timeout"> seconds, whichever comes first.
360 The default settings are 3 segments and 300 seconds (5 minutes), respectively.
361 It is also possible to force a checkpoint by using the SQL command
362 <command>CHECKPOINT</command>.
366 Reducing <varname>checkpoint_segments</varname> and/or
367 <varname>checkpoint_timeout</varname> causes checkpoints to occur
368 more often. This allows faster after-crash recovery (since less work
369 will need to be redone). However, one must balance this against the
370 increased cost of flushing dirty data pages more often. If
371 <xref linkend="guc-full-page-writes"> is set (as is the default), there is
372 another factor to consider. To ensure data page consistency,
373 the first modification of a data page after each checkpoint results in
374 logging the entire page content. In that case,
375 a smaller checkpoint interval increases the volume of output to the WAL log,
376 partially negating the goal of using a smaller interval,
377 and in any case causing more disk I/O.
381 Checkpoints are fairly expensive, first because they require writing
382 out all currently dirty buffers, and second because they result in
383 extra subsequent WAL traffic as discussed above. It is therefore
384 wise to set the checkpointing parameters high enough that checkpoints
385 don't happen too often. As a simple sanity check on your checkpointing
386 parameters, you can set the <xref linkend="guc-checkpoint-warning">
387 parameter. If checkpoints happen closer together than
388 <varname>checkpoint_warning</> seconds,
389 a message will be output to the server log recommending increasing
390 <varname>checkpoint_segments</varname>. Occasional appearance of such
391 a message is not cause for alarm, but if it appears often then the
392 checkpoint control parameters should be increased. Bulk operations such
393 as large <command>COPY</> transfers might cause a number of such warnings
394 to appear if you have not set <varname>checkpoint_segments</> high
399 To avoid flooding the I/O system with a burst of page writes,
400 writing dirty buffers during a checkpoint is spread over a period of time.
401 That period is controlled by
402 <xref linkend="guc-checkpoint-completion-target">, which is
403 given as a fraction of the checkpoint interval.
404 The I/O rate is adjusted so that the checkpoint finishes when the
405 given fraction of <varname>checkpoint_segments</varname> WAL segments
406 have been consumed since checkpoint start, or the given fraction of
407 <varname>checkpoint_timeout</varname> seconds have elapsed,
408 whichever is sooner. With the default value of 0.5,
409 <productname>PostgreSQL</> can be expected to complete each checkpoint
410 in about half the time before the next checkpoint starts. On a system
411 that's very close to maximum I/O throughput during normal operation,
412 you might want to increase <varname>checkpoint_completion_target</varname>
413 to reduce the I/O load from checkpoints. The disadvantage of this is that
414 prolonging checkpoints affects recovery time, because more WAL segments
415 will need to be kept around for possible use in recovery. Although
416 <varname>checkpoint_completion_target</varname> can be set as high as 1.0,
417 it is best to keep it less than that (perhaps 0.9 at most) since
418 checkpoints include some other activities besides writing dirty buffers.
419 A setting of 1.0 is quite likely to result in checkpoints not being
420 completed on time, which would result in performance loss due to
421 unexpected variation in the number of WAL segments needed.
425 There will always be at least one WAL segment file, and will normally
426 not be more than (2 + <varname>checkpoint_completion_target</varname>) * <varname>checkpoint_segments</varname> + 1
427 files. Each segment file is normally 16 MB (though this size can be
428 altered when building the server). You can use this to estimate space
429 requirements for <acronym>WAL</acronym>.
430 Ordinarily, when old log segment files are no longer needed, they
431 are recycled (renamed to become the next segments in the numbered
432 sequence). If, due to a short-term peak of log output rate, there
433 are more than 3 * <varname>checkpoint_segments</varname> + 1
434 segment files, the unneeded segment files will be deleted instead
435 of recycled until the system gets back under this limit.
439 There are two commonly used internal <acronym>WAL</acronym> functions:
440 <function>LogInsert</function> and <function>LogFlush</function>.
441 <function>LogInsert</function> is used to place a new record into
442 the <acronym>WAL</acronym> buffers in shared memory. If there is no
443 space for the new record, <function>LogInsert</function> will have
444 to write (move to kernel cache) a few filled <acronym>WAL</acronym>
445 buffers. This is undesirable because <function>LogInsert</function>
446 is used on every database low level modification (for example, row
447 insertion) at a time when an exclusive lock is held on affected
448 data pages, so the operation needs to be as fast as possible. What
449 is worse, writing <acronym>WAL</acronym> buffers might also force the
450 creation of a new log segment, which takes even more
451 time. Normally, <acronym>WAL</acronym> buffers should be written
452 and flushed by a <function>LogFlush</function> request, which is
453 made, for the most part, at transaction commit time to ensure that
454 transaction records are flushed to permanent storage. On systems
455 with high log output, <function>LogFlush</function> requests might
456 not occur often enough to prevent <function>LogInsert</function>
457 from having to do writes. On such systems
458 one should increase the number of <acronym>WAL</acronym> buffers by
459 modifying the configuration parameter <xref
460 linkend="guc-wal-buffers">. The default number of <acronym>WAL</acronym>
461 buffers is 8. Increasing this value will
462 correspondingly increase shared memory usage. When
463 <xref linkend="guc-full-page-writes"> is set and the system is very busy,
464 setting this value higher will help smooth response times during the
465 period immediately following each checkpoint.
469 The <xref linkend="guc-commit-delay"> parameter defines for how many
470 microseconds the server process will sleep after writing a commit
471 record to the log with <function>LogInsert</function> but before
472 performing a <function>LogFlush</function>. This delay allows other
473 server processes to add their commit records to the log so as to have all
474 of them flushed with a single log sync. No sleep will occur if
475 <xref linkend="guc-fsync">
476 is not enabled, or if fewer than <xref linkend="guc-commit-siblings">
477 other sessions are currently in active transactions; this avoids
478 sleeping when it's unlikely that any other session will commit soon.
479 Note that on most platforms, the resolution of a sleep request is
480 ten milliseconds, so that any nonzero <varname>commit_delay</varname>
481 setting between 1 and 10000 microseconds would have the same effect.
482 Good values for these parameters are not yet clear; experimentation
487 The <xref linkend="guc-wal-sync-method"> parameter determines how
488 <productname>PostgreSQL</productname> will ask the kernel to force
489 <acronym>WAL</acronym> updates out to disk.
490 All the options should be the same in terms of reliability,
491 but it's quite platform-specific which one will be the fastest.
492 Note that this parameter is irrelevant if <varname>fsync</varname>
497 Enabling the <xref linkend="guc-wal-debug"> configuration parameter
498 (provided that <productname>PostgreSQL</productname> has been
499 compiled with support for it) will result in each
500 <function>LogInsert</function> and <function>LogFlush</function>
501 <acronym>WAL</acronym> call being logged to the server log. This
502 option might be replaced by a more general mechanism in the future.
506 <sect1 id="wal-internals">
507 <title>WAL Internals</title>
510 <acronym>WAL</acronym> is automatically enabled; no action is
511 required from the administrator except ensuring that the
512 disk-space requirements for the <acronym>WAL</acronym> logs are met,
513 and that any necessary tuning is done (see <xref
514 linkend="wal-configuration">).
518 <acronym>WAL</acronym> logs are stored in the directory
519 <filename>pg_xlog</filename> under the data directory, as a set of
520 segment files, normally each 16 MB in size (but the size can be changed
521 by altering the <option>--with-wal-segsize</> configure option when
522 building the server). Each segment is divided into pages, normally
523 8 kB each (this size can be changed via the <option>--with-wal-blocksize</>
524 configure option). The log record headers are described in
525 <filename>access/xlog.h</filename>; the record content is dependent
526 on the type of event that is being logged. Segment files are given
527 ever-increasing numbers as names, starting at
528 <filename>000000010000000000000000</filename>. The numbers do not wrap,
529 but it will take a very, very long time to exhaust the
530 available stock of numbers.
534 It is advantageous if the log is located on a different disk from the
535 main database files. This can be achieved by moving the
536 <filename>pg_xlog</filename> directory to another location (while the server
537 is shut down, of course) and creating a symbolic link from the
538 original location in the main data directory to the new location.
542 The aim of <acronym>WAL</acronym> is to ensure that the log is
543 written before database records are altered, but this can be subverted by
544 disk drives<indexterm><primary>disk drive</></> that falsely report a
545 successful write to the kernel,
546 when in fact they have only cached the data and not yet stored it
547 on the disk. A power failure in such a situation might lead to
548 irrecoverable data corruption. Administrators should try to ensure
549 that disks holding <productname>PostgreSQL</productname>'s
550 <acronym>WAL</acronym> log files do not make such false reports.
554 After a checkpoint has been made and the log flushed, the
555 checkpoint's position is saved in the file
556 <filename>pg_control</filename>. Therefore, at the start of recovery,
557 the server first reads <filename>pg_control</filename> and
558 then the checkpoint record; then it performs the REDO operation by
559 scanning forward from the log position indicated in the checkpoint
560 record. Because the entire content of data pages is saved in the
561 log on the first page modification after a checkpoint (assuming
562 <xref linkend="guc-full-page-writes"> is not disabled), all pages
563 changed since the checkpoint will be restored to a consistent
568 To deal with the case where <filename>pg_control</filename> is
569 corrupt, we should support the possibility of scanning existing log
570 segments in reverse order — newest to oldest — in order to find the
571 latest checkpoint. This has not been implemented yet.
572 <filename>pg_control</filename> is small enough (less than one disk page)
573 that it is not subject to partial-write problems, and as of this writing
574 there have been no reports of database failures due solely to the inability
575 to read <filename>pg_control</filename> itself. So while it is
576 theoretically a weak spot, <filename>pg_control</filename> does not
577 seem to be a problem in practice.