2011-05-02Response Time, Throughput and Horizontal Scalability

Bigger hardware is not always faster—but it can usually take more load. It is more like a highway with more lanes—it will not make you travel faster. That’s the reason suspicious SQL response times will not automatically improve on bigger hardware.

We are not in the 1990s anymore. CPU clock rates were increasing rapidly at that time. Most response time issues disappeared on newer hardware—just because of the higher clock rate. That was like making the car go faster. However, clock rates hit the wall during the first few years of the 21st century. There is almost no improvement on that axis since then. Multi-core is the new strategy to build more powerful CPUs. Even though it allows multiple tasks to run concurrently, it doesn’t improve performance if there is only one task running at all. It’s a different axis—like having more lanes on the highway.

Scaling horizontally (adding more servers) has a similar limitation. Although more servers can process more requests, they won’t improve response times automatically. Response time reductions are typically achieved with a (balanced) search tree—even in non-relational systems like CouchDB or MongoDB.

Proper indexing aims to exploit the logarithmic scalability of the B-Tree index to its full extent. However, most response time problems are caused by sloppy indexing—that is, suboptimal index usage. The following chart, taken from Section 1, shows the difference. It plots the response time to a simple SQL query on growing data volume. Once in red, when the query uses an suboptimal index. And once more in green, using a refined index. The previous section explains the setup in detail.

Figure 3.5. Response Time by Data Volume

The response time difference is stunning. And it is hardly possible to improve it by scaling horizontally. Even if it would be easy to cut the response time by adding more servers, it’s still questionable if that is the best response to sloppy indexing.

The horizontal performance gains of the so-called NoSQL systems are mostly on the write side—often reached with the eventual consistency model. Simply put, they allow temporary inconsistencies that will eventually become consistent. That’s not done for fun. It’s a limitation implied by Brewer’s CAP Theorem. SQL, on the other hand, enforces very rigid consistency. That increases response times for write operations but does not necessarily imply bad throughput.

More hardware will typically not improve response times. In fact, it might even make the system slower because latencies can make up most of the response time. Network latencies won’t be a problem if the application and database run on the same computer. But that setup is rather uncommon in production environments where the database is typically running on dedicated hardware. Security policies might even require a firewall between the application server and the database—often doubling the network latency. The more complex the infrastructure, the more latencies accumulate, the slower the response. That’s often leading to the counterintuitive observation that the production hardware, that is supposed to be very powerful, responds slower than the cheap desktop PC that was used during development.

Another very important latency is the disk seek time. Especially spinning hard disk drives (HDD) need a rather long time to move the mechanical parts so that the requested data can be read—typically in the milliseconds range. That means that a B-Tree traversal with four levels needs four times as long—typically a few dozen milliseconds. Although that’s half an eternity for computers, it is way below human perception threshold—if it’s done only once. It is, however, very easy to write SQL statements that cause many hundred disk seeks just by joining a few tables. Although caching reduces the problem drastically and new technologies like SSD might improve the seek time by an order of magnitude, joins are often causing response time problems. The optimizer puts a huge effort into finding the best join path, but it is still limited by the available indexes on the underlying tables. The next chapter will therefore explain how to index for efficient table joins.