Automated SQL tuning through trial and (sometimes) error

ABSTRACT

SQL tuning---the attempt to improve a poorly-performing execution plan produced by the database query optimizer---is a critical aspect of database performance tuning. Ironically, as commercial databases strive to improve on the manageability front, SQL tuning is becoming more of a black art. It requires a high level of expertise in areas like (i) query optimization, run-time execution of query plan operators, configuration parameter settings, and other database internals; (ii) identification of missing indexes and other access structures; (iii) statistics maintained about the data; and (iv) characteristics of the underlying storage system. Since database systems, their workloads, and the data that they manage are not getting any simpler, database users and administrators often rely on trial and error for SQL tuning.

In this paper, we take the position that the trial-and-error (or, experiment-driven) process of SQL tuning can be automated by the database system in an efficient manner; freeing the user or administrator from this burden in most cases. A number of current approaches to SQL tuning indeed take an experiment-driven approach. We are prototyping a tool, called zTuned, that automates experiment-driven SQL tuning. This paper describes the design choices in zTuned to address three nontrivial issues: (i) how is the SQL tuning logic integrated with the regular query optimizer, (ii) how to plan the experiments to conduct so that a satisfactory (new) plan can be found quickly, and (iii) how to conduct experiments with minimal impact on the user-facing production workload. We conclude with a preliminary empirical evaluation and outline promising new directions in automated SQL tuning.

REFERENCES

• 1: Mihnea Andrei , Patrick Valduriez, User-Optimizer Communication using Abstract Plans in Sybase ASE, Proceedings of the 27th International Conference on Very Large Data Bases, p.29-38, September 11-14, 2001
• 2: S. Babu, N. Borisov, S. Duan, H. Herodotou, and V. Thummala. Automated Experiment Driven Management of (Database) Systems. In 12th Workshop on Hot Topics in Operating Systems (HotOS-XII), Monte Verita, Switzerland, 2009.
• 3: Nicolas Bruno , Surajit Chaudhuri , Ravi Ramamurthy, Power Hints for Query Optimization, Proceedings of the 2009 IEEE International Conference on Data Engineering, p.469-480, March 29-April 02, 2009 [doi>10.1109/ICDE.2009.68]
• 4: S. Chaudhuri, V. Narasayya, and R. Ramamurthy. A Pay-As-You-Go Framework for Query Execution Feedback. In Proc. of the 34th Intl. Conf. on Very Large Data Bases, pages 1141--1152. VLDB Endowment, 2008.
• 5: Chungmin Melvin Chen , Nick Roussopoulos, Adaptive selectivity estimation using query feedback, ACM SIGMOD Record, v.23 n.2, p.161-172, June 1994
• 6: S. Duan, V. Thummala, and S. Babu. Tuning Database Configuration Parameters with iTuned. In Proc. of the 35th Intl. Conf. on Very Large Data Bases, Lyon, France, 2009.
• 7: Robert Freeman, Oracle Database 11g New Features, McGraw-Hill, Inc., New York, NY, 2007
• 8: IBM DB2. Giving optimization hints to DB2, 2003. http://publib.boulder.ibm.com/infocenter/dzichelp/ v2r2/index.jsp?topic=/com.ibm.db2.admin/ p91i375.htm.
• 9: V. Markl , P. J. Haas , M. Kutsch , N. Megiddo , U. Srivastava , T. M. Tran, Consistent selectivity estimation via maximum entropy, The VLDB Journal — The International Journal on Very Large Data Bases, v.16 n.1, p.55-76, January 2007 [doi>10.1007/s00778-006-0030-1]
• 10: Microsoft Corporation. SQLServer Books Online: Query hint (transact-sql), 2007. http://technet.microsoft.com/en-us/library/ms181714.apsx.
• 11: PostgreSQL. Tuning Your PostgreSQL Server. http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server.
• 12: Michael Stillger , Guy M. Lohman , Volker Markl , Mokhtar Kandil, LEO - DB2's LEarning Optimizer, Proceedings of the 27th International Conference on Very Large Data Bases, p.19-28, September 11-14, 2001
• 13: K. Yagoub, P. Belknap, B. Dageville, K. Dias, S. Joshi, and H. Yu. Oracle's SQL Performance Analyzer. DEB, 31(1), 2008.

INDEX TERMS

Primary Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.7 Database Administration

Additional Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.3 Languages

          Nouns: SQL

General Terms:
Design, Languages, Performance