Thank you for the quick response....
just to answer one of the things here, the load is mostly reads as
writes only happen in batches every so often.
When I am saying reads I am talking of up to 2000-5000 concurrently at
any given time during high load.
-----Original Message-----
From: Johan De Meersman <vegivamp@stripped>
To: Machiel Richards <machielr@stripped>
Cc: mysql mailing list <mysql@stripped>
Subject: Re: Replication on MySQL databases
Date: Thu, 4 Nov 2010 10:21:11 +0100
If your sites are busy with *writes*, you're kind of stuck. Replication
means that every write that happens on one side, also MUST happen on the
other side, so you win nothing. Well, you win a little delay on half of
your writes, which is, to most people, really a downside, not an upside.
Your best bet in that scenario would be horizontal partitioning, that
is, put part of your tables on a second cluster. This entails quite some
changes to your application, though, and a hefty analysis of what tables
you NEVER use together in a single query. Can be quite the bugger to
implement :-) Keep in the back of your mind (but never tell management)
that you *can* actually use federated tables for accessing remote
tables, but there's plenty of drawbacks to that.
If you're talking mostly reads, you have more options. Even then,
though, it is best if your application is keenly aware of what's going
on, as you have no guarantee about the time it takes for an insert to
replicate to all your slaves - your application shouldn't panic if it
can't immediately see the data it just wrote.
Other people here will undoubtedly tell you about MMM - I keep hearing
that that's pretty good, but I (still) have no personal experience with
it, myself.
On Thu, Nov 4, 2010 at 10:04 AM, Machiel Richards <machielr@stripped>
wrote:
Good day all
I am hoping that someone can perhaps help me with some
resources
or info.
I need to go to a meeting in the next hour and was requested
this
morning to research possible load balancing options for MySQL
database.
What is currently running is a website (balanced over a
couple of
web servers all connecting to the same database) using apacje
and jdk.
2 MySQL databases running as Masler/Slave replication with
all
reads and writes going to the master and the slave being used
for data
exports and failover if required.
The websites are rather busy and during times of high
load the
master server takes some strain.
The Databases are being to new upgraded hardware soon,
including a database upgrade.
The idea is that they also want to introduce load
balancing for
the MySQL databases in order to manage the high load situations.
Any help would be appreciated as google has not yet
turned up
any sufficient info for me in this short time I had been given.
Regards
Machiel
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
