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
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