Database Query Offloading via Load-Aware Replica Routing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing high availability disaster recovery (HADR) systems in database systems require improvements in managing resource usage and query offloading to secondary databases for optimal performance and availability.
Innovation Solution
A method and system for dynamically offloading queries from a primary database to a secondary database based on load conditions, using metadata to determine resource availability and offload conditions, allowing transparent rerouting without requiring changes to client configurations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If queries are always executed on the primary database to ensure data consistency, then data reliability is maintained, but the primary database becomes overloaded and performance degrades
Solution Approach 1:
The patent introduces a query router as an intermediary component that sits between clients and database systems. This router intelligently directs read queries to secondary databases when available, while routing write queries and complex queries to the primary database. This mediator resolves the contradiction by distributing query loads without compromising data consistency, as the router ensures clients receive accurate data from replicated secondary databases.
Solution Approach 2:
The patent segments the database system into primary database nodes and secondary database nodes, each handling different types of queries. Read queries are segmented and routed to secondary databases, while write queries remain on the primary database. This segmentation allows the system to maintain data consistency on the primary database while improving overall performance by distributing read operations across multiple secondary nodes.
2Productivity
If queries are offloaded to secondary databases to improve performance, then query execution speed increases, but system complexity increases due to load management
Solution Approach 1:
The patent implements feedback mechanisms where the query router continuously monitors the status, load, and health of primary and secondary databases. Based on this real-time feedback, the router dynamically adjusts query routing decisions. This feedback loop simplifies load management by automating the decision-making process, eliminating the need for complex manual load balancing configurations while maintaining optimal performance.
Solution Approach 2:
The system employs self-service mechanisms where secondary databases automatically manage their own query execution and the query router automatically re-routes queries based on system conditions. This self-service approach reduces the complexity of centralized load management by distributing decision-making across the system components themselves.
3Use of energy by moving object
If secondary databases are used for query offloading, then resource utilization improves, but ensuring data consistency between primary and secondary databases becomes more challenging
Solution Approach 1:
The patent applies preliminary action by pre-replicating data from primary databases to secondary databases before queries are executed. This advance data preparation ensures that when queries are offloaded to secondary databases, the data is already synchronized and consistent with the primary database, eliminating data consistency issues that would arise from real-time synchronization challenges.
Solution Approach 2:
The system uses copying by creating and maintaining replicated copies of database data on secondary database nodes. These copies are kept synchronized with the primary database through replication mechanisms. This copying approach allows queries to be executed on secondary copies while maintaining data consistency, as the copies are continuously updated to reflect the current state of the primary database.
4Productivity
If dynamic load checking is performed before query offloading, then optimal query routing is achieved, but additional overhead is introduced
Solution Approach 1:
The patent performs preliminary load checking and status monitoring of database nodes before query routing decisions are made. By proactively gathering information about system state, the query router can make informed routing decisions without introducing significant overhead during query execution. This preliminary action ensures that queries are routed to the most appropriate nodes while minimizing the time penalty of load checking.
Data Source
AI summary
An approach includes collecting metadata indicative of a computer system comprising (i) a primary database, configured to connect to a secondary database, and (ii) the secondary database that replicates the primary database. The approach further includes receiving, at the primary database from a requestor, a query referencing the primary database. The approach further includes, responsive to receiving the query, performing an offload including: determining a first load on the primary database; responsive to the first load satisfying a first offload condition, determining, using the metadata, whether a second load on the secondary database satisfies a second offload condition; and responsive to the second load satisfying the second offload condition, offloading at least part of the query to the secondary database in order to cause the at least part of the query to be executed by the secondary database. The method further includes providing to the requestor results of the query.