MySQL Scaling, suggestions please
We are serving very busy site and needs to scale MySQL server which is currently running on CentOS 5.3(QuadCore/32GB/RAID10). Here are few thoughts:
1. Master-Slave: Site is sensitive about delays and any delay (which is a possibility in master-slave/master-master setup) can hamper its functionality. So we are not looking at this option. Slave is there only for backups etc.
2. MySQL Cluster: same as above, in multiple nodes there's possibility of occurring delay. I'm not arguing for bad site design (as its not able to tolerate any delay) but its what we have to live with.
3. Horizontal scaling: That's the option, I'm considering here. We will partition DB in parts and deploy DB servers for each, say, all data related to product id 1-5k will be stored and served by DB1, product id from 5-10k will be stored and served by DB2 and so on.
Now DB can be scaled like this, but we need complete overhaul of application to distribute requests to different DB servers based on criteria (like based on product id).
What's the best way to implement it? I'm considering Memcached in one of DB server to store ProductID=DBServer mapping which application will use to get know which server have that particular product and then make/use connection of that DB server. But again, I am not aware how to provide some failover to Memcached?
or any other better way to scale DB server here? any other tool which might be helpful? Your suggestions will be highly helpful.
Thanks in advance,