From: Craig Ringer <ringerc@ringerc.id.au>
To: Adarsh Sharma <adarsh.sharma@orkash.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need to tune for Heavy Write
Date: Thu, 04 Aug 2011 21:49:54 +0800
Message-ID: <4E3AA382.9050302@ringerc.id.au> (raw)
In-Reply-To: <4E3A268C.9090409@orkash.com>
References: <4E3A268C.9090409@orkash.com>
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
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: ringerc@ringerc.id.au, adarsh.sharma@orkash.com
Subject: Re: Need to tune for Heavy Write
In-Reply-To: <4E3AA382.9050302@ringerc.id.au>
* 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