Infrastructure for Highly Concurrent, High Write DB My requirements are:


3000 Connections
70-85% Write vs Read


Currently, we are maxing out a High-CPU, Extra Large Instance at 700 connections. All 8 cores are maxed. We think it's the number of concurrent connections as the memory is fine. The write itself is very simple (validations slow things). To scale to 3000, we need to go to multiple servers, current options:


MySQL Sharding
MongoDB Cluster
Cassandra
Hadoop &amp; MySQL (Hadoop caches, single dump to MySQL)
MongoDB &amp; MySQL (instead of Hadoop, we use mongo for cache)


To handle this number of connections, a number of questions:


Can MySQL Sharding handle the concurrent connections?
Can any single master handle these concurrent connections, or is a multi-head like Mongo a better option?


I apologize if I'm not describing my problem well. Please ask questions.
If you are using MySQL as the main database, you may want to consider using a Star Topology via MySQL Replication.

Now, before you say UGHHH, ROFL and OMG to MySQL Replication, hear me out.

A star topology allows you to write to one DB server (called a Distribution Mster [DM]) and send the SQL commands to several DB servers. How do you setup such a DB infrastructure?

Here is the Description

You have 5 DB servers (server A,B,C,D,E)

Server A


In MySQL Replication setup, it will be the Master
Plays special role as the DM
Master of servers B,C,D,E
All tables use the storage engine BLACKHOLE (/dev/null)
Only stores binary logs
Bare Metal Machine
Benefits


Very fast writes since all tables on the DM use BLACKHOLE
Network Latency is less of a issue since reads are 15-30% of DB Activity
All slaves are updated strictly from the DM



Servers B,C,D,E


Slave of A
Server a base for heavy SELECTs
Server Can Be Virtual or Bare Metal
For all servers whose user tables use the storage engine InnoDB


It can server as a warm standby DB Server
Nonintrusive backups can be run against it

For all servers whose user tables use the storage engine MyISAM


Set up with read-only oprion
Tables can have their row formats redone to accelerrate reads



I have written posts on this before


https://serverfault.com/questions/264374/mysql-in-star-topology/264444#264444
https://stackoverflow.com/questions/5273043/mysql-in-star-topology/5781504#5781504


To keep MySQL Replication in tip top shape


Use MySQL 5.5
Use Semisynchronous Replication
Tune InnoDB to engage more CPUs