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