Message-ID: <4BF5B582.1030809@2ndquadrant.com>
Date: Thu, 20 May 2010 18:19:46 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Balkrishna Sharma <b_ki@hotmail.com>
CC: pgsql-admin@postgresql.org
Subject: Re: Asynchronous commit | Transaction loss at server crash
References: <BAY149-w314344813824161D97E691F0E30@phx.gbl>
In-Reply-To: <BAY149-w314344813824161D97E691F0E30@phx.gbl>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Balkrishna Sharma wrote:
> I need to support several hundreds of concurrent update/inserts from 
> an online form with pretty low latency (maybe couple of milliseconds 
> at max). Think of a save to database at every 'tab-out' in an online form.

I regularly see 2000 - 4000 small write transactions per second on 
systems with a battery-backed write cache and a moderate disk array 
attached.  2000 TPS = 0.5 ms, on average.  Note however that it's 
extremely difficult to bound the worst-case behavior possible here 
anywhere near that tight.  Under a benchmark load I can normally get 
even an extremely tuned Linux configuration to occasionally pause for 
1-3 seconds at commit time, when the OS write cache is full, a 
checkpoint is finishing, and the client doing the commit is stuck 
waiting for that.  They're rare but you should expect to see that 
situation sometimes.

We know basically what causes that and how to make it less likely to 
happen in a real application.  But the possibility is still there, and 
if your design cannot tolerate an occasional latency uptick you may be 
disappointed because that's very, very hard to guarantee with the 
workload you're expecting here.  There are plenty of ideas for how to 
tune in that direction both at the source code level and by carefully 
selecting the OS/filesystem combination used, but that's not a very well 
explored territory.  The checkpoint design in the database has known 
weaknesses in this particular area, and they're impossible to solve just 
by throwing hardware at the problem.

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