Parallel Database Mirroring for Bulk Load Synchronization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database mirroring methods, such as log shipping, are inefficient for large data warehouse systems with high volumes of bulk loads, leading to unacceptably long mean-time-to-repair (MTTR) due to sequential processing at the mirror database, which cannot perform parallel I/O during catch-up, causing service unavailability.
Innovation Solution
The solution involves writing data and transaction logs in parallel to both primary and mirror databases, bypassing the conventional redo process and avoiding the Write-Ahead-Log (WAL) for bulk loads, ensuring both databases remain synchronized and reducing the need for sequential redo operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If log shipping is used for database mirroring, then data consistency between primary and mirror databases is maintained, but the mirror database cannot perform parallel I/O during catch-up, resulting in slow synchronization for large data warehouse systems
Solution Approach 1:
The patent segments the transaction log into multiple partitions that can be processed in parallel. Instead of sequentially reading the entire transaction log, the mirror database divides it into segments and applies changes from different segments simultaneously using multiple threads or processes, enabling parallel I/O operations while maintaining data consistency.
Solution Approach 2:
The patent introduces a new dimension of parallelism by implementing multi-threaded or multi-process architecture in the mirror database. Multiple worker threads can simultaneously read different portions of the transaction log and apply changes to different database pages in parallel, transforming the single-threaded sequential processing into concurrent parallel operations.
2Reliability
If the mirror database sequentially reads the transaction log to apply changes, then data integrity is ensured, but service availability decreases during high volumes of bulk loads due to increased takeover time
Solution Approach 1:
The patent implements preliminary actions by pre-processing and pre-synchronizing the mirror database during normal operation. The mirror database continuously catches up with the primary database using parallel processing, so that when a failover is needed, the synchronization is already near-complete, significantly reducing the actual takeover time while maintaining data integrity.
Solution Approach 2:
The patent changes the processing parameters by adjusting the degree of parallelism, buffer sizes, and I/O scheduling parameters in the mirror database. By optimizing these parameters, the system can process bulk loads more efficiently in parallel while ensuring data integrity through controlled commit protocols and checkpoint mechanisms.
3Reliability
If bulk load changes are written through the Write-Ahead-Log, then transaction safety is maintained, but performance decreases because all changes must go through sequential logging
Solution Approach 1:
The patent extracts the bulk load change data directly from the transaction log without requiring sequential processing. By identifying and extracting bulk load operations, the system can apply these changes in parallel to the mirror database while maintaining a reduced or separate log structure, thereby preserving transaction safety through selective logging while achieving parallel processing performance.
Data Source
AI summary
In a database cluster comprising a plurality of mirrored database segments, a mirror database pair comprising a primary database and a mirror database are maintained substantially consistent by writing changes resulting from transactions to page data in parallel to the primary database and to the mirror database. Changes are sent to the mirror database over a network using network communications. Transaction logs are also written to the primary and mirrored databases in parallel, the transaction logs being sent to the mirror database using network communications. Bulk load changes resulting from transactions are written in parallel over the network to the primary database and the mirror database without writing the changes to a transaction log. In the event of a failure, resynchronization of the mirror database can be performed quickly while avoiding a lengthy ongoing redo process on the mirror database.