TETRIS: Intelligent database workload manager with multi-objective query optimization

Modern database system workloads as those incurred by business intelligence applications involve ad-hoc, highly complex, and expensive queries. Such queries involve quite different resource consumption and priorities. More sophisticated workload managers are needed to support these difficult workloads. Previous work has proposed a new multi-objective query optimizer that can optimize queries for different purposes including best time to completion, best cpu cost, best buffer pool footprint, etc. In this work, we investigate the design of an effective workload manager that can schedule the queries, each equipped with multiple execution plans generated by the multi-objective optimizer, by choosing the best plan for each query based on the runtime environment and query characteristics.;We propose three approaches for the query scheduling policy generator.The problem to be solved by the policy generator can be cast as dynamic job scheduling while each job is equipped with multiple plans for execution. The first approach includes a set of heuristics making the locally optimal choice at each stage in hope of approximating the global optimum. The second approach models the problem as a multi-choice multi-dimensional Knapsack Problem, where queries are considered to be processed in a batch fashion. The third is to consider query scheduling as a Markov Decision Process, where the goal is to find an optimal policy that maximizes the expected total discounted reward. We implement a simulated workload manager to evaluate a group of heuristics algorithms, and the results show that query scheduling with multiple objective query optimization increases the throughput of system significantly.;We present an architecture of an intelligent workload manager incorporating a policy generator and multi-objective query optimizer. This architecture has two novelties: (1) it is integrated with an new query optimizer that optimizes queries with regard to multiple objectives, and (2) it has an effective policy generator that admits queries and chooses a suitable execution plan from multiple candidates for each query.