Received: from localhost (unknown [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id A56D32E3A4F
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 07:12:49 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 07248-04-2
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 07:12:45 -0400 (AST)
Received: from mail-relay.rwa-net.co.uk (mail-relay.rwa-net.co.uk
	[62.189.139.18])
	by postgresql.org (Postfix) with ESMTP id DEE262E39DC
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Dec 2007 07:12:44 -0400 (AST)
Received: from saturn.rwa-net.co.uk (saturn.rwa-net.co.uk [62.189.139.33])
	by mail-relay.rwa-net.co.uk (Postfix) with ESMTP id 2DF627D61E1
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Dec 2007 10:53:34 +0000 (GMT)
Received: from [127.0.0.1] by saturn.rwa-net.co.uk (GMS
	14.01.3539/NT4698.00.83343e8d) with ESMTP id nynfgaaa for
	pgsql-performance@postgresql.org; Wed, 12 Dec 2007 11:12:42 +0000
Message-ID: <475FC22A.2070505@rwa-net.co.uk>
Date: Wed, 12 Dec 2007 11:12:42 +0000
From: Matthew Lunnon <mlunnon@rwa-net.co.uk>
Reply-To: mlunnon@rwa-net.co.uk
Organization: RWA
User-Agent: Thunderbird 1.5.0.13 (Windows/20070809)
MIME-Version: 1.0
To: Claus Guttesen <kometen@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Limited performance on multi core server
References: <475FB50B.8060701@rwa-net.co.uk>
	<b41c75520712120245k40994522g562954e02899a8d2@mail.gmail.com>
In-Reply-To: <b41c75520712120245k40994522g562954e02899a8d2@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------090409080004080002040703"
X-AuthenticatedSender: mlunnon@rwa-net.co.uk
X-Info: UNKNOWN-USER
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200712/168
X-Sequence-Number: 27984

This is a multi-part message in MIME format.
--------------090409080004080002040703
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thanks for the information Claus,  Why would reducing the effective 
cache size help the processor usage?  It seems that there is plenty of 
resources on the box although I can see that 10MB of sort space could 
mount up if we had 500 connections but at the moment we do not have 
anything like that number.

Thanks
Matthew.

Claus Guttesen wrote:
>> 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.
>
>   

-- 
Matthew Lunnon
Technical Consultant
RWA Ltd.

 mlunnon@rwa-net.co.uk
 Tel: +44 (0)29 2081 5056
 www.rwa-net.co.uk
--


--------------090409080004080002040703
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Thanks for the information Claus,&nbsp; Why would reducing the effective
cache size help the processor usage?&nbsp; It seems that there is plenty of
resources on the box although I can see that 10MB of sort space could
mount up if we had 500 connections but at the moment we do not have
anything like that number.<br>
<br>
Thanks<br>
Matthew.<br>
<br>
Claus Guttesen wrote:
<blockquote
 cite="midb41c75520712120245k40994522g562954e02899a8d2@mail.gmail.com"
 type="cite">
  <blockquote type="cite">
    <pre wrap="">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?
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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.

  </pre>
</blockquote>
<br>
<pre class="moz-signature" cols="72">-- 
Matthew Lunnon
Technical Consultant
RWA Ltd.

 <a class="moz-txt-link-abbreviated" href="mailto:mlunnon@rwa-net.co.uk">mlunnon@rwa-net.co.uk</a>
 Tel: +44 (0)29 2081 5056
 <a class="moz-txt-link-abbreviated" href="http://www.rwa-net.co.uk">www.rwa-net.co.uk</a>
--</pre>
</body>
</html>

--------------090409080004080002040703--