|The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 274 pages
RRP £9.95 ($14.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
wal_leveldetermines how much information is written to the WAL. The default value is
minimal, which writes only the information needed to recover from a crash or immediate shutdown.
archiveadds logging required for WAL archiving, and
hot_standbyfurther adds information required to run read-only queries on a standby server. This parameter can only be set at server start. In
minimallevel, WAL-logging of some bulk operations, like
COPYon a table that was created or truncated in the same transaction can be safely skipped, which can make those operations much faster (see Volume 1A: 12.4.7 Disable WAL archival and streaming replication). But minimal WAL does not contain enough information to reconstruct the data from a base backup and the WAL logs, so either
hot_standbylevel must be used to enable WAL archiving (
archive_mode) and streaming replication. In
hot_standbylevel, the same information is logged as with
archive, plus information needed to reconstruct the status of running transactions from the WAL. To enable read-only queries on a standby server,
wal_levelmust be set to
hot_standbyon the primary, and
hot_standbymust be enabled in the standby. It is thought that there is little measurable difference in performance between using
archivelevels, so feedback is welcome if any production impacts are noticeable.
If this parameter is on, the PostgreSQL server
will try to make sure that updates are physically written to
disk, by issuing
fsync()system calls or various equivalent methods (see
wal_sync_method). This ensures that the database cluster can recover to a consistent state after an operating system or hardware crash. While turning off
fsyncis often a performance benefit, this can result in unrecoverable data corruption in the event of an unexpected system shutdown or crash. Thus it is only advisable to turn off
fsyncif you can easily recreate your entire database from external data. Examples of safe circumstances for turning off
fsyncinclude the initial loading a new database cluster from a backup file, using a database cluster for processing statistics on an hourly basis which is then recreated, or for a reporting read-only database clone which gets recreated frequently and is not used for failover. High quality hardware alone is not a sufficient justification for turning off
fsync. In many situations, turning off
synchronous_commitfor noncritical transactions can provide much of the potential performance benefit of turning off
fsync, without the attendant risks of data corruption.
fsynccan only be set in the ‘postgresql.conf’ file or on the server command line. If you turn this parameter off, also consider turning off
Specifies whether transaction commit will wait for WAL records
to be written to disk before the command returns a “success”
indication to the client. The default, and safe, setting is
off, there can be a delay between when success is reported to the client and when the transaction is really guaranteed to be safe against a server crash. (The maximum delay is three times
fsync, setting this parameter to
offdoes not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turning
synchronous_commitoff can be a useful alternative when performance is more important than exact certainty about the durability of a transaction. For more discussion see section 15.3 Asynchronous Commit. This parameter can be changed at any time; the behavior for any one transaction is determined by the setting in effect when it commits. It is therefore possible, and useful, to have some transactions commit synchronously and others asynchronously. For example, to make a single multistatement transaction commit asynchronously when the default is the opposite, issue
SET LOCAL synchronous_commit TO OFFwithin the transaction.
Method used for forcing WAL updates out to disk.
fsyncis off then this setting is irrelevant, since WAL file updates will not be forced out at all. Possible values are:
open_datasync(write WAL files with
fdatasync()at each commit)
fsync()at each commit, forcing write-through of any disk write cache)
fsync()at each commit)
open_sync(write WAL files with
open_* options also use
O_DIRECTif available. The utility ‘src/tools/fsync’ in the PostgreSQL source tree can do performance testing of various fsync methods. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line.
When this parameter is on, the PostgreSQL server
writes the entire content of each disk page to WAL during the
first modification of that page after a checkpoint.
This is needed because
a page write that is in process during an operating system crash might
be only partially completed, leading to an on-disk page
that contains a mix of old and new data. The row-level change data
normally stored in WAL will not be enough to completely restore
such a page during post-crash recovery. Storing the full page image
guarantees that the page can be correctly restored, but at the price
of increasing the amount of data that must be written to WAL.
(Because WAL replay always starts from a checkpoint, it is sufficient
to do this during the first change of each page after a checkpoint.
Therefore, one way to reduce the cost of full-page writes is to
increase the checkpoint interval parameters.)
Turning this parameter off speeds normal operation, but
might lead to either unrecoverable data corruption, or silent
data corruption, after a system failure. The risks are similar to turning off
fsync, though smaller, and it should be turned off only based on the same circumstances recommended for that parameter. Turning off this parameter does not affect use of WAL archiving for point-in-time recovery (PITR) (see section 10.3 Continuous Archiving and Point-In-Time Recovery (PITR)). This parameter can only be set in the ‘postgresql.conf’ file or on the server command line. The default is
The amount of memory used in shared memory for WAL data. The
default is 64 kilobytes (
64kB). The setting need only be large enough to hold the amount of WAL data generated by one typical transaction, since the data is written out to disk at every transaction commit. This parameter can only be set at server start. Increasing this parameter might cause PostgreSQL to request more
System Vshared memory than your operating system's default configuration allows. See section 3.4.1 Shared Memory and Semaphores for information on how to adjust those parameters, if necessary.
Specifies the delay between activity rounds for the WAL writer.
In each round the writer will flush WAL to disk. It then sleeps for
wal_writer_delaymilliseconds, and repeats. The default value is 200 milliseconds (
200ms). Note that on many systems, the effective resolution of sleep delays is 10 milliseconds; setting
wal_writer_delayto a value that is not a multiple of 10 might have the same results as setting it to the next higher multiple of 10. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line.
Time delay between writing a commit record to the WAL buffer
and flushing the buffer out to disk, in microseconds. A
nonzero delay can allow multiple transactions to be committed
with only one
fsync()system call, if system load is high enough that additional transactions become ready to commit within the given interval. But the delay is just wasted if no other transactions become ready to commit. Therefore, the delay is only performed if at least
commit_siblingsother transactions are active at the instant that a server process has written its commit record. The default is zero (no delay).
Minimum number of concurrent open transactions to require
before performing the
commit_delaydelay. A larger value makes it more probable that at least one other transaction will become ready to commit during the delay interval. The default is five transactions.
|ISBN 9781906966072||The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide||See the print edition|