Tom Lane wrote:
> Dimitri Fontaine <[email protected]> writes:
> > Josh Berkus <[email protected]> writes:
> >> a) Eliminate WAL logging entirely
> >> b) Eliminate checkpointing
> >> c) Turn off the background writer
> >> d) Have PostgreSQL refuse to restart after a crash and instead call an
> >> exteral script (for reprovisioning)
>
> > Well I guess I'd prefer a per-transaction setting, allowing to bypass
> > WAL logging and checkpointing.
>
> Not going to happen; this is all or nothing.
>
> > Forcing the backend to care itself for
> > writing the data I'm not sure is a good thing, but if you say so.
>
> Yeah, I think proposal (c) is likely to be a net loss.
>
> (a) and (d) are probably simple, if by "reprovisioning" you mean
> "rm -rf $PGDATA; initdb". Point (b) will be a bit trickier because
> there are various housekeeping activities tied into checkpoints.
> I think you can't actually remove checkpoints altogether, just
> skip the flush-dirty-pages part.
Based on this thread, I have developed the following documentation patch
that outlines the performance enhancements possible if durability is not
required. The patch also documents that synchronous_commit = false has
potential committed transaction loss from a database crash (as well as
an OS crash).
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.282
diff -c -c -r1.282 config.sgml
*** doc/src/sgml/config.sgml 22 Jun 2010 02:57:49 -0000 1.282
--- doc/src/sgml/config.sgml 23 Jun 2010 18:53:26 -0000
***************
*** 1463,1469 ****
really guaranteed to be safe against a server crash. (The maximum
delay is three times <xref linkend="guc-wal-writer-delay">.) Unlike
<xref linkend="guc-fsync">, setting this parameter to <literal>off</>
! does not create any risk of database inconsistency: a 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 <varname>synchronous_commit</> off
--- 1463,1470 ----
really guaranteed to be safe against a server crash. (The maximum
delay is three times <xref linkend="guc-wal-writer-delay">.) Unlike
<xref linkend="guc-fsync">, setting this parameter to <literal>off</>
! does not create any risk of database inconsistency: an operating
! system or database crash 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 <varname>synchronous_commit</> off
Index: doc/src/sgml/perform.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/perform.sgml,v
retrieving revision 1.80
diff -c -c -r1.80 perform.sgml
*** doc/src/sgml/perform.sgml 29 May 2010 21:08:04 -0000 1.80
--- doc/src/sgml/perform.sgml 23 Jun 2010 18:53:26 -0000
***************
*** 1104,1107 ****
--- 1104,1169 ----
</sect2>
</sect1>
+ <sect1 id="non-durability">
+ <title>Non-Durable Settings</title>
+
+ <indexterm zone="non-durability">
+ <primary>non-durable</primary>
+ </indexterm>
+
+ <para>
+ Durability is a database feature that guarantees the recording of
+ committed transactions even if if the server crashes or loses
+ power. However, durability adds significant database overhead,
+ so if your site does not require such a guarantee,
+ <productname>PostgreSQL</productname> can be configured to run
+ much faster. The following are configuration changes you can make
+ to improve performance in such cases; they do not invalidate
+ commit guarantees related to database crashes, only abrupt operating
+ system stoppage, except as mentioned below:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Place the database cluster's data directory in a memory-backed
+ file system (i.e. <acronym>RAM</> disk). This eliminates all
+ database disk I/O, but limits data storage to the amount of
+ available memory (and perhaps swap).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Turn off <xref linkend="guc-fsync">; there is no need to flush
+ data to disk.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Turn off <xref linkend="guc-full-page-writes">; there is no need
+ to guard against partial page writes.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Increase <xref linkend="guc-checkpoint-segments"> and <xref
+ linkend="guc-checkpoint-timeout"> ; this reduces the frequency
+ of checkpoints, but increases the storage requirements of
+ <filename>/pg_xlog</>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Turn off <xref linkend="guc-synchronous-commit">; there might be no
+ need to write the <acronym>WAL</acronym> to disk on every
+ commit. This does affect database crash transaction durability.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </sect1>
+
</chapter>
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
