Thursday
Apr102008
Mysql scalability and failover...
Thursday, April 10, 2008 at 11:05PM
Hi,
I am an owner of an large community website and currently we are having problems with our database architecture. We are using 2 database servers and spread tables across them to divide read/writes. We have about 90% reads and 10% writes. We use Memcached on all our webservers to cache as much as we can, traffic is load balanced between webservers. We have 2 extra servers ready to put to use!
We have looked into a couple of solution so far:
Continuent Uni/Cluster aka Sequoia -> Commercial version way too expensive and Java isn't as fast as it suppose to be.
MySQL Proxy -> We couldn't find any good example on how to create a master - master with failover scenario.
MySQL Clustering -> Seems to be not mature enough, had a lot of performance issues when we tried to go online with it.
MySQL DRDB HA -> Only good for failover, cannot be scaled!
MySQL Replication -> Well don't get me started ;)
So now I turn to you guys to help me out, I am with my hands in my hair and see the site still growning and performance slowly getting to its limit. Really need your help!! HELP!
I am an owner of an large community website and currently we are having problems with our database architecture. We are using 2 database servers and spread tables across them to divide read/writes. We have about 90% reads and 10% writes. We use Memcached on all our webservers to cache as much as we can, traffic is load balanced between webservers. We have 2 extra servers ready to put to use!
We have looked into a couple of solution so far:
Continuent Uni/Cluster aka Sequoia -> Commercial version way too expensive and Java isn't as fast as it suppose to be.
MySQL Proxy -> We couldn't find any good example on how to create a master - master with failover scenario.
MySQL Clustering -> Seems to be not mature enough, had a lot of performance issues when we tried to go online with it.
MySQL DRDB HA -> Only good for failover, cannot be scaled!
MySQL Replication -> Well don't get me started ;)
So now I turn to you guys to help me out, I am with my hands in my hair and see the site still growning and performance slowly getting to its limit. Really need your help!! HELP!
Reader Comments (7)
Nobody has a solution or a thought on what architecture we should follow?
Hi Diz-X,
Did you take a look at the recent HSCALE article? I think they do something along the lines you are thinking of. It's not clear what you mean about the master-master failover. Here's one option: http://forums.mysql.com/read.php?146,197443,199375#msg-199375. It might be easier to deal with failure in the proxy client. You could of course use virtual IP addresses to make the failover transparent.
Hi,
We're in a way facing the same challenges in terms of scaling out mysql. We have so far reached the same conclusions as you have on clustering and replication (to complicate matters, we're running mysql on windows servers :-S ). Maybe you have been down this road allready but we've found that we can buy a lot of time (in terms of scaling) by running on 64 bit systems and adding lots of of IO using a SAN with fast Fibre Channel disks.
Another idea could be to implement a simple sharding mechanism that allows you to partition data onto several different databases, but I guess this heavily depends on your application architechture.
My first thought is that if you're 90% reads, you're not caching nearly as aggressively as you should be. You need to focus on utilizing the cache more first, before thinking about horizontal scaling.
Todd: We are now working with MySQL Proxy with RW and replication this is good for now, but this does not add any redundancy :( We are working on for script MySQL Proxy that will broadcast all the INSERT/DELETE/REPLACE/UPDATE to all database servers and spread reads around. If an server goes down it will write all missed queries to an log and runs the queries when servers comes back up, ofcourse things as RAND(), NOW() ect will be replaced with values. We are thinking of making this script available under Open Source community when we think it is ready.
Jab: Well it could be a good option for a little time, but then you will come to a point where even the most modern hardware will let you down. Why using windows servers for MySQL? It runs much better on 64bit linux. Start using Memcached in your application, it will make things so much faster.
Jab: About the sharding, we also looked into that but there are some problems with this too. You need to make your application aware of this and this will make it more complex! There are some downsides when a shards get used a lot, then one server has a lot of loads.
Jamie: Yes, you are right. Here comes the but, all our web servers are equipped with 4GB of memory, Apache uses a lot of it already and leaves not much for memcached to be used. Until we found a way to link all the memcached together or buy a machine with 32GB in it only for memached we have to do a lot with the database.
Hi - I was wondering what you've finally come up with because I'm having exactly the same issues as your initial post looking for the best method for scalability and automated failover.
Thanks
It is very normal to run a cluster of Memcached. You shouldn't have to "link them together".
I'd like to hear more about the problems with MySQL Cluster.