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
