Database Workload Shifting via Hint-Based Routing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face challenges in maintaining high availability and load balancing during high workloads, leading to queued, buffered, or rejected requests due to insufficient system resources, and existing solutions often require additional processing systems, increasing costs and complexity.
Innovation Solution
Implementing a secondary database system as a hot-standby that executes queries from a primary database system, using routing hints to shift workload and prohibiting write transactions, while replicating transaction logs to maintain data consistency and support load balancing without disrupting the hot-standby functionality.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a secondary database system is implemented as hot-standby to maintain high availability, then system reliability is improved, but device complexity increases due to additional processing systems
Solution Approach 1:
The secondary database system is designed to serve dual purposes: maintaining hot-standby readiness for failover while simultaneously handling read workload. This multi-functionality allows the same infrastructure to provide both reliability through backup and load balancing through query execution, reducing the need for separate dedicated backup systems.
Solution Approach 2:
The patent combines the backup functionality and load balancing functionality into a single integrated system architecture. The secondary database serves both as a standby replica for failover and as an active node for handling read queries, merging what could be separate systems into one unified infrastructure.
2Productivity
If workload is shifted to the secondary database system to balance load, then productivity is improved, but data consistency may be compromised due to replication delay
Solution Approach 1:
The system applies partial action by routing only read queries to the secondary database while keeping write operations exclusively on the primary database. This selective workload distribution allows the secondary system to handle some traffic without requiring full data synchronization, accepting partial staleness for read operations in exchange for improved throughput.
Solution Approach 2:
The system changes the consistency parameter by allowing read queries to accept eventually consistent data from the secondary database. Instead of requiring strong consistency for all operations, the system permits reads to tolerate replication delay, transforming the consistency requirement from a hard constraint to a configurable parameter.
3Ease of operation
If routing hints are used to direct queries to the secondary system, then ease of operation is improved, but device complexity increases due to additional routing logic
Solution Approach 1:
The routing decision-making process is embedded within the database system itself through hint parsing and automatic query routing logic. Instead of requiring external load balancers or complex infrastructure, the database system autonomously evaluates routing hints in queries and directs them to appropriate nodes, making the system self-managing.
4Manufacturing precision
If write transactions are prohibited on the secondary system to maintain data integrity, then manufacturing precision is improved, but productivity decreases due to additional write operations on primary system
Solution Approach 1:
The system segments transaction types by routing write operations exclusively to the primary database while allowing read operations to be distributed to secondary databases. This segmentation isolates write operations to maintain data integrity on the primary system while enabling parallel read processing on secondary systems, improving overall system throughput.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A computer system is configured to provide a database system. The computer system comprises one or more processors, a primary database system implemented by the one or more processors, and a secondary database system implemented by the one or more processors. The secondary database system is configured as a hot-standby system for the primary database system. The secondary database system is capable of providing at least a minimum amount of essential functionality of the primary database system during a disruption to the primary database system. The primary database system is configured by programming instructions, executable on the computer system, to cause the one or more processors to determine from a query request from a client application directed to the primary database system that workload from a query may be shifted to the secondary database system and instruct the client application to direct the secondary database system to execute the query. Related apparatus, systems, techniques and articles are also described.