Why Did My Query Slow Down?

Abstract

Many enterprise environments have databases running on network-attached server-storage infrastructure (referred to as Storage Area Networks or SANs). Both the database and the SAN are complex systems that need their own separate administrative teams. This paper puts forth the vision of an innovative management framework to simplify administrative tasks that require an in-depth understanding of both the database and the SAN. As a concrete instance, we consider the task of diagnosing the slowdown in performance of a database query that is executed multiple times (e.g., in a periodic report-generation setting). This task is very challenging because the space of possible causes includes problems specific to the database, problems specific to the SAN, and problems that arise due to interactions between the two systems. In addition, the monitoring data available from these systems can be noisy.We describe the design of DIADS which is an integrated diagnosis tool for database and SAN administrators. DIADS generates and uses a powerful abstraction called Annotated Plan Graphs (APGs) that ties together the execution path of queries in the database and the SAN. Using an innovative workflow that combines domain-specific knowledge with machine-learning techniques, DIADS was applied successfully to diagnose query slowdowns caused by complex combinations of events across a PostgreSQL database and a production SAN.

Analyzing Query Performance and Attributing Blame for Contentions in a Cluster Computing Framework

“…(3) Root Cause Diagnosis tools: Performance diagnosis has been studied in the database community [19,15,33], for cluster computing frameworks [24,26], and in cloud based services [27]. The design of ProtoXplore is based on several concepts introduced in these works: (a) Database Community: In ADDM [19], Database Time of a SQL query is used for performing an impact analysis of any resource or activity in the database.…”

“…ProtoXplore furthers this approach to provide an endto-end query contention analysis platform while also enabling multi-step deep exploration of contention symptoms. ProtoXplore's multi-level explanations approach is motivated from DIADS [15]. They use Annotated Plan Graphs that combines the details of database operations and Storage Area Networks (SANs) to provide an integrated performance diagnosis tool.…”