Managing long-running queries

Abstract

Business Intelligence query workloads that run against very large data warehouses contain queries whose execution times range, sometimes unpredictably, from seconds to hours. The presence of even a handful of long-running queries can significantly slow down a workload consisting of thousands of queries, creating havoc for queries that require a quick response. Long-running queries are a known problem in all commercial database products. However, we have not seen a thorough classification of long-running queries nor a systematic study of the most effective corrective actions.We present here a systematic study of workload management policies, including many implemented by commercial database vendors. Our goal is to enable a system to: (1) recognize long-running queries and categorize them in terms of their impact on performance and (2) determine and take (automatically!) the most effective control actions to remedy the situation.To this end, we identify common workload management scenarios involving long-running queries, and create a taxonomy of long-running queries. We carry out an extensive set of experiments to evaluate different management policies and the relative and absolute thresholds that they may use. We find that in some scenarios, the right combination of policies can reduce the runtime of a workload by a factor of two, but that in other scenarios, any action taken increases runtime. One surprising result was that relative thresholds for execution control can compensate for inaccurate cost estimates, so that Kill&Requeue actions perform as well as Suspend&Resume.