941 lines
47 KiB
Plaintext
941 lines
47 KiB
Plaintext
<!-- doc/src/sgml/wal.sgml -->
|
|
|
|
<chapter id="wal">
|
|
<title>Reliability and the Write-Ahead Log</title>
|
|
|
|
<para>
|
|
This chapter explains how to control the reliability of
|
|
<productname>PostgreSQL</productname>, including details about the
|
|
Write-Ahead Log.
|
|
</para>
|
|
|
|
<sect1 id="wal-reliability">
|
|
<title>Reliability</title>
|
|
|
|
<para>
|
|
Reliability is an important property of any serious database
|
|
system, and <productname>PostgreSQL</productname> does everything possible to
|
|
guarantee reliable operation. One aspect of reliable operation is
|
|
that all data recorded by a committed transaction should be stored
|
|
in a nonvolatile area that is safe from power loss, operating
|
|
system failure, and hardware failure (except failure of the
|
|
nonvolatile area itself, of course). Successfully writing the data
|
|
to the computer's permanent storage (disk drive or equivalent)
|
|
ordinarily meets this requirement. In fact, even if a computer is
|
|
fatally damaged, if the disk drives survive they can be moved to
|
|
another computer with similar hardware and all committed
|
|
transactions will remain intact.
|
|
</para>
|
|
|
|
<para>
|
|
While forcing data to the disk platters periodically might seem like
|
|
a simple operation, it is not. Because disk drives are dramatically
|
|
slower than main memory and CPUs, several layers of caching exist
|
|
between the computer's main memory and the disk platters.
|
|
First, there is the operating system's buffer cache, which caches
|
|
frequently requested disk blocks and combines disk writes. Fortunately,
|
|
all operating systems give applications a way to force writes from
|
|
the buffer cache to disk, and <productname>PostgreSQL</productname> uses those
|
|
features. (See the <xref linkend="guc-wal-sync-method"/> parameter
|
|
to adjust how this is done.)
|
|
</para>
|
|
|
|
<para>
|
|
Next, there might be a cache in the disk drive controller; this is
|
|
particularly common on <acronym>RAID</acronym> controller cards. Some of
|
|
these caches are <firstterm>write-through</firstterm>, meaning writes are sent
|
|
to the drive as soon as they arrive. Others are
|
|
<firstterm>write-back</firstterm>, meaning data is sent to the drive at
|
|
some later time. Such caches can be a reliability hazard because the
|
|
memory in the disk controller cache is volatile, and will lose its
|
|
contents in a power failure. Better controller cards have
|
|
<firstterm>battery-backup units</firstterm> (<acronym>BBU</acronym>s), meaning
|
|
the card has a battery that
|
|
maintains power to the cache in case of system power loss. After power
|
|
is restored the data will be written to the disk drives.
|
|
</para>
|
|
|
|
<para>
|
|
And finally, most disk drives have caches. Some are write-through
|
|
while some are write-back, and the same concerns about data loss
|
|
exist for write-back drive caches as for disk controller
|
|
caches. Consumer-grade IDE and SATA drives are particularly likely
|
|
to have write-back caches that will not survive a power failure. Many
|
|
solid-state drives (SSD) also have volatile write-back caches.
|
|
</para>
|
|
|
|
<para>
|
|
These caches can typically be disabled; however, the method for doing
|
|
this varies by operating system and drive type:
|
|
</para>
|
|
|
|
<itemizedlist>
|
|
<listitem>
|
|
<para>
|
|
On <productname>Linux</productname>, IDE and SATA drives can be queried using
|
|
<command>hdparm -I</command>; write caching is enabled if there is
|
|
a <literal>*</literal> next to <literal>Write cache</literal>. <command>hdparm -W 0</command>
|
|
can be used to turn off write caching. SCSI drives can be queried
|
|
using <ulink url="http://sg.danny.cz/sg/sdparm.html"><application>sdparm</application></ulink>.
|
|
Use <command>sdparm --get=WCE</command> to check
|
|
whether the write cache is enabled and <command>sdparm --clear=WCE</command>
|
|
to disable it.
|
|
</para>
|
|
</listitem>
|
|
|
|
<listitem>
|
|
<para>
|
|
On <productname>FreeBSD</productname>, IDE drives can be queried using
|
|
<command>camcontrol identify</command> and write caching turned off using
|
|
<literal>hw.ata.wc=0</literal> in <filename>/boot/loader.conf</filename>;
|
|
SCSI drives can be queried using <command>camcontrol identify</command>,
|
|
and the write cache both queried and changed using
|
|
<command>sdparm</command> when available.
|
|
</para>
|
|
</listitem>
|
|
|
|
<listitem>
|
|
<para>
|
|
On <productname>Solaris</productname>, the disk write cache is controlled by
|
|
<command>format -e</command>.
|
|
(The Solaris <acronym>ZFS</acronym> file system is safe with disk write-cache
|
|
enabled because it issues its own disk cache flush commands.)
|
|
</para>
|
|
</listitem>
|
|
|
|
<listitem>
|
|
<para>
|
|
On <productname>Windows</productname>, if <varname>wal_sync_method</varname> is
|
|
<literal>open_datasync</literal> (the default), write caching can be disabled
|
|
by unchecking <literal>My Computer\Open\<replaceable>disk drive</replaceable>\Properties\Hardware\Properties\Policies\Enable write caching on the disk</literal>.
|
|
Alternatively, set <varname>wal_sync_method</varname> to
|
|
<literal>fdatasync</literal> (NTFS only) or <literal>fsync</literal>,
|
|
which prevent write caching.
|
|
</para>
|
|
</listitem>
|
|
|
|
<listitem>
|
|
<para>
|
|
On <productname>macOS</productname>, write caching can be prevented by
|
|
setting <varname>wal_sync_method</varname> to <literal>fsync_writethrough</literal>.
|
|
</para>
|
|
</listitem>
|
|
</itemizedlist>
|
|
|
|
<para>
|
|
Recent SATA drives (those following <acronym>ATAPI-6</acronym> or later)
|
|
offer a drive cache flush command (<command>FLUSH CACHE EXT</command>),
|
|
while SCSI drives have long supported a similar command
|
|
<command>SYNCHRONIZE CACHE</command>. These commands are not directly
|
|
accessible to <productname>PostgreSQL</productname>, but some file systems
|
|
(e.g., <acronym>ZFS</acronym>, <acronym>ext4</acronym>) can use them to flush
|
|
data to the platters on write-back-enabled drives. Unfortunately, such
|
|
file systems behave suboptimally when combined with battery-backup unit
|
|
(<acronym>BBU</acronym>) disk controllers. In such setups, the synchronize
|
|
command forces all data from the controller cache to the disks,
|
|
eliminating much of the benefit of the BBU. You can run the
|
|
<xref linkend="pgtestfsync"/> program to see
|
|
if you are affected. If you are affected, the performance benefits
|
|
of the BBU can be regained by turning off write barriers in
|
|
the file system or reconfiguring the disk controller, if that is
|
|
an option. If write barriers are turned off, make sure the battery
|
|
remains functional; a faulty battery can potentially lead to data loss.
|
|
Hopefully file system and disk controller designers will eventually
|
|
address this suboptimal behavior.
|
|
</para>
|
|
|
|
<para>
|
|
When the operating system sends a write request to the storage hardware,
|
|
there is little it can do to make sure the data has arrived at a truly
|
|
non-volatile storage area. Rather, it is the
|
|
administrator's responsibility to make certain that all storage components
|
|
ensure integrity for both data and file-system metadata.
|
|
Avoid disk controllers that have non-battery-backed write caches.
|
|
At the drive level, disable write-back caching if the
|
|
drive cannot guarantee the data will be written before shutdown.
|
|
If you use SSDs, be aware that many of these do not honor cache flush
|
|
commands by default.
|
|
You can test for reliable I/O subsystem behavior using <ulink
|
|
url="https://brad.livejournal.com/2116715.html"><filename>diskchecker.pl</filename></ulink>.
|
|
</para>
|
|
|
|
<para>
|
|
Another risk of data loss is posed by the disk platter write
|
|
operations themselves. Disk platters are divided into sectors,
|
|
commonly 512 bytes each. Every physical read or write operation
|
|
processes a whole sector.
|
|
When a write request arrives at the drive, it might be for some multiple
|
|
of 512 bytes (<productname>PostgreSQL</productname> typically writes 8192 bytes, or
|
|
16 sectors, at a time), and the process of writing could fail due
|
|
to power loss at any time, meaning some of the 512-byte sectors were
|
|
written while others were not. To guard against such failures,
|
|
<productname>PostgreSQL</productname> periodically writes full page images to
|
|
permanent WAL storage <emphasis>before</emphasis> modifying the actual page on
|
|
disk. By doing this, during crash recovery <productname>PostgreSQL</productname> can
|
|
restore partially-written pages from WAL. If you have file-system software
|
|
that prevents partial page writes (e.g., ZFS), you can turn off
|
|
this page imaging by turning off the <xref
|
|
linkend="guc-full-page-writes"/> parameter. Battery-Backed Unit
|
|
(BBU) disk controllers do not prevent partial page writes unless
|
|
they guarantee that data is written to the BBU as full (8kB) pages.
|
|
</para>
|
|
<para>
|
|
<productname>PostgreSQL</productname> also protects against some kinds of data corruption
|
|
on storage devices that may occur because of hardware errors or media failure over time,
|
|
such as reading/writing garbage data.
|
|
<itemizedlist>
|
|
<listitem>
|
|
<para>
|
|
Each individual record in a WAL file is protected by a CRC-32 (32-bit) check
|
|
that allows us to tell if record contents are correct. The CRC value
|
|
is set when we write each WAL record and checked during crash recovery,
|
|
archive recovery and replication.
|
|
</para>
|
|
</listitem>
|
|
<listitem>
|
|
<para>
|
|
Data pages are not currently checksummed by default, though full page images
|
|
recorded in WAL records will be protected; see <link
|
|
linkend="app-initdb-data-checksums"><application>initdb</application></link>
|
|
for details about enabling data checksums.
|
|
</para>
|
|
</listitem>
|
|
<listitem>
|
|
<para>
|
|
Internal data structures such as <filename>pg_xact</filename>, <filename>pg_subtrans</filename>, <filename>pg_multixact</filename>,
|
|
<filename>pg_serial</filename>, <filename>pg_notify</filename>, <filename>pg_stat</filename>, <filename>pg_snapshots</filename> are not directly
|
|
checksummed, nor are pages protected by full page writes. However, where
|
|
such data structures are persistent, WAL records are written that allow
|
|
recent changes to be accurately rebuilt at crash recovery and those
|
|
WAL records are protected as discussed above.
|
|
</para>
|
|
</listitem>
|
|
<listitem>
|
|
<para>
|
|
Individual state files in <filename>pg_twophase</filename> are protected by CRC-32.
|
|
</para>
|
|
</listitem>
|
|
<listitem>
|
|
<para>
|
|
Temporary data files used in larger SQL queries for sorts,
|
|
materializations and intermediate results are not currently checksummed,
|
|
nor will WAL records be written for changes to those files.
|
|
</para>
|
|
</listitem>
|
|
</itemizedlist>
|
|
</para>
|
|
<para>
|
|
<productname>PostgreSQL</productname> does not protect against correctable memory errors
|
|
and it is assumed you will operate using RAM that uses industry standard
|
|
Error Correcting Codes (ECC) or better protection.
|
|
</para>
|
|
</sect1>
|
|
|
|
<sect1 id="checksums">
|
|
<title>Data Checksums</title>
|
|
<indexterm>
|
|
<primary>checksums</primary>
|
|
</indexterm>
|
|
|
|
<para>
|
|
By default, data pages are not protected by checksums, but this can
|
|
optionally be enabled for a cluster. When enabled, each data page includes
|
|
a checksum that is updated when the page is written and verified each time
|
|
the page is read. Only data pages are protected by checksums; internal data
|
|
structures and temporary files are not.
|
|
</para>
|
|
|
|
<para>
|
|
Checksums are normally enabled when the cluster is initialized using <link
|
|
linkend="app-initdb-data-checksums"><application>initdb</application></link>.
|
|
They can also be enabled or disabled at a later time as an offline
|
|
operation. Data checksums are enabled or disabled at the full cluster
|
|
level, and cannot be specified individually for databases or tables.
|
|
</para>
|
|
|
|
<para>
|
|
The current state of checksums in the cluster can be verified by viewing the
|
|
value of the read-only configuration variable <xref
|
|
linkend="guc-data-checksums" /> by issuing the command <command>SHOW
|
|
data_checksums</command>.
|
|
</para>
|
|
|
|
<para>
|
|
When attempting to recover from page corruptions, it may be necessary to
|
|
bypass the checksum protection. To do this, temporarily set the
|
|
configuration parameter <xref linkend="guc-ignore-checksum-failure" />.
|
|
</para>
|
|
|
|
<sect2 id="checksums-offline-enable-disable">
|
|
<title>Off-line Enabling of Checksums</title>
|
|
|
|
<para>
|
|
The <link linkend="app-pgchecksums"><application>pg_checksums</application></link>
|
|
application can be used to enable or disable data checksums, as well as
|
|
verify checksums, on an offline cluster.
|
|
</para>
|
|
|
|
</sect2>
|
|
</sect1>
|
|
|
|
<sect1 id="wal-intro">
|
|
<title>Write-Ahead Logging (<acronym>WAL</acronym>)</title>
|
|
|
|
<indexterm zone="wal">
|
|
<primary>WAL</primary>
|
|
</indexterm>
|
|
|
|
<indexterm>
|
|
<primary>transaction log</primary>
|
|
<see>WAL</see>
|
|
</indexterm>
|
|
|
|
<para>
|
|
<firstterm>Write-Ahead Logging</firstterm> (<acronym>WAL</acronym>)
|
|
is a standard method for ensuring data integrity. A detailed
|
|
description can be found in most (if not all) books about
|
|
transaction processing. Briefly, <acronym>WAL</acronym>'s central
|
|
concept is that changes to data files (where tables and indexes
|
|
reside) must be written only after those changes have been logged,
|
|
that is, after WAL records describing the changes have been flushed
|
|
to permanent storage. If we follow this procedure, we do not need
|
|
to flush data pages to disk on every transaction commit, because we
|
|
know that in the event of a crash we will be able to recover the
|
|
database using the log: any changes that have not been applied to
|
|
the data pages can be redone from the WAL records. (This is
|
|
roll-forward recovery, also known as REDO.)
|
|
</para>
|
|
|
|
<tip>
|
|
<para>
|
|
Because <acronym>WAL</acronym> restores database file
|
|
contents after a crash, journaled file systems are not necessary for
|
|
reliable storage of the data files or WAL files. In fact, journaling
|
|
overhead can reduce performance, especially if journaling
|
|
causes file system <emphasis>data</emphasis> to be flushed
|
|
to disk. Fortunately, data flushing during journaling can
|
|
often be disabled with a file system mount option, e.g.,
|
|
<literal>data=writeback</literal> on a Linux ext3 file system.
|
|
Journaled file systems do improve boot speed after a crash.
|
|
</para>
|
|
</tip>
|
|
|
|
|
|
<para>
|
|
Using <acronym>WAL</acronym> results in a
|
|
significantly reduced number of disk writes, because only the WAL
|
|
file needs to be flushed to disk to guarantee that a transaction is
|
|
committed, rather than every data file changed by the transaction.
|
|
The WAL file is written sequentially,
|
|
and so the cost of syncing the WAL is much less than the cost of
|
|
flushing the data pages. This is especially true for servers
|
|
handling many small transactions touching different parts of the data
|
|
store. Furthermore, when the server is processing many small concurrent
|
|
transactions, one <function>fsync</function> of the WAL file may
|
|
suffice to commit many transactions.
|
|
</para>
|
|
|
|
<para>
|
|
<acronym>WAL</acronym> also makes it possible to support on-line
|
|
backup and point-in-time recovery, as described in <xref
|
|
linkend="continuous-archiving"/>. By archiving the WAL data we can support
|
|
reverting to any time instant covered by the available WAL data:
|
|
we simply install a prior physical backup of the database, and
|
|
replay the WAL just as far as the desired time. What's more,
|
|
the physical backup doesn't have to be an instantaneous snapshot
|
|
of the database state — if it is made over some period of time,
|
|
then replaying the WAL for that period will fix any internal
|
|
inconsistencies.
|
|
</para>
|
|
</sect1>
|
|
|
|
<sect1 id="wal-async-commit">
|
|
<title>Asynchronous Commit</title>
|
|
|
|
<indexterm>
|
|
<primary>synchronous commit</primary>
|
|
</indexterm>
|
|
|
|
<indexterm>
|
|
<primary>asynchronous commit</primary>
|
|
</indexterm>
|
|
|
|
<para>
|
|
<firstterm>Asynchronous commit</firstterm> is an option that allows transactions
|
|
to complete more quickly, at the cost that the most recent transactions may
|
|
be lost if the database should crash. In many applications this is an
|
|
acceptable trade-off.
|
|
</para>
|
|
|
|
<para>
|
|
As described in the previous section, transaction commit is normally
|
|
<firstterm>synchronous</firstterm>: the server waits for the transaction's
|
|
<acronym>WAL</acronym> records to be flushed to permanent storage
|
|
before returning a success indication to the client. The client is
|
|
therefore guaranteed that a transaction reported to be committed will
|
|
be preserved, even in the event of a server crash immediately after.
|
|
However, for short transactions this delay is a major component of the
|
|
total transaction time. Selecting asynchronous commit mode means that
|
|
the server returns success as soon as the transaction is logically
|
|
completed, before the <acronym>WAL</acronym> records it generated have
|
|
actually made their way to disk. This can provide a significant boost
|
|
in throughput for small transactions.
|
|
</para>
|
|
|
|
<para>
|
|
Asynchronous commit introduces the risk of data loss. There is a short
|
|
time window between the report of transaction completion to the client
|
|
and the time that the transaction is truly committed (that is, it is
|
|
guaranteed not to be lost if the server crashes). Thus asynchronous
|
|
commit should not be used if the client will take external actions
|
|
relying on the assumption that the transaction will be remembered.
|
|
As an example, a bank would certainly not use asynchronous commit for
|
|
a transaction recording an ATM's dispensing of cash. But in many
|
|
scenarios, such as event logging, there is no need for a strong
|
|
guarantee of this kind.
|
|
</para>
|
|
|
|
<para>
|
|
The risk that is taken by using asynchronous commit is of data loss,
|
|
not data corruption. If the database should crash, it will recover
|
|
by replaying <acronym>WAL</acronym> up to the last record that was
|
|
flushed. The database will therefore be restored to a self-consistent
|
|
state, but any transactions that were not yet flushed to disk will
|
|
not be reflected in that state. The net effect is therefore loss of
|
|
the last few transactions. Because the transactions are replayed in
|
|
commit order, no inconsistency can be introduced — for example,
|
|
if transaction B made changes relying on the effects of a previous
|
|
transaction A, it is not possible for A's effects to be lost while B's
|
|
effects are preserved.
|
|
</para>
|
|
|
|
<para>
|
|
The user can select the commit mode of each transaction, so that
|
|
it is possible to have both synchronous and asynchronous commit
|
|
transactions running concurrently. This allows flexible trade-offs
|
|
between performance and certainty of transaction durability.
|
|
The commit mode is controlled by the user-settable parameter
|
|
<xref linkend="guc-synchronous-commit"/>, which can be changed in any of
|
|
the ways that a configuration parameter can be set. The mode used for
|
|
any one transaction depends on the value of
|
|
<varname>synchronous_commit</varname> when transaction commit begins.
|
|
</para>
|
|
|
|
<para>
|
|
Certain utility commands, for instance <command>DROP TABLE</command>, are
|
|
forced to commit synchronously regardless of the setting of
|
|
<varname>synchronous_commit</varname>. This is to ensure consistency
|
|
between the server's file system and the logical state of the database.
|
|
The commands supporting two-phase commit, such as <command>PREPARE
|
|
TRANSACTION</command>, are also always synchronous.
|
|
</para>
|
|
|
|
<para>
|
|
If the database crashes during the risk window between an
|
|
asynchronous commit and the writing of the transaction's
|
|
<acronym>WAL</acronym> records,
|
|
then changes made during that transaction <emphasis>will</emphasis> be lost.
|
|
The duration of the
|
|
risk window is limited because a background process (the <quote>WAL
|
|
writer</quote>) flushes unwritten <acronym>WAL</acronym> records to disk
|
|
every <xref linkend="guc-wal-writer-delay"/> milliseconds.
|
|
The actual maximum duration of the risk window is three times
|
|
<varname>wal_writer_delay</varname> because the WAL writer is
|
|
designed to favor writing whole pages at a time during busy periods.
|
|
</para>
|
|
|
|
<caution>
|
|
<para>
|
|
An immediate-mode shutdown is equivalent to a server crash, and will
|
|
therefore cause loss of any unflushed asynchronous commits.
|
|
</para>
|
|
</caution>
|
|
|
|
<para>
|
|
Asynchronous commit provides behavior different from setting
|
|
<xref linkend="guc-fsync"/> = off.
|
|
<varname>fsync</varname> is a server-wide
|
|
setting that will alter the behavior of all transactions. It disables
|
|
all logic within <productname>PostgreSQL</productname> that attempts to synchronize
|
|
writes to different portions of the database, and therefore a system
|
|
crash (that is, a hardware or operating system crash, not a failure of
|
|
<productname>PostgreSQL</productname> itself) could result in arbitrarily bad
|
|
corruption of the database state. In many scenarios, asynchronous
|
|
commit provides most of the performance improvement that could be
|
|
obtained by turning off <varname>fsync</varname>, but without the risk
|
|
of data corruption.
|
|
</para>
|
|
|
|
<para>
|
|
<xref linkend="guc-commit-delay"/> also sounds very similar to
|
|
asynchronous commit, but it is actually a synchronous commit method
|
|
(in fact, <varname>commit_delay</varname> is ignored during an
|
|
asynchronous commit). <varname>commit_delay</varname> causes a delay
|
|
just before a transaction flushes <acronym>WAL</acronym> to disk, in
|
|
the hope that a single flush executed by one such transaction can also
|
|
serve other transactions committing at about the same time. The
|
|
setting can be thought of as a way of increasing the time window in
|
|
which transactions can join a group about to participate in a single
|
|
flush, to amortize the cost of the flush among multiple transactions.
|
|
</para>
|
|
|
|
</sect1>
|
|
|
|
<sect1 id="wal-configuration">
|
|
<title><acronym>WAL</acronym> Configuration</title>
|
|
|
|
<para>
|
|
There are several <acronym>WAL</acronym>-related configuration parameters that
|
|
affect database performance. This section explains their use.
|
|
Consult <xref linkend="runtime-config"/> for general information about
|
|
setting server configuration parameters.
|
|
</para>
|
|
|
|
<para>
|
|
<firstterm>Checkpoints</firstterm><indexterm><primary>checkpoint</primary></indexterm>
|
|
are points in the sequence of transactions at which it is guaranteed
|
|
that the heap and index data files have been updated with all
|
|
information written before that checkpoint. At checkpoint time, all
|
|
dirty data pages are flushed to disk and a special checkpoint record is
|
|
written to the WAL file. (The change records were previously flushed
|
|
to the <acronym>WAL</acronym> files.)
|
|
In the event of a crash, the crash recovery procedure looks at the latest
|
|
checkpoint record to determine the point in the WAL (known as the redo
|
|
record) from which it should start the REDO operation. Any changes made to
|
|
data files before that point are guaranteed to be already on disk.
|
|
Hence, after a checkpoint, WAL segments preceding the one containing
|
|
the redo record are no longer needed and can be recycled or removed. (When
|
|
<acronym>WAL</acronym> archiving is being done, the WAL segments must be
|
|
archived before being recycled or removed.)
|
|
</para>
|
|
|
|
<para>
|
|
The checkpoint requirement of flushing all dirty data pages to disk
|
|
can cause a significant I/O load. For this reason, checkpoint
|
|
activity is throttled so that I/O begins at checkpoint start and completes
|
|
before the next checkpoint is due to start; this minimizes performance
|
|
degradation during checkpoints.
|
|
</para>
|
|
|
|
<para>
|
|
The server's checkpointer process automatically performs
|
|
a checkpoint every so often. A checkpoint is begun every <xref
|
|
linkend="guc-checkpoint-timeout"/> seconds, or if
|
|
<xref linkend="guc-max-wal-size"/> is about to be exceeded,
|
|
whichever comes first.
|
|
The default settings are 5 minutes and 1 GB, respectively.
|
|
If no WAL has been written since the previous checkpoint, new checkpoints
|
|
will be skipped even if <varname>checkpoint_timeout</varname> has passed.
|
|
(If WAL archiving is being used and you want to put a lower limit on how
|
|
often files are archived in order to bound potential data loss, you should
|
|
adjust the <xref linkend="guc-archive-timeout"/> parameter rather than the
|
|
checkpoint parameters.)
|
|
It is also possible to force a checkpoint by using the SQL
|
|
command <command>CHECKPOINT</command>.
|
|
</para>
|
|
|
|
<para>
|
|
Reducing <varname>checkpoint_timeout</varname> and/or
|
|
<varname>max_wal_size</varname> causes checkpoints to occur
|
|
more often. This allows faster after-crash recovery, since less work
|
|
will need to be redone. However, one must balance this against the
|
|
increased cost of flushing dirty data pages more often. If
|
|
<xref linkend="guc-full-page-writes"/> is set (as is the default), there is
|
|
another factor to consider. To ensure data page consistency,
|
|
the first modification of a data page after each checkpoint results in
|
|
logging the entire page content. In that case,
|
|
a smaller checkpoint interval increases the volume of output to the WAL,
|
|
partially negating the goal of using a smaller interval,
|
|
and in any case causing more disk I/O.
|
|
</para>
|
|
|
|
<para>
|
|
Checkpoints are fairly expensive, first because they require writing
|
|
out all currently dirty buffers, and second because they result in
|
|
extra subsequent WAL traffic as discussed above. It is therefore
|
|
wise to set the checkpointing parameters high enough so that checkpoints
|
|
don't happen too often. As a simple sanity check on your checkpointing
|
|
parameters, you can set the <xref linkend="guc-checkpoint-warning"/>
|
|
parameter. If checkpoints happen closer together than
|
|
<varname>checkpoint_warning</varname> seconds,
|
|
a message will be output to the server log recommending increasing
|
|
<varname>max_wal_size</varname>. Occasional appearance of such
|
|
a message is not cause for alarm, but if it appears often then the
|
|
checkpoint control parameters should be increased. Bulk operations such
|
|
as large <command>COPY</command> transfers might cause a number of such warnings
|
|
to appear if you have not set <varname>max_wal_size</varname> high
|
|
enough.
|
|
</para>
|
|
|
|
<para>
|
|
To avoid flooding the I/O system with a burst of page writes,
|
|
writing dirty buffers during a checkpoint is spread over a period of time.
|
|
That period is controlled by
|
|
<xref linkend="guc-checkpoint-completion-target"/>, which is
|
|
given as a fraction of the checkpoint interval (configured by using
|
|
<varname>checkpoint_timeout</varname>).
|
|
The I/O rate is adjusted so that the checkpoint finishes when the
|
|
given fraction of
|
|
<varname>checkpoint_timeout</varname> seconds have elapsed, or before
|
|
<varname>max_wal_size</varname> is exceeded, whichever is sooner.
|
|
With the default value of 0.9,
|
|
<productname>PostgreSQL</productname> can be expected to complete each checkpoint
|
|
a bit before the next scheduled checkpoint (at around 90% of the last checkpoint's
|
|
duration). This spreads out the I/O as much as possible so that the checkpoint
|
|
I/O load is consistent throughout the checkpoint interval. The disadvantage of
|
|
this is that prolonging checkpoints affects recovery time, because more WAL
|
|
segments will need to be kept around for possible use in recovery. A user
|
|
concerned about the amount of time required to recover might wish to reduce
|
|
<varname>checkpoint_timeout</varname> so that checkpoints occur more frequently
|
|
but still spread the I/O across the checkpoint interval. Alternatively,
|
|
<varname>checkpoint_completion_target</varname> could be reduced, but this would
|
|
result in times of more intense I/O (during the checkpoint) and times of less I/O
|
|
(after the checkpoint completed but before the next scheduled checkpoint) and
|
|
therefore is not recommended.
|
|
Although <varname>checkpoint_completion_target</varname> could be set as high as
|
|
1.0, it is typically recommended to set it to no higher than 0.9 (the default)
|
|
since checkpoints include some other activities besides writing dirty buffers.
|
|
A setting of 1.0 is quite likely to result in checkpoints not being
|
|
completed on time, which would result in performance loss due to
|
|
unexpected variation in the number of WAL segments needed.
|
|
</para>
|
|
|
|
<para>
|
|
On Linux and POSIX platforms <xref linkend="guc-checkpoint-flush-after"/>
|
|
allows you to force OS pages written by the checkpoint to be
|
|
flushed to disk after a configurable number of bytes. Otherwise, these
|
|
pages may be kept in the OS's page cache, inducing a stall when
|
|
<literal>fsync</literal> is issued at the end of a checkpoint. This setting will
|
|
often help to reduce transaction latency, but it also can have an adverse
|
|
effect on performance; particularly for workloads that are bigger than
|
|
<xref linkend="guc-shared-buffers"/>, but smaller than the OS's page cache.
|
|
</para>
|
|
|
|
<para>
|
|
The number of WAL segment files in <filename>pg_wal</filename> directory depends on
|
|
<varname>min_wal_size</varname>, <varname>max_wal_size</varname> and
|
|
the amount of WAL generated in previous checkpoint cycles. When old WAL
|
|
segment files are no longer needed, they are removed or recycled (that is,
|
|
renamed to become future segments in the numbered sequence). If, due to a
|
|
short-term peak of WAL output rate, <varname>max_wal_size</varname> is
|
|
exceeded, the unneeded segment files will be removed until the system
|
|
gets back under this limit. Below that limit, the system recycles enough
|
|
WAL files to cover the estimated need until the next checkpoint, and
|
|
removes the rest. The estimate is based on a moving average of the number
|
|
of WAL files used in previous checkpoint cycles. The moving average
|
|
is increased immediately if the actual usage exceeds the estimate, so it
|
|
accommodates peak usage rather than average usage to some extent.
|
|
<varname>min_wal_size</varname> puts a minimum on the amount of WAL files
|
|
recycled for future usage; that much WAL is always recycled for future use,
|
|
even if the system is idle and the WAL usage estimate suggests that little
|
|
WAL is needed.
|
|
</para>
|
|
|
|
<para>
|
|
Independently of <varname>max_wal_size</varname>,
|
|
the most recent <xref linkend="guc-wal-keep-size"/> megabytes of
|
|
WAL files plus one additional WAL file are
|
|
kept at all times. Also, if WAL archiving is used, old segments cannot be
|
|
removed or recycled until they are archived. If WAL archiving cannot keep up
|
|
with the pace that WAL is generated, or if <varname>archive_command</varname>
|
|
or <varname>archive_library</varname>
|
|
fails repeatedly, old WAL files will accumulate in <filename>pg_wal</filename>
|
|
until the situation is resolved. A slow or failed standby server that
|
|
uses a replication slot will have the same effect (see
|
|
<xref linkend="streaming-replication-slots"/>).
|
|
</para>
|
|
|
|
<para>
|
|
In archive recovery or standby mode, the server periodically performs
|
|
<firstterm>restartpoints</firstterm>,<indexterm><primary>restartpoint</primary></indexterm>
|
|
which are similar to checkpoints in normal operation: the server forces
|
|
all its state to disk, updates the <filename>pg_control</filename> file to
|
|
indicate that the already-processed WAL data need not be scanned again,
|
|
and then recycles any old WAL segment files in the <filename>pg_wal</filename>
|
|
directory.
|
|
Restartpoints can't be performed more frequently than checkpoints on the
|
|
primary because restartpoints can only be performed at checkpoint records.
|
|
A restartpoint can be demanded by a schedule or by an external request.
|
|
The <structfield>restartpoints_timed</structfield> counter in the
|
|
<link linkend="monitoring-pg-stat-checkpointer-view"><structname>pg_stat_checkpointer</structname></link>
|
|
view counts the first ones while the <structfield>restartpoints_req</structfield>
|
|
the second.
|
|
A restartpoint is triggered by schedule when a checkpoint record is reached
|
|
if at least <xref linkend="guc-checkpoint-timeout"/> seconds have passed since
|
|
the last performed restartpoint or when the previous attempt to perform
|
|
the restartpoint has failed. In the last case, the next restartpoint
|
|
will be scheduled in 15 seconds.
|
|
A restartpoint is triggered by request due to similar reasons like checkpoint
|
|
but mostly if WAL size is about to exceed <xref linkend="guc-max-wal-size"/>
|
|
However, because of limitations on when a restartpoint can be performed,
|
|
<varname>max_wal_size</varname> is often exceeded during recovery,
|
|
by up to one checkpoint cycle's worth of WAL.
|
|
(<varname>max_wal_size</varname> is never a hard limit anyway, so you should
|
|
always leave plenty of headroom to avoid running out of disk space.)
|
|
The <structfield>restartpoints_done</structfield> counter in the
|
|
<link linkend="monitoring-pg-stat-checkpointer-view"><structname>pg_stat_checkpointer</structname></link>
|
|
view counts the restartpoints that have really been performed.
|
|
</para>
|
|
|
|
<para>
|
|
In some cases, when the WAL size on the primary increases quickly,
|
|
for instance during massive INSERT,
|
|
the <structfield>restartpoints_req</structfield> counter on the standby
|
|
may demonstrate a peak growth.
|
|
This occurs because requests to create a new restartpoint due to increased
|
|
XLOG consumption cannot be performed because the safe checkpoint record
|
|
since the last restartpoint has not yet been replayed on the standby.
|
|
This behavior is normal and does not lead to an increase in system resource
|
|
consumption.
|
|
Only the <structfield>restartpoints_done</structfield>
|
|
counter among the restartpoint-related ones indicates that noticeable system
|
|
resources have been spent.
|
|
</para>
|
|
|
|
<para>
|
|
There are two commonly used internal <acronym>WAL</acronym> functions:
|
|
<function>XLogInsertRecord</function> and <function>XLogFlush</function>.
|
|
<function>XLogInsertRecord</function> is used to place a new record into
|
|
the <acronym>WAL</acronym> buffers in shared memory. If there is no
|
|
space for the new record, <function>XLogInsertRecord</function> will have
|
|
to write (move to kernel cache) a few filled <acronym>WAL</acronym>
|
|
buffers. This is undesirable because <function>XLogInsertRecord</function>
|
|
is used on every database low level modification (for example, row
|
|
insertion) at a time when an exclusive lock is held on affected
|
|
data pages, so the operation needs to be as fast as possible. What
|
|
is worse, writing <acronym>WAL</acronym> buffers might also force the
|
|
creation of a new WAL segment, which takes even more
|
|
time. Normally, <acronym>WAL</acronym> buffers should be written
|
|
and flushed by an <function>XLogFlush</function> request, which is
|
|
made, for the most part, at transaction commit time to ensure that
|
|
transaction records are flushed to permanent storage. On systems
|
|
with high WAL output, <function>XLogFlush</function> requests might
|
|
not occur often enough to prevent <function>XLogInsertRecord</function>
|
|
from having to do writes. On such systems
|
|
one should increase the number of <acronym>WAL</acronym> buffers by
|
|
modifying the <xref linkend="guc-wal-buffers"/> parameter. When
|
|
<xref linkend="guc-full-page-writes"/> is set and the system is very busy,
|
|
setting <varname>wal_buffers</varname> higher will help smooth response times
|
|
during the period immediately following each checkpoint.
|
|
</para>
|
|
|
|
<para>
|
|
The <xref linkend="guc-commit-delay"/> parameter defines for how many
|
|
microseconds a group commit leader process will sleep after acquiring a
|
|
lock within <function>XLogFlush</function>, while group commit
|
|
followers queue up behind the leader. This delay allows other server
|
|
processes to add their commit records to the WAL buffers so that all of
|
|
them will be flushed by the leader's eventual sync operation. No sleep
|
|
will occur if <xref linkend="guc-fsync"/> is not enabled, or if fewer
|
|
than <xref linkend="guc-commit-siblings"/> other sessions are currently
|
|
in active transactions; this avoids sleeping when it's unlikely that
|
|
any other session will commit soon. Note that on some platforms, the
|
|
resolution of a sleep request is ten milliseconds, so that any nonzero
|
|
<varname>commit_delay</varname> setting between 1 and 10000
|
|
microseconds would have the same effect. Note also that on some
|
|
platforms, sleep operations may take slightly longer than requested by
|
|
the parameter.
|
|
</para>
|
|
|
|
<para>
|
|
Since the purpose of <varname>commit_delay</varname> is to allow the
|
|
cost of each flush operation to be amortized across concurrently
|
|
committing transactions (potentially at the expense of transaction
|
|
latency), it is necessary to quantify that cost before the setting can
|
|
be chosen intelligently. The higher that cost is, the more effective
|
|
<varname>commit_delay</varname> is expected to be in increasing
|
|
transaction throughput, up to a point. The <xref
|
|
linkend="pgtestfsync"/> program can be used to measure the average time
|
|
in microseconds that a single WAL flush operation takes. A value of
|
|
half of the average time the program reports it takes to flush after a
|
|
single 8kB write operation is often the most effective setting for
|
|
<varname>commit_delay</varname>, so this value is recommended as the
|
|
starting point to use when optimizing for a particular workload. While
|
|
tuning <varname>commit_delay</varname> is particularly useful when the
|
|
WAL is stored on high-latency rotating disks, benefits can be
|
|
significant even on storage media with very fast sync times, such as
|
|
solid-state drives or RAID arrays with a battery-backed write cache;
|
|
but this should definitely be tested against a representative workload.
|
|
Higher values of <varname>commit_siblings</varname> should be used in
|
|
such cases, whereas smaller <varname>commit_siblings</varname> values
|
|
are often helpful on higher latency media. Note that it is quite
|
|
possible that a setting of <varname>commit_delay</varname> that is too
|
|
high can increase transaction latency by so much that total transaction
|
|
throughput suffers.
|
|
</para>
|
|
|
|
<para>
|
|
When <varname>commit_delay</varname> is set to zero (the default), it
|
|
is still possible for a form of group commit to occur, but each group
|
|
will consist only of sessions that reach the point where they need to
|
|
flush their commit records during the window in which the previous
|
|
flush operation (if any) is occurring. At higher client counts a
|
|
<quote>gangway effect</quote> tends to occur, so that the effects of group
|
|
commit become significant even when <varname>commit_delay</varname> is
|
|
zero, and thus explicitly setting <varname>commit_delay</varname> tends
|
|
to help less. Setting <varname>commit_delay</varname> can only help
|
|
when (1) there are some concurrently committing transactions, and (2)
|
|
throughput is limited to some degree by commit rate; but with high
|
|
rotational latency this setting can be effective in increasing
|
|
transaction throughput with as few as two clients (that is, a single
|
|
committing client with one sibling transaction).
|
|
</para>
|
|
|
|
<para>
|
|
The <xref linkend="guc-wal-sync-method"/> parameter determines how
|
|
<productname>PostgreSQL</productname> will ask the kernel to force
|
|
<acronym>WAL</acronym> updates out to disk.
|
|
All the options should be the same in terms of reliability, with
|
|
the exception of <literal>fsync_writethrough</literal>, which can sometimes
|
|
force a flush of the disk cache even when other options do not do so.
|
|
However, it's quite platform-specific which one will be the fastest.
|
|
You can test the speeds of different options using the <xref
|
|
linkend="pgtestfsync"/> program.
|
|
Note that this parameter is irrelevant if <varname>fsync</varname>
|
|
has been turned off.
|
|
</para>
|
|
|
|
<para>
|
|
Enabling the <xref linkend="guc-wal-debug"/> configuration parameter
|
|
(provided that <productname>PostgreSQL</productname> has been
|
|
compiled with support for it) will result in each
|
|
<function>XLogInsertRecord</function> and <function>XLogFlush</function>
|
|
<acronym>WAL</acronym> call being logged to the server log. This
|
|
option might be replaced by a more general mechanism in the future.
|
|
</para>
|
|
|
|
<para>
|
|
There are two internal functions to write WAL data to disk:
|
|
<function>XLogWrite</function> and <function>issue_xlog_fsync</function>.
|
|
When <xref linkend="guc-track-wal-io-timing"/> is enabled, the total
|
|
amounts of time <function>XLogWrite</function> writes and
|
|
<function>issue_xlog_fsync</function> syncs WAL data to disk are counted as
|
|
<literal>wal_write_time</literal> and <literal>wal_sync_time</literal> in
|
|
<xref linkend="pg-stat-wal-view"/>, respectively.
|
|
<function>XLogWrite</function> is normally called by
|
|
<function>XLogInsertRecord</function> (when there is no space for the new
|
|
record in WAL buffers), <function>XLogFlush</function> and the WAL writer,
|
|
to write WAL buffers to disk and call <function>issue_xlog_fsync</function>.
|
|
<function>issue_xlog_fsync</function> is normally called by
|
|
<function>XLogWrite</function> to sync WAL files to disk.
|
|
If <varname>wal_sync_method</varname> is either
|
|
<literal>open_datasync</literal> or <literal>open_sync</literal>,
|
|
a write operation in <function>XLogWrite</function> guarantees to sync written
|
|
WAL data to disk and <function>issue_xlog_fsync</function> does nothing.
|
|
If <varname>wal_sync_method</varname> is either <literal>fdatasync</literal>,
|
|
<literal>fsync</literal>, or <literal>fsync_writethrough</literal>,
|
|
the write operation moves WAL buffers to kernel cache and
|
|
<function>issue_xlog_fsync</function> syncs them to disk. Regardless
|
|
of the setting of <varname>track_wal_io_timing</varname>, the number
|
|
of times <function>XLogWrite</function> writes and
|
|
<function>issue_xlog_fsync</function> syncs WAL data to disk are also
|
|
counted as <literal>wal_write</literal> and <literal>wal_sync</literal>
|
|
in <structname>pg_stat_wal</structname>, respectively.
|
|
</para>
|
|
|
|
<para>
|
|
The <xref linkend="guc-recovery-prefetch"/> parameter can be used to reduce
|
|
I/O wait times during recovery by instructing the kernel to initiate reads
|
|
of disk blocks that will soon be needed but are not currently in
|
|
<productname>PostgreSQL</productname>'s buffer pool.
|
|
The <xref linkend="guc-maintenance-io-concurrency"/> and
|
|
<xref linkend="guc-wal-decode-buffer-size"/> settings limit prefetching
|
|
concurrency and distance, respectively. By default, it is set to
|
|
<literal>try</literal>, which enables the feature on systems where
|
|
<function>posix_fadvise</function> is available.
|
|
</para>
|
|
</sect1>
|
|
|
|
<sect1 id="wal-internals">
|
|
<title>WAL Internals</title>
|
|
|
|
<indexterm zone="wal-internals">
|
|
<primary>LSN</primary>
|
|
</indexterm>
|
|
|
|
<para>
|
|
<acronym>WAL</acronym> is automatically enabled; no action is
|
|
required from the administrator except ensuring that the
|
|
disk-space requirements for the <acronym>WAL</acronym> files are met,
|
|
and that any necessary tuning is done (see <xref
|
|
linkend="wal-configuration"/>).
|
|
</para>
|
|
|
|
<para>
|
|
<acronym>WAL</acronym> records are appended to the <acronym>WAL</acronym>
|
|
files as each new record is written. The insert position is described by
|
|
a Log Sequence Number (<acronym>LSN</acronym>) that is a byte offset into
|
|
the WAL, increasing monotonically with each new record.
|
|
<acronym>LSN</acronym> values are returned as the datatype
|
|
<link linkend="datatype-pg-lsn"><type>pg_lsn</type></link>. Values can be
|
|
compared to calculate the volume of <acronym>WAL</acronym> data that
|
|
separates them, so they are used to measure the progress of replication
|
|
and recovery.
|
|
</para>
|
|
|
|
<para>
|
|
<acronym>WAL</acronym> files are stored in the directory
|
|
<filename>pg_wal</filename> under the data directory, as a set of
|
|
segment files, normally each 16 MB in size (but the size can be changed
|
|
by altering the <option>--wal-segsize</option> <application>initdb</application> option). Each segment is
|
|
divided into pages, normally 8 kB each (this size can be changed via the
|
|
<option>--with-wal-blocksize</option> configure option). The WAL record headers
|
|
are described in <filename>access/xlogrecord.h</filename>; the record
|
|
content is dependent on the type of event that is being logged. Segment
|
|
files are given ever-increasing numbers as names, starting at
|
|
<filename>000000010000000000000001</filename>. The numbers do not wrap,
|
|
but it will take a very, very long time to exhaust the
|
|
available stock of numbers.
|
|
</para>
|
|
|
|
<para>
|
|
It is advantageous if the WAL is located on a different disk from the
|
|
main database files. This can be achieved by moving the
|
|
<filename>pg_wal</filename> directory to another location (while the server
|
|
is shut down, of course) and creating a symbolic link from the
|
|
original location in the main data directory to the new location.
|
|
</para>
|
|
|
|
<para>
|
|
The aim of <acronym>WAL</acronym> is to ensure that the log is
|
|
written before database records are altered, but this can be subverted by
|
|
disk drives<indexterm><primary>disk drive</primary></indexterm> that falsely report a
|
|
successful write to the kernel,
|
|
when in fact they have only cached the data and not yet stored it
|
|
on the disk. A power failure in such a situation might lead to
|
|
irrecoverable data corruption. Administrators should try to ensure
|
|
that disks holding <productname>PostgreSQL</productname>'s
|
|
<acronym>WAL</acronym> files do not make such false reports.
|
|
(See <xref linkend="wal-reliability"/>.)
|
|
</para>
|
|
|
|
<para>
|
|
After a checkpoint has been made and the WAL flushed, the
|
|
checkpoint's position is saved in the file
|
|
<filename>pg_control</filename>. Therefore, at the start of recovery,
|
|
the server first reads <filename>pg_control</filename> and
|
|
then the checkpoint record; then it performs the REDO operation by
|
|
scanning forward from the WAL location indicated in the checkpoint
|
|
record. Because the entire content of data pages is saved in the
|
|
WAL on the first page modification after a checkpoint (assuming
|
|
<xref linkend="guc-full-page-writes"/> is not disabled), all pages
|
|
changed since the checkpoint will be restored to a consistent
|
|
state.
|
|
</para>
|
|
|
|
<para>
|
|
To deal with the case where <filename>pg_control</filename> is
|
|
corrupt, we should support the possibility of scanning existing WAL
|
|
segments in reverse order — newest to oldest — in order to find the
|
|
latest checkpoint. This has not been implemented yet.
|
|
<filename>pg_control</filename> is small enough (less than one disk page)
|
|
that it is not subject to partial-write problems, and as of this writing
|
|
there have been no reports of database failures due solely to the inability
|
|
to read <filename>pg_control</filename> itself. So while it is
|
|
theoretically a weak spot, <filename>pg_control</filename> does not
|
|
seem to be a problem in practice.
|
|
</para>
|
|
</sect1>
|
|
|
|
</chapter>
|