[Rt-devel] Scaling problems (mysql replication issues)

[Matthew Sullivan]

All,

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....

Suggestions....?

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,

Matthew