[Paper Review] UDO: Universal Database Optimization using Reinforcement Learning

Junxiong Wang, Immanuel Trummer+1 more|arXiv (Cornell University)|Apr 5, 2021

Cloud Computing and Resource Management30 references4 citations

TL;DR

UDO is a reinforcement learning-based system for universal offline database optimization that jointly tunes light and heavy parameters—such as indexes, transaction codes, and system configurations—by delaying reward feedback for expensive changes. It achieves near-optimal performance faster than baselines by amortizing costly reconfigurations through intelligent planning and separate Markov Decision Processes for heavy and light parameters.

ABSTRACT

UDO is a versatile tool for offline tuning of database systems for specific workloads. UDO can consider a variety of tuning choices, reaching from picking transaction code variants over index selections up to database system parameter tuning. UDO uses reinforcement learning to converge to near-optimal configurations, creating and evaluating different configurations via actual query executions (instead of relying on simplifying cost models). To cater to different parameter types, UDO distinguishes heavy parameters (which are expensive to change, e.g. physical design parameters) from light parameters. Specifically for optimizing heavy parameters, UDO uses reinforcement learning algorithms that allow delaying the point at which the reward feedback becomes available. This gives us the freedom to optimize the point in time and the order in which different configurations are created and evaluated (by benchmarking a workload sample). UDO uses a cost-based planner to minimize reconfiguration overheads. For instance, it aims to amortize the creation of expensive data structures by consecutively evaluating configurations using them. We evaluate UDO on Postgres as well as MySQL and on TPC-H as well as TPC-C, optimizing a variety of light and heavy parameters concurrently.

Motivation & Objective

• Address the challenge of optimizing a broad spectrum of database tuning parameters—ranging from physical design to system configuration—without relying on simplifying cost models.
• Overcome the high overhead of changing expensive (heavy) parameters like index creation or server restarts in reinforcement learning-based tuning.
• Enable joint optimization of interdependent parameters (e.g., indexes and configuration settings) that affect each other’s performance.
• Develop a unified framework that treats all tuning choices uniformly, avoiding the need for separate tools per parameter type.
• Demonstrate that delayed feedback and reconfiguration planning significantly improve convergence speed and solution quality in expensive tuning scenarios.

Proposed method

• Separate parameters into heavy (expensive to change, e.g., index creation) and light (cheap to change, e.g., configuration flags) to apply tailored reinforcement learning strategies.
• Use a delayed-reward reinforcement learning algorithm for heavy parameters, allowing configurations to be created and evaluated in batches to amortize expensive setup costs.
• Employ a cost-based planner to sequence configuration creation and evaluation, minimizing reconfiguration overhead by grouping similar configurations (e.g., reusing a single index for multiple evaluations).
• Model the optimization of light parameters for each fixed heavy parameter setting as a separate Markov Decision Process (MDP), enabling targeted and fast convergence.
• Use actual query execution on workload samples to measure performance and generate reward signals, avoiding reliance on predictive cost models.
• Apply no-delay reinforcement learning for light parameters to enable fast adaptation within each heavy parameter setting.

Experimental results

Research questions

• RQ1 Can a unified reinforcement learning framework effectively optimize diverse database tuning parameters—including physical design and system configuration—simultaneously?
• RQ2 How can reinforcement learning be adapted to handle the high cost of changing heavy parameters (e.g., index creation) without degrading optimization efficiency?
• RQ3 To what extent does delayed feedback in reinforcement learning improve convergence and reduce reconfiguration overhead in database tuning?
• RQ4 Does joint optimization of interdependent parameters (e.g., indexes and configuration settings) lead to better performance than independent tuning?
• RQ5 How does UDO generalize to unseen workloads or dynamic workload shifts compared to baselines relying on cost models or prior training data?

Key findings

• UDO achieves near-optimal configurations faster than baselines by leveraging delayed feedback and reconfiguration planning, especially for expensive heavy parameters.
• On TPC-H and TPC-C benchmarks, UDO reduces query run time by up to 5% compared to the nearest baseline under dynamic workload switching, even when baselines are given index recommendations from UDO.
• Generalization performance degrades by about 40% when training on only five TPC-H templates instead of 20, but UDO’s generalization is comparable to baselines, indicating robustness despite no prior training data.
• UDO outperforms a baseline using the query optimizer’s cost model for index selection, finding better index sets even though the cost model is used in the baseline.
• The use of separate MDPs for light parameters under each heavy parameter setting enables faster convergence and better adaptation, especially when heavy parameters are fixed.
• UDO’s planning component successfully amortizes the cost of expensive index creation by evaluating multiple configurations using the same index, reducing total reconfiguration time by up to 70% in some scenarios.