Method and system for transparent database connection pooling and query queuing

• US 8,874,609 B1
• Filed: 05/24/2013
• Issued: 10/28/2014
• Est. Priority Date: 08/10/2010
• Status: Active Grant

• Current Assignee: Ignite ScaleArc Solutions, Inc.
• Original Assignee: Scalarc Inc.
• Inventors: Singh, Varun, Goel, Prateek, Deshaveni, Naresh G., Sawant, Uday V.
• Primary Examiner(s): Corrielus, Jean M
• Application Number:
  US13/902,629
• Time in Patent Office: 522 Days
• Field of Search: 707/769, 707/745, 707/713, 707/719, 707/770
• US Class Current: 707/770
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application

Abstract

Roughly described, a database accelerator is installed transparently in a network between database client and server systems. It maintains a pool of connections to the database server(s) for re-use as needed. Connection requests from client systems are received and completed by the accelerator, and queries received on such client-side connections are forwarded through pool connections toward the database servers. If no appropriate connections are available when needed for a particular query, the accelerator forms one by emulating a client system requesting a connection to the desired database server. If the maximum number of connections has already been reached, then the query is queued until a connection becomes available in the pool. By installing the accelerator transparently in the network, no modifications are required to the software in either the client systems or the database servers to add connection pooling functionality to an existing client/server database environment.

Claims

1. Database accelerator apparatus, for use in a network having a set of at least one client system each running an application which makes SQL database queries via a network to a particular IP address and port combination, and a set of at least one database server each having access to at least a portion of a database, the accelerator apparatus being arranged to:

receive, through a network port, an SQL database query made by the set of client systems and directed to the particular IP address and port combination, the SQL database query including query text;

determine, by reference to data objects describing a plurality of pre-existing connections from the accelerator apparatus to the database servers, whether one of the connections is available for the SQL query, and if so, then forward the SQL query through the available connection toward the set of database servers,

wherein the data objects are allocated among a plurality of connection pools,

wherein the apparatus is further arranged to select a preferred one and a less-preferred one of the connection pools in dependence upon predetermined characteristics of the received database query text,

and wherein the determination of whether one of the connections is available for the query consists of a determination of whether a connection in the preferred pool is available for the query, and if not then whether a connection in the less-preferred pool is available for the query.

2. Apparatus according to claim 1, wherein a first subset of at least one of the database servers is designated to handle SQL read queries only and a second subset of at least one of the database servers being designated to handle SQL write queries,

wherein all of the connections in a first one of the connection pools are to servers in the first subset of servers, and all of the connections in a second one of the connection pools are to servers in the second subset of servers,

wherein the predetermined characteristics of the received SQL database query text include whether the received query is an SQL read query,

and wherein the preferred pool selected by the apparatus if the received query is an SQL read query is the first connection pool, and the less-preferred pool selected by the apparatus if the received database query is an SQL read query is the second connection pool.

3. Apparatus according to claim 1, wherein the accelerator apparatus is further arranged, if no pre-existing connection is available for the query, to attempt to create a new connection to one of the database servers, and if successful, then to forward the query through the new connection.

4. Apparatus according to claim 3, wherein the creation of a new connection includes authenticating the connection to a database server on behalf of the client system that originated the query.

5. Apparatus according to claim 3, wherein the accelerator apparatus is further arranged, if no pre-existing connection is available for the query and creation of a new connection fails, to retain the received query in a queue and to forward the query to one of the database servers through a connection after one becomes available.

6. Apparatus according to claim 1, wherein the accelerator apparatus is further arranged, if no connection is available for the query, to retain the received query in a queue and to forward the query to one of the database servers through a connection after one becomes available.

7. Apparatus according to claim 1, wherein the data objects include indications of which if any of the pre-existing connections are available for forwarding of an SQL query,

wherein the determination of whether a connection is available for the query includes determining whether a selected one of the connections is marked available in the data objects,

and wherein the accelerator apparatus is further arranged, in conjunction with forwarding the query through a selected one of the connections, to mark the selected connection as busy in the data objects.

8. Apparatus according to claim 7, wherein the accelerator apparatus is further arranged, in response to receipt of a response to the query, to mark the selected connection as available in the data objects.

9. Apparatus according to claim 1, further comprising:

a memory containing the data objects, the data objects identifying metadata for each of the plurality of pre-existing connections from the accelerator apparatus to the database servers, the metadata for each of the connections in the plurality indicating;

one of the database servers to which the connection is established; and

whether the connection is or is not currently in use.

10. Apparatus according to claim 9, wherein the metadata for each of the connections in the plurality indicates the database server to which the connection is established, by an IP address and port number.

11. Apparatus according to claim 9, wherein the metadata for each of the connections in the plurality further indicates when the last query was executed on the connection.

12. Apparatus according to claim 9, wherein the memory further contains metadata indicating, for each server in the set of database servers, a maximum number of connections allowed to the server.

13. Apparatus according to claim 12, wherein the memory further contains metadata indicating, for each server in the set of database servers, a number of database connections currently authenticated from the accelerator apparatus to the server.

14. Apparatus according to claim 9, wherein a first subset of at least one of the database servers is designated to handle SQL queries of a first type only and a second subset of at least one of the database servers is designated to handle SQL queries of a second type,

wherein all of the connections in a first one of the connection pools are to servers in the first subset of servers, and all of the connections in a second one of the connection pools are to servers in the second subset of servers.

15. Apparatus according to claim 14, wherein the queries of a first type are SQL read queries, and the queries of the second type are SQL write queries.

16. An SQL query routing method, for use in a network having a set of at least one client system each running an application which makes SQL database queries via a network to a particular IP address and port combination, and a plurality of database servers each having access to at least a portion of a database, a first subset of at least one of the database servers being designated to handle SQL read queries only and a second subset of at least one of the database servers being designated to handle SQL write queries, and for use further with data objects describing a plurality of pre-existing open connections from the accelerator to the database servers, the data objects being allocated among a plurality of connection pools, comprising the steps of a database accelerator:

receiving, through a network port, an SQL database query made by the set of client systems and directed to the particular IP address and port combination, the received SQL database query including query text;

selecting a preferred one and a less-preferred one of the connection pools in dependence upon predetermined characteristics of the received database query text; and

determining, by reference to the data objects, whether one of the connections is available for the query, and if so, then forwarding the query through the available connection toward the set of database servers,

wherein the determination of whether one of the connections is available for the query consists of a determination of whether a connection in the preferred pool is available for the query, and if not then whether a connection in the less-preferred pool is available for the query.

17. The method of claim 16, wherein all of the connections in a first one of the connection pools are to servers in the first subset of servers, and all of the connections in a second one of the connection pools are to servers in the second subset of servers,

wherein the predetermined characteristics of the received SQL database query text include whether the received query is an SQL read query,

and wherein the preferred pool selected by the apparatus if the received query is an SQL read query is the first connection pool, and the less-preferred pool selected by the apparatus if the received database query is an SQL read query is the second connection pool.

18. The method of claim 16, further comprising, if no pre-existing connection is available for the query, attempting to create a new connection to one of the database servers, and if successful, then forwarding the query through the new connection.

19. The method of claim 18, wherein creating a new connection includes authenticating the connection to a database server on behalf of the client system that originated the query.

20. The method of claim 18, further comprising, if no pre-existing connection is available for the query and creation of a new connection fails, retaining the received query in a queue and forwarding the query to one of the database servers through a connection after one becomes available.

21. The method of claim 16, further comprising, if no connection is available for the query, retaining the received query in a queue and forwarding the query to one of the database servers through a connection after one becomes available.

22. A computer readable medium for use by database accelerator apparatus in a network having a set of at least one client system each running an application which makes SQL database queries via a network to a particular IP address and port combination, and a set of at least one database server each having access to at least a portion of a database, the computer readable medium having stored thereon in a non-transitory manner, a plurality of software code portions defining logic for:

receiving, through a network port, an SQL database query made by the set of client systems and directed to the particular IP address and port combination; and

determining, by reference to data objects describing a plurality of pre-existing open connections from the accelerator apparatus to the database servers, whether one of the connections is available for the SQL query, and if so, then forward the SQL query through the available connection toward the set of database servers,

wherein the data objects are allocated among a plurality of connection pools and the received SQL database query includes query text,

wherein the plurality of software code portions further define logic for selecting a preferred one and a less-preferred one of the connection pools in dependence upon predetermined characteristics of the received database query text,

and wherein determining whether one of the connections is available for the query consists of determining whether a connection in the preferred pool is available for the query, and if not then whether a connection in the less-preferred pool is available for the query.

23. The medium of claim 22, wherein a first subset of at least one of the database servers is designated to handle SQL read queries only and a second subset of at least one of the database servers being designated to handle SQL write queries,

wherein all of the connections in a first one of the connection pools are to servers in the first subset of servers, and all of the connections in a second one of the connection pools are to servers in the second subset of servers,

wherein the predetermined characteristics of the received SQL database query text include whether the received query is an SQL read query,

and wherein the logic defined by the plurality of software code portions is such that the preferred pool selected by the apparatus if the received query is an SQL read query is the first connection pool, and the less-preferred pool selected by the apparatus if the received database query is an SQL read query is the second connection pool.

24. The medium of claim 22, wherein the plurality of software code portions further define logic for, if no pre-existing connection is available for the query, attempting to create a new connection to one of the database servers, and if successful, then forwarding the query through the new connection.

25. The medium of claim 24, wherein creating a new connection includes authenticating the connection to a database server on behalf of the client system that originated the query.

26. The medium of claim 24, wherein the plurality of software code portions further define logic for, if no pre-existing connection is available for the query and creating a new connection fails, retaining the received query in a queue and forwarding the query to one of the database servers through a connection after one becomes available.

27. The medium of claim 22, wherein the plurality of software code portions further define logic for, if no connection is available for the query, retaining the received query in a queue and forwarding the query to one of the database servers through a connection after one becomes available.

28. The medium of claim 22, wherein the data objects include indications of which if any of the pre-existing connections are available for forwarding of an SQL query,

wherein determining whether a connection is available for the query includes determining whether a selected one of the connections is marked available in the data objects,

and wherein the plurality of software code portions further define logic for, in conjunction with forwarding the query through a selected one of the connections, marking the selected connection as busy in the data objects.

29. The medium of claim 28, wherein the plurality of software code portions further define logic for, in response to receipt of a response to the query, marking the selected connection as available in the data objects.

30. Database accelerator apparatus, for use in a network having a set of at least one client system each running an application which makes SQL database queries via a network to a particular IP address and port combination, and a set of at least one database server each having access to at least a portion of a database, the accelerator apparatus comprising:

means for receiving, through a network port, an SQL database query made by the set of client systems and directed to the particular IP address and port combination; and

means for determining, by reference to data objects describing a plurality of pre-existing open connections from the accelerator apparatus to the database servers, whether one of the connections is available for the SQL query, and if so, then forward the SQL query through the available connection toward the set of database servers,

wherein the data objects are allocated among a plurality of connection pools,

and wherein the received SQL database query includes query text,

further comprising means for selecting a preferred one and a less-preferred one of the connection pools in dependence upon predetermined characteristics of the received database query text,

wherein the means for determining whether one of the connections is available for the query consists of means for determining whether a connection in the preferred pool is available for the query, and if not then whether a connection in the less-preferred pool is available for the query.

31. Apparatus according to claim 30, further comprising means for, if no pre-existing connection is available for the query, attempting to create a new connection to one of the database servers, and if successful, then to forward the query through the new connection.

32. Apparatus according to claim 30, further comprising means for, if no connection is available for the query, retaining the received query in a queue and forwarding the query to one of the database servers through a connection after one becomes available.

33. Apparatus according to claim 30, wherein the data objects include indications of which if any of the pre-existing connections are available for forwarding of an SQL query,

wherein the means for determining whether a connection is available for the query includes means for determining whether a selected one of the connections is marked available in the data objects,

and wherein the accelerator apparatus further comprises means for, in conjunction with forwarding the query through a selected one of the connections, marking the selected connection as busy in the data objects.