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

Re: High update activity, PostgreSQL vs BigDBMS

From: Guy Rouillier <guyr-ml1(at)burntmail(dot)com>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: High update activity, PostgreSQL vs BigDBMS
Date: 2006-12-31 07:26:31
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Tom Lane wrote:
> "Shoaib Mir" <shoaibmir(at)gmail(dot)com> writes:
>> Here are my few recommendations that might help you:
>> [ snip good advice ]
> Another thing to look at is whether you are doing inserts/updates as
> individual transactions, and if so see if you can "batch" them to
> reduce the per-transaction overhead.

Thank you everyone who replied with suggestions.  Unfortunately, this is 
a background activity for me, so I can only work on it when I can 
squeeze in time.  Right now, I can't do anything; I swapped out a broken 
switch in our network and the DB server is currently inaccessible ;(.  I 
will eventually work through all suggestions, but I'll start with the 
ones I can respond to without further investigation.

I'm not doing updates as individual transactions.  I cannot use the Java 
batch functionality because the code uses stored procedures to do the 
inserts and updates, and the PG JDBC driver cannot handle executing 
stored procedures in batch.  Briefly, executing a stored procedure 
returns a result set, and Java batches don't expect result sets.

So, in the code I turn autocommit off, and do a commit every 100 
executions of the stored proc.  The exact same code is running against 
BigDBMS, so any penalty from this approach should be evenly felt.

Guy Rouillier

In response to

pgsql-performance by date

Next:From: Rolf √ėstvikDate: 2006-12-31 11:33:09
Subject: Worse perfomance on 8.2.0 than on 7.4.14
Previous:From: Alex TurnerDate: 2006-12-30 03:22:35
Subject: Re: High update activity, PostgreSQL vs BigDBMS

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