Re: Multi Master Replication - Mailing list pgsql-general

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.

Regards,
--
Michael