Re: Replication on the backend

What about replication or data distribution inside the backend. This is a
valid issue?

Thanks,
Gustavo.

gustavotonini(at)gmail(dot)com (Gustavo Tonini) writes:
> What about replication or data distribution inside the backend. This
> is a valid issue?

I'm not sure what your question is...
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/x.html
"Love is like a snowmobile flying over the frozen tundra that suddenly
flips, pinning you underneath. At night, the ice weasels come."
-- Matt Groening

replication (master/slave, multi-master, etc) implemented inside
postgres...I would like to know what has been make in this area.

Gustavo.

P.S. Sorry for my bad English.

2005/12/5, Chris Browne <cbbrowne(at)acm(dot)org>:
>
> gustavotonini(at)gmail(dot)com (Gustavo Tonini) writes:
> > What about replication or data distribution inside the backend. This
> > is a valid issue?
>
> I'm not sure what your question is...
> --
> (reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
> http://www.ntlug.org/~cbbrowne/x.html
> "Love is like a snowmobile flying over the frozen tundra that suddenly
> flips, pinning you underneath. At night, the ice weasels come."
> -- Matt Groening
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

Gustavo Tonini wrote:
> replication (master/slave, multi-master, etc) implemented inside
> postgres...I would like to know what has been make in this area.
http://www.commandprompt.com/ - Master/Slave

Joshua D. Drake

>
> Gustavo.
>
> P.S. Sorry for my bad English.
>
> 2005/12/5, Chris Browne <cbbrowne(at)acm(dot)org <mailto:cbbrowne(at)acm(dot)org>>:
>
> gustavotonini(at)gmail(dot)com <mailto:gustavotonini(at)gmail(dot)com> (Gustavo
> Tonini) writes:
> > What about replication or data distribution inside the
> backend. This
> > is a valid issue?
>
> I'm not sure what your question is...
> --
> (reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
> http://www.ntlug.org/~cbbrowne/x.html
> <http://www.ntlug.org/%7Ecbbrowne/x.html>
> "Love is like a snowmobile flying over the frozen tundra that
> suddenly
> flips, pinning you underneath. At night, the ice weasels come."
> -- Matt Groening
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org
> <mailto:majordomo(at)postgresql(dot)org> so that your
> message can get through to the mailing list cleanly
>
>

> replication (master/slave, multi-master, etc) implemented inside
> postgres...I would like to know what has been make in this area.

It's not in the backend, check out things like Slony (www.slony.info)
and various other commercial solutions.

Chris

On 12/5/2005 8:18 PM, Gustavo Tonini wrote:

> replication (master/slave, multi-master, etc) implemented inside
> postgres...I would like to know what has been make in this area.

We do not plan to implement replication inside the backend. Replication
needs are so diverse that pluggable replication support makes a lot more
sense. To me it even makes more sense than keeping transaction support
outside of the database itself and add it via pluggable storage add-on.

Jan

>
> Gustavo.
>
> P.S. Sorry for my bad English.
>
> 2005/12/5, Chris Browne <cbbrowne(at)acm(dot)org>:
>>
>> gustavotonini(at)gmail(dot)com (Gustavo Tonini) writes:
>> > What about replication or data distribution inside the backend. This
>> > is a valid issue?
>>
>> I'm not sure what your question is...
>> --
>> (reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
>> http://www.ntlug.org/~cbbrowne/x.html
>> "Love is like a snowmobile flying over the frozen tundra that suddenly
>> flips, pinning you underneath. At night, the ice weasels come."
>> -- Matt Groening
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>> message can get through to the mailing list cleanly
>>
>

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

But, wouldn't the performance be better? And wouldn't asynchronous messages
be better processed?

Thanks for replies,
Gustavo.

2005/12/6, Jan Wieck <JanWieck(at)yahoo(dot)com>:
>
> On 12/5/2005 8:18 PM, Gustavo Tonini wrote:
>
> > replication (master/slave, multi-master, etc) implemented inside
> > postgres...I would like to know what has been make in this area.
>
> We do not plan to implement replication inside the backend. Replication
> needs are so diverse that pluggable replication support makes a lot more
> sense. To me it even makes more sense than keeping transaction support
> outside of the database itself and add it via pluggable storage add-on.
>
>
> Jan
>
>
> >
> > Gustavo.
> >
> > P.S. Sorry for my bad English.
> >
> > 2005/12/5, Chris Browne <cbbrowne(at)acm(dot)org>:
> >>
> >> gustavotonini(at)gmail(dot)com (Gustavo Tonini) writes:
> >> > What about replication or data distribution inside the backend. This
> >> > is a valid issue?
> >>
> >> I'm not sure what your question is...
> >> --
> >> (reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
> >> http://www.ntlug.org/~cbbrowne/x.html
> >> "Love is like a snowmobile flying over the frozen tundra that suddenly
> >> flips, pinning you underneath. At night, the ice weasels come."
> >> -- Matt Groening
> >>
> >> ---------------------------(end of
> broadcast)---------------------------
> >> TIP 1: if posting/reading through Usenet, please send an appropriate
> >> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> >> message can get through to the mailing list cleanly
> >>
> >
>
>
> --
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #================================================== JanWieck(at)Yahoo(dot)com #
>

On Tue, 2005-12-06 at 10:03 -0200, Gustavo Tonini wrote:
> But, wouldn't the performance be better? And wouldn't asynchronous
> messages be better processed?

At least for synchronous multi-master replication, the performance
bottelneck is going to be the interconnect between the nodes -
integration of the replication logic into the backend most probably
doesn't affect performance that much.

I'd rather like to ask Jan what different needs for replication he
discovered so far. And how he came to the conclusion, that it's not
possible to provide a general solution.

My point for integration into the backend is flexibility: obviously the
replication code can influence the database much more from within the
backend than from the outside. For example running one complex query on
several nodes. I know, this a very advanced feature - currently it's not
even possible to run one query on multiple backends (i.e. processors of
a multi-core system) - but I like to plan ahead instead of throwing away
code later. For such advanced features you simply have to dig around in
the backend code one day. Of course you can always add hooks, but IMHO
that only complicates matters.

Is there some discussion going on about such topics somewhere? What's up
with slony-2? The wiki on slony2.org still doesn't provide a lot of
technical information (and obviously got spammed BTW).

Regards

Markus

On 12/6/2005 8:10 AM, Markus Schiltknecht wrote:

> On Tue, 2005-12-06 at 10:03 -0200, Gustavo Tonini wrote:
>> But, wouldn't the performance be better? And wouldn't asynchronous
>> messages be better processed?
>
> At least for synchronous multi-master replication, the performance
> bottelneck is going to be the interconnect between the nodes -
> integration of the replication logic into the backend most probably
> doesn't affect performance that much.

That is exactly right. Thus far, processor, memory and disk speeds have
allways advanced on a higher pace than network speeds. Thus, the few
percent of performance gain we'd get from moving things into the backend
will be irrelevant tomorrow with 4x-core and 16x-core CPU's.

> I'd rather like to ask Jan what different needs for replication he
> discovered so far. And how he came to the conclusion, that it's not
> possible to provide a general solution.

- Asynchronous master to multi-slave. We have a few of those with
Mommoth-Replicator and Slony-I being the top players. Slony-I does
need some cleanup and/or reimplementation after we have a general
pluggable replication API in place.

- Synchronous multimaster. There are certain attempts out there, like
Postgres-R, pgcluster, Slony-II. Some more advanced, some less. But
certainly nothing I would send into the ring against Oracle-Grid.

- Asynchronous multimaster with conflict resolution. I have not seen
any reasonable attempt on this one yet. Plus, it divides again into
two camps. One is the idea to have one central system with thousands
of satellites (salesman on the street), the other being two or more
central systems doing load balancing (although this competes with
sync-mm).

> My point for integration into the backend is flexibility: obviously the
> replication code can influence the database much more from within the

We need a general API. It should be possible to define on a per-database
level which shared replication module to load on connect. The init
function of that replication module then installs all the required
callbacks at strategic points (like heap_update(), at_commit() ...) and
the rest is hidden in the module.

> Is there some discussion going on about such topics somewhere? What's up
> with slony-2? The wiki on slony2.org still doesn't provide a lot of
> technical information (and obviously got spammed BTW).

Slony-II has been slow lately in the Eastern timezone.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Hello Jan,

On Tue, 2005-12-06 at 10:10 -0500, Jan Wieck wrote:
> We need a general API. It should be possible to define on a per-database
> level which shared replication module to load on connect. The init
> function of that replication module then installs all the required
> callbacks at strategic points (like heap_update(), at_commit() ...) and
> the rest is hidden in the module.

thank you for your list of replication types. Those still have some
things in common. Thus your approach of providing hooks for different
modules might make sense. Only I fear that I would need way to many
hooks for what I want ;)

> Slony-II has been slow lately in the Eastern timezone.

What is that supposed to mean? Who sits in the eastern timezone?

Regards

Markus

gustavotonini(at)gmail(dot)com (Gustavo Tonini) writes:
> But,  wouldn't the performance be better? And wouldn't asynchronous
> messages be better processed?

Why do you think performance would be materially affected by this?

The MAJOR performance bottleneck is normally the slow network
connection between servers.

When looked at in the perspective of that bottleneck, pretty much
everything else is just noise. (Sometimes pretty loud noise, but
still noise :-).)
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/spreadsheets.html
"When the grammar checker identifies an error, it suggests a
correction and can even makes some changes for you."
-- Microsoft Word for Windows 2.0 User's Guide, p.35:

> Postgres-R, pgcluster, Slony-II. Some more advanced, some less. But
> certainly nothing I would send into the ring against Oracle-Grid.

Assuming that you mean Oracle Real Application Cluster (the Grid is more,
right?) I wonder if this technology technically still counts as replication.
AFAIK they do not replicate data but share a common data pool among different
servers. You still have communication overhead but you write a tuple only
once for all servers involved. Takes away a lot of overhead on a system
that's heavily written too.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes(at)jabber(dot)org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL

Just like MySql!

On Dec 5, 2005, at 10:35 PM, Jan Wieck wrote:

> On 12/5/2005 8:18 PM, Gustavo Tonini wrote:
>
>> replication (master/slave, multi-master, etc) implemented inside
>> postgres...I would like to know what has been make in this area.
>
> We do not plan to implement replication inside the backend.
> Replication needs are so diverse that pluggable replication support
> makes a lot more sense. To me it even makes more sense than keeping
> transaction support outside of the database itself and add it via
> pluggable storage add-on.
>
>
> Jan
>
>
>> Gustavo.
>> P.S. Sorry for my bad English.
>> 2005/12/5, Chris Browne <cbbrowne(at)acm(dot)org>:
>>>
>>> gustavotonini(at)gmail(dot)com (Gustavo Tonini) writes:
>>> > What about replication or data distribution inside the
>>> backend. This
>>> > is a valid issue?
>>>
>>> I'm not sure what your question is...
>>> --
>>> (reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
>>> http://www.ntlug.org/~cbbrowne/x.html
>>> "Love is like a snowmobile flying over the frozen tundra that
>>> suddenly
>>> flips, pinning you underneath. At night, the ice weasels come."
>>> -- Matt Groening
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>> subscribe-nomail command to majordomo(at)postgresql(dot)org so
>>> that your
>>> message can get through to the mailing list cleanly
>>>
>
>
> --
> #=====================================================================
> =#
> # It's easier to get forgiveness for being wrong than for being
> right. #
> # Let's break this rule - forgive
> me. #
> #==================================================
> JanWieck(at)Yahoo(dot)com #
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

> - Asynchronous master to multi-slave. We have a few of those with
> Mommoth-Replicator and Slony-I being the top players. Slony-I does
> need some cleanup and/or reimplementation after we have a general
> pluggable replication API in place.

Is this API actually have people working on it or just something on
the todo list?

I don't see anything in the TODO list. I'm very interesting in work that. If
is possible...

Gustavo.

I would classify it as a clustered database system (Oracle 10g that is).
Clustered meaning more than one node in the cluster.

ALy.

On Tue, 6 Dec 2005, Michael Meskes wrote:

>> Postgres-R, pgcluster, Slony-II. Some more advanced, some less. But
>> certainly nothing I would send into the ring against Oracle-Grid.
>
>Assuming that you mean Oracle Real Application Cluster (the Grid is more,
>right?) I wonder if this technology technically still counts as replication.
>AFAIK they do not replicate data but share a common data pool among different
>servers. You still have communication overhead but you write a tuple only
>once for all servers involved. Takes away a lot of overhead on a system
>that's heavily written too.
>
>Michael
>

--
Aly S.P Dharshi
aly(dot)dharshi(at)telus(dot)net

"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"

On Tue, Dec 06, 2005 at 12:35:43AM -0500, Jan Wieck wrote:
> We do not plan to implement replication inside the backend. Replication
> needs are so diverse that pluggable replication support makes a lot more
> sense. To me it even makes more sense than keeping transaction support
> outside of the database itself and add it via pluggable storage add-on.

And, as I say every single time this comes up, Oracle's and IBM's and
MS's and everybody else's replication systems are _also_ add ons. If
you don't believe me, look at the license costs. You can get a
system without it enabled, which means (by definition) it's a modular
extension.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland

Are you sure that no way to implement a generic aproach on the backend? What
does specification say? Does Oracle 10g have a core implementation of
replication (cluster)?

Gustavo.

2005/12/7, Andrew Sullivan <ajs(at)crankycanuck(dot)ca>:
>
> On Tue, Dec 06, 2005 at 12:35:43AM -0500, Jan Wieck wrote:
> > We do not plan to implement replication inside the backend. Replication
> > needs are so diverse that pluggable replication support makes a lot more
> > sense. To me it even makes more sense than keeping transaction support
> > outside of the database itself and add it via pluggable storage add-on.
>
> And, as I say every single time this comes up, Oracle's and IBM's and
> MS's and everybody else's replication systems are _also_ add ons. If
> you don't believe me, look at the license costs. You can get a
> system without it enabled, which means (by definition) it's a modular
> extension.
>
> A
>
> --
> Andrew Sullivan | ajs(at)crankycanuck(dot)ca
> In the future this spectacle of the middle classes shocking the avant-
> garde will probably become the textbook definition of Postmodernism.
> --Brad Holland
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

What is the point of these questions? If you have a concrete, practical
proposal to make, please do so. Otherwise, you have already got the
answer from the people who are actually working on replication and
understand it far beyond abstract considerations. If you think there is
a good reason to do replication directly in the backend code rather than
as an addon, possibly using an agreed API, then you need to provide hard
evidence, not mere assertion or conjecture.

cheers

andrew

Gustavo Tonini wrote:

> Are you sure that no way to implement a generic aproach on the
> backend? What does specification say? Does Oracle 10g have a core
> implementation of replication (cluster)?
>
> Gustavo.
>
>
> 2005/12/7, Andrew Sullivan <ajs(at)crankycanuck(dot)ca
> <mailto:ajs(at)crankycanuck(dot)ca>>:
>
> On Tue, Dec 06, 2005 at 12:35:43AM -0500, Jan Wieck wrote:
> > We do not plan to implement replication inside the backend.
> Replication
> > needs are so diverse that pluggable replication support makes a
> lot more
> > sense. To me it even makes more sense than keeping transaction
> support
> > outside of the database itself and add it via pluggable storage
> add-on.
>
> And, as I say every single time this comes up, Oracle's and IBM's and
> MS's and everybody else's replication systems are _also_ add ons. If
> you don't believe me, look at the license costs. You can get a
> system without it enabled, which means (by definition) it's a modular
> extension.
>
> A
>
> --
> Andrew Sullivan | ajs(at)crankycanuck(dot)ca <mailto:ajs(at)crankycanuck(dot)ca>
> In the future this spectacle of the middle classes shocking the avant-
> garde will probably become the textbook definition of Postmodernism.
> --Brad Holland
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>

On 12/8/2005 1:28 PM, Gustavo Tonini wrote:

> Are you sure that no way to implement a generic aproach on the backend? What

You mean "generic" as in a replication system that can do asynchronous
master-slave, asynchronous multimaster with conflict resolution based on
timestamps, system priority or user defined resolution stubs, can do
synchronous predicate locking but also does support thousands of
asynchronous, partial replica (salesman on the road), and last but not
least can run as a synchronous cluster in a LAN segment. All the above
can of course be mixed together ... like a central cluster of 8 load
balanced, fault tolerant systems, with async multimaster replica in all
external branch servers, partial multimaster replica for the road
warriers and some slave leaf nodes for reporting.

If you can present a prototype for the above, I am sure that we will
change our opinion and finally settle for one, builtin replication system.

> does specification say? Does Oracle 10g have a core implementation of
> replication (cluster)?

What specification?

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

> Are you sure that no way to implement a generic aproach on the
> backend? What does specification say?

What specification are you talking about?

> Does Oracle 10g have a core implementation of replication (cluster)?

Since replication is sold as a separate product from Oracle 10g,
obviously not.

We *know* (particularly those of us that have had involvement in
actually implementing replication systems used in production
environments) that "user space" implementations of replication can
function satisfactorily. We've implemented it.

You might be able to convince us that implementing replication inside
the backend is preferable, but you'll have to have exceedingly strong
evidence in order to overcome the already known factor that doing it
outside the backend works quite well.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/slony.html
"Now, suddenly, I _am_ the expanding Russian Frontier" -- Commander
Ivanova
"But with very nice borders..." -- Dr Franklin

Hello,

On Fri, 2005-12-09 at 08:47 -0500, Christopher Browne wrote:
> We *know* (particularly those of us that have had involvement in
> actually implementing replication systems used in production
> environments) that "user space" implementations of replication can
> function satisfactorily. We've implemented it.

While this might be true, allow me a sidenote: AFAIK the very first,
functional prototype we know of was Postgres-R for PostgreSQL 6.4.2 (1).
So the very same holds true for a replication solution integrated into
the backend: we know such an implementation can function satisfactorily.

As we mostly agree, the performance bottelneck is _not_ the CPU, but the
nodes interconnects (the network). Regarding communication between the
backends and the replication solution, performance isn't that much of an
issue, because the inter-node communication will allways be slower than
inter-process communication.

A different problem is how to distribute PostgreSQL with different
upcomming replication solutions. It seems to me that most people's main
concern is not being able to get a prebuilt PostgreSQL with
_just_one_replication_solution_that_works_(tm) For most users it really
doesn't matter _how_ exactly the solution technically got integrated.

This problem gets solved with hooks and preloading a library: you could
simply provide _one_ PostgreSQL package which provides hooks for
replication solutions. Those could then provide a package with their
library. This of course is only doable if the number of hooks is kept
low.

Regards

Markus

[1] pgreplication project on gborg:
http://gborg.postgresql.org/project/pgreplication/projdisplay.php