pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Claus Guttesen <kometen@gmail.com>
To: mlunnon@rwa-net.co.uk
Cc: pgsql-performance@postgresql.org
Subject: Re: Limited performance on multi core server
Date: Wed, 12 Dec 2007 11:45:24 +0100
Message-ID: <b41c75520712120245k40994522g562954e02899a8d2@mail.gmail.com> (raw)
In-Reply-To: <475FB50B.8060701@rwa-net.co.uk>
References: <475FB50B.8060701@rwa-net.co.uk>

> I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
> postgres 7.4.3.  This has been recompiled on the server for 64 stored
> procedure parameters, (I assume this makes postgres 64 bit but are not
> sure).  When the server gets under load from database connections
> executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to
> limit at about 30-35% usage with no iowait reported. If I run a simple
> select at this time it takes 5 seconds, the same query runs in 300
> millis when the server is not under load so it seems that the database
> is not performing well even though there is plenty of spare CPU.  There
> does not appear to be large amounts of disk IO and my database is about
> 5.5G so this should fit comfortably in RAM.
>
> changes to postgresql.sql:
>
> max_connections = 500
> shared_buffers = 96000
> sort_mem = 10240
> effective_cache_size = 1000000
>
> Does anyone have any ideas what my bottle neck might be and what I can
> do about it?

You might want to lower shared_buffers to a lower value. Mine is set
at 32768. Is your db performing complex sort? Remember that this value
is per connection. Maby 1024. effective_cache_size should also be
lowered to something like 32768. As far as I understand shared_buffers
and effective_cache_size have to be altered "in reverse", ie. when
lowering one the other can be raised.

HTH.

-- 
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare



view thread (23+ messages)  latest in thread

Message-ID: <b41c75520712120245k40994522g562954e02899a8d2@mail.gmail.com>
Permalink:  ../b41c75520712120245k40994522g562954e02899a8d2@mail.gmail.com/
Also on:    postgresql.org/message-id/b41c75520712120245k40994522g562954e02899a8d2@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: kometen@gmail.com, mlunnon@rwa-net.co.uk
  Subject: Re: Limited performance on multi core server
  In-Reply-To: <b41c75520712120245k40994522g562954e02899a8d2@mail.gmail.com>
  References: <475FB50B.8060701@rwa-net.co.uk> <b41c75520712120245k40994522g562954e02899a8d2@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