Re: Sync Rep Design

On 12/31/2010 11:06 AM, Heikki Linnakangas wrote:
> On 31.12.2010 09:50, Hannu Krosing wrote:
>> On 30.12.2010 22:27, Robert Haas wrote:
>>> On Thu, Dec 30, 2010 at 2:04 PM, Simon Riggs<simon(at)2ndquadrant(dot)com>
>>> wrote:
>>>> synchronous_replication (boolean)
>>>> Specifies whether transaction commit will wait for WAL records
>>>> to be replicated before the command returns a "success"
>>>> indication to the client.
>>> The word "replicated" here could be taken to mean different things,
>>> most obviously:
>>>
>>> - slave has received the WAL
>>> - slave has fsync'd the WAL
>>> - slave has applied the WAL
>> Perhaps the level of "replication guarantee" should be decided on the
>> slave side, by
>> having a configuration parameter there
>>
>> report_as_replicated = received|written_to_disk|fsynced|applied
>>
>> for different types of hosts may have wildly different guarantees and
>> performance
>> parameters for these. One could envision a WAL-archive type "standby"
>> which is
>> there for data persistence only will and never "apply" WAL.
>
> Agreed, it feels natural to specify when a piece of WAL is acknowledged
> in the standby.
>
> Regarding the rest of the proposal, I would still prefer the UI
> discussed here:
>
> http://archives.postgresql.org/message-id/4CAE030A.2060701@enterprisedb.com
>
> It ought to be the same amount of work to implement, and provides the
> same feature set, but makes administration a bit easier by being able to
> name the standbys. Also, I dislike the idea of having the standby
> specify that it's a synchronous standby that the master has to wait for.
> Behavior on the master should be configured on the master.

well that proposal is much closer to what I want as an admin - except
that it would be nice to configure that through actual DDL.
My wish would be more like:

* standby provides a unique name identifier
* standby has a flag to say the maximum(or minimum?)
replication_reported support it can do
* standby connects to the master async by default and the master
registers the standby automatically
* on the master I can do the following with every standby that is
visible to the master or has been in the past:
* enable/disable and add/remove permanently(if not added permanently
the registration is transient) - enabled if not set explicitly
* sync_rep_enabled (boolean) it (so you can still do per transaction or
per database or whatever sync rep) - disabled if not set explicitly
* sync_reply_required (booleant), (per sync standby flag to require a
reply before the master returns - if there is only one sync standby this
is default behaviour if there are more the admin can choose)
* wait_forever or timeout per standby
* maybe a way to set the report_as_replicated from the master (if
feasible) up to the max of what the standby can do

so you would get the proposed "semi sync rep" mode by simply setting
more than one standby as "sync_rep_enabled" and sync_reply_required is
false for all of them (ie any one of them can reply and the master
returns) - if you want better than that just require a reply from a
specific one or more than one.

this would also help in us providing a simple view with a nice and handy
status report on the slaves (which ones are there, which ones should be
there, how far are they in terms of applying wal, what status do they have).

Imho an interface like this would be:

a) convinient because it would not require any additional setup
requirements for async rep except providing a "name" on the standby by
default
b) it would enable the master to specify the business rules clearly
c) would still support the simple "one sync reply is enough" semisync
replication case people like to have
d) would also enable the admin to get more than ONE sync standby that is
really sync - not maybe sync
e) hot_standby_feedback (if enabled) would look at only the permanently
enabled slaves so only an "DBA approved" standby would be able to affect
the master for table bloat
f) provide the necessary meta information for providing the handy "quick
& nice" replication status overview reporting feature people want and need
g) for all the permanently enabled async nodes we could keep track of
the required oldest required WAL and keep that (optionally) so we could
get rid of the hard to size max_keep_segements and maintain that
automatically.

the only disadvantage I can see would be that you would have to manually
remove a non-functional slave from the master(and only one that you set
some explicit configuration for!) if you decide you don't need it any more.

Stefan