Diversity in database reference behavior

Abstract

Over the past fifteen years, empirical studies of the reference behavior of a number of database systems have produced seemingly contradictory results. The presence or absence of locality of reference and sequentiality have both been reported (or denied) in various papers. As such, the performance analyst or database implementor is left with little concrete guidance in the form of expected reference behavior of a database system under a realistic workload.We present empirical evidence that all of the previous results about database reference behavior are correct (or incorrect).That is, if the database reference sequence is viewed on a pertransaction instance or per-database basis, almost any reference behavior is discernible. Previous results which report the absolute absence or presence of a certain form of refer-. ence behavior were almost certainly derived from reference traces which were dominated by transactions or databases which exhibited a certain behavior. Our sample consists of roughly twenty-five million block references, from 350,000 transaction executions, directed at 175 operational on-line databases at two major corporations. As such, the sample is an order of magnitude more comprehensive than any other reported in the literature.We also present evidence that reference behavior is predictable and exploitable when viewed on a per-transaction basis or per-database basis. The implications of this predictability for effective buffer management are discussed. the database buffer. Slots in this buffer are frames into which physical blocks are placed to make data available for requesting programs.Data requests which cannot be satisfied with blocks residing in the buffer cause faults to occur. In effect, to manage a database buffer, the DBMS must implement block fetch, placement, and replacement policies which, at the conceptual level, are analogous to those required for virtual memory. Since only small portions of a database can normally be retained in the buffer area, servicing data requests requires the DBMS to routinely perform I/O operations.

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

“…See for instance [9,18,32,33,34,48,54,57,64,74,73,76,82]. However, these studies are rather limited in scope, often relying on data collected at only one or two installations.…”

Characterization of database access pattern for analytic prediction of buffer hit probability

“…As the fraction of the load to each of the files changes (due to change in application mix), the LRU analysis can also be used to predict the buffer hit probabilities for the new workload compositions, assuming the access skew characterization for each file remains unchanged (Kearns and Defazio, 1989). Figure 10 shows the changes in 7.…”

“…In addition, within both short transactions and long queries some of the pages are rereferenced, and are called locality sets (Chou and Dewitt, 1985). Viewed as a whole, the combination of transaction and query accesses generates all possib]Le access patterns (RodriguezRosell, 1976;Smith, 1978;Hawthorn and Stonebraker, 1979;Effelsberg and Loomis, 1984;Chou and Dewitt, 1985;Verkamo, 1985;Sacco and Schkolnick, 1986;Kearns and Defazio, 1989). Traditional buffer management policies (e.g., strict LRU policy that does not exploit the knowledge of access components such as sequential vs random accesses) may not provide good buffer hit probability (Smith, 1978;Effelsberg and Haerder, 1984;Teng and Gumaer, 1984;Chou and Dewitt, 1985;Sacco and Schkolnick, 1986;IBM, 1993).…”

“…In an earlier study on database reference traces (Kearns and Defazio, 1989), it was shown that the database access pattern of each transaction type and file changes very little over time. Kearns and Defazio (1.989) collected traces over five working days, and found the degree of stability notable, even for the least stable transaction types.…”

“…More specifically, our characterization method first distinguishes three types of access patterns from a trace: (1) locality within a transaction, (2) random accesses by transactions, and (3) sequential accesses by long queries. The presence of these types of access behavior has been shown (Rodriguez-Rosell, 1976;Hawthorn and Stonebraker, 1979;Effelsberg and Loomis, 1984;Verkamo, 1985;Kearns and Defazio, 1989). Each access component can be accounted for separately, both in terms of buffer management as well as buffer hit prediction.…”