Method and system for transparent database query caching

• US 8,543,554 B1
• Filed: 08/10/2010
• Issued: 09/24/2013
• Est. Priority Date: 08/10/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): Yen, Syling
• Application Number:
  US12/853,649
• Time in Patent Office: 1,141 Days
• Field of Search: 707/688, 707/690, 707/692, 707/693, 707/694, 707/706, 707/713, 707/736, 707/747, 707/758, 707/781, 707/999.002, 707/999.003, 707/999.008, 707/999.01, 709/203, 709/217, 709/219, 709225-229, 709/237, 709/238, 709/245, 370/389, 370/392, 370400-402, 370/475, 714/5, 714/6, 718/105
• US Class Current: 707/690
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: G06F 16/2471 Distributed queries

Abstract

Roughly described, a database accelerator is installed in a network having client systems which makes database queries to a particular IP address and port, and database servers which accept queries received at an IP address and port. The accelerator includes a cache, and is arranged to receive, through a network port, a database query made by a client system and directed to the particular IP address and port, return response data from the cache if available there, and if not, then forward the query through a network port to the database servers. Upon receipt of response data from the servers, the accelerator can retain it in its cache in addition to forwarding it back to the client. By installing the accelerator transparently in the network, no changes are required to either the client or server software in order to add query caching functionality to an existing client/server database arrangement.

Claims

1. Database performance enhancement 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 plurality of SQL database servers each having access to at least a portion of a database and running software which accepts SQL database queries received at a respective server IP address and port combination,

the apparatus comprising a cache memory, and the apparatus being arranged to:

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

determine whether the first SQL database query is an SQL write query or a read query, and if the first SQL database query is a read query then also determine whether the first SQL database query is one for which valid response data is already stored in the cache memory, and;

in response to determining that the first SQL database query is a write query and not a read query, forward the first SQL database query through a network port to one or more of the servers in the plurality of database servers,

and

in response to determining that the first SQL database query is a read query and not a write query and that

the first SQL database query is one for which valid response data is already stored in the cache memory,

return the response data from the cache memory,

and in response to determining that the first SQL database query is a read query and not a write query and that the first SQL database query is one for which valid response data is not stored in the cache memory, forward the first SQL database query through a network port to one or more of the servers in the plurality of database servers.

2. Apparatus according to claim 1, wherein the cache memory contains a plurality of entries each including a hash key dependent on an SQL query in correspondence with response data previously received for that query,

and wherein the step of determining whether the first SQL database query is one for which valid response data is already stored in the cache memory, comprises the steps of:

generating a first hash key from content of the first SQL database query, and further in dependence upon at least one member of the group consisting of an SQL database name to which the first SQL query is addressed, an SQL database table name, and credentials of a user requesting the query; and

comparing the first hash key to one or more of the hash keys in the cache memory.

3. Apparatus according to claim 1, further arranged to, where the first SQL database query has been forwarded to a particular server:

receive, through a network port, response data from the particular server to the first SQL database query;

store the response data in the cache memory in association with a hash key generated from content of the first SQL database query; and

forward the response data through a network port to the first client system.

4. Apparatus according to claim 3, further arranged to:

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

determine that content of the second SQL database query matches content of the first SQL database query; and

return a response from the cache memory.

5. Apparatus according to claim 1, further arranged to, where the first SQL database query has been forwarded to a particular server:

receive, through a network port, response data from the particular server to the first SQL database query; and

forward the response data through a network port to the first client system without storing the response data in the cache memory.

6. Apparatus according to claim 1, further arranged to, where the first SQL database query has been forwarded to a particular server:

receive, through a network port, response data from the particular server to the first SQL database query;

forward the response data through a network port to the first client system;

compare the first SQL database query against predetermined criteria for cacheability, and

in dependence upon the comparison, store the response data in the cache memory in association with an indication of the first SQL database query.

7. Apparatus according to claim 6, wherein comparing the first SQL database query against predetermined criteria for cacheability comprises evaluating a user-defined regular expression against the first SQL database query.

8. Apparatus according to claim 1, embodied in a physical enclosure separate from the client systems and the database servers.

9. Apparatus according to claim 1, wherein the network port through which the first SQL database query is received comprises a physical network port.

10. Apparatus according to claim 1, embodied in a virtual machine.

11. Apparatus according to claim 10, wherein the network port through which the first SQL database query is received comprises a virtual network port.

12. Apparatus according to claim 1, wherein a first one of the plurality of database servers comprises:

a first network port at which the first server receives all SQL database queries directed to the first server; and

a plurality of subsystems each having a respective different IP address, and throughout which the first server distributes workload for executing database queries received by the first server.

13. Database performance enhancement 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 plurality of SQL database servers each having access to at least a portion of a database and running software which accepts SQL database queries received at a respective server IP address and port combination,

the apparatus comprising a cache memory, and the apparatus being arranged to:

receive, through a network port, response data from one of the database servers to a first SQL database read query made by a first one of the client systems;

forward the response through a network port to the first client system;

compare the first SQL database query against predetermined criteria for cacheability; and

in dependence upon the comparison,

store the response data in the cache memory in association with an indication of the first SQL database read query.

14. Apparatus according to claim 13, further arranged to:

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

determine that the second SQL database read query matches the first SQL database read query; and

return the response data from the cache memory.

15. Apparatus according to claim 13, further arranged to:

receive, through a network port, response data from one of the database servers to a second SQL database query made by a second one of the client systems; and

forward the response data through a network port to the second client system without caching the response data.

16. Apparatus according to claim 13, wherein the predetermined criteria for cacheability is programmable by a user.

17. Apparatus according to claim 16, wherein comparing the first SQL database query against predetermined criteria for cacheability comprises evaluating a user-defined regular expression against content of the first SQL database query.

18. Apparatus according to claim 13, wherein the cache memory contents are maintained in RAM,

and wherein the apparatus is further arranged to maintain a backup of the cache memory contents in persistent storage.

19. Apparatus according to claim 18, further arranged to, upon a re-boot of the apparatus, re-populate at least part of the cache memory RAM in dependence upon the contents then in persistent storage.

32. Apparatus according to claim 13, further arranged to store in the cache memory, in association with the response data, an indication of a user-programmed time-to-live applicable to that response data.

33. Apparatus according to claim 32, wherein the predetermined criteria for cacheability include a plurality of cacheability rules,

and wherein each of the cacheability rules has associated therewith an indication of a time-to-live to be associated with response data cached in accordance with the respective cacheability rule.

20. A method for database performance enhancement, 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 SQL database servers each having access to at least a portion of a database and running software which accepts SQL database queries received at a respective server IP address and port combination, comprising the steps of:

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

determining whether the first SQL database query is an SQL write query or an SQL read Query, and if the first SQL database query is an SQL read query then also determining whether the first SQL database query is one for which valid response data is already stored in the cache memory, and;

if the first SQL database query is a write query and not a read query, then in response to determining that the first SQL database query is a write query and not a read query, forwarding the first SQL database query through a networks ort to one or more of the servers in the plurality of database servers,

and if the first SQL database query is a read query and not a write query and is one for which valid response data is already stored in the cache memory, then in response to determining that the first SQL database query is a read query and not a write query and that the first SQL database query is one for which valid response data is already stored in the cache memory, returning the response data from the cache memory,

and if the first SQL database query is a read query and not a write query and is one for which valid response data is not stored in the cache memory, then in response to determining that the first SQL database query is a read query and not a write query and that the first SQL database query is one for which valid response data is not stored in the cache memory,

forwarding the first SQL database query through a network port to one or more of the servers in the plurality of database servers.

21. A method according to claim 20, wherein the cache memory contains a plurality of entries each including a hash key dependent on an SQL query in correspondence with response data previously received for that query,

and wherein the step of determining whether the first SQL database query is one for which valid response data is already stored in the cache memory, comprises the steps of:

generating a first hash key from content of the first SQL database query, and further in dependence upon at least one member of the group consisting of an SQL database name to which the first SQL query is addressed, an SQL database table name, and credentials of a user requesting the query; and

comparing the first hash key to one or more of the hash keys in the cache memory.

22. A method according to claim 20, further comprising the steps of, where the first SQL database query has been forwarded to a particular server:

receiving, through a network port, response data from the particular server to the first SQL database query;

storing the response data in the cache memory in association with a hash key generated from content of the first SQL database query; and

forwarding the response data through a network port to the first client system.

23. A method according to claim 22, further comprising the steps of:

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

determining that content of the second SQL database query matches content of the first SQL database query; and

returning a response from the cache memory.

24. A method according to claim 20, wherein the first SQL database query has been forwarded to a particular server, further comprising the steps of:

receiving, through a network port, response data from the particular server to the first SQL database query; and

forwarding the response data through a network port to the first client system without storing the response data in the cache memory.

25. A method according to claim 20, wherein the first SQL database query has been forwarded to a particular server, further comprising the steps of:

receiving, through a network port, response data from the particular server to the first SQL database query;

forwarding the response data through a network port to the first client system;

comparing the first SQL database query against predetermined criteria for cacheability; and

in dependence upon the comparison, storing the response data in the cache memory in association with an indication of the first SQL database query.

26. A method according to claim 25, wherein the step of comparing comprises the step of evaluating a user-defined regular expression against the first SQL database query.

27. A method for database performance enhancement, 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 SQL database servers each having access to at least a portion of a database and running software which accepts SQL database queries received at a respective server IP address and port combination, comprising the steps of:

receiving, through a network port, response data from one of the database servers to a first SQL database query made by a first one of the client systems;

forwarding the response through a network port to the first client system;

comparing the first SQL database query against predetermined criteria for cacheability; and

in dependence upon the comparison,

storing the response data in a cache memory in association with an indication of the first SQL database query.

28. A method according to claim 27, further comprising the steps of:

receiving, through a network port, second response data from one of the database servers to a second SQL database query made by a second one of the client systems; and

forwarding the second response data through a network port to the second client system without caching the second response data.

29. A method according to claim 27, further comprising the steps of:

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

determining that the second SQL database query matches the first SQL database query;

and in response to the step of determining, returning response data from the cache memory.

30. A method according to claim 27, further comprising the steps of:

maintaining the cache memory contents in RAM; and

maintaining a backup of the cache memory contents in persistent storage.

31. A method according to claim 30, further comprising the step of, in response to a re-boot, re-populating at least part of the cache memory RAM in dependence upon the contents then in persistent storage.

34. A method according to claim 27, wherein the step of storing the response data in a cache memory includes storing the response data in a cache memory in further association with an indication of a user-programmed time-to-live applicable to that response data.

35. A method according to claim 34, wherein the predetermined criteria for cacheability include a plurality of cacheability rules,

and wherein each of the cacheability rules has associated therewith an indication of a time-to-live to be associated with response data cached in accordance with the respective cacheability rule.