skip to main content
research-article

A new approach to dynamic self-tuning of database buffers

Published:28 May 2008Publication History
Skip Abstract Section

Abstract

Current businesses rely heavily on efficient access to their databases. Manual tuning of these database systems by performance experts is increasingly infeasible: For small companies, hiring an expert may be too expensive; for large enterprises, even an expert may not fully understand the interaction between a large system and its multiple changing workloads. This trend has led major vendors to offer tools that automatically and dynamically tune a database system.

Many database tuning knobs concern the buffer pool for caching data and disk pages. Specifically, these knobs control the buffer allocation and thus the cache miss probability, which has direct impact on performance.

Previous methods for automatic buffer tuning are based on simulation, black-box control, gradient descent, and empirical equations. This article presents a new approach, using calculations with an analytically-derived equation that relates miss probability to buffer allocation; this equation fits four buffer replacement policies, as well as twelve datasets from mainframes running commercial databases in large corporations.

The equation identifies a buffer-size limit that is useful for buffer tuning and powering down idle buffers. It can also replace simulation in predicting I/O costs. Experiments with PostgreSQL illustrate how the equation can help optimize online buffer partitioning, ensure fairness in buffer reclamation, and dynamically retune the allocation when workloads change. It is also used, in conjunction with DB2's interface for retrieving miss data, for tuning DB2 buffer allocation to achieve targets for differentiated service.

References

  1. Autoadmin. 2006. AutoAdmin: Self-Tuning and self-administering datdbases. http://research.microsoft.com/dmx/autoadmin.Google ScholarGoogle Scholar
  2. Autonomic Computing. 2006. Autonomic computing homepage. http://www.research.ibm.com/autonomic.Google ScholarGoogle Scholar
  3. Bansal, S. and Modha, D. S. 2004. Car: Clock with adaptive replacement. In Proceedings of the 3rd USENIX Conference on File and Storage Technologies (FAST). USENIX Association, Berkeley, CA, 187--200. Google ScholarGoogle ScholarDigital LibraryDigital Library
  4. Belady, L. A. 1996. A study of replacement algorithms for virtual storage computer. IBM Syst. J. 5, 2, 78--101. Google ScholarGoogle ScholarDigital LibraryDigital Library
  5. Benoit, D. G. 2005. Automatic diagnosis of performance problems in database management systems. In Proceedings of the 2nd International Conference on Automatic Computing (ICAC). IEEE Computer Society, Washington, DC, 326--327. Google ScholarGoogle ScholarDigital LibraryDigital Library
  6. Brown, K. P., Carey, M. J., and Livny, M. 1996. Goal-Oriented buffer management revisited. In Proceedings of the ACM SIGMOD International Conference on Management of Data. ACM Press, New York, 353--364. Google ScholarGoogle ScholarDigital LibraryDigital Library
  7. Cai, L. and Lu, Y.-H. 2005. Joint power management of memory and disk. In Proceedings of the Conference on Design, Automation and Test in Europe (DATE). IEEE Computer Society, Washington, DC, 86--91. Google ScholarGoogle ScholarDigital LibraryDigital Library
  8. Chung, J.-Y., Ferguson, D., Wang, G., Nikolaou, C., and Teng, J. 1995. Goal-Oriented dynamic buffer pool management for data base systems. In Proceedings of IEEE International Conference on Engineering of Complex Computer Systems (ICECCS), Fort Lauderdale, FL. IEEE Computer Society, 191--198. Google ScholarGoogle ScholarDigital LibraryDigital Library
  9. Dageville, B. and Zait, M. 2002. SQL memory management in Oracle9i. In Proceedings of the International Conference on Very Large Databases (VLDB), Hong Kong, China. Morgan Kaufmann, 962--973. Google ScholarGoogle ScholarDigital LibraryDigital Library
  10. Dan, A. and Towsley, D. 1990. An approximate analysis of the LRU and FIFO buffer replacement schemes. In Proceedings of the ACM SIGMETRICS Conference on Measurement and Modeling of Computer Systems. ACM Press, New York, 143--152. Google ScholarGoogle ScholarDigital LibraryDigital Library
  11. Dan, A., Yu, P. S., and Chung, J. Y. 1995. Characterization of database access pattern for analytic prediction of buffer hit probability. The VLDB J. 4, 1, 127--154. Google ScholarGoogle ScholarDigital LibraryDigital Library
  12. Dias, K., Ramacher, M., Shaft, U., Venkataramani, V., and Wood, G. 2005. Automatic performance diagnosis and tuning in Oracle. In Proceedings of the Conference on Innovative Data Systems Research (CIDR), Asilomar, CA. 84--94.Google ScholarGoogle Scholar
  13. Floyd, S., Handley, M., Padhye, J., and Widmer, J. 2000. Equation-Based congestion control for unicast applications. In Proceedings of the ACM Conference on Applications, Technologies, Architectures, and Protocols for Computer Communications (SIGCOMM). ACM Press, New York, 43--56. Google ScholarGoogle ScholarDigital LibraryDigital Library
  14. Hsu, W. W., Smith, A. J., and Young, H. C. 2001. I/O reference behavior of production database workloads and the TPC benchmarks—An analysis at the logical level. ACM Trans. Database Syst. 26, 1, 96--143. Google ScholarGoogle ScholarDigital LibraryDigital Library
  15. Jain, R. 1991. The Art of Computer Systems Performance Analysis. John Wiley, New York.Google ScholarGoogle Scholar
  16. Ko, B.-J., Lee, K.-W., Amiri, K., and Calo, S. 2003. Scalable service differentiation in a shared storage cache. In Proceedings of the 23rd International Conference on Distributed Computing Systems (ICDCS). IEEE Computer Society, Washington, DC, 184--193. Google ScholarGoogle ScholarDigital LibraryDigital Library
  17. Lefurgy, C., Rajamani, K., Rawson, F., Felter, W., Kistler, M., and Keller, T. W. 2003. Energy management for commercial servers. Comput. 36, 12, 39--48. Google ScholarGoogle ScholarDigital LibraryDigital Library
  18. Lightstone, S. S., Lohman, G., and Zilio, D. 2002. Toward autonomic computing with DB2 universal database. SIGMOD Rec. 31, 3, 55--61. Google ScholarGoogle ScholarDigital LibraryDigital Library
  19. Llanos, D. R. 2006. TPCC-UVa: An open-source TPC-C implementation for global performance measurement of computer systems. SIGMOD Rec. 35, 4, 6--15. Google ScholarGoogle ScholarDigital LibraryDigital Library
  20. Lu, Y., Abdelzaher, T., Lu, C., and Tao, G. 2002. An adaptive control framework for QoS guarantees and its application to differentiated caching services. In Proceedings of the IEEE International Workshop on Quality of Service (IWQoS), Miami Beach, FL. IEEE, 23--32.Google ScholarGoogle Scholar
  21. Mattson, R. L., Gecsei, J., Slutz, D. R., and Traiger, I. L. 1970. Evaluation techniques for storage hierarchies. IBM Syst. J. 9, 2, 78--117.Google ScholarGoogle ScholarDigital LibraryDigital Library
  22. Narayanan, D., Thereska, E., and Ailamaki, A. 2005. Continuous resource monitoring for self-predicting DBMS. In Proceedings of the 13th IEEE International Symposium on Modeling, Analysis, and Simulation of Computer and Telecommunication Systems (MASCOTS). IEEE Computer Society, Washington, DC, 239--248. Google ScholarGoogle ScholarDigital LibraryDigital Library
  23. Ng, R., Faloutsos, C., and Sellis, T. 1995. Flexible and adaptable buffer management techniques for database management systems. IEEE Trans. Comput. 44, 4, 546--560. Google ScholarGoogle ScholarDigital LibraryDigital Library
  24. O'Neil, E. J., O'Neil, P. E., and Weikum, G. 1999. An optimality proof of the LRU-K page replacement algorithm. J. ACM 46, 1, 92--112. Google ScholarGoogle ScholarDigital LibraryDigital Library
  25. Padhye, J., Firoiu, V., Towsley, D. F., and Kurose, J. F. 2000. Modeling TCP Reno performance: A simple model and its empirical validation. IEEE/ACM Trans. Netw. 8, 2, 133--145. Google ScholarGoogle ScholarDigital LibraryDigital Library
  26. Storm, A. J., Garcia-Arellano, C., Lightstone, S. S., Diao, Y., and Surendra, M. 2006. Adaptive self-tuning memory in DB2. In Proceedings of the 32nd International Conference on Very Large Data Bases (VLDB). VLDB Endowment, 1081--1092. Google ScholarGoogle ScholarDigital LibraryDigital Library
  27. Suh, G. E., Rudolph, L., and Devadas, S. 2004. Dynamic partitioning of shared cache memory. J. Supercomput. 28, 1, 7--26. Google ScholarGoogle ScholarDigital LibraryDigital Library
  28. Tay, Y. C. and Zou, M. 2006. A page fault equation for modeling the effect of memory size. Perform. Eval. 63, 2, 99--130. Google ScholarGoogle ScholarDigital LibraryDigital Library
  29. Thiébaut, D., Stone, H. S., and Wolf, J. L. 1992. Improving disk cache hit-ratios through cache partitioning. IEEE Trans. Comput. 41, 6, 665--676. Google ScholarGoogle ScholarDigital LibraryDigital Library
  30. Tian, W., Martin, P., and Powley, W. 2003. Techniques for automatically sizing multiple buffer pools in DB2. In Proceedings of the Conference of the Centre for Advanced Studies on Collaborative Research (CASCON). IBM Press, 294--302. Google ScholarGoogle ScholarDigital LibraryDigital Library
  31. TPC-C Benchmark. 2006. TPC-C V5 homepage. http://www.tpc.org/tpcc/.Google ScholarGoogle Scholar
  32. Tsuei, T.-F., Packer, A. N., and Ko, K.-T. 1997. Database buffer size investigation for OLTP workloads. In Proceedings of the ACM SIGMOD International Conference on Management of Data. ACM Press, New York, 112--122. Google ScholarGoogle ScholarDigital LibraryDigital Library
  33. Waldspurger, C. A. 2002. Memory resource management in VMware ESX server. In Proceedings of the 5th Symposium on Operating Systems Design and Implementation (OSDI). ACM Press, New York, 181--194. Google ScholarGoogle ScholarDigital LibraryDigital Library
  34. Xi, Y., Martin, P., and Powley, W. 2001. An analytical model for buffer hit rate prediction. In Proceedings of the Conference of the Centre for Advanced Studies on Collaborative Research (CASCON). IBM Press, 18. Google ScholarGoogle ScholarDigital LibraryDigital Library
  35. Yu, P. S. and Cornell, D. W. 1991. Optimal buffer allocation in a multi-query environment. In Proceedings of the 7th International Conference on Data Engineering. IEEE Computer Society, Washington, DC, 622--631. Google ScholarGoogle ScholarDigital LibraryDigital Library
  36. Zhou, P., Pandey, V., Sundaresan, J., Raghuraman, A., Zhou, Y., and Kumar, S. 2004. Dynamic tracking of page miss ratio curve for memory management. In Proceedings of the 11th International Conference on Architectural Support for Programming Languages and Operating Systems (ASPLOS). ACM Press, New York, 177--188. Google ScholarGoogle ScholarDigital LibraryDigital Library

Index Terms

  1. A new approach to dynamic self-tuning of database buffers

          Recommendations

          Comments

          Login options

          Check if you have access through your login credentials or your institution to get full access on this article.

          Sign in

          Full Access

          • Published in

            cover image ACM Transactions on Storage
            ACM Transactions on Storage  Volume 4, Issue 1
            May 2008
            90 pages
            ISSN:1553-3077
            EISSN:1553-3093
            DOI:10.1145/1353452
            Issue’s Table of Contents

            Copyright © 2008 ACM

            Publisher

            Association for Computing Machinery

            New York, NY, United States

            Publication History

            • Published: 28 May 2008
            • Revised: 1 December 2007
            • Accepted: 1 December 2007
            • Received: 1 September 2007
            Published in tos Volume 4, Issue 1

            Permissions

            Request permissions about this article.

            Request Permissions

            Check for updates

            Qualifiers

            • research-article
            • Research
            • Refereed

          PDF Format

          View or Download as a PDF file.

          PDF

          eReader

          View online with eReader.

          eReader
          About Cookies On This Site

          We use cookies to ensure that we give you the best experience on our website.

          Learn more

          Got it!