Received: from localhost (unknown [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id B75572E0267
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 10:15:45 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 43109-03 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 10:15:35 -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 80ED02E025D
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Dec 2007 10:15:40 -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 824F77D6210
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Dec 2007 13:56:28 +0000 (GMT)
Received: from [127.0.0.1] by saturn.rwa-net.co.uk (GMS
	14.01.3539/NT4698.00.83343e8d) with ESMTP id etofgaaa for
	pgsql-performance@postgresql.org; Wed, 12 Dec 2007 14:15:38 +0000
Message-ID: <475FED09.2030102@rwa-net.co.uk>
Date: Wed, 12 Dec 2007 14:15:37 +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: Sven Geisler <sgeisler@aeccom.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Limited performance on multi core server
References: <475FB50B.8060701@rwa-net.co.uk> <475FCF1E.7030608@aeccom.com>
In-Reply-To: <475FCF1E.7030608@aeccom.com>
Content-Type: multipart/alternative;
	boundary="------------010807000804080905000207"
X-AuthenticatedSender: mlunnon@rwa-net.co.uk
X-Info: UNKNOWN-USER
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200712/178
X-Sequence-Number: 27994

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

Hi Sven,

Does this mean that one option I have is to use a multi core Intel based 
server instead of an AMD based server?

Matthew

Sven Geisler wrote:
> Hi Matthew,
>
> I remember that I also an issue with AMD Opterons before Pg 8.1
> There is a specific Opteron behaviour on shared memory locks which adds
> a extra "penalty" during the execution time for Pg code before 8.1.
> I can you provide my patch for Pg 8.0 which should be adaptable for Pg
> 7.4 if you can compile PostgreSQL.
>
> But if you can upgrade you should upgrade to Pg 8.2.5 64-bit. The scale
> up for your concurrent queries will be great.
>
> Sven.
>
> Matthew Lunnon schrieb:
>   
>> Hi,
>>
>> 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?
>>
>> Thanks for any help.
>>
>> Matthew.
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>     
>
>   

-- 
Matthew Lunnon
Technical Consultant
RWA Ltd.

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


--------------010807000804080905000207
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">
Hi Sven,<br>
<br>
Does this mean that one option I have is to use a multi core Intel
based server instead of an AMD based server?<br>
<br>
Matthew<br>
<br>
Sven Geisler wrote:
<blockquote cite="mid475FCF1E.7030608@aeccom.com" type="cite">
  <pre wrap="">Hi Matthew,

I remember that I also an issue with AMD Opterons before Pg 8.1
There is a specific Opteron behaviour on shared memory locks which adds
a extra "penalty" during the execution time for Pg code before 8.1.
I can you provide my patch for Pg 8.0 which should be adaptable for Pg
7.4 if you can compile PostgreSQL.

But if you can upgrade you should upgrade to Pg 8.2.5 64-bit. The scale
up for your concurrent queries will be great.

Sven.

Matthew Lunnon schrieb:
  </pre>
  <blockquote type="cite">
    <pre wrap="">Hi,

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?

Thanks for any help.

Matthew.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
    </pre>
  </blockquote>
  <pre wrap=""><!---->
  </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>

--------------010807000804080905000207--