From: Joshua D. Drake <jd@commandprompt.com>
To: femski <hypertree@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres batch write very slow - what to do
Date: Fri, 16 Mar 2007 07:00:47 -0700
Message-ID: <45FAA30F.4090401@commandprompt.com> (raw)
In-Reply-To: <9452092.post@talk.nabble.com>
References: <9452092.post@talk.nabble.com>
femski wrote:
> Folks !
>
> I have a batch application that writes approx. 4 million rows into a narrow
> table. I am using JDBC addBatch/ExecuteBatch with auto commit turned off.
> Batch size is 100. So far I am seeing Postgres take roughly five times the
> time it takes to do this in the Oracle.
If you are using 8.2 could you try with the multi value inserts?
insert into foo(bar) values (bang) (bong) (bing) ...?
>
> I have played with many parameters. Only one that seems to have any affect
> is fsync - but thats only 10% or so.
> Initially I got the warning that checkpoints were happening too often so I
> increased the segments to 24. Warnings stopped, but no real improvement in
> performance.
>
> Is postgres really that slow ? What am I missing ?
>
> Here are the changes to my postgressql.cong file.
>
> shared_buffers = 768MB
> work_mem = 256MB
> maintenance_work_mem = 128MB
> fsync = off
>
> checkpoint_segments = 24
> autovacuum = on
>
> Thank you,
>
> -Sanjay
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
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: jd@commandprompt.com, hypertree@yahoo.com
Subject: Re: Postgres batch write very slow - what to do
In-Reply-To: <45FAA30F.4090401@commandprompt.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