I/O phase characterization of TPC-H query operations

Abstract

With the introduction of newer faster processors, speedier interconnects and sophisticated I/O system designs, the processing capabilities of servers built in current market place are ever increasing, but so are the myriad commercial applications that they are used for. Unfortunately, detailed studies capturing the demands of commercial applications/workloads have been very few. In this paper we characterize the I/O activity of the 22 I/O-intensive database queries of the commercial decision support system (DSS) benchmark, namely, TPC-H and a smaller set of queries called "DSS-lite" that perform individual database operations. TPC-H query behavior varies throughout its execution but we identify "phases" in a query during which the performance is close forming a steady state. We use the DSS-lite results and query plan information to decipher the various phases of the TPC-H queries and characterize their I/O requirements, which can aid in forecasting or planning a DSS system's I/O capacity.