Skip site navigation (1) Skip section navigation (2)

Re: write scalability

From: Greg Smith <greg(at)2ndQuadrant(dot)com>
To: Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: write scalability
Date: 2011-07-26 18:02:12
Message-ID: 4E2F0124.3030904@2ndQuadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 07/26/2011 12:33 PM, Pavan Deolasee wrote:
> I think what I am suggesting is that the default pgbench test setup
> would probably not give you a good scalability as number of clients
> are increased and one reason could be the contention in the small
> table. So it might be a good idea to get rid of that and see if we get
> much better scalability and understand other bottlenecks.
>    

You can easily see this form of contention pulling down results when the 
database itself is really small and the overall transaction rate is very 
high.  With Robert using a scale=100, no more than 80 clients, and 
transaction rates peaking at <10K TPS on a 24 core box, I wouldn't 
expect this form of contention to be a large issue.  It may be dropping 
results a few percent, but I'd be surprised if it was any more than that.

It's easy enough to use "-N" to skip the updates to the smaller tellers 
and branches table to pull that out of the way.  TPS will go up, because 
it's doing less per transaction.  That's not necessarily a better test 
case though, it's just a different one.  The regular case includes a lot 
of overwriting the same blocks in the hot branches and tellers tables.  
That effectively pushes a lot more I/O toward being likely to happen at 
checkpoint time.  Those tables rarely have any significant I/O outside 
of the checkpoint in the standard "TPC-B like" scenario, because their 
usage counts stay high most of the time.

Contention for small tables that are being heavily updated is still 
important to optimize too though.  Which type of test makes more sense 
depends on what aspect of performance you're trying to focus on.  I'll 
sometimes do a full pgbench-tools "sweep" (range of multiple scales and 
clients at each scale) in both regular write and "-N" write modes, just 
to collect the slightly different data each provides.  The form of 
checkpoint I/O spike you see at sync time is very different in the two 
cases, but both usage profiles are important to consider and optimize.  
That Robert has started with the regular case doesn't worry me too much 
now that I've seen the parameters he's using, he's not running it in a 
way where I'd expect branch/teller contention to be a major limiting 
factor on the results.

-- 
Greg Smith   2ndQuadrant US    greg(at)2ndQuadrant(dot)com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us


In response to

pgsql-hackers by date

Next:From: Simon RiggsDate: 2011-07-26 18:11:19
Subject: Re: sinval synchronization considered harmful
Previous:From: Alvaro HerreraDate: 2011-07-26 17:52:09
Subject: Re: sinval synchronization considered harmful

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group