Date: Fri, 20 Jul 2007 13:05:54 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: pgsql-performance@postgresql.org
Subject: Re: User concurrency thresholding: where do I look?
In-Reply-To: <200707191819.13899.josh@agliodbs.com>
Message-ID: <Pine.GSO.4.64.0707201237070.2776@westnet.com>
References: <200707190828.59088.josh@agliodbs.com>
	<20070719173704.GA12084@alvh.no-ip.org>
	<14009.1184867115@sss.pgh.pa.us> <200707191819.13899.josh@agliodbs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Thu, 19 Jul 2007, Josh Berkus wrote:

> It's a TPCC-like workload, so heavy single-row updates, and the 
> updates/inserts are what's being measured.

There's so much going on with a TPC-C kind of workload.  Has anyone ever 
looked into quantifying scaling for more fundamental operations?  There 
are so many places a complicated workload could get caught up that 
starting there is hard.  I've found it's helpful to see the breaking 
points for simpler operations, then compare how things change as each new 
transaction element is introduced.

As an example, take a look at the MySQL SysBench tool:
http://sysbench.sourceforge.net/docs/

Specifically their "oltp" tests.  Note how you can get a handle on how 
simple selects scale, then simple inserts, then updates, and so on.  The 
only thing I've thought of they missed is testing a trivial operation that 
doesn't even touch the buffer cache ('SELECT 1'?) that would let you 
quantify just general connection scaling issues.

It seems to me that you could narrow the list of possible causes here much 
more quickly if you had a good handle on the upper concurrency of 
lower-level operations.

[Note:  it's possible to run SysBench against a PG database, but the code 
is very immature.  Last time I tried there were plenty of crashes and 
there seemed to be some transaction wrapping issues that caused deadlocks 
with some tests.]

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD