Characteristics of production database workloads and the TPC benchmarks

Abstract

There has been very little empirical analysis of any real production database workloads. Although the Transaction Processing Performance Council benchmarks C (TPC-C TM) and D (TPC-D TM) have become the standard benchmarks for on-line transaction processing and decision support systems, respectively, there has not been any major effort to systematically analyze their workload characteristics, especially in relation to those of real production database workloads. In this paper, we examine the characteristics of the production database workloads of ten of the world's largest corporations, and we also compare them to TPC-C and TPC-D. We find that the production workloads exhibit a wide range of behavior. In general, the two TPC benchmarks complement one another in reflecting the characteristics of the production workloads, but some aspects of real workloads are still not represented by either of the benchmarks. Specifically, our analysis suggests that the TPC benchmarks tend to exercise the following aspects of the system differently than the production workloads: concurrency control mechanism, workloadadaptive techniques, scheduling and resource allocation policies, and I/O optimizations for temporary and index files. We also reexamine Amdahl's rule of thumb for a typical data processing system and discover that both the TPC benchmarks and the production workloads generate on the order of 0.5 to 1.0 bit of logical I/O per instruction, surprisingly close to the much earlier figure.

Generating Shifting Workloads to Benchmark Adaptability in Relational Database Systems

“…This can be seen in figure 7. Similar observations about reference distributions were made by Hsu et al in [8]. They used the Hill equation to model the references, which is related to the log-logistic distribution.…”

“…For example, TPC-DS [6] features a new query generator that allows to generate a large set of queries which are syntactically different but semantically similar [7]. Still synthetic query streams are usually homogeneous in the frequency of queries and the ratio between different query types, while real database workloads tend to be bursty [8]. Traditionally the workload is seen as a set of SQL query classes and the physical design is tuned accordingly.…”

Workload Modeling for Computer Systems Performance Evaluation

“…Hsu et al performed a direct comparison between production database traces and traces obtained by running the TPC-C and TPC-D benchmarks [348,347]. It indicated that real production data is more diverse, being a mixture of many types of both small and large queries.…”

Mining and Modeling Database User Access Patterns

“…Previous workload studies focus on describing the statistical summaries of run-time behavior [7,12,2], clustering database transactions [11,5], predicting the buffer hit ratio [1], and improving caching performance [6,8]. Chaudhuri et al [7] suggest to use SQL-like primitives for workload summarization.…”

“…In [2], Hsu et al analyze the characteristics of the standard workload TPC-C and TPC-D, and examine the characteristics of the production database workloads of ten of the world's largest corporations. Yu et al [11] propose an affinity clustering algorithm which partitions the transactions into clusters according to their low-level database reference patterns.…”