On Fri, 4 Feb 2005, Mike Nolan wrote:
If you have so much update load that one server cannot accomodate that load, then you should wonder why you'd expect that causing every one of these updates to be applied to (say) 3 servers would "diminish" this burden.
The update/query load isn't the real issue here, it's that these two servers will be 800 miles apart and there are some advantages in having each office connect to its local database rather than having one of them connect to the remote master.
Considering that the two masters need to be connected, I don't see the advantage. Any write on the _local_ master will have to wait for the _remote_ master to complete as well.
The Slony-1 approach will work, assuming I've got suffient network bandwidth to support it plus the traffic from the remote office plus exixting outside traffic from our public website.
Local read-only access won't travel on the network, both with multi-master and with master-slave.
Write traffic _will_ be transmitted over the wire, both with multi-master and with master-slave. With multi-master _every write operation_ will be remotely duplicated, _both ways_, _synchronously_. That is, master-1 has to wait for master-2 and vice versa. If you're concerned with network performances, multi-master will only make it worse.
With master-slave, _only_ the write operations performed on the slave side need to travel, since clients will perform them directly on the master. On the master side writes are only local. Of course, the _results_ of the writes will have to be propagated to the slave (and thus they travel on the network as well), but that's another matter (delay is usually acceptable, and fits MVCC - the semantics not broken).
If you're willing to break semantics, you may run two splitted masters and find a way to synchronize them. But that requires application level knowledge, if not human intervention, to resolve conflicts.
That's one of those things you just don't know will work until you have it built, so I'm looking for other options now while I have time to consider them. Once I get on-site in two weeks it'll a lot more hectic. -- Mike Nolan
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
.TM. -- ____/ ____/ / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _____/ _____/ _/ Colombo@xxxxxx
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
