Re: erserver - Mailing list pgsql-general

On Thu, Jul 25, 2002 at 02:35:20PM +0200, Bjoern Metzdorf wrote:
>
> Well, does it work? :-)

Yes.  In fact, if you do a whois query against .info hostnames
(whois.afilias.net) you are actually seeing a replicated PostgreSQL
database most of the time.

> Are multiple slaves possible (I do suspect so)?

They're supposed to be.  I've run into a little problem recently, but
more on that below.

> How fast is the replication (some seconds)?

Quite fast.  IIRC, we benchmarked > 1000 rows/s when we stopped,
because that was fast enough for our purposes.

One thing we did discover with 7.1.3 is that RI triggers on the slave
can _really_ slow things down.  It appears that triggers get slower
and slower as a transaction rolls on, and therefore make everything
crawl if you have a lot of rows to update (if, for instance, you stop
replication for a while for some reason).  The easy answer is to drop
all the RI triggers, although that means you have to add another step
in the event you have to flip over to the slave server.  I haven't
yet discovered whether the same problem persists in 7.2.

> On what architectures does it run?

The tools are written in Perl, or at least the ones we have.  (AFAIK,
PostgreSQL, Inc. hasn't changed that).  We re-implemented the
external part of the code in Java (I am still unclear on why, to be
honest, so don't ask me.  I guess someone really liked Java), but all
the internal stuff (the triggers in the database, replication system
tables, &c.) are the same.  Our Java developer claimed both that his
implementation was faster, and that it was slower than the Perl code.
I'm not really sure which is true ;-).  In my tests, they seemed
about the same to me; and, as I say, in any case fast enough for us.

> Does it work with 7.2/7.2.1 ?

Yes.  The same code does not work with 7.1 and 7.2, but a new
function has been provided us for 7.2, and it does indeed work.  We
had the new function within a very short time of 7.2. becoming
stable.  I presume PostgreSQL, Inc. ships the function appropriate
for the database you use.  (BTW, a free plug for PostgreSQL, Inc.: we
had a lot of interaction with them when we were coming up last year,
and setting up this replication system.  They did a fine job.)

> How much is it?

That I do not know.  Nobody here ever tells me anything about money
;-)

> Any other personal experiences?

The re-implementation has caused me some pain, because the developer
who did it is no longer available to me, and, well, I now have an odd
problem with multi-slave replication that I need to figure out.  The
Perl seems to do it, though.

The Perl code is not exactly a complete replication system; it's more
a toolkit, in that you don't run "setup" and get an easy set of
dialogues to configure your replication and go to town.  That has
some obvious disadvantages, but it makes the system very flexible,
and I don't really believe that a "one-size-fits-all" replication
facility is practical.  For instance, we don't want some tables
replicated, because they are "holding" tables (that is, data goes
into them, and then is processed further afterwards by a lazy
process.  This is all to speed up transactions and reduce contention
on some frequently-accessed tables), and so would exact a performance
penalty with no benefit.

The code is similar to, but much improved from, the rserv code in
contrib/.  You can get a vague feel of how the system works by
looking at that.  It is _not_ the same code, however, by a long shot.

Failover and recovery is not that difficult, but it _is_ a manual
process.  I'm not even sure it can be made automatic, because of the
store-and-forward approach: what would you do about the potential
tuples stranded on the master when it went down?  (This is a policy
problem that is pretty tough to build into software.  Any
asynchronous-replication system that claims to have fully automated
hot failover is just papering over the problem; I know of several
cases where such failover has caused some problems, so think
carefully about it!)

This is also _only_ a master-slave replication.  You _must never_
insert data into the slave on a table which is replicated, or you
will all but certainly break something.  That means that eRserver is
not a tool for llowing scaling to huge insert volumes.  Also, if you
need the slave to handle read-only queries transparently for the
read-write database, you're out of luck: there is a gap between when
the data hits the master and when it hits the slave.  If you want
near-real-time, it's more than good enough.

On the whole, I think it's a pretty good system.

Hope that's helpful,

A
--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110