Re: PostgreSQL as a local in-memory cache

From: "jgardner(at)jonathangardner(dot)net" <jgardner(at)jonathangardner(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: PostgreSQL as a local in-memory cache
Date: 2010-06-16 06:30:30
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-performance

On Jun 15, 4:18 pm, j(dot)(dot)(dot)(at)agliodbs(dot)com (Josh Berkus) wrote:
> On 6/15/10 10:37 AM, Chris Browne wrote:
> I'd like to see some figures about WAL on RAMfs vs. simply turning off
> fsync and full_page_writes.  Per Gavin's tests, PostgreSQL is already
> close to TokyoCabinet/MongoDB performance just with those turned off; I
> wonder if actually having the WAL on a memory partition would make any
> real difference in throughput.
> I've seen a lot of call for this recently, especially since PostgreSQL
> seems to be increasingly in use as a reporting server for Hadoop.  Might
> be worth experimenting with just making wal writing a no-op.  We'd also
> want to disable checkpointing, of course.

My back-of-the-envelope experiment: Inserting single integers into a
table without indexes using a prepared query via psycopg2.

Python Script:
import psycopg2
from time import time
conn = psycopg2.connect(database='jgardner')
cursor = conn.cursor()
cursor.execute("CREATE TABLE test (data int not null)")
cursor.execute("PREPARE ins AS INSERT INTO test VALUES ($1)")
start = time()
tx = 0
while time() - start < 1.0:
cursor.execute("EXECUTE ins(%s)", (tx,));
tx += 1
print tx
cursor.execute("DROP TABLE test");

Local disk, WAL on same FS:
* Default config => 90
* full_page_writes=off => 90
* synchronous_commit=off => 4,500
* fsync=off => 5,100
* fsync=off and synchronous_commit=off => 5,500
* fsync=off and full_page_writes=off => 5,150
* fsync=off, synchronous_commit=off and full_page_writes=off => 5,500

tmpfs, WAL on same tmpfs:
* Default config: 5,200
* full_page_writes=off => 5,200
* fsync=off => 5,250
* synchronous_commit=off => 5,200
* fsync=off and synchronous_commit=off => 5,450
* fsync=off and full_page_writes=off => 5,250
* fsync=off, synchronous_commit=off and full_page_writes=off => 5,500

NOTE: If I do one giant commit instead of lots of littler ones, I get
much better speeds for the slower cases, but I never exceed 5,500
which appears to be some kind of wall I can't break through.

If there's anything else I should tinker with, I'm all ears.

In response to


Browse pgsql-performance by date

  From Date Subject
Next Message Magnus Hagander 2010-06-16 06:36:25 Re: Analysis Function
Previous Message Josh Berkus 2010-06-15 23:18:19 Re: PostgreSQL as a local in-memory cache