RDBMS Storage Format Recommendation for Cloud Migration
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database migration solutions lack recommendations for migrating to cloud database systems with different processing engines and storage formats, failing to consider performance optimizations and query optimizations during schema and data migration from source to target systems.
Innovation Solution
A method and system for recommending storage formats by analyzing workload queries and Data Definition Language (DDLs) to suggest column-based or row-based storage, optimizing performance and resource usage during database migration, and supporting hybrid, multi-modal, or specialized data models based on target system capabilities.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If row-oriented storage format is used, then data writing speed is improved, but query performance for analytical operations deteriorates
Solution Approach 1:
The patent applies dynamics by enabling the database system to dynamically switch between row-oriented and column-oriented storage formats based on the type of operation being performed. The system automatically selects row-oriented format for write operations to maintain high writing speed, and column-oriented format for analytical queries to optimize query performance, making the storage system adaptive rather than static.
Solution Approach 2:
The patent segments the storage system into two distinct storage formats (row-oriented and column-oriented) that can be used for different purposes. By dividing the storage approach into separate segments, each optimized for specific workloads, the system can simultaneously maintain fast write capabilities and efficient analytical query performance without compromise.
2Productivity
If column-oriented storage format is used, then query performance is improved, but data writing speed deteriorates
Solution Approach 1:
The system dynamically adapts its storage format based on operational requirements. For data writing operations, it switches to row-oriented format to maintain high writing speed, while for analytical queries, it uses column-oriented format to optimize query performance. This dynamic switching resolves the contradiction between writing speed and query performance.
Solution Approach 2:
The patent changes the storage format parameter (from row-oriented to column-oriented and vice versa) based on the operation type. By modifying this fundamental parameter dynamically, the system optimizes performance for the current workload while maintaining the ability to switch when needs change.
3Ease of manufacture
If row-oriented storage is used, then data insertion is simplified, but memory usage and disk access increase
Solution Approach 1:
The system dynamically selects the storage format based on the operation being performed. For data insertion operations, it uses row-oriented format which simplifies the insertion process. For analytical queries, it switches to column-oriented format which reduces memory usage by only loading relevant columns, thus resolving the contradiction between insertion simplicity and memory efficiency.
4Adaptability or versatility
If migrating to cloud database systems with different storage formats is performed without recommendations, then migration flexibility is improved, but performance optimization is lost
Solution Approach 1:
The patent performs preliminary analysis of the source database workload characteristics, query patterns, and data properties before migration. Based on this preliminary action, it generates storage format recommendations that will optimize performance in the target cloud database system, ensuring that performance optimization is not lost during migration to systems with different storage formats.
Solution Approach 2:
The system provides feedback in the form of storage format recommendations based on the analyzed workload characteristics. This feedback guides the migration process to select the appropriate storage format in the target system, balancing migration flexibility with performance optimization by using data-driven recommendations.
Data Source
AI summary
The present disclosure provides a method of recommending storage format for migrating a relational database management system (RDBMS) from a source server system to a target server system, and a system thereof. The method comprises extracting SQL queries from a source database of the source server system. Further, the method comprises parsing the SQL queries to identify a set of queries corresponding to column-based operations, identifying a number of columns (Cn) involved in the identified set of queries and for each column (C), from the number of columns (Cn), determining if values of at least a subset of column records are same. On determining that values of the at least the subset of the column records are the same, the method further comprises recommending a column storage format for the RDBMS on the target server system.