Message-ID: <4C2A9C99.40303@2ndquadrant.com>
Date: Wed, 30 Jun 2010 02:23:37 +0100
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Craig James <craig_james@emolecules.com>
CC: pgsql-performance@postgresql.org
Subject: Re: pgbench results on a new server
References: <k2pda0294261004061437p9cd3810dqe9ce528b6ddb0873@mail.gmail.com>
	<m2m603c8f071004071413w1f2d2497hbbe07d9c6491848f@mail.gmail.com>
	<4BBCFB12.1010306@emolecules.com>
	<y2r72dbd3151004071556j22244233l8fa1db295b7f9cd3@mail.gmail.com>
	<r2q603c8f071004071747n3e56d12bo11c49885b8c2eb2e@mail.gmail.com>
	<4BBD3A17.90209@emolecules.com>
	<y2l72dbd3151004071950kfe09a98fqf0c9a136375260e2@mail.gmail.com>
	<v2u603c8f071004072007t323fe469lfdd8fa5f2a283890@mail.gmail.com>
	<4C23FD52.40508@emolecules.com> <23596.1277438692@sss.pgh.pa.us>
	<4C24B3BB.3070901@emolecules.com> <2286.1277477230@sss.pgh.pa.us>
	<4C24D679.7030701@emolecules.com> <6203.1277485291@sss.pgh.pa.us>
	<4C24FB0D.4020701@emolecules.com>
	<4C24FD82.4070100@2ndquadrant.com>
	<4C28D809.7070707@emolecules.com>
In-Reply-To: <4C28D809.7070707@emolecules.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Craig James wrote:
> synchronous_commit = off 
> full_page_writes = off

I don't have any numbers handy on how much turning synchronous_commit 
and full_page_writes off improves performance on a system with a 
battery-backed write cache.  Your numbers are therefore a bit inflated 
against similar ones that are doing a regular sync commit.  Just 
something to keep in mind when comparing against other people's results.

Also, just as a general comment, increase in work_mem and 
effective_cache_size don't actually do anything to the built-in pgbench 
test results.

>> General numbers are OK, the major drop going from 30 to 40 clients is
>> larger than it should be. I'd suggest running the 40 client count one
>> again to see if that's consistent.
>
> It is consistent.  When I run pgbench from a different server, I get 
> this:
>
>    pgbench -c40 -t 2500 -U test
>    tps = 7999
>
>    pgbench -c100 -t 1000 -U test
>    tps = 6693

Looks like you're just running into the limitations of the old pgbench 
code failing to keep up with high client count loads when run on the 
same system as the server.  Nothing to be concerned about--that the drop 
is only small with the pgbench client remote says there's not actually a 
server problem here.

With that sorted out, your system looks in the normal range for the sort 
of hardware you're using.  I'm always concerned about the potential 
reliability issues that come with async commit and turning off full page 
writes though, so you might want to re-test with those turned on and see 
if you can live with the results.

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