Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
> Postgres : 8.4.2
You should definitely update to a more recent bug patch level:
http://www.postgresql.org/support/versioning
> RAM : 16 GB
> effective_cache_size = 4096MB
That should probably be more like 12GB to 15GB. It probably won't
affect the load time here, but could affect other queries.
> My application selects data from mysql database about 100000
> rows process it & insert into postgres 2 tables by making about 45
> connections.
How many cores do you have? How many disk spindles in what sort of
array with what sort of controller.
Quite possibly you can improve performance dramatically by not
turning loose a "thundering herd" of competing processes.
Can you load the target table without indexes and then build the
indexes?
Can you use the COPY command (or at least prepared statements) for
the inserts to minimize parse/plan time?
An important setting you're missing is:
wal_buffers = 16MB
-Kevin