Re: Clustering & Load Balancing & Replication

Centuries ago, Nostradamus foresaw when org(at)kewlstuff(dot)co(dot)za would write:
> Suggest you download my little application and read the documentation,
> you'll see its very different, maybe even interesting.
> Maybe they should change that to.... Postgres DOES HAVE a free multi-master
> replication system :)

It isn't systematically usable as such, without a whole lot of
end-user assembly.

> One comment they make.... "Heavy write activity can cause excessive locking,
> leading to poor performance. In fact, write performance is often worse than
> that of a single server. Read requests can be sent to any server."
> I'm not sure I agree with that... or maybe MVCC is just fantastic.... I
> tested it.
> The 2 phase commit locking is definitely happening at record level, so only
> if the multimasters all hit the same record is there the potential for lock
> conflict.
> Why will dB's being randomly used, hit the same records, I think its a low
> probability to begin with?

That's only true if you are certain that the update pattern is NOT
involving a shared set of records. IN GENERAL, heavy write activity
can cause locking to become mighty expensive, which is certainly a
true statement.

> Not happy with that, I wrote a multithreaded routine and got them to all
> smack the same record, it NEVER ROLLED BACK, and if there is performance
> degradation, I didnt notice it... again probably a testament to the MVCC
> design.

It seems likely to me that this requires some careful validation of
testing.

An effect we see is that if a set of transactions are "fighting" over
a single "balance" record, they will essentially serialize over that.

On a system with a single CPU, it is not obvious that you'll see a
degradation there because, since you only have the single CPU, it
would be serializing the activity anyways.

Try it out on an 8-way SMP system and you may see things differently.

> In any event if you look at the documentation, you'll see SPAR is not
> multimaster or nothing. Can use say one server in an office and another to
> pump data to a remote web site... not sure if you would even call that
> multimaster, thats the point, I'm not sure SPAR fits any pure theory
> category.

There are a few tests I could throw at it that tend to challenge
replication systems vis-a-vis "fidelity of results." I otta see if I
can find them in a readily deployable form.

There are two notable anomalies which have been known to break
replication systems:

1. Nondeterministic updates:

For instance, functions that are nondeterministic:

insert into rtable values (random(), now());

Or result sets that are nondeterministic:

insert into rtable2 (select * from mytable where some_attr='foo'
order by random() limit 5); -- Where there are 25 records with some_attr='foo'

2. Value swapping:

Consider the table:

create table t1 (mk integer primary key, val text unique not null);

insert into t1 (mk, val) values (1, 'chris');
insert into t1 (mk, val) values (2, 'dave');
insert into t1 (mk, val) values (3, 'brad');

begin;
update t1 set mk = 99 where mk = 1;
update t1 set mk = 1 where mk = 3;
update t1 set mk = 3 where mk = 99;
commit;

Is there a condition where a pause somewhere in there will cause
replication to break? Note that there have been replication systems
(erServer) that this set of updates can, intermittently, cause to fall
over.
--
let name="cbbrowne" and tld="linuxfinances.info" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/slony.html
"Feel free to contact me (flames about my english and the useless of
this driver will be redirected to /dev/null, oh no, it's full...)"
-- Michael Beck, describing the PC-speaker sound device