Subject: Re: Sync Rep Design
From: Simon Riggs <simon@2ndQuadrant.com>
To: Hannu Krosing <hannu@2ndquadrant.com>
Cc: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>,
	greg@2ndQuadrant.com, Josh Berkus <josh@postgresql.org>,
	Robert Haas <robertmhaas@gmail.com>,
	Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>,
	pgsql-hackers@postgresql.org
In-Reply-To: <4D1E4897.1050400@2ndquadrant.com>
References: <1293729972.1892.23804.camel@ebony>
	<4D1CC47E.7050207@kaltenbrunner.cc>	<1293735853.1892.24886.camel@ebony>
	<AANLkTi=SvN2KQ1Tzh-tRKcXD0i2FyWFBbbR=kXXdepqN@mail.gmail.com>
	<4D1D8B2F.5070001@2ndquadrant.com> <4D1DAB14.5000800@enterprisedb.com>
	<1293796137.1892.37313.camel@ebony> <4D1DCF5A.7070808@enterprisedb.com>
	<4D1E4897.1050400@2ndquadrant.com>
Content-Type: text/plain; charset="UTF-8"
Date: Sat, 01 Jan 2011 11:54:35 +0000
Message-ID: <1293882875.1892.56143.camel@ebony>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2010-12-31 at 22:18 +0100, Hannu Krosing wrote:
> On 31.12.2010 13:40, Heikki Linnakangas wrote:
> >
> > Sounds good.
> >
> > I still don't like the synchronous_standbys='' and 
> > synchronous_replication=on combination, though. IMHO that still 
> > amounts to letting the standby control the behavior on master, and it 
> > makes it impossible to temporarily add an asynchronous standby to the mix.
> A sync standby _will_have_ the ability to control the master anyway by 
> simply being there or not.
> 
> What is currently proposed is having dual power lines / dual UPS' and 
> working happily on when one of them fails.
> Requiring both of them to be present defeats the original purpose  of 
> doubling them.
> 
> So following Simons design of 2 standbys and only one required to ACK to 
> commit you get 2X reliability of single standby.
...

Yes, working out the math is a good idea. Things are much clearer if we
do that.

Let's assume we have 98% availability on any single server.

1. Having one primary and 2 standbys, either of which can acknowledge,
and we never lock up if both standbys fail, then we will have 99.9992%
server availability. (So PostgreSQL hits "5 Nines", with data
guarantees). ("Maximised availability")

2. Having one primary and 2 standbys, either of which can acknowledge,
and we lock up if both standbys fail to protect the data, then we will
have 99.996% availability. Slightly less availability, but we don't put
data at risk at any time, since any commit is always covered by at least
2 servers. ("Maximised protection")

3. If we have a primary and a single standby which must acknowledge, and
we choose to lock up if the standby fails, then we will have only 96.04%
availability.

4. If we have a primary and two standbys (named or otherwise), both of
which must acknowledge or we lock up the master, then we have an awesome
94.12% availability.

On the last two, there is also an increased likelihood of administrative
cock-ups because of more specific and complex config requirements.

-- 
 Simon Riggs           http://www.2ndQuadrant.com/books/
 PostgreSQL Development, 24x7 Support, Training and Services