Message-ID: <4C13F225.5020400@2ndquadrant.com>
Date: Sat, 12 Jun 2010 16:46:29 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Szymon Kosok <szymon@mwg.pl>
CC: pgsql-performance@postgresql.org
Subject: Re: ~400 TPS - good or bad?
References: <AANLkTilrm79Bu5PE2xd7K9nvTnE-Rq6Fy5a6XaHVfVWr@mail.gmail.com>
	<AANLkTileyxtnjSV0S-gyfhetrQZrOuG5FyHGC8NKY40k@mail.gmail.com>
In-Reply-To: <AANLkTileyxtnjSV0S-gyfhetrQZrOuG5FyHGC8NKY40k@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Szymon Kosok wrote:
> I've found in mailing list archive that scale = 1 is not good idea. So
> we have ran pgbench -s 200 (our database is ~3 GB) -c 10 -t 3000 and
> get about ~600 TPS. Good or bad?
>   
pgbench in its default only really tests commit rate, and often that's 
not what is actually important to people.  Your results are normal if 
you don't have a battery-backed RAID controller.  In that case, your 
drives are only capable of committing once per disk rotation, so if you 
have 7200RPM drives that's no more than 120 times per second.  On each 
physical disk commit, PostgreSQL will include any other pending 
transactions that are waiting around too.  So what I suspect you're 
seeing is about 100 commits/second, and on average 6 of the 10 clients 
have something ready to commit each time.  That's what I normally see 
when running pgbench on regular hard drives without a RAID controller, 
somewhere around 500 commits/second.

If you change the number of clients to 1 you'll find out what the commit 
rate for a single client is, that should help validate whether my 
suspicion is correct.  I'd expect a fairly linear increase from 100 to 
~600 TPS as your client count goes from 1 to 10, topping out at under 
1000 TPS even with much higher client counts.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us