Parallel Database Replication Across Row and Column Stores
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems face challenges in supporting mixed workloads of online transaction processing (OLTP) and online analytical processing (OLAP) efficiently, particularly in achieving real-time reporting and scalable query processing, due to limitations in data replication across row store and column store formats and high CPU consumption during parallel log replay.
Innovation Solution
The Asynchronous Parallel Table Replication (ATR) architecture enables low-overhead transaction processing by using a lock-free parallel log replay scheme, record version identifier (RVID) for partial ordering, and adaptive query routing, along with multi-version concurrency control and eager replication mechanisms to reduce propagation delay and CPU consumption, allowing for real-time analytical queries and scalable OLAP performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is replicated from row store to column store for OLAP workloads, then analytical query processing scalability is improved, but propagation delay increases and real-time reporting capability deteriorates
Solution Approach 1:
The patent segments the database system into primary servers that handle OLTP workloads on row store tables and replica servers that handle OLAP workloads on column store tables. This segmentation allows independent optimization of each subsystem while maintaining data consistency through asynchronous replication, resolving the contradiction between OLTP performance and OLAP scalability.
Solution Approach 2:
The system performs preliminary data transformation and replication to column store format in advance, maintaining pre-computed summaries and aggregates. This preliminary action enables fast OLAP query execution on replicas without blocking OLTP transactions on the primary, reducing propagation delay while maintaining analytical scalability.
2Productivity
If parallel log replay is used for asynchronous table replication, then replication throughput is improved, but CPU consumption increases
Solution Approach 1:
The patent uses incremental snapshots and log replay mechanisms to copy data from primary to replica servers. By copying only changed data portions rather than full tables, and using efficient log replay algorithms, the system achieves high replication throughput while minimizing CPU consumption on both primary and replica servers.
3Reliability
If OLTP and OLAP workloads are isolated into separate database systems, then workload interference is reduced, but system complexity and hardware costs increase
Solution Approach 1:
The patent creates a universal database system that handles both OLTP and OLAP workloads through a unified architecture. The system uses common infrastructure components (network, storage, processing units) that serve dual purposes: primary servers handle OLTP while simultaneously enabling OLAP queries on replicas, reducing overall system complexity and hardware requirements compared to completely separate systems.
Data Source
AI summary
Disclosed herein are system, method, and computer readable device embodiments for parallel replication of databases across row-store and column-store table formats. An embodiment operates by maintaining a replication log and a storage-level recovery log formatted according to separate respective log formats, logging a record-level SQL execution result, and replicating at least one database table from a primary server to at least one replica server. The primary server and the at least one replica server may each be configured to store data according to one table format of a row-store table format and a column-store table format, such that the primary server's table format is different from the at least one replica server's table format.