Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: "Greg Smith" <greg@2ndquadrant.com>, "jgardner@jonathangardner.net"
	<jgardner@jonathangardner.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL as a local in-memory cache
References: <cb0fb58c-9134-4314-a1d0-08fc39f911a6@40g2000pry.googlegroups.com>
	<87bpbcpabg.fsf@cbbrowne-laptop.afilias-int.info>
	<4C17A417.2040600@noao.edu>
	<87ocfcnqmk.fsf@cbbrowne-laptop.afilias-int.info>
	<4C180A3B.7090905@agliodbs.com>
	<4634b12c-749f-4605-9d95-121b8e2bd9e4@k17g2000pro.googlegroups.com>
	<4C188AD4.20209@2ndquadrant.com>
Date: Wed, 16 Jun 2010 13:22:50 +0200
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.ved4kcv3eorkce@apollo13>
In-Reply-To: <4C188AD4.20209@2ndquadrant.com>
User-Agent: Opera Mail/10.10 (Linux)


FYI I've tweaked this program a bit :

import psycopg2
 from time import time
conn = psycopg2.connect(database='peufeu')
cursor = conn.cursor()
cursor.execute("CREATE TEMPORARY TABLE test (data int not null)")
conn.commit()
cursor.execute("PREPARE ins AS INSERT INTO test VALUES ($1)")
cursor.execute("PREPARE sel AS SELECT 1")
conn.commit()
start = time()
tx = 0
N = 100
d = 0
while d < 10:
	for n in xrange( N ):
		cursor.execute("EXECUTE ins(%s)", (tx,));
		#~ conn.commit()
		#~ cursor.execute("EXECUTE sel" );
	conn.commit()
	d = time() - start
	tx += N
print "result : %d tps" % (tx / d)
cursor.execute("DROP TABLE test");
conn.commit();

Results (Core 2 quad, ubuntu 10.04 64 bits) :

SELECT 1 : 21000 queries/s (I'd say 50 us per query isn't bad !)
INSERT with commit every 100 inserts : 17800 insets/s
INSERT with commit every INSERT : 7650 tps

fsync is on but not synchronous_commit.