Goals and benchmarks for autonomic configuration recommenders

ABSTRACT

We are witnessing an explosive increase in the complexity of the information systems we rely upon, Autonomic systems address this challenge by continuously configuring and tuning themselves. Recently, a number of autonomic features have been incorporated into commercial RDBMS; tools for recommending database configurations (i.e., indexes, materialized views, partitions) for a given workload are prominent examples of this promising trend.In this paper, we introduce a flexible characterization of the performance goals of configuration recommenders and develop an experimental evaluation approach to benchmark the effectiveness of these autonomic tools. We focus on exploratory queries and present extensive experimental results using both real and synthetic data that demonstrate the validity of the approach introduced. Our results identify a specific index configuration based on single-column indexes as a very useful baseline for comparisons in the exploratory setting. Furthermore, the experimental results demonstrate the unfulfilled potential for achieving improvements of several orders of magnitude.

REFERENCES

• 1: S. Agrawal, S. Chaudhuri, L. Kollar, A. P. Marathe, V. R. Narasayya, and M. Syamala. Database tuning advisor for microsoft sql server 2005. In Proceedings of 30th International Conference on Very Large Data Bases, pages 1110--1121, Toronto, Canada, 2004.
• 2: Sanjay Agrawal , Surajit Chaudhuri , Vivek R. Narasayya, Automated Selection of Materialized Views and Indexes in SQL Databases, Proceedings of the 26th International Conference on Very Large Data Bases, p.496-505, September 10-14, 2000
• 3: Haran Boral , David J. DeWitt, Database machines: an idea whose time has passed? A critique of the future of database machines, Parallel architectures for database systems, IEEE Press, Piscataway, NJ, 1989
• 4: Surajit Chaudhuri , Ashish Kumar Gupta , Vivek Narasayya, Compressing SQL workloads, Proceedings of the 2002 ACM SIGMOD international conference on Management of data, June 03-06, 2002, Madison, Wisconsin [doi>10.1145/564691.564747]
• 5: S. Chaudhuri and V. R. Narasayya. TPC-D Data Generation with Skew. Available via anonymous ftp from ftp. research. microsoft. com/users/viveknar/tpcdskew.
• 6: Surajit Chaudhuri , Vivek R. Narasayya, An Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server, Proceedings of the 23rd International Conference on Very Large Data Bases, p.146-155, August 25-29, 1997
• 7: Surajit Chaudhuri , Vivek Narasayya, AutoAdmin “what-if” index analysis utility, Proceedings of the 1998 ACM SIGMOD international conference on Management of data, p.367-378, June 01-04, 1998, Seattle, Washington, United States
• 8: Surajit Chaudhuri , Vivek Narasayya, Microsoft index turning wizard for SQL Server 7.0, Proceedings of the 1998 ACM SIGMOD international conference on Management of data, p.553-554, June 01-04, 1998, Seattle, Washington, United States
• 9: William S. Cleveland, The elements of graphing data, Wadsworth Publ. Co., Belmont, CA, 1985
• 10: B. Dageville, D. Das, K. Dias, K. Yagoub, M. Zaït, and M. Ziauddin. Automatic sql tuning in oracle 10g. In Proceedings of 30th International Conference on Very Large Data Bases, pages 1098--1109, Toronto, Canada, 2004.
• 11: Min Fang , Narayanan Shivakumar , Hector Garcia-Molina , Rajeev Motwani , Jeffrey D. Ullman, Computing Iceberg Queries Efficiently, Proceedings of the 24rd International Conference on Very Large Data Bases, p.299-310, August 24-27, 1998
• 12: Jim Gray, Benchmark Handbook: For Database and Transaction Processing Systems, Morgan Kaufmann Publishers Inc., San Francisco, CA, 1992
• 13: Yannis E. Ioannidis , Stavros Christodoulakis, On the propagation of errors in the size of join results, Proceedings of the 1991 ACM SIGMOD international conference on Management of data, p.268-277, May 29-31, 1991, Denver, Colorado, United States
• 14: Sam S. Lightstone , Guy Lohman , Danny Zilio, Toward autonomic computing with DB2 universal database, ACM SIGMOD Record, v.31 n.3, September 2002 [doi>10.1145/601858.601873]
• 15: V. Markl , G. M. Lohman , V. Raman, LEO: An autonomic query optimizer for DB2, IBM Systems Journal, v.42 n.1, p.98-106, January 2003
• 16: AutoPart: Automating Schema Design for Large Scientific Databases Using Data Partitioning, Proceedings of the 16th International Conference on Scientific and Statistical Database Management (SSDBM'04), p.383, June 21-23, 2004 [doi>10.1109/SSDBM.2004.19]
• 17: Meikel Poess , Bryan Smith , Lubor Kollar , Paul Larson, TPC-DS, taking decision support benchmarking to the next level, Proceedings of the 2002 ACM SIGMOD international conference on Management of data, June 03-06, 2002, Madison, Wisconsin [doi>10.1145/564691.564759]
• 18: M. Poess and J. M. Stephens. Generating Thousand Benchmark Queries in Seconds. In Proceedings of the Thirtieth International Conference on Very Large Data Bases, pages 1045--1053, Toronto, Canada, August 31 - September 3 2004.
• 19: K. Runapongsa, J. M. Patel, R. Bordawekar, and S. Padmanabhan. XIST: An XML Index Selection Tool. In XSym, 2004.
• 20: T. Sawyer. Doing your own benchmark. In J. Gray, editor, The Benchmark Handbook for Database and Transaction Systems (2nd Edition). Morgan Kaufmann, 1993.
• 21: Transaction Processing Performance Council. TPC Benchmark H - Decision Support, 1999. Revision 1.3.0.
• 22: G. Valentin, M. Zuliani, D. C. Zilio, and A. S. Guy Lohman. DB2 Advisor: An Optimizer Smart Enough to Recommend its own Indexes. In ICDE, 2000.
• 23: C. H. Wu, H. Huang, L. Arminski, J. Castro-Alvear, Y. Chen, Z.-Z. Hu, R. S. Ledley, K. C. Lewis, H.-W. Mewes, B. C. Orcutt, B. E. Suzek, A. Tsugita, C. R. Vinayaka, L.-S. L. Yeh, J. Zhang, , and W. C. Barker. The protein information resource: an integrated public resource of functional annotation of proteins. Nucleic Acids Research, 30, 2002.
• 24: Daniel C. Zilio , Calisto Zuzarte , Guy M. Lohman , Hamid Pirahesh , Jarek Gryz , Eric Alton , Dongming Liang , Gary Valentin, Recommending Materialized Views and Indexes with IBM DB2 Design Advisor, Proceedings of the First International Conference on Autonomic Computing (ICAC'04), p.180-188, May 17-18, 2004
• 25: D. C. Zilio, J. Rao, S. Lightstone, G. M. Lohman, A. Storm, C. Garcia-Arellano, and S. Fadden. Db2 design advisor: Integrated automatic physical database design. In Proceedings of 30th International Conference on Very Large Data Bases, pages 1087--1097, Toronto, Canada, 2004.