I am trying to build a 1TB database on MySQL which will need strong read performance. I intend to replicate my database to prevent downtime.
Do my database servers need load balancing to improve performance, or would a memcached setup be better?
What is the best failover solution for databases?
If you want to have a solution for automated failover you could take a look at Galera replication with Galera load balancer up front of your servers. Galera will allow you to have a multi master cluster that uses the InnoDB storage engine. You can write and read to all servers. If you don’t want to use a load balancer you can make your database logic aware of multiple database servers and distribute load in the database abstraction logic.
Continuent also provide a really nice MySQL replication/clustering product (Tungsten), the basic software is free however the full clustering system costs money.
Memcached: You should always implement caching whenever possible and try to cache as much static or infrequently changing data as you can. This is another thing you can handle in your database abstraction logic.
Implementing a true multi master system using a software like Galera or Tungsten with Memcached can provide HA and speed if done correctly.
