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@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
