From: Bruce Momjian <bruce@momjian.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dimitri Fontaine <dfontaine@hi-media.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: PostgreSQL as a local in-memory cache
Date: Wed, 23 Jun 2010 15:40:08 -0400 (EDT)
Message-ID: <201006231940.o5NJe8Q05526@momjian.us> (raw)
In-Reply-To: <24597.1276803510@sss.pgh.pa.us>
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. +
Attachments:/pgpatches/durability (text/x-diff, 4.0K) download
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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bruce@momjian.us, tgl@sss.pgh.pa.us, dfontaine@hi-media.com, josh@agliodbs.com
Subject: Re: PostgreSQL as a local in-memory cache
In-Reply-To: <201006231940.o5NJe8Q05526@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox