From: Gregory Stark <stark@enterprisedb.com>
To: "Greg Smith" <gsmith@gregsmith.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Postgres Benchmark Results
In-Reply-To: <Pine.GSO.4.64.0705222325530.6041@westnet.com> (Greg Smith's
	message of "Tue, 22 May 2007 23:48:33 -0400 (EDT)")
Organization: EnterpriseDB
References: <op.tsmrv71ecigqcu@apollo13> <46508CB9.1040404@cybertec.at>
	<20070521210125.GM62346@nasby.net>
	<46523925.9080901@g2switchworks.com>
	<20070522004737.GK6111@alvh.no-ip.org>
	<87lkfhl1xv.fsf@oxford.xeocode.com>
	<Pine.GSO.4.64.0705222325530.6041@westnet.com>
Date: Wed, 23 May 2007 09:31:26 +0100
Message-ID: <87r6p87xgh.fsf@oxford.xeocode.com>
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Greg Smith" <gsmith@gregsmith.com> writes:

> On Tue, 22 May 2007, Gregory Stark wrote:
>
>> However as mentioned a while back in practice it doesn't work quite right and
>> you should expect to get 1/2 the expected performance. So even with 10 clients
>> you should expect to see 5*120 tps on a 7200 rpm drive and 5*250 tps on a
>> 15kprm drive.
>
> I would agree that's the approximate size of the upper-bound.  There are so
> many factors that go into the effectiveness of commit_delay that I wouldn't
> word it so strongly as to say you can "expect" that much benefit. The exact
> delay amount (which can be hard to set if your client load varies greatly),
> size of the transactions, balance of seek-bound reads vs. memory based ones in
> the transactions, serialization in the transaction stream, and so many other
> things can slow the effective benefit.

This is without commit_delay set at all. Just the regular WAL sync behaviour.

> Also, there are generally other performance issues in the types of systems you
> would think would get the most benefit from this parameter that end up slowing
> things down anyway.  I've been seeing a best case of closer to 2*single tps
> rather than 5* on my single-drive systems with no write caching, but I'll admit
> I haven't done an exhausting look at it yet (too busy with the real systems
> that have good controllers).  One of these days...

Certainly there can be other bottlenecks you reach before WAL fsyncs become
your limiting factor. If your transactions are reading significant amounts of
data you'll be limited by i/o from your data drives. If your data is on the
same drive as your WAL your seek times will be higher than the rotational
latency too.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com