From: Merlin Moncure <mmoncure@gmail.com>
To: Bob Dusek <bob@copienttech.com>
Cc: femski <hypertree@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres batch write very slow - what to do
Date: Fri, 16 Mar 2007 08:30:40 -0500
Message-ID: <b42b73150703160630g75f2b2a8he2a197d7598aeec9@mail.gmail.com> (raw)
In-Reply-To: <1F3542D36E2E6C4780CDA3AC0102BF70129777@mail01.copienttech.com>
References: <1F3542D36E2E6C4780CDA3AC0102BF70129777@mail01.copienttech.com>
On 3/16/07, Bob Dusek <bob@copienttech.com> wrote:
> This may or may not be related to what you're seeing... but, when we
> changed from Postgres 7.4.2 to 7.4.8, our batch processing slowed down
> fairly significantly.
>
> Here's what we were doing:
>
> Step 1) Build a larg file full of SQL insert statements.
> Step 2) Feed the file directly to "psql" using "psql dbname <
> insertfile".
>
> The time of execution for step 2 seemed like it nearly doubled from
> 7.4.2 to 7.4.8, for whatever reason (could have been the way Suse
> compiled the binaries). Perhaps the slowdown was something we could
> have/should have tweaked with config options.
> At any rate, what we did to speed it up was to wrap the entire file in a
> transaction, as such: "BEGIN; ..filecontents.. COMMIT;"
>
> Apparently the autocommit stuff in the version of 7.4.8 we were using
> was just *doggedly* slow.
>
> Perhaps you're already using a transaction for your batch, though. Or,
> maybe the problem isn't with Postgres. Just thought I'd share.
If you are inserting records one by one without transaction (and no
fsync), i/o is going to determine your insertion speed. not really
sure what was happening in your case...it looks like quite a different
type of issue from the OP.
anyways, to the OP some quick googling regarding postgresql jdbc
driver showed that the batch insert case is just not as optimized (in
the driver) as it could be. The driver could do multi statement
inserts or use the libpq copy api, either of which would result in
huge performance gain.
merlin
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: mmoncure@gmail.com, bob@copienttech.com, hypertree@yahoo.com
Subject: Re: Postgres batch write very slow - what to do
In-Reply-To: <b42b73150703160630g75f2b2a8he2a197d7598aeec9@mail.gmail.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