Home / server / Questions / 200967
In Process
Chad Johnson
Asked: 2010-11-12 14:55:23 +0800 CST

Scalable database architecture for heavy read and heavy write?

  • 772

I am developing a web application which will require scaling to multiple servers. I'll need multiple web servers running my application code, and there will be heavy reads and heavy writes to the database. Clearly I cannot just use one database.

I've dealt with database replication where there was a single master and multiple slaves. With that, we had multiple read-only API servers, each with a dedicated server and a dedicated (replicated) slave database.

But how would I deal with writes? It seems like it would be a bad idea to have all writes happen to one single database due to bottnecking, and instead I'd want write to multiple separate databases. But then I'd have multiple master databases, and I can imagine it would be a disaster if the masters became out of sync. And, I could imagine there would be primary key collisions.

Another option may be to have multiple databases with the same schema, but each is not a copy of the other. This sounds scary though, as one could crash, and I would lose data. This seems like a maintenance nightmare, but maybe I am wrong.

What would be a good, tested, practical, scalable architecture for the needs I described? Links to articles or just posts here would be great.

replication database scalability

3 Answers

  1. There's nothing remotely close to one-size-fits-all here, but if you're looking to move beyond traditional relational database solutions, then I suggest you Google "NoSQL" and start reading.

    • 3

  2. short answer: sharding

    long answer: you either develop your own "nosql" layer on top of a sql db (usualy mysql) or you use one of the many hot topic nosql tools (mongodb, cassandra, redis, etc).

    however since your post included zero metrics its extremely unlikely you actually need to do this, if you did you'd be in numbers-mode. Most likely you'll get by with a healthy dose of ram and some time spent tuning sql queries, or, a couple grand worth of SSD/high-end-storage.

    • 2

  3. If you can fit your data differently to standard relational tables than i would consider NoSQL ..

    A heavy write style cluster data store would be cassandra ( NoSQL ) It is designed to be write non-blocking... :D

    Otherwise.. Attempt sharding with SQL and as the bigger companies sometimes do, run mysql in a master<---> master configuration..

    What type of data is written and read?

    • 1