Re: High concurrency OLTP database performance tuning

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