Diversity in database reference behavior

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 per-transaction 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 reference 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.