Re: Multi Master Replication - Mailing list pgsql-general

On Fri, Dec 20, 2013 at 8:48 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Thu, Dec 19, 2013 at 11:18 PM, Chris Travers <chris.travers@gmail.com> wrote:
>>
>>
>>
>> On Thu, Dec 19, 2013 at 6:52 AM, Wolfgang Keller <feliphil@gmx.net> wrote:
>>>
>>> > 2.  With sync replication, you have coordination problems and
>>> > therefore it is never (at least IME) a win compared to master-slave
>>> > replication since all writes must occur in the same order in the set,
>>> > or you need global sequences, or such.
>> I am not quite sure what the point is.   I am not sure you will get the same
>> write extensibility if you list every table as replicated instead of
>> partitioned.  What Postgres-XC gives you ideally is a no-storage and
>> multi-master coordination layer on top of master-slave data nodes.  Some
>> things may need to be replicated multi-master between data nodes but that's
>> not a win write throughput-wise.
> You'd kill the write scalability of the application by marking all the
> tables as replicated. The communication between nodes uses SQL
> strings, so a DML on a replicated table needs to occur on all the
> nodes, and on top of that you need 2PC for a transaction commit if
> more than 2 nodes are involved in write operations in this
> transaction.
>
>> I am btw a reasonable fan of Postgres-XC within its problem domain, but it
>> is not a synchronous multi-master replication solution as far as write
>> scaling goes.
> OLTP applications that have a schema tunable for replication/partition
> to maximize join pushdown might be a good definition of the
> application range that could benefit from XC.
>
>> My point still holds, which is that synchronous multi-master replication
>> will never beat master-slave in write throughput.  My understanding of
>> Postgres-XC is that you'd mark tables as replicated (instead of partitioned)
>> when they are going to be joined against by different nodes and infrequently
>> updated (and hence the write overhead is less of a problem than the
>> cross-node join overhead).
> Yep, exactly. Those tables are actually master tables and the point is
> to maximize the number of join clause push down to minimize the amount
> of data exchanged between the nodes because of the shared-nothing
> infrastructure. The type of tables that should be marked as
> partitioned is the once that keep growing and need to scale of the
> type "user" tables. This is actually how DBT-1 has been tuned when
> doing scaling testing with it: partition user and adress tables,
> replicate stock and item tables.
I actually wrote something stupid here, stock is partitioned and it
makes sense as it faces lot of updates:
http://images.wikia.com/postgresxc/images/6/66/PG-XC_Architecture.pdf (page 23)
Thanks to Chris for pointing that out.
--
Michael