The answer is to use batching. I'm sure that most of you will have heard of batching, but it may not be immediately apparent exactly why batching works, nor how much of an improvement can be obtained from using it. In database terms, a batch is a single string or statement sent to the server. If you are using ISQL with a Sybase or SQL Server database, the text before you type "go" is a single batch. If you are writing application code, multiple queries can be concatenated together into a single string (separated by white space) and sent as a single batch.
On an abstract level, a batch of work usually consists of a fixed cost or overhead and a marginal cost. The overhead is the fixed cost of performing the batch no matter how large the batch, whilst the marginal cost is the additional cost per extra element of work.
The various costs include these factors:
- The overhead in sending network packets can be large compared to the marginal cost of increasing the size of a packet
- An acknowledgement packet is probably the same size no matter how large the original packet was
- The time taken to set up and start parsing or compilation can be great compared to the time taken to parse or compile each additional query.
For OLTP systems, when the overheads are comparable to the execution times for individual queries, they can become bottlenecks themselves.
So the reason that database batching works is simple: When the fixed cost of a batch is significant compared with the marginal cost of a query, combining many queries into a single batch effectively shares the overhead between them. The more queries in a batch, the smaller the overhead per query.
Results in Practice
To illustrate the savings, I created a test system consisting of 500,000 rows of pseudo-orders, each having a unique identifier and 10 further attributes. I wrote a simple stored procedure that updated orders and called it multiple times, using random values. To remove the effects of caching, the data cache was sized to be larger than the order database, and was "warmed" beforehand by running a set of queries to ensure the whole database was held in memory.
The results of running the test with different numbers of updates in the same batch are shown in Table 1.
As you can see from the table, the throughput increases dramatically when we move from a batch size of 1 to a batch size of 5. This improvement continues as we increase the batch size, although by a smaller amount each time.
I should point out that this behavior is only useful for true OLTP queries — those queries that require a small amount of work (network communication, CPU cycles, I/O etc.) to process. Only in these types of queries are the overheads a significant proportion of the overall cost. If we are running queries that take many seconds to complete, batching them will yield little noticeable gain.
In contrast to batching, if we use a purely multithreaded approach, the fixed costs are multiplied: Every query carries the same overhead.
The actual implementation of batching will depend on your development platform. Using Java and with JDBC to connect to the database, you can use the batching facility provided by JDBC to implement this easily. There is one very important caveat, however: You must verify that the batching functionality is properly implemented in your JDBC driver/database server. Early versions of some JDBC drivers, for instance, did not actually batch queries into a single string — they were functionally equivalent to sending individual queries.
If your version of JDBC, or particular JDBC driver, does not batch correctly, or you are using connection libraries that do not support batching, then you will need to implement this yourself. The way to do this is to simply to build up a single string, concatenating all the queries together.
One important point to mention is that, as yet, we have not changed the transaction granularity — each query is still in a separate transaction. If we look back at the timeline shown in Figure 2, we can see that the commit portion of the update itself adds an overhead to the execution time because we must wait for the log to be flushed to non-volatile storage. We can take our batching one step further, and share this overhead between queries by combining them into single transactions.
Indeed, in the absence of contention with other connections, this will have a further dramatic effect on throughput. Care should be taken, however, to balance the transaction granularity with the increased contention that will result: Locks will be held for longer, and there will be an increased risk of deadlocks. If we use query batching but maintain single-update transaction granularity, we get the best of both worlds: Efficiency is improved without a corresponding increase in contention.
The lead developer of the order management system was quite horrified when I suggested dropping connection pools and moving to a single-threaded model using batching. He wouldn't even consider reducing the number of threads in an attempt to reduce the contention issues. The horrendous number of deadlocks wasn't enough, it would seem, to convince him.
Had he done so, he would have seen his database contention all but disappear, and may have been quite surprised exactly how much of an improvement he obtained. While the results on my test system are very modest, I have often observed quite a dramatic increase in throughput with real systems: One particular application improved its throughput by over 36 times, simply by implementing batch sizes of 500 — and there wasn't a single connection pool in sight!
Your results will (of course) vary, depending on where the bottlenecks lie in your particular system, but my suggestion is to use a two-pronged approach to increasing throughput:
- Batching queries is efficient because it shares (and thus minimizes) a number of overheads; creating parallel threads multiplies them. Always try batching your OLTP queries before you implement multiple threads, and tune the batch, transaction, and network packet sizes according to your application requirements.
- Use multiple threads of execution only after you have exhausted the improvement that you get from batching queries. Make sure your throughput is actually increasing by means of testing. Remember: Less is more. Start with a low ratio of threads to processor cores, and increase the number gradually — monitoring the throughput until you reach a peak.
Combining the two approaches can yield significant performance improvements.