pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Merlin Moncure <mmoncure@gmail.com>
To: Cosimo Streppone <cosimo@streppone.it>
Cc: Pg Performance list <pgsql-performance@postgresql.org>
Subject: Re: High concurrency OLTP database performance tuning
Date: Thu, 31 Aug 2006 13:42:06 -0400
Message-ID: <b42b73150608311042y12afc09dkfc3c77412d250d5c@mail.gmail.com> (raw)
In-Reply-To: <44F7040E.7040805@streppone.it>
References: <44F7040E.7040805@streppone.it>

On 8/31/06, Cosimo Streppone <cosimo@streppone.it> wrote:
> Good morning,
> - postgresql.conf, especially:
>       effective_cache_size (now 5000)
>       bgwriter_delay (500)
>       commit_delay/commit_siblings (default)

while thse settings may help, don't expect too much.  ditto shared
buffers.  your fsync is false btw.  the major gotcha in high
transaction volume systems is stats_command_string (leave it off).

> - start to use tablespaces for most intensive tables
this is an i/o optimization mostly.  again, dont expect much.

> - analyze the locks situation while queries run
> - upgrade to 8.1.n
absolutely you want to do this.  when I moved my converted isam
projects which dont sound too far from your workload, I saw a huge
speed increase with 8.1.

> - convert db partition filesystem to ext2/xfs?
>    (now ext3+noatime+data=writeback)
> - ???

meh. :-)

I think application level improvements are the name of the game here.
Make sure your application or middleware is using the parameterized
query interface in libpq.

Another possible optimiation is to attempt application level caching
in conjunction with some server side locking,  Since details are
light, only general hints are possible :)

consider move to opteron or intel woodcrest platform. a single opteron
170 will easily beat your two xeons, and 2x270 will be a whole new
world.  woodcrests are great as well if you can get them.

also, if you are not already on a *nix kernel, get yourself on one.

Merlin



view thread (6+ messages)  latest in thread

Message-ID: <b42b73150608311042y12afc09dkfc3c77412d250d5c@mail.gmail.com>
Permalink:  ../b42b73150608311042y12afc09dkfc3c77412d250d5c@mail.gmail.com/
Also on:    postgresql.org/message-id/b42b73150608311042y12afc09dkfc3c77412d250d5c@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: mmoncure@gmail.com, cosimo@streppone.it
  Subject: Re: High concurrency OLTP database performance tuning
  In-Reply-To: <b42b73150608311042y12afc09dkfc3c77412d250d5c@mail.gmail.com>
  References: <44F7040E.7040805@streppone.it> <b42b73150608311042y12afc09dkfc3c77412d250d5c@mail.gmail.com>

* 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