Tuesday, July 17, 2012

Scaling The Relational Database

Scalability of a web application is the ability to handle increased load whether it is requests or number of users or data without having to redesign or re-architect the application. Scalability should not be confused with performance or raw speed.

One can scale by using bigger components : bigger machine, more memory, more cpu. This is vertical scaling. One can also scale by adding more copies of the same component to share the workload. This is horizontal scaling.

In a typical multi tiered web application, the middle tier, where the application logic executes, scales easily by going stateless or using a session cookie with state stored to a centralized storage. The middle tier thus scales horizontally by just adding more application servers. In reality, it has just punted the problem down the stack to the centralized storage which generally is a relational database. The database thus becomes that hardest component to scale.

The typical multi tiered web application starts with the architecture shown in figure 1. As the application become popular, the number of users increase, the number of concurrent reads and writes increase. The application slows down to a crawl and eventually grinds to halt like a braking train. In the rest of this article we discuss some strategies to avoid such a situation.

To understand issues involved in scaling the database, it is useful to think in terms of the two primary client operations on a database; READ and WRITE. Clients either read from a database or write to the database. READs can be scaled easily by adding additional servers, replicating the data and distributing the read requests across servers. Scaling WRITEs is much more complicated. Simply distributing write requests across servers will not work because the it is difficult to maintain consistency of data across servers.

Scaling reads: Master - Slave configuration

As mentioned above, a simple master slave configuration as shown in figure 2 will scale READs. In most web applications 80% of the traffic is read requests and 20% write request. Hence most of time, this configuration provides significant relief.

All WRITE requests are sent only to the master. READ requests are sent to the slaves. The master is replicated to the slaves. Note that a READ from a slave is not any faster than a READ from a master. This is because every WRITE on the master leads to a WRITE on the slave because of replication. However because there can be multiple slaves and READ request distributed across slaves, the system as whole has higher through put. As the number of READ requests go up, you can continue to scale by simply adding more slaves.

Master - Master configuration

In the master - master configuration shown in figure 3, the two servers are setup to replicate to each other. READ and WRITE requests are sent to both servers. While this gives the appearance of scaling WRITEs as well, this approach has some serious disadvantages.

Since there can be a replication lag, the data in the servers might not be identical in certain time windows, leading to read inconsistency. If any columns are ids that needs to be incremented, the logic will need to be implemented at an application level, since that has to be coordinated across the servers. You will not be able to use database features like auto incrementing ids. This does not scale beyond a couple of  servers as each WRITE on every server has to be replicated to every other server.

Scaling writes : partitioning the database

The only way to scale WRITEs is to partition the database. The WRITE requests are sent to different instances of the database which may have the same or different schema. There is no replication or sharing between the instances.

Figure 4 shows an architecture where the database is partitioned by moving some of the tables to different database instances. Tables that needs joins need to be on the same instance. You cannot do SQL joins across servers. This approach works when you have many tables in the schema and some of the tables are not really related to others. This increases application complexity. The application needs connections to several instances and be aware of which instance has which table.

If you have a schema with few tables , but a large number of rows in the table, then another strategy is to keep the schema in instances the same but partition the data across servers based on some key range. For example , a USER table which has a billion rows with users from the all over the world can be partitioned across instances based on the geographical location of the user, say the continent. Figure 5 shows such an architecture. Again this requires the application logic to be smart enough to know which database instance to connect to , based on say a key value. To keep application logic simple, it helps to write a layer that handles the partitioning for the application.

Scaling even further : NoSql

If your data is even larger. of the order of  petabytes or several hundred terrabytes and ACID consistancy is not a hard requirement, you might consider NoSql datastores as discussed in What is NoSql ?