Message-ID: <4DF24B22.60209@2ndQuadrant.com>
Date: Fri, 10 Jun 2011 12:49:38 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110505 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: how much postgres can scale up?
References: <000601cc2761$a2e3a280$e8aae780$@devshock.com>
In-Reply-To: <000601cc2761$a2e3a280$e8aae780$@devshock.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 06/10/2011 07:29 AM, Anibal David Acosta wrote:
> When 1 client connected postgres do 180 execution per second
> With 2 clients connected postgres do 110 execution per second
> With 3 clients connected postgres do 90 execution per second
>
> Finally with 6 connected clients postgres do 60 executions per second
> (totally 360 executions per second)
>
> While testing, I monitor disk, memory and CPU and not found any overload.
>
> I know that with this information you can figure out somethigns, but in
> normal conditions, Is normal the degradation of performance per connection
> when connections are incremented?
> Or should I spect 180 in the first and something similar in the second
> connection? Maybe 170?
>    

Let's reformat this the way most people present it:

clients tps
1    180
2    220
3    270
6    360

It's common for a single connection doing INSERT statements to hit a 
bottleneck based on how fast the drives used can spin.  That's anywhere 
from 100 to 200 inserts/section, approximately, unless you have a 
battery-backed write cache.  See 
http://wiki.postgresql.org/wiki/Reliable_Writes for more information.

However, multiple clients can commit at once when a backlog occurs.  So 
what you'll normally see in this situation is that the rate goes up 
faster than this as clients are added.  Here's a real sample, from a 
server that's only physically capable of doing 120 commits/second on its 
7200 RPM drive:

clients tps
1     107
2     109
3     163
4     216
5     271
6     325
8     432
10     530
15     695

This is how it's supposed to scale even on basic hardware  You didn't 
explore this far enough to really know how well your scaling is working 
here though.  Since commit rates are limited by disk spin in this 
situation, the situation for 1 to 5 clients is not really representative 
of how a large number of clients will end up working.  As already 
mentioning, turning off synchronous_commit should give you an 
interesting alternate set of numbers.

It's also possible there may be something wrong with whatever client 
logic you are using here.  Something about the way you've written it may 
be acquiring a lock that blocks other clients from executing efficiently 
for example.  I'd suggest turning on log_lock_waits and setting 
deadlock_timeout to a small number, which should show you some extra 
logging in situations where people are waiting for locks.  Running some 
queries to look at the lock data such as the examples at 
http://wiki.postgresql.org/wiki/Lock_Monitoring might be helpful too.

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