From: Greg Smith <greg@2ndquadrant.com>
To: Balkrishna Sharma <b_ki@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Asynchronous commit | Transaction loss at server crash
Date: Thu, 20 May 2010 18:19:46 -0400
Message-ID: <4BF5B582.1030809@2ndquadrant.com> (raw)
In-Reply-To: <BAY149-w314344813824161D97E691F0E30@phx.gbl>
References: <BAY149-w314344813824161D97E691F0E30@phx.gbl>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: greg@2ndquadrant.com, b_ki@hotmail.com
Subject: Re: Asynchronous commit | Transaction loss at server crash
In-Reply-To: <4BF5B582.1030809@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox