Message-ID: <45FAA30F.4090401@commandprompt.com>
Date: Fri, 16 Mar 2007 07:00:47 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
Organization: Command Prompt, Inc.
User-Agent: Thunderbird 1.5.0.9 (X11/20070103)
MIME-Version: 1.0
To: femski <hypertree@yahoo.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Postgres batch write very slow - what to do
References: <9452092.post@talk.nabble.com>
In-Reply-To: <9452092.post@talk.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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/