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. In addition, we characterize the reference characteristics of workloads similar to the Transaction Processing Performance Council (TPC) benchmarks C (TPC-C) and D (TPC-D), which are de facto standard performance measures for on-line transaction processing (OLTP) systems and decision support systems (DSS) respectively. Since benchmarks such as TPC-C and TPC-D can only be used effectively if their strengths and limitations are understood, a major focus of our analysis is on identifying aspects of the benchmarks that stress the system differently than the production workloads. We discover that for the most part, the reference behavior of TPC-C and TPC-D fall within the range of behavior exhibited by the production workloads. However, there are some noteworthy exceptions that affect well-known I/O optimization techniques such as caching (LRU is further from the optimal for TPC-C while there is little sharing of pages between transactions for TPC-D), prefetching (TPC-C exhibits no significant sequentiality) and write buffering (write buffering is less effective for the TPC benchmarks). While the two TPC benchmarks generally complement one another in reflecting the characteristics of the production workloads, there remain aspects of the real workloads that are not represented by either of the benchmarks.
Cited By
- Seifert A and Scholl M A multi-version cache replacement and prefetching policy for hybrid data delivery environments Proceedings of the 28th international conference on Very Large Data Bases, (850-861)
- Hsu W, Smith A and Young H (2001). Characteristics of production database workloads and the TPC benchmarks, IBM Systems Journal, 40:3, (781-802), Online publication date: 1-Mar-2001.
- Hsu W, Smith A and Young H (2001). I/O reference behavior of production database workloads and the TPC benchmarks—an analysis at the logical level, ACM Transactions on Database Systems (TODS), 26:1, (96-143), Online publication date: 1-Mar-2001.
Recommendations
I/O reference behavior of production database workloads and the TPC benchmarks—an analysis at the logical level
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 amoungs of data. The key to achieving ...
Characterization of L3 cache behavior of SPECjAppServer2002 and TPC-C
ICS '05: Proceedings of the 19th annual international conference on SupercomputingWith the proliferation of e-businesses, Java™ Middleware and OLTP applications are gaining importance. As the gap between CPU and memory latencies continues to increase, the performance of these applications running on multiprocessor systems will become ...
Characteristics of production database workloads and the TPC benchmarks
End-to-end securityThere has been very little empirical analysis of any real production database workloads. Although the Transaction Processing Performance Council benchmarks C (TPC-CTM) and D (TPC-DTM) have become the standard benchmarks for on-line transaction ...