Intermediary Device for SQL Query Load Balancing and Connection Multiplexing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database servers face increased load and resource consumption due to multiple queries from clients, leading to decreased productivity, as each query often requires opening new connections, especially in environments with a high volume of queries.
Innovation Solution
A device intermediates between clients and database servers, load balances SQL queries by selecting the appropriate server based on query content and maintains connections for multiplexing, using policies that identify predetermined data such as client user name, database, and request query object to optimize resource allocation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If database servers receive multiple queries from clients, then query processing capability is improved, but server load increases and productivity decreases
Solution Approach 1:
The system segments incoming SQL queries into different categories (read queries, write queries, authenticated queries, unauthenticated queries) and routes them to different server pools. This segmentation distributes the query load across multiple servers rather than concentrating all queries on a single server, thereby improving overall query processing capability while managing server load effectively.
Solution Approach 2:
The system introduces an intermediary component (the device with processing logic) that sits between clients and database servers. This intermediary receives queries from clients, processes them according to defined policies, and routes them to appropriate servers. The intermediary absorbs and manages the complexity of query distribution, allowing servers to focus on executing queries rather than managing connection multiplexing, thus improving productivity while controlling server load.
2Ease of operation
If database servers open new connections for each query, then query service capability is improved, but resource consumption increases
Solution Approach 1:
The system establishes and maintains a pool of persistent database connections in advance, before queries arrive. These pre-established connections are kept ready and can be multiplexed to handle multiple queries. By performing the connection establishment action beforehand, the system eliminates the need to open new connections for each query, thereby maintaining query service capability while significantly reducing resource consumption associated with repeated connection operations.
Solution Approach 2:
The system creates universal connections that can serve multiple purposes and multiple queries. A single persistent connection can be multiplexed to handle various types of queries (read, write, authenticated, unauthenticated) by routing different queries through the same connection pool. This multi-functionality of connections reduces the total number of connections needed, improving ease of query service while reducing resource consumption.
Data Source
AI summary
The present invention is related to a method for load balancing and connection multiplexing structured query language (SQL) queries among a plurality of database servers. A device intermediary to a plurality of clients and a plurality of database servers receives an SQL query to access a database provided by the plurality of database servers from a client via a first connection established between the device and the client. The device identifies for the SQL query a policy for selecting among the plurality of servers. The policy includes an expression to identify predetermined data from content of the SQL query. The device may select a server from the plurality of servers based on applying the expression of the policy to content of the SQL query and forward the SQL query to the selected server via a second connection established between the device and the selected server.