Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 15DE46345F9
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 23 Jun 2010 16:40:24 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 19328-03-2
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 23 Jun 2010 19:40:16 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from momjian.us (momjian.us [70.90.9.53])
	by mail.postgresql.org (Postfix) with ESMTP id 8D7A363492F
	for <pgsql-performance@postgresql.org>;
	Wed, 23 Jun 2010 16:40:16 -0300 (ADT)
Received: (from bruce@localhost) by momjian.us (8.11.6/8.11.6) id
 o5NJe8Q05526;
	Wed, 23 Jun 2010 15:40:08 -0400 (EDT)
From: Bruce Momjian <bruce@momjian.us>
Message-Id: <201006231940.o5NJe8Q05526@momjian.us>
Subject: Re: PostgreSQL as a local in-memory cache
In-Reply-To: <24597.1276803510@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed, 23 Jun 2010 15:40:08 -0400 (EDT)
CC: Dimitri Fontaine <dfontaine@hi-media.com>,
 Josh Berkus <josh@agliodbs.com>,
	postgres performance list <pgsql-performance@postgresql.org>
X-Mailer: ELM [version 2.4ME+ PL124 (25)]
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ELM1277322008-740-0_"
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.79 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201006/409
X-Sequence-Number: 39265


--ELM1277322008-740-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"

Tom Lane wrote:
> Dimitri Fontaine <dfontaine@hi-media.com> writes:
> > Josh Berkus <josh@agliodbs.com> 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  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +

--ELM1277322008-740-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/x-diff
Content-Disposition: inline; filename="/pgpatches/durability"

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>

--ELM1277322008-740-0_--