Re: Need to tune for Heavy Write

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