Database access characterization for buffer hit prediction

Abstract

The characterization of database accesses from reference traces for the purpose of buffer hit prediction is extremely useful for workload management and system capacity planning in various ways. In this paper, we use a characterization method that first distinguishes three types of access pattern from a trace: 1) locality within a transaction, 2) random accesses by transactions and 3) sequential accesses by long queries. We then propose a concise way to characterize the access skew across the randomly accessed pages by assuming that the large number of data pages may be logically grouped into a small number of partitions such that the frequency of accessing each page within a partition can be treated as equal. Finally, we provide extensive validation of the buffer hit predictions both for single node as well as multiple node systems based on our access characterization using production database traces. We further show that the proposed approach can be applied to predict the buffer hit probability of a composite workload from those of its component files.