Database Replication System Using Query Normalization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Log-based replication methods incur high overhead and performance impact when handling large transactions with many data row changes, leading to asymmetric resource loading in target databases.
Innovation Solution
A method and system for replicating database tables that involves defining subscriptions, executing queries on the source database, normalizing queries, determining semantic appropriateness for target databases, and sending normalized queries for execution, allowing for optimized replication by choosing between traditional log-based and query statement replication.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If log-based replication is used to capture and propagate data changes, then data synchronization is achieved, but replication overhead and network usage increase significantly when handling large transactions with many data row changes
Solution Approach 1:
The patent segments the replication process into two distinct modes: log-based replication for small transactions and statement-based replication for large transactions. The system divides transactions into categories based on their impact size, applying different replication strategies to each segment. This segmentation allows the system to optimize for both data synchronization reliability and replication overhead efficiency.
Solution Approach 2:
The patent implements dynamic replication mode selection that adapts based on transaction characteristics. The system dynamically determines whether to use log-based or statement-based replication by evaluating transaction size, row changes, and other factors. This dynamic approach allows the replication mechanism to automatically adjust its behavior to minimize overhead while maintaining synchronization reliability.
2Manufacturing precision
If log-based replication propagates each atomic operation individually to target databases, then exact data copying is achieved, but target database performance deteriorates due to severe resource loading
Solution Approach 1:
The patent applies partial action by selectively applying log-based replication only to transactions that benefit from it (small transactions with few row changes). For large transactions, the system uses statement-based replication that applies the entire transaction as a single unit rather than propagating each atomic operation individually. This partial application of log-based replication maintains data copying accuracy where needed while avoiding the performance penalty on target databases.
Solution Approach 2:
The patent changes the replication parameter from atomic operation level to statement level based on transaction characteristics. By monitoring transaction size, row changes, and other parameters, the system dynamically adjusts the replication granularity. This parameter change allows the system to maintain data copying accuracy for small transactions while improving target database performance for large transactions by reducing the number of individual operations applied.
3Reliability
If all transactions are replicated using log-based methods, then complete data synchronization is maintained, but network usage and log scanning costs increase significantly
Solution Approach 1:
The patent segments transactions into different categories based on their characteristics (small vs. large transactions, number of row changes, transaction type). This segmentation enables the system to apply log-based replication only to transactions where it is most efficient (small transactions), while using statement-based replication for larger transactions. This reduces overall network usage and log scanning costs while maintaining complete data synchronization across all transaction types.
Solution Approach 2:
The patent changes the replication approach parameter based on transaction parameters such as size, row changes, and complexity. By evaluating these parameters, the system dynamically selects between log-based and_statement-based replication. This parameter-driven approach optimizes network usage by avoiding excessive log scanning and data transmission for transactions that can be efficiently replicated using statement-based methods, while maintaining complete synchronization.
Data Source
AI summary
A system, method, and computer program product are provided for replicating a table in a source database to a target database. In accordance with an embodiment of the present invention, a subscription to the table is defined and a query is executed on the source database. The query is then normalized, and it is determined whether the query would cause semantically appropriate changes to the target database. If so, the normalized query is sent to the target database for execution, in accordance with an additional embodiment of the present invention.