Reinforcement Learning for Distributed Database Layout Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current techniques for designing physical data layouts in distributed parallel databases are inefficient, requiring extensive processing time and often failing to achieve optimal performance due to the complexity of enumerating all possible node configurations and the limitations of query optimizers.
Innovation Solution
A data layout model generation system using reinforcement learning to automatically determine optimal node configurations and data layouts by acquiring sample data, estimating layouts based on states, calculating rewards, and updating configurations to improve query processing costs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If all possible physical data layouts are enumerated and full search is performed using branch and bound method, then an optimum physical data layout can be derived, but very long processing time is required
Solution Approach 1:
The patent performs preliminary actions by pre-calculating and storing cost information for intermediate results of query execution in a cost information storage unit. This allows the branch and bound search to efficiently access pre-computed cost data without recalculating during the search process, significantly reducing processing time while maintaining accuracy in finding the optimum physical data layout.
Solution Approach 2:
The patent extracts only the necessary cost information for intermediate results and stores it separately in a dedicated cost information storage unit. This extraction approach allows the system to focus computational resources on storing and retrieving only the critical cost data needed for the branch and bound search, rather than computing everything during the search process.
2Loss of time
If query optimizer is used to predict query cost at high speed, then processing time is reduced, but performance of query cost prediction is not high and optimum physical data layout is not necessarily obtained
Solution Approach 1:
The patent introduces a cost information storage unit as an intermediary between the query optimizer and the branch and bound search process. This intermediary stores pre-calculated cost information for intermediate results, allowing the system to combine the speed of pre-computation with the accuracy of detailed cost analysis, avoiding the inaccuracies of pure query optimizer prediction while maintaining faster processing than full enumeration.
3Measurement precision
If database administrator performs physical layout design with rich knowledge, then appropriate data layout can be achieved, but it becomes difficult due to increase in number of queries, tables, and amount of data
Solution Approach 1:
The patent implements a self-service system where the database management system automatically performs physical data layout optimization using branch and bound search with pre-stored cost information. This eliminates the need for database administrators to manually analyze complex query patterns and data distributions, allowing the system to handle increased complexity of queries, tables, and data volumes automatically without requiring proportional increases in administrator expertise or effort.
Data Source
AI summary
A data layout model generation system generates, with reinforcement learning, a node configuration and a data layout key in a distributed parallel database. This system includes a sample acquisition processor that acquires, on the basis of a predetermined acquisition method, sample data from data stored in the distributed parallel database, a data layout estimator having, as states in the reinforcement learning, the node configuration and the data layout key including information regarding an order of sorting columns that constitute the data and information regarding a method for distribution between nodes, the data layout estimator estimating layout of the data on the basis of the state and the sample data, a reward calculator that calculates a reward in the reinforcement learning on the basis of a result obtained by estimating the layout of the data, the node configuration, and a processing cost of a query executed on the distributed parallel database.