Scaling problems (mysql replication issues)


I have a bit of a problem, and would like to get some suggestions going
(and possibly a feature or two if appropriate).

I am running RT globally with support people around the world, currently
I am using 3.0.x which is from the ports of FreeBSD 5.3

I am running mysql 4.0.x as a backend as 4.1.x has already broken
horribly stuff and I was forced to downgrade. As I am using 4.0.x there
is a limited number of things that can be done with replication - in
particular, no multimaster replication, only ‘circular’ replication.

I have found that running a remote webserver in Europe with RT
connecting to a database in Australia is “painful” when trying to load
pages (Search ticket or otherwise). Likewise getting web pages from the
Australian server from Europe is also painfully slow on occasion…

Therefore I thought “I know I’ll replicate, to masters, circular
replication, one in Europe, one in Aus, what could go wrong…?” …
oops… no scratch that … BIG OOPS!.. Around 3 hours later the
replication broke with duplicate keys for the Transaction table…
Fortunately the only updates that broke were staff writing things…


I was thinking about modifying the code to allow a pair of database
handles - one writable to the remote ‘master’ and one readonly to the
local ‘slave’ database - this approach has worked for most things for me
in the past - however I can’t get my head around the code (RT) at the
moment… Would this be a good idea or a non starter in the first
place? Has anyone tried this…? Are there any existing solutions to
this problem…?

Thanks for your time,