Re: Need to tune for Heavy Write

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Adarsh Sharma" <adarsh(dot)sharma(at)orkash(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Need to tune for Heavy Write
Date: 2011-08-04 13:57:30
Message-ID: 4E3A5EFA020000250003FA4E@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andy Colson 2011-08-04 13:57:59 Re: Parameters for PostgreSQL
Previous Message Craig Ringer 2011-08-04 13:49:54 Re: Need to tune for Heavy Write