The Transaction Processing Performance Council (TPC) benchmarks C (TPC-C**)¹ and
D (TPC-D**)² have emerged as the de facto standard benchmarks for on-line transaction processing (OLTP) systems and decision support systems (DSS), respectively. By establishing objectives that are easily measurable and repeatable, such standard benchmarks define a transparent playing field and focus attention on what the benchmarks consider to be important. However, the real utility of the benchmarks is determined by whether they represent the workloads of interest. To effectively make use of a benchmark, therefore, we have to carefully evaluate its characteristics against those of the target workloads to understand how closely they correspond. Although the TPC-C and TPC-D benchmarks have become widely accepted and, as a result, are heavily used for both systems design and marketing, there has not been any major effort to empirically determine their workload characteristics, let alone to establish how representative their characteristics are of real workloads.

In fact, there has been very little empirical analysis of any real production database workloads. This reflects the fact that production systems are by definition critical to the proper functioning of an organization, so that it is very difficult to get access to them for the purpose of conducting a scientific study, especially if the study requires any software changes or if data are to be collected and removed from the system. Therefore, although the hallmark of a good benchmark is that it should capture all the essential characteristics of the workload of interest without undue complexity, we often do not have a clear picture of the characteristics of the target workload. This lack of information is highly undesirable because a poorly designed benchmark may impede real progress in the field if it is not realistic and end up focusing energy and attention on issues that do not often arise in production environments.

In this research, we use trace-driven simulations^3,4 to empirically examine the characteristics of the peak production database workloads of ten of the world's largest corporations as well as workloads similar to the TPC-C and TPC-D benchmarks. Our main focus in this paper is on what we call descriptive system-level characteristics. These characteristics are the logical properties of a workload that a user or system administrator can readily understand and relate to without requiring detailed knowledge of the internals of the system. We compare and contrast such characteristics of the production workloads with those of the TPC benchmarks, paying special attention to any performance implications. In a companion paper,⁵ we examine in detail the I/O reference behavior of the workloads.

The traces used in this study were collected on systems running IBM's industrial-strength DATABASE 2* (DB2*) relational database management system (DBMS) and, to the best of our knowledge, represent by far the most complete and diverse set of production workloads ever reported in the literature. We cannot overemphasize the amount of time, effort, and cost that these traces represent. This research would not have been possible without the support and help of many. Note that because our TPC benchmark setups have not been audited per the benchmark specifications, our benchmark workloads should only be referred to as TPC-like. In the rest of this paper, when the terms TPC-C and TPC-D are used to refer to our benchmark workloads, they should be taken to mean TPC-C-like and TPC-D-like, respectively.

Our analysis indicates that in some cases, the TPC benchmarks fall reasonably within the range of real workload behavior, and in other cases, they are not representative of real workloads. Some of our findings are (1) TPC-C tends to have longer transactions and fewer read-only transactions than the production workloads, whereas some of the transactions done by TPC-D are much longer but are read-only and are run serially, (2) the production workloads have I/O demands that are much more bursty than the TPC benchmarks, (3) unlike TPC-C, which has very regular transactions, and TPC-D, which has long queries that are run serially, the production workloads tend to have many concurrent and diverse transactions, and (4) TPC-C has no I/O activity involving temporary objects, whereas most of the references for TPC-D are directed at index objects.

The next section of this paper contains a brief overview of previous work in the area of workload characterization and analysis. The third section discusses our methodology and describes the traces that we use. The characteristics of our workloads are presented in the fourth section. Concluding remarks are then given. Because of space constraints, we can only highlight some of the results of our analysis in this paper. More detailed graphs and data are available from our Web site⁶ and in References 5 and 7.

Related work

There have been several published studies of the reference behavior of database workloads, but mostly for hierarchical and network databases. See, for instance, References 8 through 16. For a more complete bibliography, the reader is referred to Reference 5. Unfortunately, most of these studies rely on data collected at one or two installations. Furthermore, they do not provide descriptive characteristics of the workloads being analyzed, even though the reference behavior clearly depends on the workload imposed on the database. Without knowing the kinds of workload that are being examined, interpreting the results of the studies is very difficult. Consequently, there seem to be conflicting conclusions as to whether locality or sequentiality is present in the database reference stream. The work reported by Zivkov and Smith,¹⁶ which investigated design issues in disk caches using data from commercial installations, is one of the notable exceptions that provides some characteristics of the workloads analyzed. In addition, a recent study of lock contention in database systems contains some transaction statistics from commercial DB2 installations.¹⁷

Though the TPC-C and TPC-D² benchmarks have clearly been extensively studied and optimized by both database and system vendors, there has not been any systematic attempt to characterize these workloads empirically and to compare their characteristics with those of production database workloads. On the basis of static analysis of accesses to tables, Leutenegger and Dias¹⁸ looked at the data access skew of TPC-C. The paper by Tsuei et al.¹⁹ contains an empirical study of how the database size, buffer size, and the number of CPUs affect the throughput and buffer hit rate of TPC-C on symmetric multiprocessors (SMPs). Given that business applications are increasingly developed on standard business application systems commonly known as Enterprise Resource Planning (ERP) systems, Doppelhammer et al.²⁰ examined the performance of TPC-D queries when implemented on such systems. Recently, Hsu et al.²¹ analyzed the query plans taken from certified TPC-D setups and considered the potential benefit of off-loading TPC-D operations to storage systems with embedded processors.

File usage characteristics in commercial computing environments are studied in the paper by Ramakrishnan et al.,²² but most of the work in this area has focused on academic environments (e.g., see References 23–27). Several other studies have focused on the effectiveness of caching in the file system.^28,29 There has also been a large body of work on characterizing scientific workloads in parallel and supercomputing environments. Again, we refer the interested reader to Reference 5 for a more detailed bibliography.

Methodology

The methodology used in this paper is trace-driven simulation.^3,4 In trace-driven simulation, relevant information about a system is collected while the system is handling the workload of interest. This is referred to as tracing the system and is usually achieved either by using hardware probes or by instrumenting the software. In the second phase, the resulting trace of the system is played back to drive a model of the system under study. In other words, trace-driven simulation is a form of event-driven simulation where the events are taken from a real system operating under conditions similar to the ones being simulated. More comprehensive discussions of this technique and its strengths and weaknesses can be found in References 3 and 4.

The traces used in this study were collected by instrumenting commercial DBMSs. Instrumenting a DBMS allows the trace information to be collected at a logical level. This method reduces dependencies on the system being traced and allows the trace to be used in a wider variety of studies, including simulations of systems different from the original system. The traces contain references to all database objects (base tables, indexes, temporary spaces, catalogs, views, and plans) except the log. Some of the traces contain references to large pages, i.e., those with sizes that are multiples of the 4KB base page size. For consistency, we converted these traces to refer to 4KB pages.

In this study, we examined a total of 14 traces representing both industry-standard benchmarks (TPC-C and TPC-D^1,2) and the production workloads of ten of the world's largest corporations. The TPC benchmark traces were collected on a multiprocessor personal computer (PC) server running DB2 Universal Database* (DB2/UDB) v5³⁰ on Windows NT** 4.0. The production traces were collected on IBM mainframe computers running various versions of DB2/Multiple Virtual Storage (DB2/MVS), now known as DB2/390.³¹

In order to make our characterization more useful for subsequent mathematical analyses and modeling by others, we fitted our data to various functional forms through nonlinear regression, which we solved by using the Levenberg-Marquardt method.³² When appropriate, we also fitted standard probability distributions to our data by using the method of maximum likelihood to obtain parameter estimates and then optimizing those estimates by the Levenberg-Marquardt algorithm.³²

Trace collection. We instrumented DB2/UDB at the source level to collect relevant trace information for the TPC benchmarks. Because the act of tracing a system may affect its behavior, we paid special attention to minimizing any such disturbances. For instance, our tracing facility collects the trace records in shared memory before batch writing them asynchronously to disk. The shared memory buffer is double-buffered so that trace collection is not blocked during write-backs. Each trace record is time-stamped with minimal overhead by directly accessing the processor cycle counter. At certain trace points, it is expensive to collate all the interesting information. In such cases, enough data are written to the trace so that an off-line postprocessing step can be used to reconstruct the information. We collected trace records for both logical and physical reads and writes, prefetch requests initiated by DB2, references to the database log, and transaction starts and ends. By comparing the TPC-C throughput results when trace collection is enabled and disabled, we estimate that this tracing mechanism imposes an overhead of less than 5 percent. This figure is dramatically lower than tracing overheads that have been previously observed; GTF (Generalized Trace Utility) tracing can require over 50 percent of the CPU time.

The production traces were collected using a custom DB2/390 tracing package developed at the IBM Almaden Research Center. This tracing package was designed to collect trace data with a minimum amount of overhead so that it can be run on customer production systems with little throughput impact. It was built upon the existing DB2 Instrumentation Facility and its performance trace.³³ The basic approach is to use a DB2 exit routine to collect the required data from a specially instrumented DB2 build. The collected data are assembled into trace records and stored in large memory buffers that are written out by a separate task operating asynchronously in another address space. This tracing package collects trace records for buffer manager requests, transaction boundaries, and locking events. In tests conducted on an IBM 4381-T92 when handling a DB2 transaction-oriented workload at 70 percent CPU utilization, the trace collection added only about 4 percent to the CPU utilization.

The buffer pool interface in both DB2/UDB and DB2/390 allows pages to be “fixed,” or pinned, in memory.^34,35 Once a page is fixed, the buffer pool interface can be bypassed so that data within the page can be directly manipulated by the various DBMS components. Such bypassing allows the DBMS components to use the buffer pool as working storage, thereby eliminating the need for the components to make local copies of the data. Consequently, there are references within the pinned pages that result from direct manipulations by the DBMS components that are using the buffer pool as working storage. Since our traces were collected at the level of the buffer pool interface, they do not contain such references that reflect the direct use of buffer pool storage as working storage.

Workload description. The TPC-C benchmark models the operational end of the business environment where real-time transactions are processed.¹ It is set in the context of a wholesale supplier and is centered around its order-processing operations consisting of business transactions that enter new orders, query the status of existing orders, deliver outstanding orders, enter payments from customers, and monitor warehouse stock levels. The TPC-C performance metric is the number of orders processed per minute. The benchmark specifies a method for scaling the database based on an assumed business expansion path of the supplier. Our particular trace was collected on a benchmark set up with a scale of 800 warehouses.

The TPC-D benchmark models the analysis end of the business environment where trends are analyzed and refined to support sound business decisions.² The TPC-D database is a decision support database that tracks, possibly with some delay, the OLTP database through batch updates. The benchmark consists of 17 read-only queries that are far more complex than most OLTP transactions and that typically examine large volumes of data using a rich set of operators and selectivity constraints. To exercise the update functionality of the DBMS, the benchmark includes two update functions that modify a small percentage of the database. The TPC-D benchmark defines both a power test to measure the raw query execution power of a system with a single active user and a throughput test that may be omitted. Our trace captures the entire run of a power test. This test starts off with the first update function (UF1). Next, the 17 queries are processed in a sequence specified by the benchmark. Finally, the second update function (UF2) is executed.

As with TPC-C, the TPC-D benchmark specifies a method for scaling the database. Our trace was collected on a system with a scale factor of 30, which means that the two largest tables, ORDER and LINEITEM, contained 45 million and 180 million tuples, respectively. In general, the actual workload imposed on the system varies with the scale factor and could lead to very different strategies or plans for performing the queries. Although the analysis presented in this paper is specifically for a scale factor of 30, we believe that most of the qualitative results apply to other scale factors as well.

More details about the benchmarks can be found in Reference 7 and in the benchmark specifications.^1,2 Note that the TPC benchmark rules prohibit publicly disclosing TPC performance figures that have not been independently audited. Therefore, we withhold from this paper any data that may be used to derive our TPC metrics. This omission of absolute TPC performance numbers should not compromise our understanding of the logical characteristics of the benchmarks.

Our other traces were collected in the day-to-day production environments of a diverse group of very large corporations. The industries represented include aerospace, banking, consumer goods, direct mail marketing, financial services, insurance, retail, telecommunications, and utilities. In all cases, our traces include the peak production database workload as identified by the system managers. This workload is typically a combination of transaction processing and long-running queries. The trace referred to as Telecom in Zivkov and Smith¹⁶ and Phone in Singhal and Smith¹⁷ is the first 30 minutes of the trace we call TelecomB1.

Trace description. Table 1 summarizes the characteristics of the various traces that are used in this paper. Because of the large number of production workloads, we often also present the arithmetic mean of their results, denoted as “Prod. Ave.” In the table, the term object refers to a logical collection of data, such as a database table or an index, that is managed as an entity in much the same way as a file. Data size represents the total size of all the objects in the system and was obtained from the catalog dumps that were taken when the systems were traced. The footprint of a trace is defined as the amount of data referenced at least once in the trace. The traces record information from the perspective of the DBMS. Therefore, the object count includes DBMS system objects such as catalogs, views, and query plans. In addition, the transactions recorded are database transactions, several of which may be needed to perform a single business transaction.

Table 1  Summary of trace characteristics

Trace Aerospace Bank ConsGds DirMktg1 DirMktg2 FinSvcs Insurance Source Aerospace company Banking corp. Consumer goods company Direct mail marketing firm Direct mail marketing firm Financial services firm Insurance company Platform MVS on IBM S/370 MVS on IBM S/370 MVS on IBM S/370 MVS on IBM S/370 MVS on IBM S/370 MVS on IBM S/370 MVS on IBM S/370 DBMS DB2/MVS DB2/MVS DB2/MVS DB2/MVS DB2/MVS DB2/MVS DB2/MVS Date Collected 2/3/1992 5/13/1991 9/8/1992 9/18/1991 9/19/1991 6/6/1991 10/7/1992 Duration (h:m) 2:29 22:57 1:59 1:03 2:02 3:54 2:41 # Objects 2203 1281 626 1446 1446 3124 1953 Data Size (MB) 33558 53079 3423 18191 18191 10064 38095 Footprint (MB) 1397 9600 726 1137 1362 2127 1732 # References 7779007 35916414 7133845 6401880 14396125 15664004 20648874 # Xacts 98931 85173 66102 11892 14906 20956 70242 Read Ratio (%) 93.8 90.6 86.9 95.4 95.6 90.9 84.8

Trace Retail TelecomA TelecomB1 TelecomB2 Utility Prod. Ave. TPC-C TPC-D Source Discount store Telecom. company A Telecom. company B Telecom. company B Utility company — TPC benchmark C TPC benchmark D Platform MVS on IBM S/370 MVS on IBM S/370 MVS on IBM S/370 MVS on IBM S/370 MVS on IBM S/370 MVS on IBM S/370 WinNT on Intel X86 WinNT on Intel X86 DBMS DB2/MVS DB2/MVS DB2/MVS DB2/MVS DB2/MVS DB2/MVS DB2/UDB DB2/UDB Date Collected 7/1/1992 4/15/1992 10/8/1990 10/9/1990 5/14/1991 — 2/10/1998 3/8/1998 Duration (h:m) 4:52 1:40 2:27 1:42 3:16 4:15 (withheld) (withheld) # Objects 434 521 255 255 1139 1224 101 192 Data Size (MB) 72188 197422 15114 15114 39070 42792 70246 77824 Footprint (MB) 6769 2986 947 976 5727 2957 13267 51580 # References 38646360 13072916 11531195 13757374 37653369 18550114 196067649 218130354 # Xacts 797637 84378 36508 25899 118191 119235 890885 230 Read Ratio (%) 86.9 85.9 93.0 98.1 89.3 90.9 87.4 97.8

The production traces were taken from the primary systems in use at some of the world's largest corporations in the early 1990s. These installations had some of the highest-end IBM mainframe systems available at the time. Unfortunately, we do not have information regarding their exact system configurations. Note that the mainframe platform is very different from the PC platform on which our benchmark traces were collected. MVS has its roots in the IBM System/360* architecture and was originally designed to provide full support for large-scale batch processing in production environments. Windows NT, in contrast, is a recent operating system with a built-in graphical interface that is designed to support interactive use on both workstations and servers. In addition, DB2/MVS and DB2/UDB are two distinct implementations of relational DBMSs. The focus of this paper is on the logical characteristics of the workloads, which should be relatively independent of the physical attributes of the systems. Nevertheless, some dependencies are unavoidable at times, and we note them where appropriate.

By analyzing what is by far the largest set of production traces ever reported in the literature, we believe that our results are illustrative of the actual production workloads in very large corporations in the early 1990s; nevertheless, neither the individual traces nor the averages can be assumed to be typical or representative of any other system. Our data represent only a sample. In addition, given the rapid progress in database technology and applications, especially in the decision support area, workloads from the early 1990s are likely to be different from workloads and benchmarks several years later. For instance, companies are increasingly building their mission-critical applications on standard business application systems rather than directly on database systems. Despite these disclaimers, we believe that most of the characteristics observed in these production workloads are common to many database systems today and that valuable insights are gained by comparing them to the TPC benchmarks.

Figure 1 plots the trace footprint as a function of the number of references, which is a measure of the trace length. Because there is a wide variation in the footprint of our traces, we plot the footprint as a percentage of the total data size of the workload and use two different scales in Figures 1 A and 1B to facilitate comparison among the workloads. From the two parts of the figure, only a few of the traces (e.g., TPC-D, Bank, ConsGds, FinSvcs, TelecomA) approach steady state in the sense that they do not appear to be actively referencing new data. Though the artificial nature of TPC-C is apparent in the smoothness of its footprint profile, the rate at which it references new pages is within the spectrum defined by the other traces. The write footprint profiles are presented in Figure 2. These profiles show how the percentage of pages written increases with the number of references. Compared to most of the production traces, the TPC traces generate modified pages at a much higher rate. We examine the write behavior of the various workloads in greater detail in Reference 5.

Figure 1 Figure 2

An important issue in using trace-driven simulations to study memory hierarchy design is that the traces must have a sufficiently large footprint for the memory configurations of interest. However, estimating the length of trace required is difficult because the relationship between the trace length and footprint is not well understood. In this paper, we empirically determine this relationship by looking at the average footprint of our production traces. Because the traces are of different lengths, the number of traces being averaged will decrease with the trace length so that the resulting curve will contain discontinuities if we simply average the footprints. Therefore, we take the average of the rate of increase of the footprint and then integrate the resulting expression. More formally, we define the average footprint after X references as

	x	d	(fi(x)) dx
		dx
	0

where f_i(x) denotes the footprint of trace i after x references. This expression is plotted as the lines labeled “Prod. Ave.” in Figures 1 and 2. Note that we omit “Bank” in plotting the average because its footprint profile is distinct from any of the other production workloads.

We find that the relationship between trace length and footprint can be accurately described by the Hill equation that was originally proposed for modeling the absorption of oxygen by hemoglobin.³⁶ The Hill model, Hill(f_max, k, n), represents a family of sigmoidal saturation curves defined by

f(x) =	fmax • xn
	k + xn

where f_max is the asymptotic value of f(x) and k and n are parameters that determine the shape and slope of the curve. In our current context, the value of f_max represents the percentage of data that is predicted to be in active use. For instance, from Figure 1, the model predicts that 23.2 percent of the data will be referenced if the trace is infinitely long. From Figure 2, the model predicts that only 9.84 percent of the data will be written to.

Workload characteristics

In this section we describe the various characteristics of our workloads.

Transaction characteristics. Transactions are the building blocks of a database workload. The characteristics of transactions are therefore good reflections of the nature of the workload. Table 2 summarizes the transaction characteristics of our workloads. In this table, we consider the logical read ratio that is defined in terms of references to permanent objects only. Table 2 also contains data on the page reuse of transactions. Page reuse is defined as the ratio of the number of references to the number of pages referenced and is an indication of the locality of reference exhibited by the transactions.

Table 2	Transaction characteristics—%-tile denotes the percentile at which the average value occurs

Trace Aerospace Bank ConsGds DirMktg1 DirMktg2 FinSvcs Insurance # 98931 85173 66102 11892 14906 20956 70242 % Read-only 76.4 43.4 27.6 74.3 78.2 71.2 81.2 Ave. 95.6 86.0 90.3 96.2 97.0 94.7 96.9 (%-tile) (22.9) (48.7) (54.7) (21.9) (19.3) (24.8) (15.6) Median 100 92.3 88.3 100 100 100 100 Std. Dev. 8.74 13.97 7.99 7.82 7.04 10.1 9.47 90%-tile 100 100 100 100 100 100 100 10%-tile 80 69.2 80 82.3 85.7 78.0 90.2 Ave. 78.6 422 108 538 966 747 294 (%-tile) (90.6) (96.1) (78.4) (91.1) (90.3) (97.0) (90.7) Median 4 29 39 24 29 25 32 Std. Dev. 4306 15739 381 8889 15968 17602 11708 90%-tile 68 162 197 433 895 319 257 10%-tile 2 6 7 3 3 4 3 Ave. 22.3 119 61.6 120 144 152 56.4 (%-tile) (84.6) (96.3) (77.2) (89.7) (88.5) (89.8) (78.9) Median 3 28 21 14 15 14 21 Std. Dev. 439 2715 351 903 1123 1833 366 90%-tile 33 90 90 124 242 159 114 10%-tile 2 3 6 3 3 3 3 Ave. 1.81 12.4 5.93 3.77 6.98 16.0 6.37 (%-tile) (81.5) (87.6) (63.4) (79.7) (90.0) (94.0) (82.8) Median 0 3 2 0 0 0 0 Std. Dev. 16.2 454 8.77 63.0 178 290 178 90%-tile 3 16 13 6 7 10 13 10%-tile 0 0 0 0 0 0 0 Ave. 4.76 11.8 15.6 8.19 8.70 8.40 13.0 (%-tile) (69.9) (56.4) (65.9) (65.1) (62.4) (66.6) (62.9) Median 2 8 8 6 7 6 7 Std. Dev. 5.43 10.9 14.0 7.29 7.88 8.21 16.2 90%-tile 13 35 37 17 18 18 31 10%-tile 1 2 2 2 2 2 1 Ave. 1.55 2.12 1.85 2.62 3.18 2.38 1.88 (%-tile) (74.4) (80.2) (60.8) (74.9) (77.5) (73.2) (70.0) Median 1 1.47 1.71 1.45 1.53 1.55 1.36 Std. Dev. 2.43 5.75 1.08 5.04 9.56 11.6 2.68 90%-tile 2.05 3.33 2.33 4.6 5.55 3.21 3.34 10%-tile 1 1 1 1 1 1 1

Trace Retail TelecomA TelecomB1 TelecomB2 Utility Prod. Ave. TPC-C TPC-D # 797637 84378 36508 25899 118191 119235 890885 230 % Read-only 32.3 19.3 60.8 59.7 89.8 59.5 7.96 37.4 Ave. 93.1 88.3 95.3 95.1 98.8 93.9 86.5 89.1 (%-tile) (39.0) (38.5) (36.1) (35.5) (10.1) (30.6) (54.5) (59.1) Median 94.5 92.6 100 100 100 97.3 84.6 82.2 Std. Dev. 7.06 14.6 8.25 8.45 4.59 9.01 4.84 8.74 90%-tile 100 100 100 100 100 100 91.4 100 10%-tile 83.3 50 85.7 85.2 98.3 80.7 82.8 82.1 Ave. 48.5 155 316 531 319 376.9 220 948393 (%-tile) (86.0) (95.2) (88.2) (90.7) (93.8) (90.7) (58.1) (92.2) Median 19 51 49 51 28 31.7 153 63892 Std. Dev. 7951 5439 8948 28885 24724 12545 247 6273027 90%-tile 63 115 400 459 232 300 492 117929 10%-tile 13 4 8 8 3 5.3 26 10 Ave. 16.6 56.6 83.4 74.5 57.7 80.3 73.0 87509 (%-tile) (64.7) (90.7) (82.5) (80.0) (80.6) (83.6) (57.9) (90.9) Median 13 26 35 36 18 20.3 61 12501.5 Std. Dev. 328 1350 547 743 1517 1018 81.3 484698 90%-tile 29 53 115 117 76 104 161 24776 10%-tile 5 2 7 7 3 3.9 12 4 Ave. 2.11 9.88 5.45 4.79 5.66 6.76 16.5 14068 (%-tile) (65.5) (84.8) (72.5) (64.8) (82.1) (79.1) (51.0) (92.6) Median 1 4 0 0 3 1.1 4 6003 Std. Dev. 141 938 86.5 18.7 306 223 14.7 59347 90%-tile 4 13 8 8 8 9.1 36 6298 10%-tile 0 0 0 0 0 0 4 0 Ave. 7.14 11.4 11.8 12.0 12.9 10.5 10.0 4.6 (%-tile) (60.8) (46.8) (51.7) (56.5) (61.9) (60.6) (55.0) (80.4) Median 6 12 11 11 7 7.6 7 4 Std. Dev. 4.55 7.73 8.42 8.60 12.9 9.34 3.65 3.43 90%-tile 14 21 22 26 29 23.4 14 6 10%-tile 2 2 4 4 2 2.2 7 2.1 Ave. 1.73 2.97 3.01 3.26 2.04 2.38 2.75 5.79 (%-tile) (60.3) (92.3) (87.2) (87.3) (65.8) (75.3) (63.3) (86.1) Median 1.58 2 1.34 1.4 1.79 1.52 2.5 5.11 Std. Dev. 1.09 211 5.57 6.09 1.76 22.0 1.17 5.14 90%-tile 2.47 2.67 3.81 4.12 3 3.37 3.43 9.17 10%-tile 1 1.25 1 1 1 1.02 1.94 2.75

The table shows that the production workloads are very diverse in their transaction characteristics. In certain cases, however, TPC-C and TPC-D still fall outside the broad range of behavior exhibited by the production workloads. For instance, the proportion of read-only transactions in the production workloads varies from 19 percent in TelecomA to 90 percent in Utility with an average of about 60 percent. In contrast, only 8 percent of the transactions of TPC-C are read-only. Since read-only transactions are easier to isolate from one another, these percentages suggest that TPC-C stresses the concurrency control mechanism more than the production workloads. Notice also that the TPC-D transactions have a lot more references than those of the production workloads, but they involve fewer objects and have much better locality.

Figure 3 plots the distribution of transaction size, which is the number of references in a transaction. The distribution in Figure 3B is weighted in the sense that a transaction of size s is counted s times. Notice that the transactions in the TPC benchmarks, especially those in TPC-D, tend to be larger than those of the production workloads. In addition, the transactions of TPC-D have a wide range of sizes. When two-phase locking is used to ensure that transactions are serializable,³⁷ locks tend to be released only when transactions end so that long transactions typically imply long lock waits. Therefore, the transaction size, which can be considered the virtual transaction length or duration, is a very important factor in analyzing concurrency control mechanisms. To make our data more useful for mathematical modeling, we fitted the data with standard probability distributions. As shown in Figure 3, the lognormal distribution (denoted LogNorm(µ,), where µ is the mean and is the standard deviation), turns out to be a good fit.

Figure 3

Since short transactions can be blocked for long periods by long transactions holding the necessary locks, system performance is sensitive to the second and third moments of the transaction size.³⁸ In addition, the distribution of transaction size affects not only the absolute but also the relative performance of different concurrency control schemes.¹⁷ Therefore, we also present the average and higher moments of the transaction size for our various workloads in Table 3.

Table 3  First, second, and third moments of the number of references in a transaction (S)

Aerospace Bank ConsGds DirMktg1 DirMktg2 FinSvcs Insurance   E[S] 78.6 422 108 538 966 747 294 E[S2] 1.85 × 107 2.48 × 108 1.57 × 105 7.93 × 107 2.56 × 108 3.10 × 108 1.37 × 108 E[S3] 1.65 × 1013 5.23 × 1014 5.46 × 109 3.85 × 1013 1.79 × 1014 3.98 × 1014 1.97 × 1014

Retail TelecomA TelecomB1 TelecomB2 Utility Prod. Ave. TPC-C TPC-D   E[S] 48.5 155 316 531 319 377 220 9.48 × 105 E[S2] 6.32 × 107 2.96 × 107 8.02 × 107 8.35 × 108 6.11 × 108 2.22 × 108 1.09 × 105 4.03 × 1013 E[S3] 3.93 × 1014 2.12 × 1013 8.04 × 1013 2.69 × 1015 2.48 × 1015 5.85 × 1014 7.51 × 107 3.04 × 1021

In Figure 4, we plot the distribution of the transaction footprint, or the number of pages referenced by a transaction. The lognormal distribution is again a good fit. Figures 3 and 4 show that most of the transactions are small, but large transactions account for most of the references and most of the pages referenced. In contrast to TPC-C, the production workloads are made up of transactions with a wide range of sizes and footprints. Such a mixture of large and small transactions complicates the task of scheduling and allocating resources to satisfy the different performance requirements of the transactions. For instance, a suitable balance has to be found between allowing large transactions to make good forward progress and preventing them from monopolizing the buffer pool. Regrettably, this issue is beyond the scope of the current study, which only considers the characteristics of workloads as they have been scheduled and tuned in production environments.

Figure 4

Degree of concurrency. In order to effectively utilize system resources, database systems allow the concurrent execution of multiple transactions through concurrency control mechanisms, such as locking, that provide each transaction with an isolated view of the system. The degree of concurrency, i.e., the number of concurrently active transactions, in a workload directly affects issues such as lock contention and deadlocks. Furthermore, for each active transaction in the system, the DBMS has to maintain a database agent and its associated context, which is nontrivial and includes various control blocks and private memory. The time-averaged number of transactions that are active in the various workloads at any one time is summarized in the last row of Table 4. The production workloads again exhibit very diverse characteristics with the time-averaged degree of concurrency ranging from slightly below 5 in ConsGds to nearly 80 in Aerospace.

Table 4	Degree of concurrency averaged over various time intervals showing the peak or maximum value observed for each interval size

Trace Aerospace Bank ConsGds DirMktg1 DirMktg2 FinSvcs Insurance   100-ms 131 27.0 15.0 25.0 23.0 48.0 26.0 1-s 128 27.0 12.8 24.3 22.6 48.0 26.0 10-s 125 26.8 11.0 20.9 20.4 47.5 24.3 1-min 124 25.7 8.8 19.7 18.1 33.9 20.7 10-min 119 21.2 7.3 14.0 13.4 29.6 17.2 100-min 108 11.0 5.0 12.2 11.9 25.9 13.2 Trace Len. 77.7 5.39 4.91 12.1 11.6 20.5 12.8

Trace Retail TelecomA TelecomB1 TelecomB2 Utility Prod. Ave. TPC-C TPC-D   100-ms 137 17.0 19.6 16.0 38.9 43.6 60.0 8.00 1-s 134 15.4 19.1 16.0 36.0 42.4 60.0 8.00 10-s 130 13.8 18.2 14.2 27.7 40.0 60.0 8.00 1-min 122 11.6 15.5 12.3 15.9 35.7 60.0 8.00 10-min