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

Bigger hardware is not always faster—but it can handle more load. It is more like a wider highway than a faster car—you cannot drive faster, well, you are not allowed to, just because there are three lanes free. That is why hardware will not fix slow SQL queries automatically.

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. It was like every new car drives twice as fast as the old one. However, clock rates hit the wall during the first few years of the 21st century. There was almost no improvement on that axis anymore. To continue building ever more powerful CPUs, the vendors had to move to a multi-core strategy. Even though it allows multiple tasks to run concurrently, it does not improve performance if there is only one task at all. Performance has just more than one dimension.

Scaling horizontally (adding more servers) has similar limitations. Although more servers can process more requests, they do not improve response times of single request. To make the request faster, you will typically use a (balanced) search tree—even in non-relational systems like CouchDB or MongoDB.

Proper indexing aims to fully exploit the logarithmic scalability of the B-Tree index. Sloppy indexing, on the other hand, causes sub-optimal index usage. The following chart, taken from Section 1, shows the difference between proper and sloppy indexing.

The response time difference is stunning. It is hardly possible to compensate for sloppy indexing by adding hardware. Even if you manage to cut the response time with some extra servers, it is still questionable if that is the best response to sloppy indexing.

Many of the so-called NoSQL systems still claim so solve all performance problems by scaling horizontally. This scalability is, however, mostly on the write side and accomplished by allowing temporary inconsistencies (eventual consistency). SQL databases maintain strict consistency. That increases the response time of 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.