From: Guy Rouillier <guyr-ml1@burntmail.com>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: High update activity, PostgreSQL vs BigDBMS
Date: Sun, 31 Dec 2006 02:26:31 -0500
Message-ID: <45976627.5090201@burntmail.com> (raw)
In-Reply-To: <6903.1167317814@sss.pgh.pa.us>
References: <45935A49.6080602@burntmail.com>
<bf54be870612280106n1247520dtb48e10cb4bdc43e9@mail.gmail.com>
<6903.1167317814@sss.pgh.pa.us>
Tom Lane wrote:
> "Shoaib Mir" <shoaibmir@gmail.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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: guyr-ml1@burntmail.com
Subject: Re: High update activity, PostgreSQL vs BigDBMS
In-Reply-To: <45976627.5090201@burntmail.com>
* 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