Skip site navigation (1) Skip section navigation (2)

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
Message-ID: 4634b12c-749f-4605-9d95-121b8e2bd9e4@k17g2000pro.googlegroups.com (view raw or flat)
Thread:
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)")
conn.commit()
cursor.execute("PREPARE ins AS INSERT INTO test VALUES ($1)")
conn.commit()
start = time()
tx = 0
while time() - start < 1.0:
    cursor.execute("EXECUTE ins(%s)", (tx,));
    conn.commit()
    tx += 1
print tx
cursor.execute("DROP TABLE test");
conn.commit();

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

Responses

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group