Обсуждение: Multi Master Replication

On 12/18/2013 1:31 AM, itishree sukla wrote:
> I need suggestion about setting up multi master replication between
> two postgresql server place two different geographical area. As i know
> using some third party tool like Bucardo,RubyRep it can be achievable,
> not sue which is the good one to use. If any one  can provide me some
> online documentation links, it will help me as well.

that sort of replication is very problematic.   its virtually impossible
to maintain ACID (Atomicity, Consistency, Isolation, Durability) and
maintain any semblance of performance.

question for you, what do you expect to happen if the communications
link between the servers is interrupted, and updates continue to be sent
to both servers?

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On Wed, Dec 18, 2013 at 1:16 PM, John R Pierce <pierce@hogranch.com> wrote:
> On 12/18/2013 1:31 AM, itishree sukla wrote:
>>
>> I need suggestion about setting up multi master replication between two
>> postgresql server place two different geographical area. As i know using
>> some third party tool like Bucardo,RubyRep it can be achievable, not sue
>> which is the good one to use. If any one  can provide me some online
>> documentation links, it will help me as well.
>
>
> that sort of replication is very problematic.   its virtually impossible to
> maintain ACID (Atomicity, Consistency, Isolation, Durability) and maintain
> any semblance of performance.
>
> question for you, what do you expect to happen if the communications link
> between the servers is interrupted, and updates continue to be sent to both
> servers?

When people start talking multi-master replication my first response
is to ask what problem you're trying to solve. Sometimes MM Rep IS the
answer. But quite often it's not the best one for your problem. So to
OP I'd ask what problem they're trying to solve.

--
To understand recursion, one must first understand recursion.

On Thu, Dec 19, 2013 at 7:12 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, Dec 18, 2013 at 1:16 PM, John R Pierce <pierce@hogranch.com> wrote:
>> that sort of replication is very problematic.   its virtually impossible to
>> maintain ACID (Atomicity, Consistency, Isolation, Durability) and maintain
>> any semblance of performance.
Yep, there's usually a trade-off between performance and data
consistency. OLTP applications can benefit from MM with a
shared-nothing architecture, more than data warehouse type of things
that need to transfer a lot of data for join operations, or SQL
operations that use non-pushable clauses (for example stable/volatile
functions).

>> question for you, what do you expect to happen if the communications link
>> between the servers is interrupted, and updates continue to be sent to both
>> servers?
Split-brain is another problem, hard to solve. Even harder if you have
several types nodes in your cluster dedicated to provide some piece
building the MM system.

> When people start talking multi-master replication my first response
> is to ask what problem you're trying to solve. Sometimes MM Rep IS the
> answer. But quite often it's not the best one for your problem. So to
> OP I'd ask what problem they're trying to solve.
Yes that's actually the right approach, multi-master replication is
often cited as a marketing term for a fantastic technology that can
solve a lot of problems, which could be solved with a couple of
Postgres servers using a single-master, multiple-slave approach, or by
simply design a system that can do data sharding among a set of
Postgres servers to achieve some kind of write scalability.

Regards,
--
Michael

On Wed, Dec 18, 2013 at 10:25 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:

> On Thu, Dec 19, 2013 at 7:12 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

>> When people start talking multi-master replication my first response
>> is to ask what problem you're trying to solve. Sometimes MM Rep IS the
>> answer. But quite often it's not the best one for your problem. So to
>> OP I'd ask what problem they're trying to solve.

> Yes that's actually the right approach, multi-master replication is
> often cited as a marketing term for a fantastic technology that can
> solve a lot of problems, which could be solved with a couple of
> Postgres servers using a single-master, multiple-slave approach, or by
> simply design a system that can do data sharding among a set of
> Postgres servers to achieve some kind of write scalability.

Sharding with plproxy is pretty easy and can scale hugely.

On Thu, Dec 19, 2013 at 2:05 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> Sharding with plproxy is pretty easy and can scale hugely.
Yeah indeed, the writable postgres_fdw could also be used as a
solution, if designed carefully.
--
Michael

> 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.

*snip*

>  You will never get better read or write throughput,

Better read throughput is trivial to achieve even with other solutions
than multi-master replication.

And for better write throughput, the developers of Postgres-XC
(supported by NTT, among others) beg to differ:

http://postgresxc.wikia.com/wiki/Postgres-XC_Wiki
http://postgres-xc.sourceforge.net/

As does Bettina Kemme (of Postgres-R fame).

Sincerely,

Wolfgang

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

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

14 replies so far, and the OP hasn't chimed in with any feedback as to
what their presumed requirements are based on.


*meh*

Sometime ago i was looking for something like this and because at this time
XC was a little baby i tried installing bucardo but i gave up when stucked
fighting with perl modules. So, after testing some other solutions i decided
to make my own, just "touching" the trigger part of the pyreplica project
and building a daemon in a binary compiled program (no script) for a better
performance. It's working well by now, with some tables and few nodes (12
nodes deployed across long distances), but i was lucky then because the
database and the systems were still in design and development phases.
There are many things you have to keep in mind, as all people is warning
you. I tell you, it can be done, but you MUST redesign your existing
database preparing for multimaster paradigm where the key is avoid node
level concurrency, and your client database apps to THINK what to do when
nodes are down, but if you are not ready to prepare your database (design)
or you have not access to the source code of your client database apps then
you have problems.
I suggest you read this
http://www.dbspecialists.com/files/presentations/mm_replication.html and
this
http://scale-out-blog.blogspot.com/2012/04/if-you-must-deploy-multi-master.html.
Finally, i can mention another posible solution that i never tried but maybe
can help you, SymmetricDS.
Good luck.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Multi-Master-Replication-tp5783855p5784468.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

We use symmetricDS pretty extensively, across oracle and postgres
databases. It has its flaws and its strengths. It shines when there's
multiple database platforms involved, when the volume of transactions is
not too high, and supports multi master. Its optimized for wan topologies,
so its great if you have small amounts of data over longer distances, not
as great when there's several gigabytes needing transfer within the same
datacenter. It also has inbuilt insert vs update conflict resolution.

On the flip side, as the volume of writes increases, it can quickly lead
to significant database bloat and high CPU usage. Its also not as fast as
some other tools (like slony, or ora2pg) for initial loads of tables.

It exposes all of its switches and knobs (and there are a *lot* of
tunables), which make it both very flexible, but also prone to breakage if
you fiddle with it too much. Its in active development and we've found the
community forums to be pretty helpful when we run into undocumented bugs.

Let me know if you need help with it.

Thanks,
Karthik