Systems and methods for load balancing and connection multiplexing among database servers
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.
1. A system comprising:
a device comprising one or more processors, coupled to memory, and intermediary to a plurality of clients and a plurality of servers, the plurality of servers providing access to a database;
a policy for selecting a server among the plurality of servers, the policy comprising an expression to identify content in structured query language (SQL) queries;
wherein the device is configured to:
identify the policy for a SQL query received from a client of the plurality of clients to access the database;
select a server from the plurality of servers based on applying the expression of the policy to content of the SQL query;
modify the SQL query to replace a first cursor identifier of the SQL query with a second cursor identifier identified from a cursor map, the second cursor identifier comprising a server identifier for the selected server, the cursor map updated with the second cursor identifier associated with the first cursor identifier; and
forward the SQL query to the selected server.
2. The system of claim 1 , wherein the device is further configured to receive the SQL query via a first connection established between the device and the client.
3. The system of claim 1 , wherein the device is further configured to forward the SQL query via a second connection established between the device and the server.
4. The system of claim 1 , wherein the SQL query comprises a write request to the database and device is further configured apply the expression of the policy to identify that content of the SQL query has a command to write to the database.
5. The system of claim 4 , where the device is further configured to select the server based at least on the SQL query having the command to write to the database.
6. The system of claim 1 , wherein the SQL query comprises a read request to the database and device is further configured apply the expression of the policy to identify that content of the SQL query has a command to read from the database.
7. The system of claim 6 , where the device is further configured to select the server based at least on the SQL query having the command to read from the database.
8. The system of claim 1 , wherein the device is further configured to select the server based at least on a property of a connection between the device and one of the client or the server.
9. The system of claim 1 , wherein the policy comprises the expression to identify one of the following content of the SQL query: client user name, client database, and request query object.
10. The system of claim 1 , wherein the cursor map identifies a relationship between the first cursor identifier for the client and the second cursor identifier of the server.
11. A system comprising:
a device comprising one or more processors, coupled to memory, and intermediary to a plurality of clients and a plurality of servers, the plurality of servers providing access to a database, the device configured to:
receive a structured query language (SQL) query to access the database,
select, based on at least one of a policy or load balancing, a server from the plurality of servers over which to multiplex SQL queries;
store, in a cursor map, a first cursor identifier for the client identified from the SQL query;
identify via the cursor map a server cursor identifier for a selected server, the device maintaining in the cursor map one or more server cursor identifiers to reuse for the selected server;
modify the SQL query to use the server cursor identifier instead of the first cursor identifier; and
forward the SQL query to the selected server.
12. The system of claim 11 , wherein the device is further configured to receive the SQL query via a first connection established between the device and the client.
13. The system of claim 11 , wherein the device is further configured to forward the SQL query via a second connection established between the device and the server.
14. The system of claim 11 , wherein the device is further configured to select the server based at least on a property of a connection between the device and one of the client or the server.
15. The system of claim 11 , wherein policy comprises an expression to identify predetermined data from content of the SQL query.
16. The system of claim 15 , wherein the predetermined data comprises one or more of the following: client user name, client database and request query object.
17. The system of claim 15 , wherein the device is further configured to select the selected server from the plurality of servers based on applying the expression of the policy to content of the SQL query.
18. The system of claim 11 , wherein the server identifier corresponds to a context of the server.
19. The system of claim 11 , wherein the server identifier is configured to be generated by the server responsive to the SQL query corresponding to a new statement from the client.
20. The system of claim 11 , wherein the device is further configured to generate the server cursor identifier for the selected server.