Method and system for transparent database connection pooling and query queuing

• US 8,484,242 B1
• Filed: 08/24/2010
• Issued: 07/09/2013
• Est. Priority Date: 08/24/2010
• Status: Active Grant

• Current Assignee: Ignite ScaleArc Solutions, Inc.
• Original Assignee: Scalarc Inc.
• Inventors: Singh, Varun, Sawanat, Uday V., Goel, Prateek, Deshaveni, Naresh G.
• Primary Examiner(s): Corrielus, Jean M
• Application Number:
  US12/862,525
• Time in Patent Office: 1,050 Days
• Field of Search: 707/715, 707/741, 707/758, 707/802, 707/770, 707/737, 707/769, 707/745, 707/719, 707/713, 709/217
• 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 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, a database query made by the set of client systems and directed to the particular IP address and port combination, the received database query including database query text;

determine, 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 query, and if so, then forward the query through the available connection toward the set of database servers, wherein the data objects are allocated among a plurality of connection pools including at least one data object allocated to a first connection pool and at least one data object allocated to a second connection pool, all of the connections in the first connection pool being to servers designated to handle read queries only, and all of the connections in the second connection pool being to servers designated to handle write queries, and at least one of the connections in the second connection pool being to a server also designated to handle read queries; and

select a preferred pool and a less-preferred pool of the connection pools in dependence upon whether the received query is a read query,

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

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

2. Apparatus according to claim 1, wherein the accelerator apparatus is further arranged, if no pre-existing open 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.

3. Apparatus according to claim 2, 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.

4. Apparatus according to claim 2, wherein the accelerator apparatus is further arranged, if no pre-existing open 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.

5. 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.

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

wherein each of the sub-determinations of whether a connection is available for the query includes determining whether a selected connection 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.

7. Apparatus according to claim 6, 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.

8. A query routing method, for use in a network having a set of at least one client system each running an application which makes 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, comprising the steps of a database accelerator:

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

determining, by reference to data objects describing a plurality of pre-existing open connections from the accelerator to the database servers, 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 data objects are allocated among a plurality of connection pools including at least one data object allocated to a first connection pool and at least one data object allocated to a second connection pool, all of the connections in the first connection pool being to servers designated to handle read queries only, and all of the connections in the second connection pool being to servers designated to handle write queries, and at least one of the connections in the second connection pool being to a server also designated to handle read queries; and

selecting a preferred pool and a less-preferred pool of the connection pools in dependence upon whether the received query is a read query,

wherein the preferred pool selected by the database accelerator if the received query is a read query is the first connection pool, and the less-preferred pool selected by the database accelerator if the received database query is a read query is the second connection pool, and

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

9. Database accelerator apparatus, for use in a network having a set of at least one client system each running an application which makes 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:

a network port through which the accelerator apparatus receives queries directed to the particular IP address and port combination;

a network port through which the accelerator apparatus forwards queries to one or more of the database servers; and

a memory containing metadata for each of a plurality of authenticated open 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,

wherein a first subset of at least one of the database servers is designated to handle read queries only and a second subset of at least one of the database servers is designated to handle write queries,

wherein the connections in the plurality of authenticated connections are divided into a plurality of connection pools,

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.

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 connections currently authenticated from the accelerator apparatus to the server.