Debian Codegraph @

doc/src/sgml/wal.sgml

raw
1 <!-- doc/src/sgml/wal.sgml -->
2
3 <chapter id="wal">
4 <title>Reliability and the Write-Ahead Log</title>
5
6 <para>
7 This chapter explains how to control the reliability of
8 <productname>PostgreSQL</productname>, including details about the
9 Write-Ahead Log.
10 </para>
11
12 <sect1 id="wal-reliability">
13 <title>Reliability</title>
14
15 <para>
16 Reliability is an important property of any serious database
17 system, and <productname>PostgreSQL</productname> does everything possible to
18 guarantee reliable operation. One aspect of reliable operation is
19 that all data recorded by a committed transaction should be stored
20 in a nonvolatile area that is safe from power loss, operating
21 system failure, and hardware failure (except failure of the
22 nonvolatile area itself, of course). Successfully writing the data
23 to the computer's permanent storage (disk drive or equivalent)
24 ordinarily meets this requirement. In fact, even if a computer is
25 fatally damaged, if the disk drives survive they can be moved to
26 another computer with similar hardware and all committed
27 transactions will remain intact.
28 </para>
29
30 <para>
31 While forcing data to the disk platters periodically might seem like
32 a simple operation, it is not. Because disk drives are dramatically
33 slower than main memory and CPUs, several layers of caching exist
34 between the computer's main memory and the disk platters.
35 First, there is the operating system's buffer cache, which caches
36 frequently requested disk blocks and combines disk writes. Fortunately,
37 all operating systems give applications a way to force writes from
38 the buffer cache to disk, and <productname>PostgreSQL</productname> uses those
39 features. (See the <xref linkend="guc-wal-sync-method"/> parameter
40 to adjust how this is done.)
41 </para>
42
43 <para>
44 Next, there might be a cache in the disk drive controller; this is
45 particularly common on <acronym>RAID</acronym> controller cards. Some of
46 these caches are <firstterm>write-through</firstterm>, meaning writes are sent
47 to the drive as soon as they arrive. Others are
48 <firstterm>write-back</firstterm>, meaning data is sent to the drive at
49 some later time. Such caches can be a reliability hazard because the
50 memory in the disk controller cache is volatile, and will lose its
51 contents in a power failure. Better controller cards have
52 <firstterm>battery-backup units</firstterm> (<acronym>BBU</acronym>s), meaning
53 the card has a battery that
54 maintains power to the cache in case of system power loss. After power
55 is restored the data will be written to the disk drives.
56 </para>
57
58 <para>
59 And finally, most disk drives have caches. Some are write-through
60 while some are write-back, and the same concerns about data loss
61 exist for write-back drive caches as for disk controller
62 caches. Consumer-grade IDE and SATA drives are particularly likely
63 to have write-back caches that will not survive a power failure. Many
64 solid-state drives (SSD) also have volatile write-back caches.
65 </para>
66
67 <para>
68 These caches can typically be disabled; however, the method for doing
69 this varies by operating system and drive type:
70 </para>
71
72 <itemizedlist>
73 <listitem>
74 <para>
75 On <productname>Linux</productname>, IDE and SATA drives can be queried using
76 <command>hdparm -I</command>; write caching is enabled if there is
77 a <literal>*</literal> next to <literal>Write cache</literal>. <command>hdparm -W 0</command>
78 can be used to turn off write caching. SCSI drives can be queried
79 using <ulink url="http://sg.danny.cz/sg/sdparm.html"><application>sdparm</application></ulink>.
80 Use <command>sdparm --get=WCE</command> to check
81 whether the write cache is enabled and <command>sdparm --clear=WCE</command>
82 to disable it.
83 </para>
84 </listitem>
85
86 <listitem>
87 <para>
88 On <productname>FreeBSD</productname>, IDE drives can be queried using
89 <command>camcontrol identify</command> and write caching turned off using
90 <literal>hw.ata.wc=0</literal> in <filename>/boot/loader.conf</filename>;
91 SCSI drives can be queried using <command>camcontrol identify</command>,
92 and the write cache both queried and changed using
93 <command>sdparm</command> when available.
94 </para>
95 </listitem>
96
97 <listitem>
98 <para>
99 On <productname>Solaris</productname>, the disk write cache is controlled by
100 <command>format -e</command>.
101 (The Solaris <acronym>ZFS</acronym> file system is safe with disk write-cache
102 enabled because it issues its own disk cache flush commands.)
103 </para>
104 </listitem>
105
106 <listitem>
107 <para>
108 On <productname>Windows</productname>, if <varname>wal_sync_method</varname> is
109 <literal>open_datasync</literal> (the default), write caching can be disabled
110 by unchecking <literal>My Computer\Open\<replaceable>disk drive</replaceable>\Properties\Hardware\Properties\Policies\Enable write caching on the disk</literal>.
111 Alternatively, set <varname>wal_sync_method</varname> to
112 <literal>fdatasync</literal> (NTFS only) or <literal>fsync</literal>,
113 which prevent write caching.
114 </para>
115 </listitem>
116
117 <listitem>
118 <para>
119 On <productname>macOS</productname>, write caching can be prevented by
120 setting <varname>wal_sync_method</varname> to <literal>fsync_writethrough</literal>.
121 </para>
122 </listitem>
123 </itemizedlist>
124
125 <para>
126 Recent SATA drives (those following <acronym>ATAPI-6</acronym> or later)
127 offer a drive cache flush command (<command>FLUSH CACHE EXT</command>),
128 while SCSI drives have long supported a similar command
129 <command>SYNCHRONIZE CACHE</command>. These commands are not directly
130 accessible to <productname>PostgreSQL</productname>, but some file systems
131 (e.g., <acronym>ZFS</acronym>, <acronym>ext4</acronym>) can use them to flush
132 data to the platters on write-back-enabled drives. Unfortunately, such
133 file systems behave suboptimally when combined with battery-backup unit
134 (<acronym>BBU</acronym>) disk controllers. In such setups, the synchronize
135 command forces all data from the controller cache to the disks,
136 eliminating much of the benefit of the BBU. You can run the
137 <xref linkend="pgtestfsync"/> program to see
138 if you are affected. If you are affected, the performance benefits
139 of the BBU can be regained by turning off write barriers in
140 the file system or reconfiguring the disk controller, if that is
141 an option. If write barriers are turned off, make sure the battery
142 remains functional; a faulty battery can potentially lead to data loss.
143 Hopefully file system and disk controller designers will eventually
144 address this suboptimal behavior.
145 </para>
146
147 <para>
148 When the operating system sends a write request to the storage hardware,
149 there is little it can do to make sure the data has arrived at a truly
150 non-volatile storage area. Rather, it is the
151 administrator's responsibility to make certain that all storage components
152 ensure integrity for both data and file-system metadata.
153 Avoid disk controllers that have non-battery-backed write caches.
154 At the drive level, disable write-back caching if the
155 drive cannot guarantee the data will be written before shutdown.
156 If you use SSDs, be aware that many of these do not honor cache flush
157 commands by default.
158 You can test for reliable I/O subsystem behavior using <ulink
159 url="https://brad.livejournal.com/2116715.html"><filename>diskchecker.pl</filename></ulink>.
160 </para>
161
162 <para>
163 Another risk of data loss is posed by the disk platter write
164 operations themselves. Disk platters are divided into sectors,
165 commonly 512 bytes each. Every physical read or write operation
166 processes a whole sector.
167 When a write request arrives at the drive, it might be for some multiple
168 of 512 bytes (<productname>PostgreSQL</productname> typically writes 8192 bytes, or
169 16 sectors, at a time), and the process of writing could fail due
170 to power loss at any time, meaning some of the 512-byte sectors were
171 written while others were not. To guard against such failures,
172 <productname>PostgreSQL</productname> periodically writes full page images to
173 permanent WAL storage <emphasis>before</emphasis> modifying the actual page on
174 disk. By doing this, during crash recovery <productname>PostgreSQL</productname> can
175 restore partially-written pages from WAL. If you have file-system software
176 that prevents partial page writes (e.g., ZFS), you can turn off
177 this page imaging by turning off the <xref
178 linkend="guc-full-page-writes"/> parameter. Battery-Backed Unit
179 (BBU) disk controllers do not prevent partial page writes unless
180 they guarantee that data is written to the BBU as full (8kB) pages.
181 </para>
182 <para>
183 <productname>PostgreSQL</productname> also protects against some kinds of data corruption
184 on storage devices that may occur because of hardware errors or media failure over time,
185 such as reading/writing garbage data.
186 <itemizedlist>
187 <listitem>
188 <para>
189 Each individual record in a WAL file is protected by a CRC-32C (32-bit) check
190 that allows us to tell if record contents are correct. The CRC value
191 is set when we write each WAL record and checked during crash recovery,
192 archive recovery and replication.
193 </para>
194 </listitem>
195 <listitem>
196 <para>
197 Data pages are checksummed by default, and full page images
198 recorded in WAL records are always checksum protected.
199 </para>
200 </listitem>
201 <listitem>
202 <para>
203 Internal data structures such as <filename>pg_xact</filename>, <filename>pg_subtrans</filename>, <filename>pg_multixact</filename>,
204 <filename>pg_serial</filename>, <filename>pg_notify</filename>, <filename>pg_stat</filename>, <filename>pg_snapshots</filename> are not directly
205 checksummed, nor are pages protected by full page writes. However, where
206 such data structures are persistent, WAL records are written that allow
207 recent changes to be accurately rebuilt at crash recovery and those
208 WAL records are protected as discussed above.
209 </para>
210 </listitem>
211 <listitem>
212 <para>
213 Individual state files in <filename>pg_twophase</filename> are protected by CRC-32C.
214 </para>
215 </listitem>
216 <listitem>
217 <para>
218 Temporary data files used in larger SQL queries for sorts,
219 materializations and intermediate results are not currently checksummed,
220 nor will WAL records be written for changes to those files.
221 </para>
222 </listitem>
223 </itemizedlist>
224 </para>
225 <para>
226 <productname>PostgreSQL</productname> does not protect against correctable memory errors
227 and it is assumed you will operate using RAM that uses industry standard
228 Error Correcting Codes (ECC) or better protection.
229 </para>
230 </sect1>
231
232 <sect1 id="checksums">
233 <title>Data Checksums</title>
234 <indexterm>
235 <primary>checksums</primary>
236 </indexterm>
237
238 <para>
239 By default, data pages are protected by checksums, but this can
240 optionally be disabled for a cluster. When enabled, each data page includes
241 a checksum that is updated when the page is written and verified each time
242 the page is read. Only data pages are protected by checksums; internal data
243 structures and temporary files are not.
244 </para>
245
246 <para>
247 Checksums can be disabled when the cluster is initialized using <link
248 linkend="app-initdb-data-checksums"><application>initdb</application></link>.
249 They can also be enabled or disabled at a later time as an offline
250 operation. Data checksums are enabled or disabled at the full cluster
251 level, and cannot be specified individually for databases or tables.
252 </para>
253
254 <para>
255 The current state of checksums in the cluster can be verified by viewing the
256 value of the read-only configuration variable <xref
257 linkend="guc-data-checksums" /> by issuing the command <command>SHOW
258 data_checksums</command>.
259 </para>
260
261 <para>
262 When attempting to recover from page corruptions, it may be necessary to
263 bypass the checksum protection. To do this, temporarily set the
264 configuration parameter <xref linkend="guc-ignore-checksum-failure" />.
265 </para>
266
267 <sect2 id="checksums-offline-enable-disable">
268 <title>Off-line Enabling of Checksums</title>
269
270 <para>
271 The <link linkend="app-pgchecksums"><application>pg_checksums</application></link>
272 application can be used to enable or disable data checksums, as well as
273 verify checksums, on an offline cluster.
274 </para>
275
276 </sect2>
277 </sect1>
278
279 <sect1 id="wal-intro">
280 <title>Write-Ahead Logging (<acronym>WAL</acronym>)</title>
281
282 <indexterm zone="wal">
283 <primary>WAL</primary>
284 </indexterm>
285
286 <indexterm>
287 <primary>transaction log</primary>
288 <see>WAL</see>
289 </indexterm>
290
291 <para>
292 <firstterm>Write-Ahead Logging</firstterm> (<acronym>WAL</acronym>)
293 is a standard method for ensuring data integrity. A detailed
294 description can be found in most (if not all) books about
295 transaction processing. Briefly, <acronym>WAL</acronym>'s central
296 concept is that changes to data files (where tables and indexes
297 reside) must be written only after those changes have been logged,
298 that is, after WAL records describing the changes have been flushed
299 to permanent storage. If we follow this procedure, we do not need
300 to flush data pages to disk on every transaction commit, because we
301 know that in the event of a crash we will be able to recover the
302 database using the log: any changes that have not been applied to
303 the data pages can be redone from the WAL records. (This is
304 roll-forward recovery, also known as REDO.)
305 </para>
306
307 <tip>
308 <para>
309 Because <acronym>WAL</acronym> restores database file
310 contents after a crash, journaled file systems are not necessary for
311 reliable storage of the data files or WAL files. In fact, journaling
312 overhead can reduce performance, especially if journaling
313 causes file system <emphasis>data</emphasis> to be flushed
314 to disk. Fortunately, data flushing during journaling can
315 often be disabled with a file system mount option, e.g.,
316 <literal>data=writeback</literal> on a Linux ext3 file system.
317 Journaled file systems do improve boot speed after a crash.
318 </para>
319 </tip>
320
321
322 <para>
323 Using <acronym>WAL</acronym> results in a
324 significantly reduced number of disk writes, because only the WAL
325 file needs to be flushed to disk to guarantee that a transaction is
326 committed, rather than every data file changed by the transaction.
327 The WAL file is written sequentially,
328 and so the cost of syncing the WAL is much less than the cost of
329 flushing the data pages. This is especially true for servers
330 handling many small transactions touching different parts of the data
331 store. Furthermore, when the server is processing many small concurrent
332 transactions, one <function>fsync</function> of the WAL file may
333 suffice to commit many transactions.
334 </para>
335
336 <para>
337 <acronym>WAL</acronym> also makes it possible to support on-line
338 backup and point-in-time recovery, as described in <xref
339 linkend="continuous-archiving"/>. By archiving the WAL data we can support
340 reverting to any time instant covered by the available WAL data:
341 we simply install a prior physical backup of the database, and
342 replay the WAL just as far as the desired time. What's more,
343 the physical backup doesn't have to be an instantaneous snapshot
344 of the database state &mdash; if it is made over some period of time,
345 then replaying the WAL for that period will fix any internal
346 inconsistencies.
347 </para>
348 </sect1>
349
350 <sect1 id="wal-async-commit">
351 <title>Asynchronous Commit</title>
352
353 <indexterm>
354 <primary>synchronous commit</primary>
355 </indexterm>
356
357 <indexterm>
358 <primary>asynchronous commit</primary>
359 </indexterm>
360
361 <para>
362 <firstterm>Asynchronous commit</firstterm> is an option that allows transactions
363 to complete more quickly, at the cost that the most recent transactions may
364 be lost if the database should crash. In many applications this is an
365 acceptable trade-off.
366 </para>
367
368 <para>
369 As described in the previous section, transaction commit is normally
370 <firstterm>synchronous</firstterm>: the server waits for the transaction's
371 <acronym>WAL</acronym> records to be flushed to permanent storage
372 before returning a success indication to the client. The client is
373 therefore guaranteed that a transaction reported to be committed will
374 be preserved, even in the event of a server crash immediately after.
375 However, for short transactions this delay is a major component of the
376 total transaction time. Selecting asynchronous commit mode means that
377 the server returns success as soon as the transaction is logically
378 completed, before the <acronym>WAL</acronym> records it generated have
379 actually made their way to disk. This can provide a significant boost
380 in throughput for small transactions.
381 </para>
382
383 <para>
384 Asynchronous commit introduces the risk of data loss. There is a short
385 time window between the report of transaction completion to the client
386 and the time that the transaction is truly committed (that is, it is
387 guaranteed not to be lost if the server crashes). Thus asynchronous
388 commit should not be used if the client will take external actions
389 relying on the assumption that the transaction will be remembered.
390 As an example, a bank would certainly not use asynchronous commit for
391 a transaction recording an ATM's dispensing of cash. But in many
392 scenarios, such as event logging, there is no need for a strong
393 guarantee of this kind.
394 </para>
395
396 <para>
397 The risk that is taken by using asynchronous commit is of data loss,
398 not data corruption. If the database should crash, it will recover
399 by replaying <acronym>WAL</acronym> up to the last record that was
400 flushed. The database will therefore be restored to a self-consistent
401 state, but any transactions that were not yet flushed to disk will
402 not be reflected in that state. The net effect is therefore loss of
403 the last few transactions. Because the transactions are replayed in
404 commit order, no inconsistency can be introduced &mdash; for example,
405 if transaction B made changes relying on the effects of a previous
406 transaction A, it is not possible for A's effects to be lost while B's
407 effects are preserved.
408 </para>
409
410 <para>
411 The user can select the commit mode of each transaction, so that
412 it is possible to have both synchronous and asynchronous commit
413 transactions running concurrently. This allows flexible trade-offs
414 between performance and certainty of transaction durability.
415 The commit mode is controlled by the user-settable parameter
416 <xref linkend="guc-synchronous-commit"/>, which can be changed in any of
417 the ways that a configuration parameter can be set. The mode used for
418 any one transaction depends on the value of
419 <varname>synchronous_commit</varname> when transaction commit begins.
420 </para>
421
422 <para>
423 Certain utility commands, for instance <command>DROP TABLE</command>, are
424 forced to commit synchronously regardless of the setting of
425 <varname>synchronous_commit</varname>. This is to ensure consistency
426 between the server's file system and the logical state of the database.
427 The commands supporting two-phase commit, such as <command>PREPARE
428 TRANSACTION</command>, are also always synchronous.
429 </para>
430
431 <para>
432 If the database crashes during the risk window between an
433 asynchronous commit and the writing of the transaction's
434 <acronym>WAL</acronym> records,
435 then changes made during that transaction <emphasis>will</emphasis> be lost.
436 The duration of the
437 risk window is limited because a background process (the <quote>WAL
438 writer</quote>) flushes unwritten <acronym>WAL</acronym> records to disk
439 every <xref linkend="guc-wal-writer-delay"/> milliseconds.
440 The actual maximum duration of the risk window is three times
441 <varname>wal_writer_delay</varname> because the WAL writer is
442 designed to favor writing whole pages at a time during busy periods.
443 </para>
444
445 <caution>
446 <para>
447 An immediate-mode shutdown is equivalent to a server crash, and will
448 therefore cause loss of any unflushed asynchronous commits.
449 </para>
450 </caution>
451
452 <para>
453 Asynchronous commit provides behavior different from setting
454 <xref linkend="guc-fsync"/> = off.
455 <varname>fsync</varname> is a server-wide
456 setting that will alter the behavior of all transactions. It disables
457 all logic within <productname>PostgreSQL</productname> that attempts to synchronize
458 writes to different portions of the database, and therefore a system
459 crash (that is, a hardware or operating system crash, not a failure of
460 <productname>PostgreSQL</productname> itself) could result in arbitrarily bad
461 corruption of the database state. In many scenarios, asynchronous
462 commit provides most of the performance improvement that could be
463 obtained by turning off <varname>fsync</varname>, but without the risk
464 of data corruption.
465 </para>
466
467 <para>
468 <xref linkend="guc-commit-delay"/> also sounds very similar to
469 asynchronous commit, but it is actually a synchronous commit method
470 (in fact, <varname>commit_delay</varname> is ignored during an
471 asynchronous commit). <varname>commit_delay</varname> causes a delay
472 just before a transaction flushes <acronym>WAL</acronym> to disk, in
473 the hope that a single flush executed by one such transaction can also
474 serve other transactions committing at about the same time. The
475 setting can be thought of as a way of increasing the time window in
476 which transactions can join a group about to participate in a single
477 flush, to amortize the cost of the flush among multiple transactions.
478 </para>
479
480 </sect1>
481
482 <sect1 id="wal-configuration">
483 <title><acronym>WAL</acronym> Configuration</title>
484
485 <para>
486 There are several <acronym>WAL</acronym>-related configuration parameters that
487 affect database performance. This section explains their use.
488 Consult <xref linkend="runtime-config"/> for general information about
489 setting server configuration parameters.
490 </para>
491
492 <para>
493 <firstterm>Checkpoints</firstterm><indexterm><primary>checkpoint</primary></indexterm>
494 are points in the sequence of transactions at which it is guaranteed
495 that the heap and index data files have been updated with all
496 information written before that checkpoint. At checkpoint time, all
497 dirty data pages are flushed to disk and a special checkpoint record is
498 written to the WAL file. (The change records were previously flushed
499 to the <acronym>WAL</acronym> files.)
500 In the event of a crash, the crash recovery procedure looks at the latest
501 checkpoint record to determine the point in the WAL (known as the redo
502 record) from which it should start the REDO operation. Any changes made to
503 data files before that point are guaranteed to be already on disk.
504 Hence, after a checkpoint, WAL segments preceding the one containing
505 the redo record are no longer needed and can be recycled or removed. (When
506 <acronym>WAL</acronym> archiving is being done, the WAL segments must be
507 archived before being recycled or removed.)
508 </para>
509
510 <para>
511 The checkpoint requirement of flushing all dirty data pages to disk
512 can cause a significant I/O load. For this reason, checkpoint
513 activity is throttled so that I/O begins at checkpoint start and completes
514 before the next checkpoint is due to start; this minimizes performance
515 degradation during checkpoints.
516 </para>
517
518 <para>
519 The server's checkpointer process automatically performs
520 a checkpoint every so often. A checkpoint is begun every <xref
521 linkend="guc-checkpoint-timeout"/> seconds, or if
522 <xref linkend="guc-max-wal-size"/> is about to be exceeded,
523 whichever comes first.
524 The default settings are 5 minutes and 1 GB, respectively.
525 If no WAL has been written since the previous checkpoint, new checkpoints
526 will be skipped even if <varname>checkpoint_timeout</varname> has passed.
527 (If WAL archiving is being used and you want to put a lower limit on how
528 often files are archived in order to bound potential data loss, you should
529 adjust the <xref linkend="guc-archive-timeout"/> parameter rather than the
530 checkpoint parameters.)
531 It is also possible to force a checkpoint by using the SQL
532 command <command>CHECKPOINT</command>.
533 </para>
534
535 <para>
536 Reducing <varname>checkpoint_timeout</varname> and/or
537 <varname>max_wal_size</varname> causes checkpoints to occur
538 more often. This allows faster after-crash recovery, since less work
539 will need to be redone. However, one must balance this against the
540 increased cost of flushing dirty data pages more often. If
541 <xref linkend="guc-full-page-writes"/> is set (as is the default), there is
542 another factor to consider. To ensure data page consistency,
543 the first modification of a data page after each checkpoint results in
544 logging the entire page content. In that case,
545 a smaller checkpoint interval increases the volume of output to the WAL,
546 partially negating the goal of using a smaller interval,
547 and in any case causing more disk I/O.
548 </para>
549
550 <para>
551 Checkpoints are fairly expensive, first because they require writing
552 out all currently dirty buffers, and second because they result in
553 extra subsequent WAL traffic as discussed above. It is therefore
554 wise to set the checkpointing parameters high enough so that checkpoints
555 don't happen too often. As a simple sanity check on your checkpointing
556 parameters, you can set the <xref linkend="guc-checkpoint-warning"/>
557 parameter. If checkpoints happen closer together than
558 <varname>checkpoint_warning</varname> seconds,
559 a message will be output to the server log recommending increasing
560 <varname>max_wal_size</varname>. Occasional appearance of such
561 a message is not cause for alarm, but if it appears often then the
562 checkpoint control parameters should be increased. Bulk operations such
563 as large <command>COPY</command> transfers might cause a number of such warnings
564 to appear if you have not set <varname>max_wal_size</varname> high
565 enough.
566 </para>
567
568 <para>
569 To avoid flooding the I/O system with a burst of page writes,
570 writing dirty buffers during a checkpoint is spread over a period of time.
571 That period is controlled by
572 <xref linkend="guc-checkpoint-completion-target"/>, which is
573 given as a fraction of the checkpoint interval (configured by using
574 <varname>checkpoint_timeout</varname>).
575 The I/O rate is adjusted so that the checkpoint finishes when the
576 given fraction of
577 <varname>checkpoint_timeout</varname> seconds have elapsed, or before
578 <varname>max_wal_size</varname> is exceeded, whichever is sooner.
579 With the default value of 0.9,
580 <productname>PostgreSQL</productname> can be expected to complete each checkpoint
581 a bit before the next scheduled checkpoint (at around 90% of the last checkpoint's
582 duration). This spreads out the I/O as much as possible so that the checkpoint
583 I/O load is consistent throughout the checkpoint interval. The disadvantage of
584 this is that prolonging checkpoints affects recovery time, because more WAL
585 segments will need to be kept around for possible use in recovery. A user
586 concerned about the amount of time required to recover might wish to reduce
587 <varname>checkpoint_timeout</varname> so that checkpoints occur more frequently
588 but still spread the I/O across the checkpoint interval. Alternatively,
589 <varname>checkpoint_completion_target</varname> could be reduced, but this would
590 result in times of more intense I/O (during the checkpoint) and times of less I/O
591 (after the checkpoint completed but before the next scheduled checkpoint) and
592 therefore is not recommended.
593 Although <varname>checkpoint_completion_target</varname> could be set as high as
594 1.0, it is typically recommended to set it to no higher than 0.9 (the default)
595 since checkpoints include some other activities besides writing dirty buffers.
596 A setting of 1.0 is quite likely to result in checkpoints not being
597 completed on time, which would result in performance loss due to
598 unexpected variation in the number of WAL segments needed.
599 </para>
600
601 <para>
602 On Linux and POSIX platforms <xref linkend="guc-checkpoint-flush-after"/>
603 allows you to force OS pages written by the checkpoint to be
604 flushed to disk after a configurable number of bytes. Otherwise, these
605 pages may be kept in the OS's page cache, inducing a stall when
606 <literal>fsync</literal> is issued at the end of a checkpoint. This setting will
607 often help to reduce transaction latency, but it also can have an adverse
608 effect on performance; particularly for workloads that are bigger than
609 <xref linkend="guc-shared-buffers"/>, but smaller than the OS's page cache.
610 </para>
611
612 <para>
613 The number of WAL segment files in <filename>pg_wal</filename> directory depends on
614 <varname>min_wal_size</varname>, <varname>max_wal_size</varname> and
615 the amount of WAL generated in previous checkpoint cycles. When old WAL
616 segment files are no longer needed, they are removed or recycled (that is,
617 renamed to become future segments in the numbered sequence). If, due to a
618 short-term peak of WAL output rate, <varname>max_wal_size</varname> is
619 exceeded, the unneeded segment files will be removed until the system
620 gets back under this limit. Below that limit, the system recycles enough
621 WAL files to cover the estimated need until the next checkpoint, and
622 removes the rest. The estimate is based on a moving average of the number
623 of WAL files used in previous checkpoint cycles. The moving average
624 is increased immediately if the actual usage exceeds the estimate, so it
625 accommodates peak usage rather than average usage to some extent.
626 <varname>min_wal_size</varname> puts a minimum on the amount of WAL files
627 recycled for future usage; that much WAL is always recycled for future use,
628 even if the system is idle and the WAL usage estimate suggests that little
629 WAL is needed.
630 </para>
631
632 <para>
633 Independently of <varname>max_wal_size</varname>,
634 the most recent <xref linkend="guc-wal-keep-size"/> megabytes of
635 WAL files plus one additional WAL file are
636 kept at all times. Also, if WAL archiving is used, old segments cannot be
637 removed or recycled until they are archived. If WAL archiving cannot keep up
638 with the pace that WAL is generated, or if <varname>archive_command</varname>
639 or <varname>archive_library</varname>
640 fails repeatedly, old WAL files will accumulate in <filename>pg_wal</filename>
641 until the situation is resolved. A slow or failed standby server that
642 uses a replication slot will have the same effect (see
643 <xref linkend="streaming-replication-slots"/>).
644 Similarly, if <link linkend="runtime-config-wal-summarization">
645 WAL summarization</link> is enabled, old segments are kept
646 until they are summarized.
647 </para>
648
649 <para>
650 In archive recovery or standby mode, the server periodically performs
651 <firstterm>restartpoints</firstterm>,<indexterm><primary>restartpoint</primary></indexterm>
652 which are similar to checkpoints in normal operation: the server forces
653 all its state to disk, updates the <filename>pg_control</filename> file to
654 indicate that the already-processed WAL data need not be scanned again,
655 and then recycles any old WAL segment files in the <filename>pg_wal</filename>
656 directory.
657 Restartpoints can't be performed more frequently than checkpoints on the
658 primary because restartpoints can only be performed at checkpoint records.
659 A restartpoint can be demanded by a schedule or by an external request.
660 The <structfield>restartpoints_timed</structfield> counter in the
661 <link linkend="monitoring-pg-stat-checkpointer-view"><structname>pg_stat_checkpointer</structname></link>
662 view counts the first ones while the <structfield>restartpoints_req</structfield>
663 the second.
664 A restartpoint is triggered by schedule when a checkpoint record is reached
665 if at least <xref linkend="guc-checkpoint-timeout"/> seconds have passed since
666 the last performed restartpoint or when the previous attempt to perform
667 the restartpoint has failed. In the last case, the next restartpoint
668 will be scheduled in 15 seconds.
669 A restartpoint is triggered by request due to similar reasons like checkpoint
670 but mostly if WAL size is about to exceed <xref linkend="guc-max-wal-size"/>
671 However, because of limitations on when a restartpoint can be performed,
672 <varname>max_wal_size</varname> is often exceeded during recovery,
673 by up to one checkpoint cycle's worth of WAL.
674 (<varname>max_wal_size</varname> is never a hard limit anyway, so you should
675 always leave plenty of headroom to avoid running out of disk space.)
676 The <structfield>restartpoints_done</structfield> counter in the
677 <link linkend="monitoring-pg-stat-checkpointer-view"><structname>pg_stat_checkpointer</structname></link>
678 view counts the restartpoints that have really been performed.
679 </para>
680
681 <para>
682 In some cases, when the WAL size on the primary increases quickly,
683 for instance during massive <command>INSERT</command>,
684 the <structfield>restartpoints_req</structfield> counter on the standby
685 may demonstrate a peak growth.
686 This occurs because requests to create a new restartpoint due to increased
687 WAL consumption cannot be performed because the safe checkpoint record
688 since the last restartpoint has not yet been replayed on the standby.
689 This behavior is normal and does not lead to an increase in system resource
690 consumption.
691 Only the <structfield>restartpoints_done</structfield>
692 counter among the restartpoint-related ones indicates that noticeable system
693 resources have been spent.
694 </para>
695
696 <para>
697 There are two commonly used internal <acronym>WAL</acronym> functions:
698 <function>XLogInsertRecord</function> and <function>XLogFlush</function>.
699 <function>XLogInsertRecord</function> is used to place a new record into
700 the <acronym>WAL</acronym> buffers in shared memory. If there is no
701 space for the new record, <function>XLogInsertRecord</function> will have
702 to write (move to kernel cache) a few filled <acronym>WAL</acronym>
703 buffers. This is undesirable because <function>XLogInsertRecord</function>
704 is used on every database low level modification (for example, row
705 insertion) at a time when an exclusive lock is held on affected
706 data pages, so the operation needs to be as fast as possible. What
707 is worse, writing <acronym>WAL</acronym> buffers might also force the
708 creation of a new WAL segment, which takes even more
709 time. Normally, <acronym>WAL</acronym> buffers should be written
710 and flushed by an <function>XLogFlush</function> request, which is
711 made, for the most part, at transaction commit time to ensure that
712 transaction records are flushed to permanent storage. On systems
713 with high WAL output, <function>XLogFlush</function> requests might
714 not occur often enough to prevent <function>XLogInsertRecord</function>
715 from having to do writes. On such systems
716 one should increase the number of <acronym>WAL</acronym> buffers by
717 modifying the <xref linkend="guc-wal-buffers"/> parameter. When
718 <xref linkend="guc-full-page-writes"/> is set and the system is very busy,
719 setting <varname>wal_buffers</varname> higher will help smooth response times
720 during the period immediately following each checkpoint.
721 </para>
722
723 <para>
724 The <xref linkend="guc-commit-delay"/> parameter defines for how many
725 microseconds a group commit leader process will sleep after acquiring a
726 lock within <function>XLogFlush</function>, while group commit
727 followers queue up behind the leader. This delay allows other server
728 processes to add their commit records to the WAL buffers so that all of
729 them will be flushed by the leader's eventual sync operation. No sleep
730 will occur if <xref linkend="guc-fsync"/> is not enabled, or if fewer
731 than <xref linkend="guc-commit-siblings"/> other sessions are currently
732 in active transactions; this avoids sleeping when it's unlikely that
733 any other session will commit soon. Note that on some platforms, the
734 resolution of a sleep request is ten milliseconds, so that any nonzero
735 <varname>commit_delay</varname> setting between 1 and 10000
736 microseconds would have the same effect. Note also that on some
737 platforms, sleep operations may take slightly longer than requested by
738 the parameter.
739 </para>
740
741 <para>
742 Since the purpose of <varname>commit_delay</varname> is to allow the
743 cost of each flush operation to be amortized across concurrently
744 committing transactions (potentially at the expense of transaction
745 latency), it is necessary to quantify that cost before the setting can
746 be chosen intelligently. The higher that cost is, the more effective
747 <varname>commit_delay</varname> is expected to be in increasing
748 transaction throughput, up to a point. The <xref
749 linkend="pgtestfsync"/> program can be used to measure the average time
750 in microseconds that a single WAL flush operation takes. A value of
751 half of the average time the program reports it takes to flush after a
752 single 8kB write operation is often the most effective setting for
753 <varname>commit_delay</varname>, so this value is recommended as the
754 starting point to use when optimizing for a particular workload. While
755 tuning <varname>commit_delay</varname> is particularly useful when the
756 WAL is stored on high-latency rotating disks, benefits can be
757 significant even on storage media with very fast sync times, such as
758 solid-state drives or RAID arrays with a battery-backed write cache;
759 but this should definitely be tested against a representative workload.
760 Higher values of <varname>commit_siblings</varname> should be used in
761 such cases, whereas smaller <varname>commit_siblings</varname> values
762 are often helpful on higher latency media. Note that it is quite
763 possible that a setting of <varname>commit_delay</varname> that is too
764 high can increase transaction latency by so much that total transaction
765 throughput suffers.
766 </para>
767
768 <para>
769 When <varname>commit_delay</varname> is set to zero (the default), it
770 is still possible for a form of group commit to occur, but each group
771 will consist only of sessions that reach the point where they need to
772 flush their commit records during the window in which the previous
773 flush operation (if any) is occurring. At higher client counts a
774 <quote>gangway effect</quote> tends to occur, so that the effects of group
775 commit become significant even when <varname>commit_delay</varname> is
776 zero, and thus explicitly setting <varname>commit_delay</varname> tends
777 to help less. Setting <varname>commit_delay</varname> can only help
778 when (1) there are some concurrently committing transactions, and (2)
779 throughput is limited to some degree by commit rate; but with high
780 rotational latency this setting can be effective in increasing
781 transaction throughput with as few as two clients (that is, a single
782 committing client with one sibling transaction).
783 </para>
784
785 <para>
786 The <xref linkend="guc-wal-sync-method"/> parameter determines how
787 <productname>PostgreSQL</productname> will ask the kernel to force
788 <acronym>WAL</acronym> updates out to disk.
789 All the options should be the same in terms of reliability, with
790 the exception of <literal>fsync_writethrough</literal>, which can sometimes
791 force a flush of the disk cache even when other options do not do so.
792 However, it's quite platform-specific which one will be the fastest.
793 You can test the speeds of different options using the <xref
794 linkend="pgtestfsync"/> program.
795 Note that this parameter is irrelevant if <varname>fsync</varname>
796 has been turned off.
797 </para>
798
799 <para>
800 Enabling the <xref linkend="guc-wal-debug"/> configuration parameter
801 (provided that <productname>PostgreSQL</productname> has been
802 compiled with support for it) will result in each
803 <function>XLogInsertRecord</function> and <function>XLogFlush</function>
804 <acronym>WAL</acronym> call being logged to the server log. This
805 option might be replaced by a more general mechanism in the future.
806 </para>
807
808 <para>
809 There are two internal functions to write WAL data to disk:
810 <function>XLogWrite</function> and <function>issue_xlog_fsync</function>.
811 When <xref linkend="guc-track-wal-io-timing"/> is enabled, the total
812 amounts of time <function>XLogWrite</function> writes and
813 <function>issue_xlog_fsync</function> syncs WAL data to disk are counted as
814 <varname>write_time</varname> and <varname>fsync_time</varname> in
815 <xref linkend="pg-stat-io-view"/> for the <varname>object</varname>
816 <literal>wal</literal>, respectively.
817 <function>XLogWrite</function> is normally called by
818 <function>XLogInsertRecord</function> (when there is no space for the new
819 record in WAL buffers), <function>XLogFlush</function> and the WAL writer,
820 to write WAL buffers to disk and call <function>issue_xlog_fsync</function>.
821 <function>issue_xlog_fsync</function> is normally called by
822 <function>XLogWrite</function> to sync WAL files to disk.
823 If <varname>wal_sync_method</varname> is either
824 <literal>open_datasync</literal> or <literal>open_sync</literal>,
825 a write operation in <function>XLogWrite</function> guarantees to sync written
826 WAL data to disk and <function>issue_xlog_fsync</function> does nothing.
827 If <varname>wal_sync_method</varname> is either <literal>fdatasync</literal>,
828 <literal>fsync</literal>, or <literal>fsync_writethrough</literal>,
829 the write operation moves WAL buffers to kernel cache and
830 <function>issue_xlog_fsync</function> syncs them to disk. Regardless
831 of the setting of <varname>track_wal_io_timing</varname>, the number
832 of times <function>XLogWrite</function> writes and
833 <function>issue_xlog_fsync</function> syncs WAL data to disk are also
834 counted as <varname>writes</varname> and <varname>fsyncs</varname>
835 in <structname>pg_stat_io</structname> for the <varname>object</varname>
836 <literal>wal</literal>, respectively.
837 </para>
838
839 <para>
840 The <xref linkend="guc-recovery-prefetch"/> parameter can be used to reduce
841 I/O wait times during recovery by instructing the kernel to initiate reads
842 of disk blocks that will soon be needed but are not currently in
843 <productname>PostgreSQL</productname>'s buffer pool.
844 The <xref linkend="guc-maintenance-io-concurrency"/> and
845 <xref linkend="guc-wal-decode-buffer-size"/> settings limit prefetching
846 concurrency and distance, respectively. By default, it is set to
847 <literal>try</literal>, which enables the feature on systems that support
848 issuing read-ahead advice.
849 </para>
850 </sect1>
851
852 <sect1 id="wal-internals">
853 <title>WAL Internals</title>
854
855 <indexterm zone="wal-internals">
856 <primary>LSN</primary>
857 </indexterm>
858
859 <para>
860 <acronym>WAL</acronym> is automatically enabled; no action is
861 required from the administrator except ensuring that the
862 disk-space requirements for the <acronym>WAL</acronym> files are met,
863 and that any necessary tuning is done (see <xref
864 linkend="wal-configuration"/>).
865 </para>
866
867 <para>
868 <acronym>WAL</acronym> records are appended to the <acronym>WAL</acronym>
869 files as each new record is written. The insert position is described by
870 a Log Sequence Number (<acronym>LSN</acronym>) that is a byte offset into
871 the WAL, increasing monotonically with each new record.
872 <acronym>LSN</acronym> values are returned as the datatype
873 <link linkend="datatype-pg-lsn"><type>pg_lsn</type></link>. Values can be
874 compared to calculate the volume of <acronym>WAL</acronym> data that
875 separates them, so they are used to measure the progress of replication
876 and recovery.
877 </para>
878
879 <para>
880 <acronym>WAL</acronym> files are stored in the directory
881 <filename>pg_wal</filename> under the data directory, as a set of
882 segment files, normally each 16 MB in size (but the size can be changed
883 by altering the <option>--wal-segsize</option> <application>initdb</application> option). Each segment is
884 divided into pages, normally 8 kB each (this size can be changed via the
885 <option>--with-wal-blocksize</option> configure option). The WAL record headers
886 are described in <filename>access/xlogrecord.h</filename>; the record
887 content is dependent on the type of event that is being logged. Segment
888 files are given ever-increasing numbers as names, starting at
889 <filename>000000010000000000000001</filename>. The numbers do not wrap,
890 but it will take a very, very long time to exhaust the
891 available stock of numbers.
892 </para>
893
894 <para>
895 It is advantageous if the WAL is located on a different disk from the
896 main database files. This can be achieved by moving the
897 <filename>pg_wal</filename> directory to another location (while the server
898 is shut down, of course) and creating a symbolic link from the
899 original location in the main data directory to the new location.
900 </para>
901
902 <para>
903 The aim of <acronym>WAL</acronym> is to ensure that the log is
904 written before database records are altered, but this can be subverted by
905 disk drives<indexterm><primary>disk drive</primary></indexterm> that falsely report a
906 successful write to the kernel,
907 when in fact they have only cached the data and not yet stored it
908 on the disk. A power failure in such a situation might lead to
909 irrecoverable data corruption. Administrators should try to ensure
910 that disks holding <productname>PostgreSQL</productname>'s
911 <acronym>WAL</acronym> files do not make such false reports.
912 (See <xref linkend="wal-reliability"/>.)
913 </para>
914
915 <para>
916 After a checkpoint has been made and the WAL flushed, the
917 checkpoint's position is saved in the file
918 <filename>pg_control</filename>. Therefore, at the start of recovery,
919 the server first reads <filename>pg_control</filename> and
920 then the checkpoint record; then it performs the REDO operation by
921 scanning forward from the WAL location indicated in the checkpoint
922 record. Because the entire content of data pages is saved in the
923 WAL on the first page modification after a checkpoint (assuming
924 <xref linkend="guc-full-page-writes"/> is not disabled), all pages
925 changed since the checkpoint will be restored to a consistent
926 state.
927 </para>
928
929 <para>
930 To deal with the case where <filename>pg_control</filename> is
931 corrupt, we should support the possibility of scanning existing WAL
932 segments in reverse order &mdash; newest to oldest &mdash; in order to find the
933 latest checkpoint. This has not been implemented yet.
934 <filename>pg_control</filename> is small enough (less than one disk page)
935 that it is not subject to partial-write problems, and as of this writing
936 there have been no reports of database failures due solely to the inability
937 to read <filename>pg_control</filename> itself. So while it is
938 theoretically a weak spot, <filename>pg_control</filename> does not
939 seem to be a problem in practice.
940 </para>
941 </sect1>
942
943 </chapter>