Umeshwar Dayal
Abstract
We explore how to manage database workloads that contain a mixture of OLTP-like queries that run for milliseconds as well as business intelligence queries and maintenance tasks that last for hours. As data warehouses grow in size to petabytes and complex analytic queries play a greater role in day-to-day business operations, factors such as inaccurate cardinality estimates, data skew, and resource contention all make it notoriously difficult to predict how such queries will behave before they start executing. However, traditional workload management assumes that accurate expectations for the resource requirements and performance characteristics of a workload are available at compile-time, and relies on such information in order to make critical workload management decisions. In this paper, we describe our approach to dealing with inaccurate predictions. First, we evaluate the ability of workload management algorithms to handle workloads that include unexpectedly long-running queries. Second, we describe a new and more accurate method for predicting the resource usage of queries before runtime. We have carried out an extensive set of experiments, and report on a few of our results.
- M. F. Arlitt. Characterizing Web user sessions. SIGMETRICS Performance Evaluation Review, 28(2):50--63, 2000. Google Scholar
Digital Library
- F. R. Bach and M. I. Jordan. Kernel Independent Component Analysis. Journal of Machine Learning Research, 3:1--48, 2003. Google ScholarDigital Library
- D. G. Benoit. Automated Diagnosis and Control of DBMS Resources. In EDBT PhD. Workshop, 2000.Google Scholar
- M. J. Carey, M. Livny, and H. Lu. Dynamic Task Allocation In A Distributed Database System. In ICDCS, pages 282--291, 1985.Google Scholar
- S. Chaudhuri, R. Kaushik, and R. Ramamurthy. When Can We Trust Progress Estimators for SQL Queries? In Proc. of the ACM SIGMOD Intl. Conf. on Management of Data, pages 575--586, 2005. Google ScholarDigital Library
- S. Chaudhuri, V. Narasayya, and R. Ramamurthy. Estimating Progress of Execution for SQL Queries. In Proc. of the ACM SIGMOD Intl. Conf. on Management of Data, pages 803--814, 2004. Google ScholarDigital Library
- D. L. Davison and G. Graefe. Dynamic Resource Brokering for Multi-User Query Execution. In Proc. of the ACM SIGMOD Intl. Conf. on Management of Data, pages 281--292, 1995. Google ScholarDigital Library
- L. Eeckhout, H. Vandierendonck, and K. D. Bosschere. How Input Data Sets Change Program Behaviour. In 5th Workshop on Computer Architecture Evaluation Using Commercial Workloads, 2002.Google Scholar
- S. Elnaffar, P. Martin, and R. Horman. Automatically Classifying Database Workloads. In Proc. of ACM Conference on Information and Knowledge Management (CIKM), pages 622--624, 2002. Google ScholarDigital Library
- A. Ganapathi, H. Kuno, U. Dayal, J. Wiener, A. Fox, M. Jordan, and D. Patterson. Predicting Multiple Metrics for Queries: Better Decisions Enabled by Machine Learning. In Proc. of the 21st Intl. Conf. on Data Engineering (ICDE), to appear, 2009. Google ScholarDigital Library
- P. Gillin. BI @ the Speed of Business. Computer World Technology, December 2007.Google Scholar
- C. Gupta and A. Mehta. PQR: Predicting Query Execution Times for Autonomous Workload Management. In Proc. Intl Conf on Autonomic Computing, 2008. Google ScholarDigital Library
- K. Keeton, D. A. Patterson, Y. Q. He, R. C. Raphael, and W. E. Baker. Performance Characterization of a Quad Pentium Pro SMP using OLTP Workloads. In The 25th Intl. Symposium on Computer Architecture (ISCA), pages 15--26, 1998. Google ScholarDigital Library
- S. Krompass, D. Gmach, A. Scholz, S. Seltzsam, and A. Kemper. Quality of Service Enabled Database Applications. In Proc. of the 4 th Intl. Conf. on Service-Oriented Computing (ICSOC), volume 4294 of Lecture Notes in Computer Science (LNCS), pages 215--226, 2006. Google ScholarDigital Library
- S. Krompass, H. Kuno, U. Dayal, and A. Kemper. Dynamic Workload Management for Very Large Data Warehouses: Juggling Feathers and Bowling Balls. In Proc. of the 33rd Intl. Conf. on Very Large Data Bases (VLDB), 2007. Google ScholarDigital Library
- J. L. Lo, L. A. Barroso, S. J. Eggers, K. Gharachorloo, H. M. Levy, and S. S. Parekh. An Analysis of Database Workload Performance on Simultaneous Multithreaded Processors. In The 25th Intl. Symposium on Computer Architecture (ISCA), pages 39--50, 1998. Google ScholarDigital Library
- G. Luo, J. F. Naughton, C. J. Ellmann, and M. W. Watzke. Toward a Progress Indicator for Database Queries. In Proc. of the ACM SIGMOD Intl. Conf. on Management of Data, pages 791--802, 2004. Google ScholarDigital Library
- G. Luo, J. F. Naughton, C. J. Ellmann, and M. W. Watzke. Increasing the Accuracy and Coverage of SQL Progress Indicators. In Proc. of the 21 st Intl. Conf. on Data Engineering (ICDE), pages 853--864, 2005. Google ScholarDigital Library
- G. Luo, J. F. Naughton, and P. S. Yu. Multi-query SQL Progress Indicators. In Proc. of the 10th Intl. Conf. on Extending Database Technology (EDBT), pages 921--941, 2006. Google ScholarDigital Library
- V. Markl and G. Lohman. Learning Table Access Cardinalities with LEO. In Proc. of the ACM SIGMOD Intl. Conf. on Management of Data, page 613, 2002. Google ScholarDigital Library
- M. Mehta and D. J. DeWitt. Dynamic Memory Allocation for Multiple-Query Workload. In Proc. of the 19th Intl. Conf. on Very Large Data Bases (VLDB), August 1993. Google ScholarDigital Library
- J. Moore, J. Chase, K. Farkas, and P. Ranganathan. Data Center Workload Monitoring, Analysis, and Emulation, 2005.Google Scholar
- B. Schroeder, M. Harchol-Balter, A. Iyengar, and E. M. Nahum. Achieving Class-Based QoS for Transactional Workloads. In Proc. of the 22nd Intl. Conf. on Data Engineering (ICDE), page 153, 2006. Google ScholarDigital Library
- M. Stillger, G. M. Lohman, V. Markl, and M. Kandil. LEO -DB2's LEarning Optimizer. In Proc. of the 27th Intl. Conf. on Very Large Data Bases (VLDB), pages 19--28, 2001. Google ScholarDigital Library
- G. Weikum, C. Hasse, A. Mönkeberg, and P. Zabback. The COMFORT Automatic Tuning Project. Information Systems, 19(5):381--432, 1994. Google ScholarDigital Library
- C. White. The Next Generation of Business Intelligence: Operational BI. DM Review Magazine, May 2005.Google Scholar
- R. M. Yoo, H. Lee, K. Chow, and H.-H. S. Lee. Constructing a Non-Linear Model with Neural Networks for Workload Characterization. In IISWC, pages 150--159, 2006.Google ScholarCross Ref
- P. S. Yu, M.-S. Chen, H.-U. Heiss, and S. Lee. On Workload Characterization of Relational Database Environments. Software Engineering, 18(4):347--355, 1992. Google ScholarDigital Library
- N. Zhang, P. J. Haas, V. Josifovski, G. M. Lohman, and C. Zhang. Statistical Learning Techniques for Costing XML Queries. In Proc. of the 31st Intl. Conf. on Very Large Data Bases (VLDB), pages 289--300, 2005. Google ScholarDigital Library
Index Terms
- Managing operational business intelligence workloads
Recommendations
Managing dynamic mixed workloads for operational business intelligence
DNIS'10: Proceedings of the 6th international conference on Databases in Networked Information SystemsAs data warehousing technology gains a ubiquitous presence in business today, companies are becoming increasingly reliant upon the information contained in their data warehouses to inform their operational decisions. This information, known as business ...
Predicting Multiple Metrics for Queries: Better Decisions Enabled by Machine Learning
ICDE '09: Proceedings of the 2009 IEEE International Conference on Data EngineeringOne of the most challenging aspects of managing a very large data warehouse is identifying how queries will behave before they start executing. Yet knowing their performance characteristics --- their runtimes and resource usage --- can solve two ...
Operational Business Intelligence: Processing Mixed Workloads
Modern business applications have operational business intelligence features that require processing analytical queries and business transactions together at the same time and on the same data. This results in mixed workloads, which are a big challenge ...
Comments