Why Did My Query Slow Down

Many enterprise environments have databases running on network-attached server-storage infrastructure (referred t o 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 sett ing). This task is very challenging because the space of possible causes includes problems specific to the database, problems s pecific 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. D IADS 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. 1. INTRODUCTION Database deployments in enterprise environments are typically business critical and support high transaction rates. These deployments run on enterprise-class storage subsystems with terabyte-scale data mapped to the database either through a file system (referred to as System Managed Storage ) or raw volumes (referred to as Database Managed Storage ). Traditionally, storage was attached directly to high-end database servers to meet their capacity, throughput, and bandwidth requirements. However, economic realities of high administration costs for i slands of disconnected resources, combined with under-utilization of statically-provisioned server and storage hardware, have transformed the direct-attached architectures into a netw ork-attached setup with multiple application servers (including databa ses) connected to a consolidated and virtualized storage pool; an architecture known popularly as a Storage Area Network (SAN) . SANs are very complex systems. A typical SAN has a hierarchy of core and edge fibre-channel switches with zoning configuration that controls the connectivity of server port s with one or more heterogeneous storage controllers. The storage controllers manage a large number of raw disks by aggregating them into logical entities like pools and volumes. Given this complexity, database administrators are forced to treat th e SAN as a black-box, entrusting SAN administrators to config ure the required CPU, network, and storage resources for meeting their database’s performance requirements. Such a silo-based approach for database and SAN management is the state-of-art today. In a typical real-world scenario, database administrators open problem tickets for the SAN administrator to analyze and fix issues related to query slowdo wns: “Queries to the RepDB database used for report generation ha ve a 30% slow down in response time, compared to performance two weeks back.” Unless there is an obvious failure or degradation in the stor age hardware or the connectivity fabric, the SAN administrator’s response to this problem ticket could be: “The I/O rate for RepDB tablespace volumes has increased 40% , with increased sequential reads, but the response time is withinnormal bounds.”This “blame game” may continue for several weeks before the problem is actually fixed. In reality, the query sl owdown problem could be due to any number of causes including suboptimal plan selection by the database due to incorrect cost models, lock contention for the database tables, CPU saturation of a database server, congestion in the controller ports, an d others. The lack of consistent end-to-end information may lead to either throwing iron at the problemand creating islands of underutilized resources, or employing highly paid consultants who understand both databases and SANs to solve the original problem tickets. Our vision in this paper is an integrated database and SAN management framework. This framework combines details of both database operations as well as SAN configuration and per formance into a novel data structure referred to as an Annotated Plan Graph (APG) . The framework uses a combination of machine learning algorithms and domain knowledge to help administrators with key day-to-day tasks such as optimized allocation of SAN resources for varying database workload characteristics, diagnosis of database performance slowdowns, and what-if analysis related to workload or configuration c hanges. � This work is supported by an NSF CAREER award and faculty awards from IBM.