On Enhancing Root Cause Analysis with SQL Summaries for Failures in Database Workload Replays at SAP HANA
Neetha Jambigi, Joshua Hammesfahr, Moritz Mueller, Thomas Bach, Michael Felderer · 2024
Read On Enhancing Root Cause Analysis with SQL Summaries for Failures in Database Workload Replays at SAP HANA by Neetha Jambigi, Joshua Hammesfahr, Moritz Mueller, Thomas Bach, Michael Felderer (2024) in the Tower of Babel, a free online library of public-domain works preserved in clean, searchable text. No sign-up. The full text is served as a plain-text file at a predictable URL, where text is present: https://antarctic-labs.com/tower-of-babel/arxiv-2412-13679.txt.
Introduction
Neetha Jambigi, Michael Felderer, and co-authors add smart text sums to a tool that finds why database replay tests fail, in a 2024 paper. Replaying a saved work load checks a new database build for faults. False fails from time shifts or thread quirks can swamp real ones, though.
A learned sorter sorts fails by cause but stumbles on new kinds of faults. The team adds short sums of failed queries made by a large language model. The sums give the sorter fresh clues that split causes more cleanly. Scores rose by a clear margin on their data, and users gain plain notes on each fault as well.
Catalog record
| CREATOR | Neetha Jambigi, Joshua Hammesfahr, Moritz Mueller, Thomas Bach, Michael Felderer |
|---|---|
| YEAR | 2024 |
| COLLECTION | PAPERS |
| FORMAT | TXT |
| FILE SIZE | 29 KB |
| VERSION | 1.0 |
| LICENSE | http://creativecommons.org/licenses/by/4.0/ |
| RIGHTS | CC BY |
| SOURCE | arXiv |
Source: https://arxiv.org/abs/2412-13679
Browse the full library · About the Tower of Babel
Full text
The opening of the work, from the library's hosted text:
On Enhancing Root Cause Analysis with SQL Summaries for Failures in Database Workload Replays at SAP HANA Neetha Jambigi, Joshua Hammesfahr, Moritz Mueller, Thomas Bach, Michael Felderer Abstract Capturing the workload of a database and replaying this workload for a new version of the database can be an effective approach for regression testing. However, false positive errors caused by many factors such as data privacy limitations, time dependency or non-determinism in multi-threaded environment can negatively impact the effectiveness. Therefore, we employ a machine learning based framework to automate the root cause analysis of failures found during replays. However, handling unseen novel issues not found in the training data is one general challenge of machine learning approaches with respect to generalizability of the learned model. We describe how we continue to address this challenge for more robust long-term solutions. From our experience, retraining with new failures is inadequate due to features overlapping across distinct root causes. Hence, we leverage a large language model (LLM) to analyze failed SQL statements and extract concise failure summaries as an additional feature to enhance the classification process. Our experiments show the F1-Macro score improved by 4.77% for our data. We consider our approach beneficial for providing end users with additional information to gain more insights into the found issues and to improve the assessment of the replay results. I Introduction Replaying recorded database queries poses several practical challenges, including the complexity of large replays, recording costs, legal considerations, and the occurrence of false positives. Non-deterministic factors, such as multi-threading and concurrency, randomness, and missing functionality in the recording tool, contribute to errors that do not signify regressions [ 1 ] . External factors like network and infrastructure issues further contribute to a variety of reasons for false positives in practice [ 2 ] . This can result in up to 1 million failures per replay [ 1 ] . Deciding whether a failure is a true positive or false positive case requires to identify the root cause of an error to finally assess whether a software regression exists. However, manual root cause identification and assessment is impractical due to the large amount of failures. Previous work proposed a machine learning-based automated root cause analysis system called MIRA to attribute root causes to failures arising from a capture and replay-based testing approach [ 1 ] . MIRA has been in productive use for capture and replay testing the database management system SAP HANA [ 3 , 4 ] . In addition to testing for regressions, capture and replay is also used to perform evaluations of new features, identify performance issues, and other purposes. The approach is rather successful as MIRA helped to detect one-third of potential software regressions for upcoming major versions [ 1 ] . MIRA is a supervised learning setup that relies on the features of a SQL statement collected during the replays as shown in Table I . MIRA attempts to learn representations for the SQL statement strings in combination with other textual attributes like error messages using TFIDF [ 5 ] , Doc2Vec [ 6 ] , or fasttext [ 7 ] in order to use them for classifying replay failures. However, as the observed software evolves, new failures arising from new software features demand continuous adaptation of MIRA to be able to identify root causes for new replay failures. With the current set of attributes, failures from several different root cause categories tend to be almost indistinguishable by classification models, due to extremely overlapping features across failure categories. Manual exploration of regressions arising during replays encompasses a comprehensive understanding of the events preceding a statement’s failure during the replay process. More than 10% of the root cause categories require this historical context to be classified under the right category. To tackle these issues arising from data evolution, we propose encoding the historical information of a failed SQL statement to further contextualize a replay failure. Previous work focus on developing effective representations for SQL queries using natural language processing techniques in order to model various aspects of databases [ 8 , 9 , 10 , 11 ] . Our approach involves constructing a new feature by utilizing a large language model (LLM) analyzing a sequence of failed SQL statements to extract failure summaries. In the following sections we provide an overview of MIRA and the application in a production environment, outline the encountered challenges, and present a method for enhancing the current setup to accommodate the evolving data landscape. II Failure Analysis with MIRA In this section we discuss the performance of MIRA in a production environment, user feedback on its performance, and possible measures to address drifting data. II-A Performance in Production Captured workload consists typically of SQL statements. SQL statements executed during a replay are referred to as ’events’. Failed events from a replay are forwarded to MIRA and this workflow is presented as a part of Figure 2 . Each failed event is assigned a root cause category along with measures to indicate the certainty of the classification. The predictions that are deemed uncertain are manually assessed and reclassified by ’operators’ who are the domain experts. During the time MIRA has been in use, there have been several instances of reclassifications by operators. These include instances where additional context is required to explain the reclassification, as well as a few instances of human error, and instances that the classifier could benefit from. The classifier undergoes weekly retraining, incorporating new failure data and operator-reclassified events. Before each training cycle, cross-validation, coupled with hyperparameter optimization, is conducted, monitoring F1-Scores. A significant decline in F1-Score from the previous iteration may signify potential issues with training items with overlapping features of different classes or human errors in the reclassification process. An expert reviews these items and makes decisions concerning training data and subsequent classifier training. The current setup in MIRA in production is a KNN [ 12 ] classifier with custom distance (CD) [ 1 ] using features as shown in Table I .We employ KNN because of its interpretability for analyzing predictions, which is important in production settings. We concatenate and vectorize textual attributes as a single feature using fasttext [ 7 ] and one-hot-encode the categorical attributes. Table I : Overview of Data Types Attribute Type( #Values ) Example Error Code Categorical (142) -303, 111 Request Name Categorical (10) 1, 2 SQL Type Categorical (7) 1, 19, 5 SQL Sub Type Categorical (79) 1, 2, 3 Error Message Text (-) ’Cannot find table/view X’ SQL Statement Strings Text (-) ’Select * from table X’ Over the course of 2.5 years, over 1,000 replays from 180 unique workload captures have been processed through MIRA. Currently, the total number of failure categories has grown to 233, which is 2.5 times the original count of 93 [ 1 ] . Over the span of the past 12 months, on average 30 replays per month have been processed through MIRA with an average of failures from 9 unique root causes per replay. The average number of failed statements per replay is 31,600 and the average size of a replay is over 670 million events. The latest model configuration achieved an average F1-Macro score of 82.45%