August 20th, 2012, 11:56 AM
MySQL Replication and Load Balancing
I'm in the process of building a web app.
The service providers I intend to use are now offering really easy to set up load balancers.
Perhaps not every client of mine will want load balancing, but for those who do I'd like to offer this.
I was thinking of putting the MySQL part on it's own server, then load balancing connections between just the application servers. Each app server would be identical and read from / write to the mysql server.
But having just one db server is another single point of failure, so I was thinking of then load balancing between 2 or more database servers with the intention that each database server be identical.
reading about mysql replication it takes a master-slave approach so one has to write to just one database, then read from any
once again, a single point of failure.
then I read about MySQL cluster, but found this in the manual pages
Originally Posted by http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster.html
I know nothing about replication (or mysql cluster) and would love so find out more: docs, books, links, best practices, nomenclature, etc
can anyone point me in the right direction?
August 22nd, 2012, 09:18 AM
August 22nd, 2012, 10:38 AM
Your post may be longer than you thought i would expect, but it is helpful because I don't know much about mysql replication and clustering
We'll be using rackspace cloud, which means the biggest server I can get has 30Gb available RAM and a 1.2TB hard drive. This represents a dedicated box, all other variations of RS cloud servers are slices of one of these big boxes (from 256Mb doubling up each time)
Such a server would cost us ~£950/month just to run - ie not taking bandwidth into consideration (~£120/TB/month) - or ~£15/month for the little 256Mb slice
So, its really redundancy that I'd be looking for.
Since writing the OP I considered having two load balancers (or one if I can balance based on ports) so that web requests would balance between app servers; each app server would write to just one (master) mysql server which would then feed down to a number of load balanced slaves - BUT I DON'T KNOW HOW LONG IT WOULD TAKE TO REPLICATE
This is a web app in which I anticipate a far greater number of reads than writes.
August 24th, 2012, 10:35 AM
Unless the master or slave is under heavy load the replication itself usually takes a couple of milliseconds.
Originally Posted by Northie
Basically the latency in the network and the execution time of the query.
For a simple test on the same box look here.
The replication time is usually never a problem unless you are running something that need concurrency which should be in a transaction and only executed against the master anyway.
And some more useful information here and here .
And out of an monitoring perspective I recommend the pt-heartbeat.
So that you are sure that the slaves are all in sync since the default status information from within MySQL is not reliable.