Infrastructure for Highly Concurrent, High Write DB

Question

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 & MySQL (Hadoop caches, single dump to MySQL)
• MongoDB & MySQL (instead of Hadoop, we use mongo for cache)

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

1. Can MySQL Sharding handle the concurrent connections?
2. 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.

Answer 1

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

Answer 2

MySQL Cluster might be another approach to sharding. Check post here.

I'm also a great fan of Cassandra, but it depends a lot on your data model and the queries you want to perform. Cassandra in blazing fast to writes, because they are always sequential on disk.

Answer 3

If you are going to go multi-headed (which you probably need to if you really need 3K active connections) I'd probably look at Riak or maybe Cassandra. It really depends on what your app does as to how well these will fit, but from what you have described I think it would fit in something like Riak.

That said, a sharded approach seems pretty doable, if you can find a good way to segment the data, and can minimize any need for cross shard stuff. I'd stay away from any of the ring / star / mmm stuff in mysql, and just stick to straight sharding. Actually, if you were willing to use Postgres, you could prototype pretty easily using schemas on something like heroku, and then fork and split off databases as they start to outgrow individual nodes.

Oh, and while I think you could try to scale something like this vertically (single node handling all 3K conns), I don't think you can do it in the cloud.

Answer 4

If it's an option for your specific application, maybe you can use some asynchronous way to write data to your database (work-queue, batched inserts ...) and/or shift away the many client connections from your database with some proxy in front.

With sharding you can generally scale fine (2x db-servers == 2x connections), but it highly depends on the nature of your dataset and how you can split it across shards.

Answer 5

I personally prefer MongoDB for it's ease of administration, scalability, general ease-of-use. Also, unless I actually need a RDBMS, I'm going to use a no-SQL.

With that said, choose the DB that makes the most sense for your application. If you need Transactions or can't design your app without Joins (or it just plain makes more sense with them) then use a RDBMS (MySQL, PostGres, etc.)

While I personally prefer MongoDB, the idea that MySQL doesn't scale orcan't handle a high rate of transactions is purely false. The Facebook Engineering team (and the MySQL team within it) goes into great detail with it. Also check out the Etsy Ops team blog; they love MySQL as well.

Finally, I wouldn't use MongoDB for a MySQL cache; use Memcached for that.

Redis is also an in-RAM key-value store that is good for handling certain use-cases. There are some blog entries on blog.agoragames.com that describe some use-cases.

You should also check out CouchDB if you're thinking No-SQL. Just be aware that it requires regular maint to keep it's disk-utilization down. (It trades speed and convenience for Disk util...)

Finally, capacity planning is not easy to predict. You need to test in as realistic conditions as possible and be prepared to remediate based on what you see. Sadly "Computer Science" is as much Art as Science.