Database Reference
In-Depth Information
Service becomes more popular; too many reads hitting the database
Add memcached to cache common queries. Reads are now no longer strictly ACID;
cached data must expire.
Service continues to grow in popularity; too many writes hitting the database
Scale MySQL vertically by buying a beefed-up server with 16 cores, 128 GB of RAM,
and banks of 15k RPM hard drives. Costly.
New features increase query complexity; now we have too many joins
Denormalize your data to reduce joins. (That's not what they taught me in DBA
school!)
Rising popularity swamps the server; things are too slow
Stop doing any server-side computations.
Some queries are still too slow
Periodically prematerialize the most complex queries, and try to stop joining in most
cases.
Reads are OK, but writes are getting slower and slower
Drop secondary indexes and triggers (no indexes?).
At this point, there are no clear solutions for how to solve your scaling problems. In any
case, you'll need to begin to scale horizontally. You can attempt to build some type of par-
titioning on your largest tables, or look into some of the commercial solutions that provide
multiple master capabilities.
Countless applications, businesses, and websites have successfully achieved scalable,
fault-tolerant, and distributed data systems built on top of RDBMSs and are likely using
many of the previous strategies. But what you end up with is something that is no longer a
true RDBMS, sacrificing features and conveniences for compromises and complexities.
Any form of slave replication or external caching introduces weak consistency into your
now denormalized data. The inefficiency of joins and secondary indexes means almost all
queries become primary key lookups. A multiwriter setup likely means no real joins at all,
and distributed transactions are a nightmare. There's now an incredibly complex network
topology to manage with an entirely separate cluster for caching. Even with this system
and the compromises made, you will still worry about your primary master crashing and
the daunting possibility of having 10 times the data and 10 times the load in a few months.
