Message-ID: <4E3AA382.9050302@ringerc.id.au>
Date: Thu, 04 Aug 2011 21:49:54 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
To: Adarsh Sharma <adarsh.sharma@orkash.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Need to tune for Heavy Write
References: <4E3A268C.9090409@orkash.com>
In-Reply-To: <4E3A268C.9090409@orkash.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 4/08/2011 12:56 PM, Adarsh Sharma wrote:
> Dear all,
>
>  From the last few days, I researched a lot on Postgresql Performance
> Tuning due to slow speed of my server.
> My application selects data from mysql database about 100000 rows ,
> process it & insert into postgres 2 tables by making about 45 connections.

Why 45?

Depending on your disk subsystem, that may be way too many for optimum 
throughput. Or too few, for that matter.

Also, how are you doing your inserts? Are they being done in a single 
big transaction per connection, or at least in resonable chunks? If 
you're doing stand-alone INSERTs autocommit-style you'll see pretty 
shoddy performance.

Have you looked into using COPY to bulk load your data? Possibly using 
the libpq or jdbc copy APIs, or possibly using server-side COPY?

> fsync=off full_page_writes=off synchronous_commit=off

!!!!

I hope you don't want to KEEP that data if you have a hardware fault or 
power loss. Setting fsync=off is pretty much saying "I don't mind if you 
eat my data".

Keep. Really. Really. Good. Backups.

--
Craig Ringer