|PostgreSQL Reference Manual - Volume 3 - Server Administration Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 204 pages
RRP £13.95 ($24.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
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. However, using
fsyncresults in a performance penalty: when a transaction is committed, PostgreSQL must wait for the operating system to flush the write-ahead log to disk. When
fsyncis disabled, the operating system is allowed to do its best in buffering, ordering, and delaying writes. This can result in significantly improved performance. However, if the system crashes, the results of the last few committed transactions may be lost in part or whole. In the worst case, unrecoverable data corruption may occur. (Crashes of the database software itself are not a risk factor here. Only an operating-system-level crash creates a risk of corruption.) Due to the risks involved, there is no universally correct setting for
fsync. Some administrators always disable
fsync, while others only turn it off during initial bulk data loads, where there is a clear restart point if something goes wrong. Others always leave
fsyncenabled. The default is to enable
fsync, for maximum reliability. If you trust your operating system, your hardware, and your utility company (or your battery backup), you can consider disabling
fsync. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line. If you turn this parameter off, also consider turning off
Method used for forcing WAL updates out to disk.
fsyncis off then this setting is irrelevant, since 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
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 a price
in 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 a corrupt database after an operating system crash
or power failure. The risks are similar to turning off
fsync, though smaller. It may be safe to turn off this parameter if you have hardware (such as a battery-backed disk controller) or file-system software that reduces the risk of partial page writes to an acceptably low level (e.g., ReiserFS 4). 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 may 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.
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 0954612043||PostgreSQL Reference Manual - Volume 3 - Server Administration Guide||See the print edition|