Systems and methods for load balancing and connection multiplexing among database servers
The present application is related to a system and 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 method comprising:
(a) receiving, by a device intermediary to a plurality of clients and a plurality of servers, a structured query language (SQL) query to access a database provided by the plurality of servers from a client via a first connection established between the device and the client;
(b) identifying, by the device, for the SQL query a policy for selecting among the plurality of servers, the policy comprising an expression to identify predetermined data from content of the SQL query;
(c) selecting, by the device, a server from the plurality of servers based on applying the expression of the policy to content of the SQL query; and
(d) forwarding, by the device, the SQL query to the selected server via a second connection established between the device and the selected server, the SQL query modified by the device to replace a first cursor identifier of the SQL query with a second cursor identifier identified from a cursor map maintained by the device, the second cursor identifier comprising a server identifier for the selected server to use for the SQL query via the second connection to the selected server; and
(e) updating, by the device, a cursor map maintained by the device with the server identifier as the second cursor identifier.
2. The method of claim 1 , wherein (a) further comprises receiving, by the device, the SQL query comprising a write request to the database.
3. The method of claim 2 , wherein (c) further comprises applying, by the device, the expression of the policy to identify the SQL query has a command to write to the database.
4. The method of claim 3 , further comprising selecting the server based on the SQL query having the command to write to the database.
5. The method of claim 1 , wherein (a) further comprises receiving, by the device, the SQL query comprising a read request to the database.
6. The method of claim 5 , wherein (c) further comprises applying, by the device, the expression of the policy to identify the SQL query has a command to read the database.
7. The method of claim 6 , further comprising selecting the server based on the SQL query having the command to read the database.
8. The method of claim 1 , wherein (c) further comprises selecting, by the device, the server based on a property of one of the first connection or the second connection.
9. The method of claim 1 , wherein the policy comprises the expression to identify one of the following predetermined data for the SQL query: client user name, client database, and request query object.
10. The method of claim 1 , wherein (d) further comprises multiplexing, by the device, via the second connection the SQL query using the server cursor identifier.
11. The method of claim 1 , further comprising receiving, by the device, the server identifier generated by the server responsive to the SQL query corresponding to a new statement created by the client, the server identifier corresponding to a context created by the server.
12. A method comprising:
(a) receiving, by a device intermediary to a plurality of clients and a plurality of servers, a structured query language (SQL) query to access a database provided by the plurality of servers from a client via a first connection established between the device and the client, the device maintaining one or more second connections to at least one server selected, based on at least one of a policy or load balancing from the plurality of servers over which to multiplex SQL queries;
(b) storing, by the device in a cursor map, a first cursor identifier for the client identified from the SQL query;
(c) identifying, by the device, 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 via the one or more second connections to the selected server; and
(d) forwarding, by the device, the SQL query to the selected server, the device modifying the SQL query to use the server cursor identifier instead of the first cursor identifier.
13. The method of claim 12 , further comprising selecting, by the device, the selected server based on a property of one of the first connection or a property of the one or more second connections.
14. The method of claim 12 , further comprising identifying, by the device, for the SQL query a policy for selecting among the plurality of servers, the policy comprising an expression to identify predetermined data from content of the SQL query.
15. The method of claim 14 , wherein the policy comprises the expression to identify one of the following predetermined data for the SQL query: client user name, client database, and request query object.
16. The method of claim 14 , further comprising selecting, by the device, the selected server from the plurality of servers based on applying the expression of the policy to content of the SQL query.
17. The method of claim 14 , further comprising:
applying, by the device, the expression of the policy to identify the SQL query has a command to write to the database; and
selecting the server based on the SQL query having the command to write to the database.
18. The method of claim 14 , further comprising:
applying, by the device, the expression of the policy to identify the SQL query has a command to read the database; and
selecting the server based on the SQL query having the command to read the database.
19. The method of claim 12 , wherein (c) further comprises generating, by the device, the server cursor identifier for the selected server.
20. The method of claim 12 , wherein (c) further comprises retrieving, by the device, from the cursor map, the server cursor identifier using the first cursor identifier, the cursor map comprising identification of a relationship between one or more client cursors identifiers and the one or more server cursor identifiers to reuse.
21. The method of claim 12 , wherein (d) further comprises multiplexing, by the device, via the one or more second connections the SQL query using the second cursor identifier.
22. The method of claim 12 , further comprising selecting, by a load balancer of the device, the selected server based on at least one of a status or a load of each of the plurality of servers.