I/O reference behavior of production database workloads and the TPC benchmarks—an analysis at the logical level

Abstract

As improvements in processor performance continue to far outpace improvements in storage performance, I/O is increasingly the bottleneck in computer systems, especially in large database systems that manage huge amounts of data. The key to achieving good I/O performance is to thoroughly understand its characteristics. In this paper, we present a comprehensive analysis of the logical I/O reference behavior of the peak production database workloads from ten of the world's largest corporations by focusing on how these workloads respond to different techniques for caching, prefetching and write buffering. Our findings include several broadly applicable rules of thumb that describe how effective the various I/O optimization techniques are for the production workloads. For instance, our results indicate that the buffer pool miss ratio tends to be related to the ratio of buffer pool size to data size by an inverse square root rule. A similar fourth root rule relates the write miss ratio and the ratio of buffer pool size to data size.

Rules of thumb in data engineering

“…Interestingly, Hsu, Smith, and Young, came to similar conclusions in their very detailed study of TPC-C and other workload behaviors [10]. Their excellent study shows the wide spectrum of behaviors, both across workloads, and within a given workload.…”

Workload Modeling for Computer Systems Performance Evaluation

“…A detailed analysis of traces from 10 large-scale production systems running IBM's DB2 in the early 1990s was conducted by Hsu et al [347]. One of their interesting results is that significant inter-transaction locality exists, indicating that in real workloads transactions are not independent of each other.…”

“…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.…”

Projecting the performance of decision support workloads on systems with smart storage (SmartSTOR)

“…A brief description of the benchmark is provided in Appendix A. Readers who are interested in the characteristics of the benchmark are referred to [12], which contains a comprehensive analysis of the benchmark characteristics and how they compare with those of real production database workloads.…”