Managing long-running queries

ABSTRACT

Business Intelligence query workloads that run against very large data warehouses contain queries whose execution times range, sometimes unpredictably, from seconds to hours. The presence of even a handful of long-running queries can significantly slow down a workload consisting of thousands of queries, creating havoc for queries that require a quick response. Long-running queries are a known problem in all commercial database products. However, we have not seen a thorough classification of long-running queries nor a systematic study of the most effective corrective actions.

We present here a systematic study of workload management policies, including many implemented by commercial database vendors. Our goal is to enable a system to: (1) recognize long-running queries and categorize them in terms of their impact on performance and (2) determine and take (automatically!) the most effective control actions to remedy the situation.

To this end, we identify common workload management scenarios involving long-running queries, and create a taxonomy of long-running queries. We carry out an extensive set of experiments to evaluate different management policies and the relative and absolute thresholds that they may use. We find that in some scenarios, the right combination of policies can reduce the runtime of a workload by a factor of two, but that in other scenarios, any action taken increases runtime. One surprising result was that relative thresholds for execution control can compensate for inaccurate cost estimates, so that Kill&Requeue actions perform as well as Suspend&Resume.

REFERENCES

• 1: James Alan Basney , Miron Livny, Network and cpu co-allocation in high throughput computing environments, The University of Wisconsin - Madison, 2001
• 2: D. G. Benoit. Automated Diagnosis and Control of DBMS Resources. In EDBT PhD. Workshop, 2000.
• 3: Badrish Chandramouli , Christopher N. Bond , Shivnath Babu , Jun Yang, Query suspend and resume, Proceedings of the 2007 ACM SIGMOD international conference on Management of data, June 11-14, 2007, Beijing, China [doi>10.1145/1247480.1247542]
• 4: Surajit Chaudhuri , Raghav Kaushik , Ravishankar Ramamurthy, When can we trust progress estimators for SQL queries?, Proceedings of the 2005 ACM SIGMOD international conference on Management of data, June 14-16, 2005, Baltimore, Maryland [doi>10.1145/1066157.1066223]
• 5: Surajit Chaudhuri , Raghav Kaushik , Abhijit Pol , Ravi Ramamurthy, Stop-and-restart style execution for long running decision support queries, Proceedings of the 33rd international conference on Very large data bases, September 23-27, 2007, Vienna, Austria
• 6: Surajit Chaudhuri , Vivek Narasayya , Ravishankar Ramamurthy, Estimating progress of execution for SQL queries, Proceedings of the 2004 ACM SIGMOD international conference on Management of data, June 13-18, 2004, Paris, France [doi>10.1145/1007568.1007659]
• 7: C. Chekuri and S. Khanna. Approximation Algorithms for Minimizing Average Weighted Completion Time, chapter 11. CRC Press, 2004.
• 8: W.-J. Chen, B. Comeau, T. Ichikawa, S. S. Kumar, M. Miskimen, H. T. Morgan, L. Pay, and T. Väättänen. Workload Manager for Linux, Unix, and Windows. http://www.redbooks.ibm.com/redbooks/pdfs/sg247524.pdf, May 2008.
• 9: HP Neoview Workload Management Services Guide, August 2007.
• 10: Stefan Krompass , Harumi Kuno , Umeshwar Dayal , Alfons Kemper, Dynamic workload management for very large data warehouses: juggling feathers and bowling balls, Proceedings of the 33rd international conference on Very large data bases, September 23-27, 2007, Vienna, Austria
• 11: N. Lei. Workload Management for DB2 Data Warehouse. http://www.redbooks.ibm.com/redpapers/pdfs/redp3927.pdf.
• 12: Gang Luo , Jeffrey F. Naughton , Curt J. Ellmann , Michael W. Watzke, Toward a progress indicator for database queries, Proceedings of the 2004 ACM SIGMOD international conference on Management of data, June 13-18, 2004, Paris, France [doi>10.1145/1007568.1007658]
• 13: Gang Luo , Jeffrey F. Naughton , Curt J. Ellmann , Michael W. Watzke, Increasing the Accuracy and Coverage of SQL Progress Indicators, Proceedings of the 21st International Conference on Data Engineering, p.853-864, April 05-08, 2005 [doi>10.1109/ICDE.2005.79]
• 14: G. Luo, J. F. Naughton, and P. S. Yu. Multi-query SQL Progress Indicators. In 10^th Intl. Conf. on Extending Database Technology (EDBT), 2006.
• 15: Microsoft. Managing SQL Server Workloads with Resource Governor. http://msdn.microsoft.com/en-us/library/bb933866.aspx, December 2008.
• 16: Oracle Discoverer Administrator Administration Guide 10g Release 2 (10.1.2.1). http://download.oracle.com/docs/pdf/B13916_04.pdf, July 2005.
• 17: Oracle Database Resource Manager. http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/dbrm.htm#i1010776, March 2008.
• 18: Raghunath Othayoth Nambiar , Meikel Poess, The making of TPC-DS, Proceedings of the 32nd international conference on Very large data bases, September 12-15, 2006, Seoul, Korea
• 19: Teradata. Teradata Dynamic Workload Manager User Guide, Release 13.0.0.0 (B035-2513-088A), August 2008.
• 20: Gerhard Weikum , Christof Hasse , Axel Mönkeberg , Peter Zabback, The COMFORT automatic tuning project, Information Systems, v.19 n.5, p.381-432, July 1994 [doi>10.1016/0306-4379(94)90004-3]