Papers
Topics
Authors
Recent
2000 character limit reached

Stage: Query Execution Time Prediction in Amazon Redshift (2403.02286v1)

Published 4 Mar 2024 in cs.DB

Abstract: Query performance (e.g., execution time) prediction is a critical component of modern DBMSes. As a pioneering cloud data warehouse, Amazon Redshift relies on an accurate execution time prediction for many downstream tasks, ranging from high-level optimizations, such as automatically creating materialized views, to low-level tasks on the critical path of query execution, such as admission, scheduling, and execution resource control. Unfortunately, many existing execution time prediction techniques, including those used in Redshift, suffer from cold start issues, inaccurate estimation, and are not robust against workload/data changes. In this paper, we propose a novel hierarchical execution time predictor: the Stage predictor. The Stage predictor is designed to leverage the unique characteristics and challenges faced by Redshift. The Stage predictor consists of three model states: an execution time cache, a lightweight local model optimized for a specific DB instance with uncertainty measurement, and a complex global model that is transferable across all instances in Redshift. We design a systematic approach to use these models that best leverages optimality (cache), instance-optimization (local model), and transferable knowledge about Redshift (global model). Experimentally, we show that the Stage predictor makes more accurate and robust predictions while maintaining a practical inference latency and memory overhead. Overall, the Stage predictor can improve the average query execution latency by $20\%$ on these instances compared to the prior query performance predictor in Redshift.

Definition Search Book Streamline Icon: https://streamlinehq.com
References (69)
  1. Learning-based Query Performance Modeling and Prediction. In IEEE 28th International Conference on Data Engineering (ICDE 2012), Washington, DC, USA (Arlington, Virginia), 1-5 April, 2012, Anastasios Kementsietsidis and Marcos Antonio Vaz Salles (Eds.). IEEE Computer Society, 390–401. https://doi.org/10.1109/ICDE.2012.64
  2. Automatic Database Management System Tuning Through Large-scale Machine Learning. In Proceedings of the 2017 ACM International Conference on Management of Data, SIGMOD Conference 2017, Chicago, IL, USA, May 14-19, 2017, Semih Salihoglu, Wenchao Zhou, Rada Chirkova, Jun Yang, and Dan Suciu (Eds.). ACM, 1009–1024. https://doi.org/10.1145/3035918.3064029
  3. Enhancing one-class support vector machines for unsupervised anomaly detection. In Proceedings of the ACM SIGKDD workshop on outlier detection and description. 8–15.
  4. Bahareh Sadat Arab and Boris Glavic. 2017. Answering Historical What-if Queries with Provenance, Reenactment, and Symbolic Execution. In 9th USENIX Workshop on the Theory and Practice of Provenance, TaPP 2017, Seattle, WA, USA, June 23, 2017, Adam Bates and Bill Howe (Eds.). USENIX Association. https://www.usenix.org/conference/tapp17/workshop-program/presentation/arab
  5. Amazon Redshift Re-invented. In SIGMOD ’22: International Conference on Management of Data, Philadelphia, PA, USA, June 12 - 17, 2022, Zachary G. Ives, Angela Bonifati, and Amr El Abbadi (Eds.). ACM, 2205–2217. https://doi.org/10.1145/3514221.3526045
  6. Pyro: Deep universal probabilistic programming. The Journal of Machine Learning Research 20, 1 (2019), 973–978.
  7. A multi-objective optimization for resource allocation of emergent demands in cloud computing. J. Cloud Comput. 10, 1 (2021), 20. https://doi.org/10.1186/s13677-021-00237-7
  8. Tianqi Chen and Carlos Guestrin. 2016. XGBoost: A Scalable Tree Boosting System. In Proceedings of the 22nd ACM SIGKDD International Conference on Knowledge Discovery and Data Mining, San Francisco, CA, USA, August 13-17, 2016, Balaji Krishnapuram, Mohak Shah, Alexander J. Smola, Charu C. Aggarwal, Dou Shen, and Rajeev Rastogi (Eds.). ACM, 785–794. https://doi.org/10.1145/2939672.2939785
  9. SLA-tree: a framework for efficiently supporting SLA-based decisions in cloud computing. In EDBT 2011, 14th International Conference on Extending Database Technology, Uppsala, Sweden, March 21-24, 2011, Proceedings, Anastasia Ailamaki, Sihem Amer-Yahia, Jignesh M. Patel, Tore Risch, Pierre Senellart, and Julia Stoyanovich (Eds.). ACM, 129–140. https://doi.org/10.1145/1951365.1951383
  10. Approximating prediction uncertainty for random forest regression models. Photogrammetric Engineering & Remote Sensing 82, 3 (2016), 189–197.
  11. Schism: a Workload-Driven Approach to Database Replication and Partitioning. Proc. VLDB Endow. 3, 1 (2010), 48–57. https://doi.org/10.14778/1920841.1920853
  12. AI Meets AI: Leveraging Query Executions to Improve Index Recommendations. In Proceedings of the 2019 International Conference on Management of Data, SIGMOD Conference 2019, Amsterdam, The Netherlands, June 30 - July 5, 2019, Peter A. Boncz, Stefan Manegold, Anastasia Ailamaki, Amol Deshpande, and Tim Kraska (Eds.). ACM, 1241–1258. https://doi.org/10.1145/3299869.3324957
  13. Tsunami: A Learned Multi-dimensional Index for Correlated Data and Skewed Workloads. Proc. VLDB Endow. 14, 2 (2020), 74–86. https://doi.org/10.14778/3425879.3425880
  14. NGBoost: Natural Gradient Boosting for Probabilistic Prediction. In Proceedings of the 37th International Conference on Machine Learning, ICML 2020, 13-18 July 2020, Virtual Event (Proceedings of Machine Learning Research, Vol. 119). PMLR, 2690–2700. http://proceedings.mlr.press/v119/duan20a.html
  15. Contender: A Resource Modeling Approach for Concurrent Query Performance Prediction. In Proceedings of the 17th International Conference on Extending Database Technology, EDBT 2014, Athens, Greece, March 24-28, 2014, Sihem Amer-Yahia, Vassilis Christophides, Anastasios Kementsietsidis, Minos N. Garofalakis, Stratos Idreos, and Vincent Leroy (Eds.). OpenProceedings.org, 109–120. https://doi.org/10.5441/002/EDBT.2014.11
  16. Yarin Gal and Zoubin Ghahramani. 2016. Dropout as a Bayesian Approximation: Representing Model Uncertainty in Deep Learning. In Proceedings of the 33nd International Conference on Machine Learning, ICML 2016, New York City, NY, USA, June 19-24, 2016 (JMLR Workshop and Conference Proceedings, Vol. 48), Maria-Florina Balcan and Kilian Q. Weinberger (Eds.). JMLR.org, 1050–1059. http://proceedings.mlr.press/v48/gal16.html
  17. HypeR: Hypothetical Reasoning With What-If and How-To Queries Using a Probabilistic Causal Approach. In SIGMOD ’22: International Conference on Management of Data, Philadelphia, PA, USA, June 12 - 17, 2022, Zachary G. Ives, Angela Bonifati, and Amr El Abbadi (Eds.). ACM, 1598–1611. https://doi.org/10.1145/3514221.3526149
  18. Tilmann Gneiting and Matthias Katzfuss. 2014. Probabilistic forecasting. Annual Review of Statistics and Its Application 1 (2014), 125–151.
  19. Probabilistic programming. In Future of Software Engineering Proceedings. 167–181.
  20. Cardinality Estimation in DBMS: A Comprehensive Benchmark Evaluation. Proc. VLDB Endow. 15, 4 (2021), 752–765. https://doi.org/10.14778/3503585.3503586
  21. Benjamin Hilprecht and Carsten Binnig. 2022. Zero-Shot Cost Models for Out-of-the-box Learned Cost Prediction. Proc. VLDB Endow. 15, 11 (2022), 2361–2374. https://www.vldb.org/pvldb/vol15/p2361-hilprecht.pdf
  22. DeepDB: Learn from Data, not from Queries! Proc. VLDB Endow. 13, 7 (2020), 992–1005. https://doi.org/10.14778/3384345.3384349
  23. RadixSpline: a single-pass learned index. In Proceedings of the Third International Workshop on Exploiting Artificial Intelligence Techniques for Data Management, aiDM@SIGMOD 2020, Portland, Oregon, USA, June 19, 2020, Rajesh Bordawekar, Oded Shmueli, Nesime Tatbul, and Tin Kam Ho (Eds.). ACM, 5:1–5:5. https://doi.org/10.1145/3401071.3401659
  24. Thomas N. Kipf and Max Welling. 2016. Semi-Supervised Classification with Graph Convolutional Networks. (2016). https://doi.org/10.48550/ARXIV.1609.02907 Publisher: arXiv Version Number: 4.
  25. The Case for Learned Index Structures. In Proceedings of the 2018 International Conference on Management of Data, SIGMOD Conference 2018, Houston, TX, USA, June 10-15, 2018, Gautam Das, Christopher M. Jermaine, and Philip A. Bernstein (Eds.). ACM, 489–504. https://doi.org/10.1145/3183713.3196909
  26. Simple and Scalable Predictive Uncertainty Estimation using Deep Ensembles. In Advances in Neural Information Processing Systems 30: Annual Conference on Neural Information Processing Systems 2017, December 4-9, 2017, Long Beach, CA, USA, Isabelle Guyon, Ulrike von Luxburg, Samy Bengio, Hanna M. Wallach, Rob Fergus, S. V. N. Vishwanathan, and Roman Garnett (Eds.). 6402–6413. https://proceedings.neurips.cc/paper/2017/hash/9ef2ed4b7fd2c810847ffa5fa85bce38-Abstract.html
  27. Robust Estimation of Resource Consumption for SQL Queries using Statistical Techniques. Proc. VLDB Endow. 5, 11 (2012), 1555–1566. https://doi.org/10.14778/2350229.2350269
  28. Improving one-class SVM for anomaly detection. In Proceedings of the 2003 international conference on machine learning and cybernetics (IEEE Cat. No. 03EX693), Vol. 5. IEEE, 3077–3081.
  29. Fine-Grained Modeling and Optimization for Intelligent Resource Management in Big Data Processing. Proc. VLDB Endow. 15, 11 (2022), 3098–3111. https://www.vldb.org/pvldb/vol15/p3098-lyu.pdf
  30. Ensemble Distribution Distillation. In 8th International Conference on Learning Representations, ICLR 2020, Addis Ababa, Ethiopia, April 26-30, 2020. OpenReview.net. https://openreview.net/forum?id=BygSP6Vtvr
  31. Uncertainty in Gradient Boosting via Ensembles. In 9th International Conference on Learning Representations, ICLR 2021, Virtual Event, Austria, May 3-7, 2021. OpenReview.net. https://openreview.net/forum?id=1Jv6b0Zq3qi
  32. Bao: Making Learned Query Optimization Practical. In Proceedings of the 2021 International Conference on Management of Data (SIGMOD ’21). China. https://doi.org/10.1145/3448016.3452838 Award: ’best paper award’.
  33. Neo: A Learned Query Optimizer. PVLDB 12, 11 (2019), 1705–1718.
  34. Ryan Marcus and Olga Papaemmanouil. 2016. WiSeDB: A Learning-based Workload Management Advisor for Cloud Databases. Proc. VLDB Endow. 9, 10 (2016), 780–791. https://doi.org/10.14778/2977797.2977804
  35. Ryan Marcus and Olga Papaemmanouil. 2019. Plan-Structured Deep Neural Network Models for Query Performance Prediction. Proc. VLDB Endow. 12, 11 (2019), 1733–1746. https://doi.org/10.14778/3342263.3342646
  36. Nicolai Meinshausen. 2006. Quantile Regression Forests. J. Mach. Learn. Res. 7 (2006), 983–999. http://jmlr.org/papers/v7/meinshausen06a.html
  37. WHY SO? or WHY NO? Functional Causality for Explaining Query Answers. In Proceedings of the Fourth International VLDB workshop on Management of Uncertain Data (MUD 2010) in conjunction with VLDB 2010, Singapore, September 13, 2010 (CTIT Workshop Proceedings Series, Vol. WP10-04), Ander de Keijzer and Maurice van Keulen (Eds.). Centre for Telematics and Information Technology (CTIT), University of Twente, The Netherlands, 3–17. http://ewi1276.ewi.utwente.nl:3000/papers/MUD2010_whyso.pdf
  38. Alexandra Meliou and Dan Suciu. 2012. Tiresias: the database oracle for how-to queries. In Proceedings of the ACM SIGMOD International Conference on Management of Data, SIGMOD 2012, Scottsdale, AZ, USA, May 20-24, 2012, K. Selçuk Candan, Yi Chen, Richard T. Snodgrass, Luis Gravano, and Ariel Fuxman (Eds.). ACM, 337–348. https://doi.org/10.1145/2213836.2213875
  39. Lucas Mentch and Giles Hooker. 2016. Quantifying Uncertainty in Random Forests via Confidence Intervals and Hypothesis Tests. J. Mach. Learn. Res. 17 (2016), 26:1–26:41. http://jmlr.org/papers/v17/14-168.html
  40. Preventing Bad Plans by Bounding the Impact of Cardinality Estimation Errors. PVLDB 2, 1 (2009), 982–993. https://doi.org/10.14778/1687627.1687738
  41. Learning Multi-Dimensional Indexes. In Proceedings of the 2020 International Conference on Management of Data, SIGMOD Conference 2020, online conference [Portland, OR, USA], June 14-19, 2020, David Maier, Rachel Pottinger, AnHai Doan, Wang-Chiew Tan, Abdussalam Alawini, and Hung Q. Ngo (Eds.). ACM, 985–1000. https://doi.org/10.1145/3318464.3380579
  42. Robust Query Driven Cardinality Estimation under Changing Workloads. Proc. VLDB Endow. 16, 6 (2023), 1520–1533. https://doi.org/10.14778/3583140.3583164
  43. HR-SQL: Extending SQL with hypothetical reasoning and improved recursion for current database systems. Inf. Comput. 271 (2020), 104485. https://doi.org/10.1016/J.IC.2019.104485
  44. Jakub Nowotarski and Rafał Weron. 2018. Recent advances in electricity price forecasting: A review of probabilistic forecasting. Renewable and Sustainable Energy Reviews 81 (2018), 1548–1568.
  45. Adeola Ogunleye and Qing-Guo Wang. 2020. XGBoost Model for Chronic Kidney Disease Diagnosis. IEEE ACM Trans. Comput. Biol. Bioinform. 17, 6 (2020), 2131–2140. https://doi.org/10.1109/TCBB.2019.2911071
  46. Quickstep: A Data Platform Based on the Scaling-Up Approach. Proc. VLDB Endow. 11, 6 (2018), 663–676. https://doi.org/10.14778/3184470.3184471
  47. Scikit-learn: Machine Learning in Python. J. Mach. Learn. Res. 12 (Nov. 2011), 2825–2830. http://dl.acm.org/citation.cfm?id=1953048.2078195
  48. CatBoost: unbiased boosting with categorical features. In Advances in Neural Information Processing Systems 31: Annual Conference on Neural Information Processing Systems 2018, NeurIPS 2018, December 3-8, 2018, Montréal, Canada, Samy Bengio, Hanna M. Wallach, Hugo Larochelle, Kristen Grauman, Nicolò Cesa-Bianchi, and Roman Garnett (Eds.). 6639–6649. https://proceedings.neurips.cc/paper/2018/hash/14491b756b3a51daac41c24863285549-Abstract.html
  49. Harry V Roberts. 1965. Probabilistic prediction. J. Amer. Statist. Assoc. 60, 309 (1965), 50–62.
  50. Auto-WLM: Machine Learning Enhanced Workload Management in Amazon Redshift. In Companion of the 2023 International Conference on Management of Data, SIGMOD/PODS 2023, Seattle, WA, USA, June 18-23, 2023, Sudipto Das, Ippokratis Pandis, K. Selçuk Candan, and Sihem Amer-Yahia (Eds.). ACM, 225–237. https://doi.org/10.1145/3555041.3589677
  51. The graph neural network model. IEEE transactions on neural networks 20, 1 (2008), 61–80.
  52. Ji Sun and Guoliang Li. 2019. An End-to-End Learning-based Cost Estimator. Proc. VLDB Endow. 13, 3 (2019), 307–319. https://doi.org/10.14778/3368289.3368296
  53. STeP: Scalable Tenant Placement for Managing Database-as-a-Service Deployments. In Proceedings of the Seventh ACM Symposium on Cloud Computing, Santa Clara, CA, USA, October 5-7, 2016, Marcos K. Aguilera, Brian Cooper, and Yanlei Diao (Eds.). ACM, 388–400. https://doi.org/10.1145/2987550.2987575
  54. Machine learning-XGBoost analysis of language networks to classify patients with epilepsy. Brain Informatics 4, 3 (2017), 159–169. https://doi.org/10.1007/S40708-017-0065-7
  55. Q-Cop: Avoiding bad query mixes to minimize client timeouts under heavy loads. In Proceedings of the 26th International Conference on Data Engineering, ICDE 2010, March 1-6, 2010, Long Beach, California, USA, Feifei Li, Mirella M. Moro, Shahram Ghandeharizadeh, Jayant R. Haritsa, Gerhard Weikum, Michael J. Carey, Fabio Casati, Edward Y. Chang, Ioana Manolescu, Sharad Mehrotra, Umeshwar Dayal, and Vassilis J. Tsotras (Eds.). IEEE Computer Society, 397–408. https://doi.org/10.1109/ICDE.2010.5447850
  56. SkinnerDB: Regret-bounded Query Evaluation via Reinforcement Learning. PVLDB 11, 12 (2018), 2074–2077. https://doi.org/10.14778/3229863.3236263
  57. Self-Tuning Query Scheduling for Analytical Workloads. In SIGMOD ’21: International Conference on Management of Data, Virtual Event, China, June 20-25, 2021, Guoliang Li, Zhanhuai Li, Stratos Idreos, and Divesh Srivastava (Eds.). ACM, 1879–1891. https://doi.org/10.1145/3448016.3457260
  58. B. P. Welford. 1962. Note on a Method for Calculating Corrected Sums of Squares and Products. Technometrics 4, 3 (1962), 419–420. https://doi.org/10.1080/00401706.1962.10490022 arXiv:https://www.tandfonline.com/doi/pdf/10.1080/00401706.1962.10490022
  59. Andrew Gordon Wilson and Pavel Izmailov. 2020. Bayesian Deep Learning and a Probabilistic Perspective of Generalization. In Advances in Neural Information Processing Systems 33: Annual Conference on Neural Information Processing Systems 2020, NeurIPS 2020, December 6-12, 2020, virtual, Hugo Larochelle, Marc’Aurelio Ranzato, Raia Hadsell, Maria-Florina Balcan, and Hsuan-Tien Lin (Eds.). https://proceedings.neurips.cc/paper/2020/hash/322f62469c5e3c7dc3e58f5a4d1ea399-Abstract.html
  60. FASTgres: Making Learned Query Optimizer Hinting Effective. Proceedings of the VLDB Endowment 16, 11 (Aug. 2023), 3310–3322. https://doi.org/10.14778/3611479.3611528
  61. Semi-supervised multi-view graph convolutional networks with application to webpage classification. Inf. Sci. 591 (2022), 142–154. https://doi.org/10.1016/J.INS.2022.01.013
  62. Predicting query execution time: Are optimizer cost models really unusable?. In 29th IEEE International Conference on Data Engineering, ICDE 2013, Brisbane, Australia, April 8-12, 2013, Christian S. Jensen, Christopher M. Jermaine, and Xiaofang Zhou (Eds.). IEEE Computer Society, 1081–1092. https://doi.org/10.1109/ICDE.2013.6544899
  63. FactorJoin: A New Cardinality Estimation Framework for Join Queries. Proc. ACM Manag. Data 1, 1 (2023), 41:1–41:27. https://doi.org/10.1145/3588721
  64. Balsa: Learning a Query Optimizer Without Expert Demonstrations. In Proceedings of the 2022 International Conference on Management of Data (SIGMOD ’22). Association for Computing Machinery, New York, NY, USA, 931–944. https://doi.org/10.1145/3514221.3517885
  65. NeuroCard: One Cardinality Estimator for All Tables. Proc. VLDB Endow. 14, 1 (2020), 61–73. https://doi.org/10.14778/3421424.3421432
  66. Buffer Pool Aware Query Scheduling via Deep Reinforc ement Learning. In 2nd International Workshop on Applied AI for Datab ase Systems and Applications (AIDB@VLDB ’20), Bingsheng He, Berthold Reinwald, and Yingjun Wu (Eds.). Tokyo, Japan. https://drive.google.com/file/d/1trNYAcQ3S71SHu5dbtkBR2hjcK-VWFSx/view?usp=sharing
  67. A Data-Driven Design for Fault Detection of Wind Turbines Using Random Forests and XGboost. IEEE Access 6 (2018), 21020–21031. https://doi.org/10.1109/ACCESS.2018.2818678
  68. Query performance prediction for concurrent queries using graph embedding. Proceedings of the VLDB Endowment 13, 9 (2020), 1416–1428.
  69. FLAT: Fast, Lightweight and Accurate Method for Cardinality Estimation. Proc. VLDB Endow. 14, 9 (2021), 1489–1502. https://doi.org/10.14778/3461535.3461539
Citations (3)
View on Semantic Scholar

Summary

We haven't generated a summary for this paper yet.

Ai Sparkles Streamline Icon: https://streamlinehq.com Summarize Now

Whiteboard

Paper to Video (Beta)

Open Problems

We haven't generated a list of open problems mentioned in this paper yet.

Ai Sparkles Streamline Icon: https://streamlinehq.com Generate Now

Continue Learning

We haven't generated follow-up questions for this paper yet.

Ai Sparkles Streamline Icon: https://streamlinehq.com Generate Now

Collections

Sign up for free to add this paper to one or more collections.

User Circle Single Streamline Icon: https://streamlinehq.com Sign Up