Interaction-aware scheduling of report-generation workloads

Abstract

The typical workload in a database system consists of a mix of multiple queries of different types that run concurrently. Interactions among the different queries in a query mix can have a significant impact on database performance. Hence, optimizing database performance requires reasoning about query mixes rather than considering queries individually. Current database systems lack the ability to do such reasoning. We propose a new approach based on planning experiments and statistical modeling to capture the impact of query interactions. Our approach requires no prior assumptions about the internal workings of the database system or the nature and cause of query interactions; making it portable across systems.To demonstrate the potential of modeling and exploiting query interactions, we have developed a novel interactionaware query scheduler for report-generation workloads. Our scheduler, called QShuffler, uses two query scheduling algorithms that leverage models of query interactions. The first algorithm is optimized for workloads where queries are submitted in large batches. The second algorithm targets workloads where queries arrive continuously, and scheduling decisions have to be made on-line. We report an experimental evaluation of QShuffler using TPC-H workloads running on IBM DB2. The evaluation shows that QShuffler, by modeling and exploiting query interactions, can consistently out-

Towards predicting query execution time for concurrent and dynamic database workloads

“…CO = ns · cs + nr · cr + nt · ct + ni · ci + no · co. (1) Here cs, cr, ct, ci, and co are cost units as follows: 1) cs: seq page cost, the I/O cost to sequentially access a page.…”

“…We have compared our analytic-model based approach with a machine-learning based approach that is a variant of the approach used in [1,2]. Our experimental evaluation over the TPC-H benchmark shows that, the analytic-model based approach can lead to comparable and often better prediction accuracy than the machinelearning based approach.…”

“…CPU interactions are usually negative, namely, the pipelines are competing with each other to share CPU cycles. On the other hand, I/O interactions can be either positive or negative [1] (see Example 2 as well). Therefore, we propose separating the modeling of CPU and I/O interactions.…”

“…For CPU interactions, we derive a simple model for the CPUrelated cost units ct, ci, and co. For I/O interactions, we extend the idea from [1], using machine-learning techniques to build regression models for the I/O-related cost units cs and cr. Due to space limitations, in the following, we focus on illustrating the basic ideas.…”

“…The existing work on concurrent query prediction [2,6], however, assumes that the workload is static, namely, the queries participating in the workload are known beforehand. While some workloads certainly conform to this assumption (e.g., the report-generation workloads described in [1]), others do not. Real-world database workloads can be dynamic, in that the set of queries that will be submitted to the system cannot be known a priori.…”