Re: write scalability

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