Обсуждение: proposal: multiple read-write masters in a cluster with wal-streaming synchronization

Mark Dilger wrote:
> This is not entirely "pie in the sky", but feel free to tell me why this is crazy.

Have you seen http://wiki.postgresql.org/wiki/BDR ?

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2013-12-31 13:51:08 -0800, Mark Dilger wrote:
> The BDR documentation http://wiki.postgresql.org/images/7/75/BDR_Presentation_PGCon2012.pdf
> says,
>
>     "Physical replication forces us to use just one
>      node: multi-master required for write scalability"
>
>     "Physical replication provides best read scalability"
>
> I am inclined to agree with the second statement, but
> I think my proposal invalidates the first statement, at
> least for a particular rigorous partitioning over which
> server owns which data.

I think you *massively* underestimate the amount of work implementing
this would require.
For one, you'd need to have a catalog that is written to on only one
server, you cannot have several nodes writing to the same table, even if
it's to disparate oid ranges. So you'd need to partition the whole
catalog by oid ranges - which would be a major efficiency loss for many,
many cases.

Not to speak of breaking pg_upgrade and noticeably increasing the size
of the catalog due to bigger oids and additional relations.

> So for me, multi-master with physical replication seems
> possible, and would presumably provide the best
> read scalability.

What you describe isn't really multi master though, as every row can
only be written to by a single node (the owner).

Also, why would this have a better read scalability? Whether a row is
written by streaming rep or not doesn't influence read speed.

> Or I can use logical replication such as BDR, but then the servers
> are spending more effort than with physical replication,
> so I get less bang for the buck when I purchase more
> servers to add to the cluster.

The efficiency difference really hasn't to be big if done right. If
you're so write-heavy that the difference is becoming a problem you
wouldn't implement a shared-everything architecture anyway.

> Am I missing something here?  Does BDR really provide
> an equivalent solution?

Not yet, but the plan is to get there.
> Second, it seems that BDR leaves to the client the responsibility
> for making schemas the same everywhere.  Perhaps this is just
> a limitation of the implementation so far, which will be resolved
> in the future?

Hopefully something that's going to get lifted.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

On Tue, Dec 31, 2013 at 3:51 PM, Mark Dilger <markdilger@yahoo.com> wrote:
> The BDR documentation
> http://wiki.postgresql.org/images/7/75/BDR_Presentation_PGCon2012.pdf
> says,
>
>     "Physical replication forces us to use just one
>      node: multi-master required for write scalability"
>
>     "Physical replication provides best read scalability"
>
> I am inclined to agree with the second statement, but
> I think my proposal invalidates the first statement, at
> least for a particular rigorous partitioning over which
> server owns which data.
>
> In my own workflow, I load lots of data from different
> sources.  The partition the data loads into depends on
> which source it came from, and it is never mixed or
> cross referenced in any operation that writes the data.
> It is only "mixed" in the sense that applications query
> data from multiple sources.
>
> So for me, multi-master with physical replication seems
> possible, and would presumably provide the best
> read scalability.  I doubt that I am in the only database
> user who has this kind of workflow.
>
> The alternatives are ugly.  I can load data from separate
> sources into separate database servers *without* replication
> between them, but then the application layer has to
> emulate queries across the data.  (Yuck.)  Or I can use
> logical replication such as BDR, but then the servers
> are spending more effort than with physical replication,
> so I get less bang for the buck when I purchase more
> servers to add to the cluster.  Or I can use FDW to access
> data from other servers, but that means the same data
> may be pulled across the wire arbitrarily many times, with
> corresponding impact on the bandwidth.
>
> Am I missing something here?  Does BDR really provide
> an equivalent solution?

I think BDR is better: while it does only support schema-equivalent
replication that is the typical case for distributed write systems
like this.  Also, there are a lot less assumptions about the network
architecture in the actual data itself (for example, what happens when
you want to change onwer/mege/split data?).  IMNSHO, It's better that
each node is managing WAL for itself, not the other way around except
in the very special case you want an exact replica of the database on
each node at all times as with the current HS/SR.

A **huge** amount of work has/is being put in to wal based logical
replication support (LLSR in the BDR docs) that should mostly combine
the flexibility of trigger based logical replication with the
robustness of wal based replication that we have in core now.  LLSR a
low level data transmission framework that can be wrapped by higher
level user facing stuff like BDR.  LLSR, by the way, does not come
attached with the assumption that all databases have the same schema.
If I were you, I'd be studying up on LLSR and seeing how it could be
molded into the use cases you're talking about.  From a development
point of view, the replication train hasn't just left the station,
it's a space shuttle that just broke out of earth's orbit.  By my
reckoning a new 'from the ground up' implementation of replication
requiring in-core changes has an exactly zero percent change of being
adopted.

merlin

On 2014-01-02 10:18:52 -0800, Mark Dilger wrote:
> I anticipated that my proposal would require partitioning the catalogs.
> For instance, autovacuum could only run on locally owned tables, and
> would need to store the analyze stats data in a catalog partition belonging
> to the local server, but that doesn't seem like a fundamental barrier to
> it working.

It would make every catalog lookup noticeably more expensive.

>  The partitioned catalog tables would get replicated like
> everything else.  The code that needs to open catalogs and look things
> up could open the specific catalog partition needed if it already knew the
> Oid of the table/index/whatever that it was interested in, as the catalog
> partition desired would have the same modulus as the Oid of the object
> being researched. 

Far, far, far from every lookup is by oid. Most prominently the names of
database objects. Those will have to scan every catalog partition. Not
fun.

> Your point about increasing the runtime of pg_upgrade is taken.  I will
> need to think about that some more.

It's not about increasing the runtime, it's about simply breaking
it. pg_upgrade relies on binary compatibility of user relation's files
and you're breaking that if you change the width of datatypes.

> Your claim that what I describe is not multi-master is at least partially
> correct, depending on how you think about the word "master".  Certainly
> every server is the master of its own chunk.

Well, you're essentially just describing a sharded system - that's not
usually coined multimaster.

> Your claim that BDR doesn't have to be much slower than what I am
> proposing is quite interesting, as if that is true I can ditch this idea and
> use BDR instead.  It is hard to empirically test, though, as I don't have
> the alternate implementation on hand.

Well, I can tell you that for the changeset extraction stuff (which is the
basis for BDR) the biggest bottleneck so far seems to be the CRC
computation when reading the WAL - and that's something plain WAL apply
has to do as well. And it is optimizable.
When actually testing decoding & apply, for workloads fitting into
memory I had to try very hard to construe situations where apply was a
big bottleneck. It is easier for seek bound workloads, where the standby
is less powerful than the primary, since there's more random reads for
those due to full page writes removing the need for reads in many cases.

> I think the expectation that performance will be harmed if postgres
> uses 8 byte Oids is not quite correct.
>
> Several years ago I ported postgresql sources to use 64bit everything.
> Oids, varlena headers, variables tracking offsets, etc.  It was a fair
> amount of work, but all the doom and gloom predictions that I have
> heard over the years about how 8-byte varlena headers would kill
> performance, 8-byte Oids would kill performance, etc, turned out to
> be quite inaccurate.

Well, it can increase the size of the database, turning a system where
the hot set fits into memory into one where it doesn't anymore. But
really, the performance concerns were more about the catalog lookups.

Fundamentally, I think there's nothing I see preventing such a scheme
from being implemented - but I think there's about zap chance of it ever
getting integrated, it's just far to invasive with very high costs in
scenarios where it's not used for not all that much gain. Not to speak
about the amount of engineering it would require to implement.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

On 2014-01-02 11:35:57 -0800, Mark Dilger wrote:
> BTW, since the space shuttle has already left orbit, as you
> metaphorically put it, maybe there should be more
> visibility to the wider world about this?  You can go to
> postgresql.org and find diddly squat about it.  I grant you
> that it is not a completed project yet, and so maybe you
> want to wait before making major announcements, but
> the sort of people who would use this feature are probably
> the sort of people who would not mind hearing about it
> early.

Well, changeset extraction isn't committed yet, so it's not surprising
that you don't find anything there ;). Parts of the patches (notably
wal_level=logical, enriching the WAL to allow decoding) have landed, the
others are being worked over in response to review comments of Robert.

I am pretty sure there will be more publicity once it's committed ;)

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services