Message-ID: <4CEF14BD.7060104@2ndquadrant.com>
Date: Thu, 25 Nov 2010 21:00:29 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Ivan Voras <ivoras@freebsd.org>
CC: pgsql-performance@postgresql.org
Subject: Re: Performance under contention
References: <icccng$kpg$1@dough.gmane.org>
In-Reply-To: <icccng$kpg$1@dough.gmane.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Ivan Voras wrote:
> PostgreSQL 9.0.1, 10 GB shared buffers, using pgbench with a scale 
> factor of 500 (7.5 GB database)
>
> with pgbench -S (SELECT-queries only) the performance curve is:
>
> -c#    result
> 4    33549
> 8    64864
> 12    79491
> 16    79887
> 20    66957
> 24    52576
> 28    50406
> 32    49491
> 40    45535
> 50    39499
> 75    29415

Two suggestions to improve your results here:

1) Don't set shared_buffers to 10GB.  There are some known issues with 
large settings for that which may or may not be impacting your results.  
Try 4GB instead, just to make sure you're not even on the edge of that area.

2) pgbench itself is known to become a bottleneck when running with lots 
of clients.  You should be using the "-j" option to spawn multiple 
workers, probably 12 of them (one per core), to make some of this go 
away.  On the system I saw the most improvement here, I got a 15-25% 
gain having more workers at the higher client counts.

> The "sbwait" part is from FreeBSD - IPC sockets, but so much blocking 
> on semwait indicates large contention in PostgreSQL.

It will be interesting to see if that's different after the changes 
suggested above.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services and Support        www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books