Database Cluster Data Redistribution via Temporary Table
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In database clusters, especially those with shared nothing architecture, data redistribution during scale-out or scale-in processes is time-consuming, locking data and preventing users from performing operations for extended periods, severely affecting service performance.
Innovation Solution
A method involving the creation of a temporary table with dual-insertion capabilities, using transaction identifiers to distinguish new and old data, allowing concurrent access and import during redistribution, and employing transactions to manage metadata exchange and node distribution, ensuring efficient data redistribution with minimal downtime.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data redistribution is performed by locking the table to ensure consistency, then data consistency is maintained, but service availability deteriorates as users cannot perform operations on the data for extended periods
Solution Approach 1:
The patent segments the data redistribution process into multiple independent phases: initial data copy, incremental change tracking via triggers, and final metadata exchange. This segmentation allows the system to maintain data consistency while permitting concurrent read/write operations during the redistribution process, thereby resolving the contradiction between reliability and productivity
Solution Approach 2:
The patent introduces intermediate structures including temporary tables and trigger mechanisms that act as mediators between the original table and the redistribution process. These intermediaries enable data to be redistributed without locking the original table, allowing users to continue operations while maintaining consistency through the intermediary layer
2Productivity
If the database cluster is scaled out by adding new nodes, then storage capacity and processing power are improved, but data redistribution time increases causing service interruption
Solution Approach 1:
The patent performs preliminary actions by creating temporary tables and establishing trigger mechanisms before actual data redistribution begins. This preliminary setup enables the system to handle data migration incrementally without service interruption, as the infrastructure is prepared in advance to support concurrent operations
Solution Approach 2:
The patent ensures continuity of useful action by implementing a mechanism where read/write operations can continue throughout the data redistribution process. The trigger-based incremental copying allows the system to maintain continuous service availability while progressively migrating data to new nodes, eliminating traditional service interruptions
3Reliability
If all data in the table is redistributed before allowing user operations, then data consistency is ensured, but the time users are locked out increases from hours to days
Solution Approach 1:
The patent introduces dynamics into the data redistribution process by implementing incremental updates through triggers that automatically track and copy changes as they occur. This dynamic approach allows the system to maintain data consistency while progressively redistributing data over time, rather than requiring a static, all-or-nothing redistribution that locks users out for extended periods
Data Source
AI summary
A data redistribution method, apparatus and a database cluster includes at least one first node, and an original table is stored on the at least one first node. A data redistribution process for redistributing the original table to the at least one first node and a second node is started, and the data redistribution process includes: starting a first transaction for creating a temporary table on the at least one first node and the second node, and associating an import operation for the original table with an import operation for the temporary table; starting a second transaction for selecting an old tuple from the original table based on a transaction identifier of the first transaction, and redistribute the selected old tuple to the temporary table. After the second transaction ends, metadata of the original table and metadata of the temporary table are exchanged.