pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hannu Krosing <hannu@tm.ee>
To: Christopher Browne <cbbrowne@libertyrms.info>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance Concern
Date: Fri, 24 Oct 2003 23:58:11 +0300
Message-ID: <1067029090.5995.28.camel@fuji.krosing.net> (raw)
In-Reply-To: <60ekx2cuyw.fsf@dev6.int.libertyrms.info>
References: <KKEBKDPPLALEFHBEAOCCGEBLDEAA.john@pagakis.com>
	<60ekx2cuyw.fsf@dev6.int.libertyrms.info>

Christopher Browne kirjutas R, 24.10.2003 kell 22:10:

> That might be something of an improvement, but it oughtn't be
> cripplingly different to use a text field rather than an integer.

I suspect his slowness comes from not running analyze when it would be
time to start using indexes for fk checks - if you run analyze on an
empty table and then do 10000 inserts, then all these will run their
checks using seqscan, as this is the fastest way to do it on an empty
table ;)

> What's crippling is submitting 100,000 queries in 100,000
> transactions.  Cut THAT down to size and you'll see performance return
> to being reasonable.

even this should not be too crippling.

I 0nce did some testing for insert performance and got about 9000
inserts/sec on 4 CPU Xeon with 2GB ram and RAID-5 (likely with battery
backed cache).

This 9000 dropped to ~250 when I added a primary key index (to a
60.000.000 record table, so that the pk index fit only partly in
memory), all this with separate transactions, but with many clients
running concurrently. (btw., the clients were not java/JDBC but
Python/psycopg)


With just one client you are usually stuck to 1 trx/disk revolution, at
least with no battery-backed write cache.

even 250/sec should insert 10000 in 40 sec.

--------------
Hannu




view thread (16+ messages)  latest in thread

Message-ID: <1067029090.5995.28.camel@fuji.krosing.net>
Permalink:  ../1067029090.5995.28.camel@fuji.krosing.net/
Also on:    postgresql.org/message-id/1067029090.5995.28.camel@fuji.krosing.net

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: hannu@tm.ee, cbbrowne@libertyrms.info
  Subject: Re: Performance Concern
  In-Reply-To: <1067029090.5995.28.camel@fuji.krosing.net>
  References: <KKEBKDPPLALEFHBEAOCCGEBLDEAA.john@pagakis.com> <60ekx2cuyw.fsf@dev6.int.libertyrms.info> <1067029090.5995.28.camel@fuji.krosing.net>

* 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