Web Development

Improving OLTP Database Throughput via Batching

By John Lane, February 17, 2011

How to improve throughput for On-line Transaction Processing (OLTP) database applications without introducing the overhead of contention between threads

John Lane is a freelance consultant specializing in high-performance systems, developing application code, optimized databases, algorithms, and protocols. He can be contacted through his website at http://www.sturnus.co.uk/performance/.

Parallel threads and JDBC connection pools can be useful, but should not be used blindly. When handling numerous transactions with small overhead on heavily contended systems, batch processing often provides a better solution.

Order Management System

The case history is simple enough. The system consisted of several Java-based order management servers that held details of orders for financial instruments. The orders themselves were made up of more than 100 attributes, including product type, order size, bid and ask prices, etc., and they would frequently be updated after they were placed.

[Click image to view at full size]

Figure 1.

To allow for the database being unavailable, the servers had their own local persistent storage, to which they would initially save order details. Periodically, they would send new or updated order details to the database in bulk. Because the order management system was Java based, the updates were implemented as stored procedure calls via JConnect (JDBC) to a Sybase Adaptive Server Enterprise (ASE) database server.

The system developers decided that to maximize throughput, they would use 20 parallel threads, with a JDBC connection pool to access the database. The database server utilized three of the available single-core processors, but the system suffered from a high degree of contention and deadlocking. In fact, the system was designed with a backing-off algorithm such that the time between deadlock retries would be increased with each failure, until it eventually gave up and waited for an hour before repeating the process.

Inevitably, the order management servers would end up deadlocking with themselves over different pool connections, and thus updates would often fail 100 times. As a result, orders frequently took a considerable length of time to be persisted to the database. What went wrong?
Note that while this article uses this particular system as a case study, it could equally be applied to any On-line Transaction Processing (OLTP) database application.

Query Lifecycle

Several steps occur during the life of a single database query. These will vary, depending on your particular RDBMS, but will generally include:

Initialization of the client library (e.g. JDBC) structures

Sending the query to the server, using a vendor-specific protocol over some communication mechanism, e.g. Tabular Data Stream (TDS) over TCP/IP

Parsing and compilation of the query

Executing the query

Returning the query results to the client

If we were to construct a rough time line of the query, it would look something like that shown in Figure 2.

[Click image to view at full size]

Figure 2.

When a query executes on a server, it continues to run until either it has used up its allotted time slice (and is swapped out for another query), or a condition occurs that prevents it from continuing. There are many such conditions, but some of the more significant ones are as follows:

A lock cannot be obtained because another connection is already holding it

A page is not in the cache and must be read from disc

A write to disk is required

At the point where one of these conditions occurs, the query will be put to sleep until the condition has been resolved. For example, if a query has been put to sleep because it was waiting for a lock, it will resume once the lock has been obtained.
From the perspective of a single thread, the time spent sleeping — waiting for a lock or I/O — is "dead time." It follows that if we want the query to execute faster, we should reduce the length of time that each step takes to complete or minimize the amount of dead time.

In reality, of course, ours may not be the only query running on the server. So dead time for our query is time that the server can use to do something else, such as executing another query. Indeed, for many different execution threads, and systems as a whole, we tend to think more about overall throughput of the system rather than response time for individual queries. It would seem that the next logical step, therefore, is to make use of the multithreaded nature of servers by using multiple connections — in effect, "filling in" the dead time with other queries.

We must remember, however, that running multiple threads is only useful if there is indeed some dead time. Even with a low ratio of threads to processor cores, there can be sufficient work for the server to fill in all the available time. Further connections will only result in the queries being queued up, and the server experiencing increased contention for resources. This is important to bear in mind with systems that are heavily CPU bound, because there will be little natural dead time for other queries to make use of. This is what happened on our system: The developers of the order management system failed to take into consideration that the ratio of 20 threads to 3 processor cores was far too high on a CPU-bound database server, and it contributed to their high contention and deadlocking problems. So how can we improve throughput without introducing the overhead of contention between threads?

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task.
However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

This month's Dr. Dobb's Journal

This month,
Dr. Dobb's Journal is devoted to mobile programming. We introduce you to Apple's new Swift programming language, discuss the perils of being the third-most-popular mobile platform, revisit SQLite on Android
, and much more!