Hello Carl, On 7/18/2012 11:11 AM, Carl Kabbe wrote:
We are actually facing both capacity and availability issues at the same time. ...
It sounds to me like you need a combination of sharding (one master per client or set of clients) combined with multiple slaves (one for backups only). If you share read queries between master and slave already, then you can continue with this. By using the one slave for backups only, it only needs to process the replication stream so it will be able to maintain itself most up to date. This would be the machine you switch to in event of failover. All machines (masters and slaves) need to have the same capacity.
Separating your clients to multiple machines will help with uptime and throughput. If you lose one, only some of your clients lose their connection while you fail over. Also, because each master does not need to handle ALL of your clients at one time (just some of them), you can use much cheaper hardware to handle the load. The other advantage is disk usage. By sharing your traffic over multiple disks (not just one big RAID array or SAN or NAS for ALL of your clients at once) you are actually providing more capacity for transactions than you would with a single large array.
Yes, this may make maintenance a little more interesting but this way you won't need to invest in such huge servers and you gain the redundancy you need to meet the HA goals you stated. Backups will be more numerous but they will be smaller (and possibly client specific). Backups can also happen in parallel (from multiple sources) which will make your maintenance windows smaller. Heavy traffic from one client will not drag down the performance of another (with the exception of clogging your network pipes). It's a win-win.
Go simple, not bigger. Divide and conquer is what I believe is your best approach.
-- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
