Predicting completion times of batch query workloads using interaction-aware models and simulation

A question that database administrators (DBAs) routinely need to answer is how long a batch query workload will take to complete. This question arises, for example, while planning the execution of different report-generation workloads to fit within available time windows. To answer this question accurately, we need to take into account that the typical workload in a database system consists of mixes of concurrent queries. Interactions among different queries in these mixes need to be modeled, rather than the conventional approach of considering each query separately. This paper presents a new approach for estimating workload completion times that takes the significant impact of query interactions into account. This approach builds performance models using an experiment-driven technique, by sampling the space of possible query mixes and fitting statistical models to the observed performance at these samples. No prior assumptions are made about the internal workings of the database system or the cause of query interactions, making the models robust and portable. We show that a careful choice of sampling and statistical modeling strategies can result in accurate models, and we present a novel interaction-aware workload simulator that uses these models to estimate workload completion times. An experimental evaluation with complex TPC-H queries on IBM DB2 shows that this approach consistently predicts workload completion times with less than 20% error.

[1]  Leonie Kohl,et al.  Fundamental Concepts in the Design of Experiments , 2000 .

[2]  Kamesh Munagala,et al.  Interaction-aware scheduling of report-generation workloads , 2011, The VLDB Journal.

[3]  Archana Ganapathi,et al.  Predicting Multiple Metrics for Queries: Better Decisions Enabled by Machine Learning , 2009, 2009 IEEE 25th International Conference on Data Engineering.

[4]  Chetan Gupta,et al.  PQR: Predicting Query Execution Times for Autonomous Workload Management , 2008, 2008 International Conference on Autonomic Computing.

[5]  C. Ireland.  Fundamental concepts in the design of experiments , 1964 .

[6]  Christopher Stewart,et al.  Exploiting nonstationarity for performance prediction , 2007, EuroSys '07.

[7]  Jin Chen,et al.  Adaptive Learning of Metric Correlations for Temperature-Aware Database Provisioning , 2007, Fourth International Conference on Autonomic Computing (ICAC'07).

[8]  Shivnath Babu,et al.  Tuning Database Configuration Parameters with iTuned , 2009, Proc. VLDB Endow..

[9]  Shivnath Babu,et al.  Interaction-aware prediction of business intelligence workload completion times , 2010, 2010 IEEE 26th International Conference on Data Engineering (ICDE 2010).

[10]  George Candea,et al.  A Scalable, Predictable Join Operator for Highly Concurrent Data Warehouses , 2009, Proc. VLDB Endow..

[11]  Kamesh Munagala,et al.  Modeling and exploiting query interactions in database systems , 2008, CIKM '08.

[12]  Herodotos Herodotou,et al.  Automated Experiment-Driven Management of (Database) Systems , 2009, HotOS.

[13]  H. Ryser.  Combinatorial Mathematics: THE PRINCIPLE OF INCLUSION AND EXCLUSION , 1963 .

[14]  Amr El Abbadi,et al.  Multiple query optimization in middleware using query teamwork , 2005, Softw. Pract. Exp..

[15]  Chetan Gupta,et al.  BI batch manager: a system for managing batch workloads on enterprise data-warehouses , 2008, EDBT '08.

[16]  Kevin Wilkinson,et al.  Managing long-running queries , 2009, EDBT '09.

[17]  Kevin Wilkinson,et al.  Managing operational business intelligence workloads , 2009, OPSR.

[18]  Prasan Roy,et al.  Efficient and extensible algorithms for multi query optimization , 1999, SIGMOD '00.

[19]  Ian Witten,et al.  Data Mining , 2000 .

[20]  Kenneth Salem,et al.  Storage workload estimation for database management systems , 2007, SIGMOD '07.

[21]  Benoît Dageville,et al.  Self-Tuning for SQL Performance in Oracle Database 11g , 2009, 2009 IEEE 25th International Conference on Data Engineering.

[22]  Qi Zhang,et al.  R-Capriccio: A Capacity Planning and Anomaly Detection Tool for Enterprise Services with Live Workloads , 2007, Middleware.

[23]  Jose Renato Santos,et al.  JustRunIt: Experiment-Based Management of Virtualized Data Centers , 2009, USENIX Annual Technical Conference.

[24]  Tim Brecht,et al.  Q-Cop: Avoiding bad query mixes to minimize client timeouts under heavy loads , 2010, 2010 IEEE 26th International Conference on Data Engineering (ICDE 2010).

[25]  Thomas J. Santner,et al.  The Design and Analysis of Computer Experiments , 2003, Springer Series in Statistics.

[26]  Terence Kelly,et al.  Detecting Performance Anomalies in Global Applications , 2005, WORLDS.

[27]  Ashraf Aboulnaga,et al.  Automatic virtual machine configuration for database workloads , 2008, SIGMOD Conference.

[28]  Shivnath Babu,et al.  Query interactions in database workloads , 2009, DBTest '09.

[29]  Jin Chen,et al.  Autonomic Provisioning of Backend Databases in Dynamic Content Web Servers , 2006, 2006 IEEE International Conference on Autonomic Computing.

[30]  Qi Zhang,et al.  A Regression-Based Analytic Model for Dynamic Resource Provisioning of Multi-Tier Applications , 2007, Fourth International Conference on Autonomic Computing (ICAC'07).

[31]  Gregory R. Ganger,et al.  Relative fitness modeling , 2009, CACM.