From: Christopher Browne <cbbrowne@acm.org>
Subject: Re: Need for speed
Date: Fri, 19 Aug 2005 08:00:26 -0400
Organization: cbbrowne Computing Inc
Lines: 33
Message-ID: <m3oe7ugmqt.fsf@mobile.int.cbbrowne.com>
References: 
 <DB28E9B548192448A4E8C8A3C1B1E475611C63@sj1-exch-01.us.corp.kailea.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Jumbo Shrimp,
	berkeley-unix)
Cancel-Lock: sha1:2WATvvUFlUBNDukomBEVrYVzI0c=
To: pgsql-performance@postgresql.org

>> Ulrich Wisser wrote:
>> >
>> > one of our services is click counting for on line advertising. We do
>> > this by importing Apache log files every five minutes. This results in a
>> > lot of insert and delete statements. 
> ...
>> If you are doing mostly inserting, make sure you are in a transaction,
>
> Well, yes, but you may need to make sure that a single transaction
> doesn't have too many inserts in it.  I was having a performance
> problem when doing transactions with a huge number of inserts (tens
> of thousands), and I solved the problem by putting a simple counter
> in the loop (in the Java import code, that is) and doing a commit
> every 100 or so inserts.

Are you sure that was an issue with PostgreSQL?

I have certainly observed that issue with Oracle, but NOT with
PostgreSQL.

I have commonly done data loads where they loaded 50K rows at a time,
the reason for COMMITting at that point being "programming paranoia"
at the possibility that some data might fail to load and need to be
retried, and I'd rather have less fail...

It would seem more likely that the issue would be on the Java side; it
might well be that the data being loaded might bloat JVM memory usage,
and that the actions taken at COMMIT time might keep the size of the
Java-side memory footprint down.
-- 
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://cbbrowne.com/info/
If we were meant to fly, we wouldn't keep losing our luggage.