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.
- Autoadmin. 2006. AutoAdmin: Self-Tuning and self-administering datdbases. http://research.microsoft.com/dmx/autoadmin.Google Scholar
- Autonomic Computing. 2006. Autonomic computing homepage. http://www.research.ibm.com/autonomic.Google Scholar
- 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 Scholar
Digital Library
- Belady, L. A. 1996. A study of replacement algorithms for virtual storage computer. IBM Syst. J. 5, 2, 78--101. Google Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- Jain, R. 1991. The Art of Computer Systems Performance Analysis. John Wiley, New York.Google Scholar
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- Lightstone, S. S., Lohman, G., and Zilio, D. 2002. Toward autonomic computing with DB2 universal database. SIGMOD Rec. 31, 3, 55--61. Google Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- Suh, G. E., Rudolph, L., and Devadas, S. 2004. Dynamic partitioning of shared cache memory. J. Supercomput. 28, 1, 7--26. Google Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- TPC-C Benchmark. 2006. TPC-C V5 homepage. http://www.tpc.org/tpcc/.Google Scholar
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
- 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 Scholar
Digital Library
Index Terms
A new approach to dynamic self-tuning of database buffers
Recommendations
Dynamic self-invalidation: reducing coherence overhead in shared-memory multiprocessors
ISCA '95: Proceedings of the 22nd annual international symposium on Computer architectureThis paper introduces dynamic self-invalidation (DSI), a new technique for reducing cache coherence overhead in shared-memory multiprocessors. DSI eliminates invalidation messages by having a processor automatically invalidate its local copy of a cache ...
A New Flexible Multi-flow LRU Cache Management Paradigm for Minimizing Misses
The Least Recently Used (LRU) caching and its variants are used in large-scale data systems in order to provide high-speed data access for a wide class of applications. Nonetheless, a fundamental question still remains open: in order to minimize miss ...
Dynamic self-invalidation: reducing coherence overhead in shared-memory multiprocessors
Special Issue: Proceedings of the 22nd annual international symposium on Computer architecture (ISCA '95)This paper introduces dynamic self-invalidation (DSI), a new technique for reducing cache coherence overhead in shared-memory multiprocessors. DSI eliminates invalidation messages by having a processor automatically invalidate its local copy of a cache ...






Comments