Abstract
The analytic prediction of buffer hit probability, based on the characterization of database accesses from real reference traces, is extremely useful for workload management and system capacity planning. The knowledge can be helpful for proper allocation of buffer space to various database relations, as well as for the management of buffer space for a mixed transaction and query environment. Access characterization can also be used to predict the buffer invalidation effect in a multi-node environment which, in turn, can influence transaction routing strategies. However, it is a challenge to characterize the database access pattern of a real workload reference trace in a simple manner that can easily be used to compute buffer hit probability. In this article, we use a characterization method that 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. We then propose a concise way to characterize the access skew across randomly accessed pages by logically grouping the large number of data pages into a small number of partitions such that the frequency of accessing each page within a partition can be treated as equal. Based on this approach, we present a recursive binary partitioning algorithm that can infer the access skew characterization from the buffer hit probabilities for a subset of the buffer sizes. We validate the buffer hit predictions for single and multiple node systems using production database traces. We further show that the proposed approach can predict the buffer hit probability of a composite workload from those of its component files.
Similar content being viewed by others
References
Chou, H.T. and Dewitt, D.J. An evaluation of buffer management strategies for relational database systems.Eleventh International Conference on Very Large Databases, Stockholm, Sweden, 1985.
Cornell, D.W. and Yu, P.S. Integration of buffer management and query optimization in relational database environment.Fifteenth International Conference on Very Large Databases, Amsterdam, Netherlands, 1989.
Dan, A. and Towsley, D. An approximate analysis of the LRU and FIFO buffer replacement schemes.ACM SIGMETRICS, Denver, CO, 1990.
Dan, A., Dias, D.M., and Yu, P.S. Buffer analysis for a data sharing environment with skewed data access.IEEE Transactions on Knowledge and Data Engineering, 6(2):331–337, 1994a.
Dan, A., Yu, P.S., and Dias, D.M. Performance modelling and comparisons of global shared buffer management policies in a cluster environment.IEEE Transactions on Computers. 43(11):1281–1297, 1994b.
Dan, A., Yu, P.S., and Chung, J.Y. Characterization of database access skew in a transaction processing environment.IBM Research Report RC 17436, 1991.
Date, C.J. and White, C.J.,A Guide to DB2, Third edition, Reading, MA: Addision-Wesley, 1989.
Effelsberg, W. and Loomis, M.E.S. Logical, internal, and physical reference behavior in CODASYL database systems.ACM Transactions on Database Systems, 9(2):187–213, 1984.
Effelsberg, W. and Haerder, T. Principles of database buffer management.ACM Transactions on Database Systems, 9(4):560–595, 1984.
Faloutsos, C., Ng, R., and Sellis, T. Predictive load control for flexible buffer allocation.Seventeenth International Conference on Very Large Databases, Barcelona, Spain, 1991.
Gray, J., ed.The Benchmark Handbook for Database and Transaction Processing Systems. San Mateo, CA: Morgan Kaufmann, 1991.
Hawthorn, P. and Stonebraker, M. Performance analysis of a relational data base management system.ACM SIGMOD, Boston, MA, 1979.
IBM Database 2 Administration Guide, Vol. III, Section 7,Performance Monitoring and Tuning, SC26-4888-00, 1993.
Kearns, J.P. and Defazio, S. Diversity in database reference behavior.Performance Evaluation Review, 17(1):11–19, 1989.
Kronenberg, N., Levy, H., and Strecker, W.D. VAXcluster: A closely-coupled distributed system.ACM Transactions on Computer Systems 4:130–146, 1986.
Mattson, R.L., Gecsei, J., Slutz, D.R., and Traiger, I.L. Evaluation techniques for storage hierarchies.IBM Systems Journal, 9(2):78–117, 1970.
Ng, R., Faloutsos, C., and Sellis, T. Flexible buffer allocation based on marginal gains.ACM SIGMOD, Atlantic City, NJ, 1990.
Nicola, V.F., Dan, A., and Dias, D.M. Analysis of the generalized clock buffer replacement scheme for database transaction processing.ACM SIGMETRICS, Newport, RI, 1992.
Press, W.H., Flannery, B.P., Teukolsky, S.A., and Vetterling, W.T.Numerical Recipes. New York, NY: Cambridge University Press 1986.
Rodriguez-Rosell, J. Empirical data reference behavior in data base systems.Computer, 9(11):3–13, 1976.
Sacco, G.M. and Schkolnick, M. Buffer management in relational database systems.ACM Transactions on Database Systems, 11(4):473–498, 1986.
Smith, A.J. Sequentiality and prefetching in database systems.ACM Transactions on Database Systems, 3(3):223–247, 1978.
Strickland, J.P., Uhrowczik, P.P., and Watts, V.L. IMS/VS: An evolving system.IBM Systems Journal, 21:490–510, 1982.
Tay, Y.C., Suri, R., and Goodman, N. A mean value performance model for locking in databases: The no-waiting case.Journal of the ACM, 32(3):618–651, 1985.
Teng, J.Z., and Gumaer, R.A. Managing IBM Database 2 Buffers to Maximize Performance.IBM Systems Journal, 23(2):211–218, 1984.
Van den Berg, J. and Towsley, D. Properties of the miss ratio for a 2-level storage model with LRU or FIFO replacement strategy and Independent References.IEEE Transactions on Computers, 42(4):508–512, 1993.
Verkamo, A.I. Empirical results on locality in database referencing.ACM SIGMETRICS,, Austin, TX, 1985.
Yu, P.S., Dias, D.M., and Lavenberg, S.S. On the analytical modeling of database concurrency control.Journal of the ACM, 40(4):831–872, 1993.
Yu, P.S. and Cornell, D.W. Optimal buffer allocation in a multi-query environment.Seventh International Conference on Data Engineering, Kobe, Japan, 1991.
Author information
Authors and Affiliations
Rights and permissions
About this article
Cite this article
Dan, A., Yu, P.S. & Chung, JY. Characterization of database access pattern for analytic prediction of buffer hit probability. VLDB Journal 4, 127–154 (1995). https://doi.org/10.1007/BF01232474
Accepted:
Issue Date:
DOI: https://doi.org/10.1007/BF01232474