Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 6C7062E013A for ; Wed, 12 Dec 2007 07:06:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 44684-01 for ; Wed, 12 Dec 2007 07:06:18 -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 AFC1B2E013E for ; Wed, 12 Dec 2007 07:06:21 -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 F34937D61DA for ; Wed, 12 Dec 2007 10:47:10 +0000 (GMT) Received: from [127.0.0.1] by saturn.rwa-net.co.uk (GMS 14.01.3539/NT4698.00.83343e8d) with ESMTP id twnfgaaa for pgsql-performance@postgresql.org; Wed, 12 Dec 2007 11:06:19 +0000 Message-ID: <475FC0AB.1080403@rwa-net.co.uk> Date: Wed, 12 Dec 2007 11:06:19 +0000 From: Matthew Lunnon 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 CC: pgsql-performance@postgresql.org Subject: Re: Limited performance on multi core server References: <475FB50B.8060701@rwa-net.co.uk> <475FBC7F.6040707@aeccom.com> In-Reply-To: <475FBC7F.6040707@aeccom.com> Content-Type: multipart/alternative; boundary="------------060704070701030700080802" X-AuthenticatedSender: mlunnon@rwa-net.co.uk X-Info: UNKNOWN-USER X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/166 X-Sequence-Number: 27982 This is a multi-part message in MIME format. --------------060704070701030700080802 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Limiting the queries was our initial thought but we then hit a problem with connection pooling which didn't implement a fifo algorithm. Looks like I'll have to look deeper into the connection pooling. So you think the problem might be context switching on the server, I'll take a closer look at the this Thanks Matthew Sven Geisler wrote: > Hi Matthew, > > I know exactly what you experience. > We had a 4-way DC Opteron and Pg 7.4 too. > You should monitor context switches. > > > First suggest upgrade to 8.2.5 because the scale up is much better with 8.2. > > You need to limit the number of concurrent queries to less than 8 (8 > cores) if you need to stay with Pg 7.4. > > The memory setting is looking good to me. I would increase sort_mem and > effective_cache_size, but this would solve your problem. > > Best regards > 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 -- --------------060704070701030700080802 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Limiting the queries was our initial thought but we then hit a problem with connection pooling which didn't implement a fifo algorithm.  Looks like I'll have to look deeper into the connection pooling.

So you think the problem might be context switching on the server, I'll take a closer look at the this

Thanks

Matthew

Sven Geisler wrote: 
Hi Matthew,

I know exactly what you experience.
We had a 4-way DC Opteron and Pg 7.4 too.
You should monitor context switches.


First suggest upgrade to 8.2.5 because the scale up is much better with 8.2.

You need to limit the number of concurrent queries to less than 8 (8
cores) if you need to stay with Pg 7.4.

The memory setting is looking good to me. I would increase sort_mem and
effective_cache_size, but this would solve your problem.

Best regards
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
--
--------------060704070701030700080802--