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. S. Agrawal, S. Chaudhuri, and V. R. Narasayya. Automated Selection of Materialized Views and Indexes in SQL Databases. In VLDB, 2000.
3. H. Boral and D. J. DeWitt. Database machines: An idea whose time has passed? a critique of the future of database machines. In Proceedings of the International Workshop on Database Machines, 1983. Reprinted in Parallel Architectures for Database Systems. IEEE Computer Society Press, 1989.
4. S. Chaudhuri, A. K. Gupta, and V. Narasayya. Compressing sql workloads. In SIGMOD. ACM Press, 2002.
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. S. Chaudhuri and V. R. Narasayya. An efficient cost-driven index selection tool for microsoft sql server. In VLDB. Morgan Kaufmann Publishers Inc., 1997.
7. S. Chaudhuri and V. R. Narasayya. AutoAdmin 'What-if' Index Analysis Utility. In SIGMOD, 1998.
8. S. Chaudhuri and V. R. Narasayya. Microsoft Index Tuning Wizard for SQL Server 7.0. In SIGMOD, 1998.
9. W. Cleveland. The Elements of Graphing Data. Hobart Press, 1994.
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. M. Fang, N. Shivakumar, H. Garcia-Molina, R. Motwani, and J. D. Ullman. Computing Iceberg Queries Efficiently. In Proceedings of 24rd International Conference on Very Large Data Bases, pages 299--310, New York City, New York, USA, August 24--27 1998.
12. J. Gray, editor. The Benchmark Handbook for Database and Transaction Systems (2nd Edition). Morgan Kaufmann, 1993.
13. Y. E. Ioannidis and S. Christodoulakis. On the propagation of errors in the size of join results. In SIGMOD, 1991.
14. S. S. Lightstone, G. Lohman, and D. Zilio. Toward Autonomic Computing with DB2 Universal Database. ACM SIGMOD Record, 31(3), 2002.
15. V. Markl, G. M. Lohman, and V. Raman. LEO: An Autonomic Query Optimizer for DB2. IBM Systems Journal, 42(1), 2003.
16. S. Papadomanolakis and A. Ailamaki. Autopart: Automating schema design for large scientific databases using data partitioning. In Proceedings of the 16th International Conference on Scientific and Statistical Database Management, 2004.
17. M. Poess, B. Smith, L. Kollar, and P. Larson. Tpc-ds, taking decision support benchmarking to the next level. In Proceedings of the 2002 ACM SIGMOD international conference on Management of data, pages 582--587. ACM Press, 2002.
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. D. Zilio, C. Zuzarte, S. Lightstone, W. Ma, G. Lohman, R. Cochrane, H. Pirahesh, L. Colby, J. Gryz, E. Alton, D. Liang, and G. Valentin. Recommending Materialized Views and Indexes with IBM DB2 Design Advisor. In Proceedings of the International Conference on Autonomic Computing, 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.

• Information systems

  • Data management systems

    • Database management system engines

  • Information retrieval

• Theory of computation

  • Theory and algorithms for application domains

    • Database theory