Managing Long-Running Queries Using A Query Progress Indicator
Query Progress Indicator , Execution Control , Workload Management System , Long-Running Queries
Database management systems (DBMSs) are the essential tools to monitor and manage data and transactions in databases. Normally a database receives hundreds of requests, called workloads so dynamic workload management is an important component of an autonomic DBMS solution. Controlling concurrently running queries in DBMSs is one form of workload management which is called execution control and it aims to guarantee desired performance levels for queries with varying priorities. One of the challenges in making such control-decisions is determining how much more processing time the query requires prior to completion. Query progress indicators attempt to estimate the progress of running queries. This offers a means to identify the long-running queries at early stages, potentially before other important workloads are negatively impacted, and thereby, assists in making a better decision to select the appropriate execution control mechanism for handling long-running queries. Our work illustrates a strategy and defines policies for making decisions based on a simple progress indicator that we implement in PostgreSQL, to select the time and type of execution control action to apply to recover from a declining workload performance in a DBMS. Based on the progress of the long running OLAP queries, they are either throttled or killed at different progress points to maximize system efficiency. We define a utility measure to indicate throughput recovery of the important workloads with respect to the increase in the total workload execution time. Our experiments show that throttling combined with a progress indicator results in faster and better system recovery.