|
The TPC-C database benchmark -- What does it really mean?We explain how TPC-C works and what exactly it reports so you can interpret results
|
Vendors compete for database business largely on the basis of published benchmarks such as TPC-C. Yet users often do not understand very much about what goes into these benchmarks and what they mean. This article describes what the TPC-C is and how it can relate to your work. (2,000 words)
Mail this article to a friend |
The TPC-C benchmark simulates a large wholesale outlet's inventory management system. The operation consists of a number of warehouses, each with about ten terminals representing point-of-sale or point-of-inquiry stations. Transactions are defined to handle a new order entry, inquire about order status, and settle payment. These three user interaction transactions are straightforward.
Two other transactions model behind-the-scenes activity at warehouses. These are the stocking level inquiry and the delivery transactions. The stocking level inquiry scans a warehouse's inventory for items which are out of stock or are nearly so. The delivery transaction collects a number of orders and marks them as having been delivered. One instance of either of these transactions represents much more load than an instance of the new order, order status, or payment transactions.
The TPC-C is a descendant of the previous TPC-A. In fact, changing the names of the fields in the new order transaction effectively produces a duplicate of the TPC-A transaction. Despite this clear lineage, the newer TPC-C is far richer in functionality than its predecessors.
In addition to having many more types of transactions, TPC-C also
mandates a much higher level of (simulated) user interaction. While
the TPC-A application consisted of exactly one call to
scanf(3)
and one call to printf(3)
, TPC-C
requires an entire application program to accept user input. The
application's operation is precisely specified, to prevent subtly
different interpretations of the specification to result in large
variations in benchmark scores. The specification even mandates the
appearance of the user interface screen on the terminals!
Although this is a major improvement over TPC-A, the TPC-C application still falls short of representing the typical database application. The most significant deficiency is that user input is not validated using the methods common to most applications. Most commercial applications are built with some sort of forms package, such as Windows for Data or JYACC. In addition to managing screen formats, these packages normally handle validation of input against the database. Typically, input data is validated on a field-by-field basis, as soon as the user leaves the field (such as using tab or return). In contrast, the TPC-C specification merely mandates that the input is validated; it does not specify how or when. So vendors customarily validate all input in a single batch, right before attempting to run the transaction. This reduces the number of interactions between the application and database and saves a great deal of overhead compared with normal applications. It's certainly possible to write applications this way, but in practice this approach is taken only when performance is critical, because it requires more programming effort and can sometimes be confusing to end users.
If your applications do not do batch input validation, you'll have to aim for considerably higher performance from your system. Although the SQL code to validate input is almost always very simple compared to the transactions themselves, most applications do a tremendous amount of it. As a result, it's often wise to add a third to a half to the target system's capability if you don't have an existing system to measure.
Advertisements
|
|
|
|
WAN considerations
Input validation is especially relevant when the clients and servers
communicate over a wide area network, because SQL data is
customarily transmitted over the network in relatively inefficient
form. DBMS systems communicate between client and server via TCP/IP,
and for a variety of complex reasons, they send each column of each
row in a separate packet. A column is something like a first name or
salary, although it might be something quite large, such as a
compressed photographic image. Most columns are pretty small,
averaging less than 200 bytes, so the TCP/IP overhead of 48 bytes
per packet becomes significant. The overhead isn't a big deal on
LANs, but on the restricted bandwidth of a WAN, this can be an
issue.
Even more problematic is the end-to-end round trip time on WANs. On a network such as an Ethernet, round-trip time might be one to five milliseconds, while the same trip on a wide area network could easily take 100 times as long. When the application makes a single call to the DBMS for validation like TPC-C, network round-trip time might not be significant. Many applications do so many round trips that the entire client/server configuration could easily miss its performance goals for this reason alone.
Client/server implementations
TPC-C is virtually always run in client/server mode, meaning that
the reported score is for a cluster of systems. Almost universally,
vendors separate the many instances of the user application from the
core database system. The only thing that runs on the machine that
is reported is the database engine itself. For example, consider a
result such as "Sun Ultra Enterprise 6000, 23,143 tpm-C using 16
processors, Oracle\x117.3.3, Solaris 2.6, and 11 Ultra-1/170
front-end systems." The approximately 20,000 simulated users log
into one of the eleven front-end systems, and their SQL requests are
sent to the Ultra Enterprise 6000 for processing.
This arrangement can have significant bearing on the interpretation of TPC-C results. If you are trying to size a system that will run application code as well as the database engine, you'll get quite an unpleasant surprise by relying too directly on TPC-C results. Fortunately, this sort of arrangement represents the minority of applications. The dominant database processing architecture is now client/server, in which the front-end application code runs on client systems, such as a PC or workstation, and the database system runs only the database engine itself.
The only fly in this ointment is that there are relatively few discrete client systems in most TPC-C configurations. For example, in the previous example there are only eleven client systems, each handling nearly 1,900 users. This type of client concentration is unlikely to occur in the real world. A system supporting 20,000 users would usually be connecting to more than 10,000 different client systems. The number of client systems is important, because vendors always take advantage of the limited number of clients systems and use a transaction processing (TP) monitor or some other form of connection multiplexor. This optimization isn't available if your application has 1,000 client systems, each connecting once to the DBMS server. The result is that there are 1,000 client connections on the server. However, TPC-C configurations universally use a TP monitor or some other software to reduce the number of active connections to just one to 10 per client system. As a result, there are many fewer active connections on the server, making it far easier to manage.
Batch processing
Another consideration that TPC-C does not take into account is batch
processing. Most real OLTP applications have at least two distinct
components: an online portion that creates and processes
transactions and a batch portion that reports on period work. Often
these batch jobs also reconcile daily activity with master databases
or extract data to support related decision support processing. For
example, bill processing and invoice reconciliation are tasks that
are almost always handled in batch jobs.
The TPC-C is far richer than either TPC-A or TPC-B in this regard because it includes the delivery and stocking level transactions. Both of these transactions manipulate far more than the individual records associated with line items and orders; instead they deal with groups of business transactions. However, both of these operations are quite small compared to typical batch operations. Real applications often include significant batch components. For example, the Oracle Financials application suite contains the concept of a "concurrent manager," essentially a batch processing stream used to handle large and unwieldy processing requests that would not be interactive in nature.
Because batch processing requires no user interaction, it tends to consume processor and I/O resources much more quickly than online users. It's not unusual for individual batch jobs to consume an entire processor and attendant I/O resources. When your application has a significant batch component, TPC-C is unlikely to reflect your environment very well. Unfortunately, there isn't much you can do to extrapolate TPC-C results to reflect this workload, either.
TPC-C reporting rules
One of the curious -- and very misleading -- things about TPC-C
scores is that they only report the rate of the new order
transaction. The other four transactions are used only as background
load to provide a context for the new order transactions. I'm not
completely sure why the TPC designed the reporting rules this way,
but this often confuses users of the results. The background
transactions are defined to be at least 57 percent of the mix, so
new orders are at most 43 percent of the work. This means that a
score of 1000 (new order) transactions per minute actually
represents over 2300 transactions (of all types) per minute. Anyone
attempting to size a system "according to TPC-C" should account for
true amount of work being done in the reported runs.
TPC-C scores in context
We've seen that delivered transaction rates are somewhat more than
doubled, and that they are most relevant in a client/server
environment. But what do these rates really mean? Let's take a look
at a large-scale result, but not one of the top scores, the Ultra
Enterprise 4000 using Informix 7.3 and ten Ultra-1/170 clients. The
reported transaction rate is 15,461 transactions per minute, so this
combination delivered about 39,955 transactions (of all five types)
each minute. Servicing about 15,000 users, this appears to be a
really big system. If we deflate the score by the additional 50
percent work (or so) necessary to handle real-life input validation,
the score becomes 10,312 tpm-C. That's a lot of transactions every
minute!
Server consolidation
Without consolidating multiple applications onto a single system,
most systems have no requirement for anything like this type of
throughput. With multiple applications running on a single system,
transaction requirements can approach these levels, especially when
the applications handle very large populations of users. TPC-C does
not reflect these environments at all. It uses a single application
with a single database instance, and the database locking strategies
are designed accordingly. When many applications are consolidated
onto a single system, they ordinarily do not use a single database
instance, and multiple applications almost never share databases.
Scalability of multidatabase configurations is different than that of single database systems as used in TPC-C. Scalability of a given system might be better or worse than seen in TPC-C. Scalability might be worse due to a variety of considerations, such as processor cache saturation or resource management issues within either the operating system or DBMS. Scalability might be better in a multidatabase or multi-instance configuration if the applications have suitable locking strategies and particularly when little or no data is shared between applications.
Summing it up
The TPC-C is best used for approximate comparisons between generally
similar systems. Because it is a highly optimized application with
characteristics such as a single application, batch input
validation, client/server configuration with very few client systems
and minimal batch processing, TPC-C doesn't predict actual end-user
performance as well as one might like. By considering many of these
common deviations from real workloads, a user can plan a
configuration without unrealistic expectations.
|
Brian L. Wong is Chief Scientist for the Enterprise Engineering group at Sun Microsystems. He has been a data processing consultant and software architect and has held various positions at Sun. Reach Brian at brian.wong@sunworld.com.
If you have technical problems with this magazine, contact webmaster@sunworld.com
URL: http://www.sunworld.com/swol-08-1997/swol-08-database.html
Last modified:
|
Resources
About the author
Brian L. Wong is Chief Scientist for the Enterprise Engineering group at Sun Microsystems. He has been a data processing consultant and software architect and has held various positions at Sun.
Reach Brian at brian.wong@sunworld.com.
If you have technical problems with this magazine, contact webmaster@sunworld.com
URL: http://www.sunworld.com/swol-08-1997/swol-08-database.html
Last modified: