Top Authors

Architecting Scalable Applications in the Cloud: Database Tier

This week brings me to the fourth and final post in my series on Architecting Scalable Applications in the Cloud, where previously I’ve discussed techniques for hardening your application infrastructure at the load balancing, web/application, and caching tiers. In today’s post I will delve into the details of the most common bottleneck of a typical application – the database tier. The diagram below should look familiar to you if you have read any of the previous posts in my best practices series, and I will use the diagram again as a starting point for this discussion.

Classic Three-Tier Architecture for Cloud-Based Applications

The standard, tried-and-true method for architecting a highly available database tier in the cloud is to have a single master and one (or more) slave(s) replicating from the master, and to have each of these servers in segregated zones such that they are on separate power, cooling, and network infrastructures. In an ideal world, the capabilities of that master database would suffice for all of the application’s lifecycles, from its infancy, through the growth phase, the maturity/maintenance phase, and then through the end-of-life cycle. Of course we all know this is never the case, as the demands on the database tier fluctuate greatly over time, so a “one size fits all” approach is not really feasible. Thus, we need to find options for scaling the database to accommodate the needs of these different phases of the application lifecycle.

Database Scaling

For an application to continue to be successful as its lifecycle progresses, it has to be scalable at all levels of the architecture. As more and more users interact with the application, the resource demands of each tier will continue to increase. I have previously covered some of the mechanisms that can be used to scale the front end load balancers, the application server array, and the caching tier. However, the success or failure of many applications is dependent on a well-conceptualized, architected, and implemented database system.

While the ultimate goal of database design would allow the automated horizontal scaling of the database tier, the practical implementation of such a solution continues to remain an elusive goal. However, there are design concepts you can follow to allow database scaling to varying degrees, which include both vertical and horizontal scaling of the database tier.

Vertical Scaling

In the early stages of an application, when database load is light, a small instance size can often be effectively used for both the master and slave databases. As load increases, the master database can be migrated to a larger instance size, allowing it to take advantage of additional processing power, I/O throughput, and available memory.

For database requests that involve complex queries or joins of multiple tables, the additional memory provided by the larger instance types can assist greatly in accelerating the query response. When possible, the working set of a database should be contained in memory as this greatly reduces the disk I/O requirements of the application, and can greatly enhance the application’s overall performance. Situations may arise in which the CPUs of an instance are greatly underutilized, but the majority of memory is in use. Although this may appear as a poor use of a powerful (and costly) resource, the performance gains realized by containing the working set entirely in memory can far outweigh the costs incurred by these more expensive instance sizes.

RightScale has scripts that allow the migration of a database from one instance size to a larger instance with virtually no database downtime. Many of our customers initiate an application deployment with their master and slave databases on small- to medium-size instances and then migrate their databases to larger instance sizes as user traffic escalates (and thus revenue increases, mitigating the costs of the larger instances).

Horizontal Scaling

I highly recommend implementing one or more slave databases in addition to the master database, regardless of the phase of an application’s lifecycle. The presence of multiple slave databases increases the overall reliability and availability of the application, as well as enabling horizontal scaling of the database using a proxy mechanism for database reads.

In a proxy configuration (shown in the diagram below), the application servers send their database write requests to the master database, while the read requests are directed to a load balancer (or preferably, a pair of load balancers as I discussed in my post on load balancing best practices), which distributes those read requests to a pool of slave databases.

Database Read Proxy

To implement a solution such as this, your application needs to be made master/slave aware. That is, part of your application configuration must contain access information for the master database as well as the slave database (well, actually the load balancer, but your application doesn’t need to know that the “slave” it is talking to is not actually a slave). This requires intelligence built into your application to direct read and write requests appropriately, and also a modification to the typical load balancer setup to distribute TCP traffic instead of the standard and more common HTTP.

It is important to note that replication lag to the slave databases may result in outdated data being returned in response to a read request if the read is made quickly after the data is written to the master database. For applications that rapidly write and then read the same data object, a proxy solution may not be the most effective method of database scaling. With a read-proxy implementation, although database write performance is unaffected, read performance is enhanced since all read requests are distributed among all available slaves.

For applications that are read-intensive, a proxy solution such as the one shown above can provide a significant decrease in database load, and therefore a significant increase in application performance. Each application is unique, but read versus write requests should be benchmarked throughout an application’s lifecycle to see what, if any, benefit would be gained from a database proxy solution.

Database Sharding

Another option for horizontal database scaling with which many RightScale customers have had success is database sharding. Sharding is in essence the partitioning of your database tables into two or more distinct pieces, and placing these partitions on separate database servers. In the simplest approach, the application servers write to a single server using a modified connector, so they do not need to be aware of which shards reside on which servers, as this is handled by the sharding server (however, this sharding server is a potential single point of failure).

Optionally (and preferably) the sharding server can be removed and the application can be modified to be aware of the sharding implementation, and can contact the appropriate database server based on the data object to be accessed. Sharding is similar to a proxy solution in that database reads are spread across multiple servers, but due to the table partitioning, it also enables the database writes to be distributed, thus reducing the load on any individual database server.

Depending on the application characteristics and the sharding approach implemented, a fairly even distribution of database queries can often be accomplished, thus dramatically improving the overall database throughput of the deployment. Applications that rely on cross-table joins are not good candidates for sharding, since these queries may involve multiple servers, and can thus be slow to process. However, most databases can be sharded if planning is undertaken early in the design phase. Numerous RightScale customers with high traffic rates rely heavily on database sharding to handle the loads generated by hundreds of thousands (and sometimes millions) of concurrent users. Sharding can be implemented by the application developer, or if outside expertise is required, RightScale has partnered with CodeFutures, a technology provider that has assisted many RightScale customers with sharding implementations.

Why the Master-Master Database Is Not the Best Option

In a discussion of horizontal database scaling, the concept of a master-master implementation bears mention as well. In a master-master design, there are (as the name implies) multiple master databases, with each master having the ability to modify any data object within the database. It is the responsibility of each master to propagate any changes to the other masters to ensure database consistency. This is in direct contrast to the master-slave implementation in which changes to the data objects can only be made by the lone master.

While a master-master solution makes failover and failback scenarios easier to implement, it does not help in a true horizontal scaling fashion in that each master still needs to perform each and every write. It is for this reason that the master-master implementation is not a recommended best practice. With the potential for latency between masters, particularly as additional masters are added or aggressive replication systems are implemented, it is extremely difficult to ensure consistency between masters, and thus the integrity of the data. While some RightScale customers have had limited success implementing their own master-master solutions using highly modified application data processing schemes, it is not a recommended or supported architecture.

When NoSQL Solutions Make Sense

Recently, many new database/datastore technologies have become more commonplace in scalable application architectures. One of the most common categories of these new technologies is NoSQL database management systems. As the name implies, these technologies do not provide the common query interface present in SQL solutions.

Instead of the common relational aspects of most SQL implementations, these technologies are essentially key/value stores such that a particular database object is mapped to a specific unique key. The object that is stored can be of virtually any format, from a simple string or integer to a serialized object or complex blob file.

No relational associations exist between these objects, so NoSQL implementations are not viable database solutions for applications that require RDBMS-like functionality. However, for applications that do not require relational tables and structured queries, NoSQL solutions may provide a fast, scalable solution. In NoSQL implementations, multiple nodes are combined to provide the datastore, with additional nodes added to enable horizontal scalability. However, coordinated backup and recovery can be challenging, so careful evaluation of the capabilities and limitations of NoSQL solutions should performed prior to deciding on an application’s database architecture.

Don’t Let the Database Tier Be Your Bottleneck

The database tier is generally the linchpin of any application, and it’s oftentimes the primary bottleneck you will encounter as your application scales. As such, investigating the options for increasing the availability of your database implementation, as well as maximizing the effective life of the solution you currently have in place, is critical. However, finding options for those “good problems to have” situations (such as your application suddenly increasing in popularity) are just as important, if not more so.

Scaling each tier of an application’s architecture has its own unique set of challenges, each with its own set of benefits and drawbacks, but the database tier is typically where the real complexity comes into play. As is the case in most technology decisions, there are numerous options, but generally “one size fits all” is not one of them. So it always makes sense to investigate all the options that exist with regard to database scaling and availability. You’ll need to determine which ones provide the greatest benefit while also being aware that modifications to other components of the architecture may be required to allow you to take full advantage of the chosen solution.

I hope that my series on Architecting Scalable Applications in the Cloud has given you some ideas to assist you in creating a scalable, available, and resilient application architecture. Much of the information I’ve shared can be found in more detail in my white paper on Building Scalable Applications In the Cloud.

Thanks for the great articles. One thing worth mentioning in addition to your suggestions though is the potential benefit of tuning your database configuration and optimizing your database access: ensuring proper indexes are in place, selectively de-normalizing, eliminating N+1 queries, etc. It's not going put off horizontal scaling forever, but one large machine (especially with SSD or an in memory data set) can serve a staggering volume if properly configured and used efficiently.

Hey Mike,
Yup -- all very good points. This thinking is sort of in line with what I mentioned in the post regarding the caching tier... "the best way to prolong the effectiveness of the database tier you have is…to not need it" :) As you mention, finding ways to get every drop out of your current configuration will allow you to postpone implementing scaling changes in the future. I try to keep my posts away from RightScale promos, but much of what you mention is captured in our ServerTemplates (MySQL configuration tuning based on selected instance sizes), our Professional Services engagements (identifying sub-optimal queries, etc.), and also through our partner network (CodeFutures, Couchbase, etc.). Thanks again for your thoughts!