Re: PostgreSQL as a local in-memory cache

From: Pierre C
Subject: Re: PostgreSQL as a local in-memory cache
Date: ,
Msg-id: op.ved4kcv3eorkce@apollo13
(view: Whole thread, Raw)
In response to: Re: PostgreSQL as a local in-memory cache  (Greg Smith)
Responses: Re: PostgreSQL as a local in-memory cache  (Jonathan Gardner)
List: pgsql-performance

Tree view

Re: PostgreSQL as a local in-memory cache  (Chris Browne, )
 Re: PostgreSQL as a local in-memory cache  (Steve Wampler, )
  Re: PostgreSQL as a local in-memory cache  (Craig James, )
 Re: PostgreSQL as a local in-memory cache  ("", )
 Re: PostgreSQL as a local in-memory cache  (Chris Browne, )
  Re: PostgreSQL as a local in-memory cache  (Jaime Casanova, )
  Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
 Re: PostgreSQL as a local in-memory cache  ("", )
  Re: PostgreSQL as a local in-memory cache  (Mark Kirkwood, )
  Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
   Re: PostgreSQL as a local in-memory cache  (Jonathan Gardner, )
  Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
  Re: PostgreSQL as a local in-memory cache  (Greg Smith, )
   Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
    Re: PostgreSQL as a local in-memory cache  (Jonathan Gardner, )
     Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
   Re: PostgreSQL as a local in-memory cache  (Jonathan Gardner, )
  Re: PostgreSQL as a local in-memory cache  (Alvaro Herrera, )
  Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
   Re: PostgreSQL as a local in-memory cache  (Jonathan Gardner, )
    Re: PostgreSQL as a local in-memory cache  (Balkrishna Sharma, )
   Re: PostgreSQL as a local in-memory cache  (Craig James, )

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.



pgsql-performance by date:

From: "Kevin Grittner"
Date:
Subject: Re: Parallel queries for a web-application |performance testing
From: "jgardner@jonathangardner.net"
Date:
Subject: Re: PostgreSQL as a local in-memory cache