MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:July 18 2012 3:56pm
Subject:Re: Looking for consultant
View as plain text  
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 

Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

Looking for consultantCarl Kabbe18 Jul
  • Re: Looking for consultantJohnny Withers18 Jul
  • Re: Looking for consultantShawn Green18 Jul
    • Re: Looking for consultantCarl Kabbe18 Jul
      • Re: Looking for consultantAdrian Fita18 Jul
      • Re: Looking for consultantShawn Green18 Jul
      • Re: Looking for consultantAntonis Kopsaftis18 Jul
        • RE: Looking for consultantRick James18 Jul
          • Re: Looking for consultantHoward Hart19 Jul
            • RE: Looking for consultantBenjamin Stillman19 Jul