Method and system for transparent database query caching

• US 10,417,243 B1
• Filed: 08/29/2013
• Issued: 09/17/2019
• Est. Priority Date: 08/10/2010
• Status: Active Grant

• Current Assignee: Ignite ScaleArc Solutions, Inc.
• Original Assignee: Ignite ScaleArc Solutions, Inc.
• Inventors: Singh, Varun, Sawant, Uday V., Goel, Prateek, Deshaveni, Naresh G.
• Primary Examiner(s): Yen, Syling
• Application Number:
  US14/014,135
• Time in Patent Office: 2,210 Days
• Field of Search: 707688-690, 707692-694, 707706, 707713, 707736, 707747, 707758, 707781, 707999002- 3, 707 8, 707 1, 709203, 709217, 709219, 709225-229, 709237, 709238, 709245, 714 5, 714 6, 718105, 370389, 370392, 370400, 370401, 370402, 370475
• 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, in dependence upon content of the first SQL query and database authentication credentials of a user making the query, whether the first SQL database query is one for which valid response data is already cached in the cache memory, wherein the authentication credentials include a user identifier, and the user identifier is distinct from the query;

return the response data from the cache memory if so;

and if not, then forward the first SQL database query through a network port toward one or more of the servers in the plurality of database servers;

and, when the first SQL database query has been forwarded toward 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 toward a first one of the set of client systems;

compare the first SQL database query, which is distinct from the user identifier, against criteria for cacheability defined by a database administrator; and

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

2. The apparatus of claim 1, wherein:

the cache memory includes 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

the step of determining whether the first SQL database query is one for which valid response data is already cached 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. The apparatus of claim 1, wherein:

first SQL database query has been forwarded toward a particular server, and

caching the response data in the cache memory in association with an indication of the first SQL database query comprises caching the response data in the cache memory in association with a hash key generated from content of the first SQL database query.

4. The apparatus of 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. The apparatus of claim 1, wherein the first SQL database query has been forwarded toward a particular server, the first SQL database query does not satisfy the criteria for cacheability, and the apparatus further arranged to:

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

6. The apparatus of claim 1, wherein comparing the first SQL database query against criteria for cacheability comprises evaluating a regular expression defined by the database administrator against the first SQL database query.

7. The apparatus of claim 1, embodied in a physical enclosure separate from the client systems and the database servers.

8. The apparatus of claim 1, wherein the network port through which the first SQL database query is received comprises a physical network port.

9. The apparatus of claim 1, embodied in a virtual machine.

10. The apparatus of claim 9, wherein the network port through which the first SQL database query is received comprises a virtual network port.

11. The apparatus of 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.

12. The apparatus of claim 1, 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 the second SQL database query is an SQL write query; and

in response to determining that the second SQL database query is an SQL write query,

forward the second SQL database query through a network port toward one or more of the servers in the plurality of database servers.

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, accompanied by authentication credentials of one or more users making the 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, wherein each of the authentication credentials includes a user identifier, and the user identifier is distinct from the query made by the user;

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

compare the first SQL database query, which is distinct from the user identifier, against criteria for cacheability defined by a database administrator; and

where the criteria are satisfied,

cache the response data in the cache memory in association with an indication of the first SQL database read query and database authentication credentials of a user making the query.

14. The apparatus of 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. The apparatus of 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 toward the second client system without caching the response data.

16. The apparatus of claim 13, wherein comparing the first SQL database query against criteria for cacheability comprises evaluating a regular expression defined by the database administrator against content of the first SQL database query.

17. The apparatus of 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.

18. The apparatus of claim 17, 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.

19. The apparatus of claim 13, further arranged to cache in the cache memory, in association with the response data, an indication of a user-programmed time-to-live applicable to that response data.

20. The apparatus of claim 19, wherein;

the criteria for cacheability include a plurality of cacheability rules, and

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.

21. 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, accompanied by authentication credentials of one or more users making the 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, wherein each of the authentication credentials includes a user identifier, and the user identifier is distinct from the query made by the user;

determining, in dependence upon content of the first SQL database query and database authentication credentials of a user making the query, whether the first SQL database query is one for which valid response data is already cached in a cache memory;

returning the response data from the cache memory if so;

and if not, then forwarding the first SQL database query through a network port toward one or more of the servers in the plurality of database servers;

and, when the first SQL database query has been forwarded toward a particular server,

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 toward a first one of the set of client systems;

comparing the first SQL database query, which is distinct from the user identifier, against criteria for cacheability defined by a database administrator; and

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

22. The method of claim 21, 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

the step of determining whether the first SQL database query is one for which valid response data is already cached 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.

23. The method of claim 21, when the first SQL database query has been forwarded toward a particular server, caching the response data in the cache memory in association with an indication of the first SQL database query comprises;

cacheing the response data in the cache memory in association with a hash key generated from content of the first SQL database query.

24. The method of claim 23, 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.

25. The method of claim 21, wherein:

the first SQL database query has been forwarded toward a particular server, and

the first SQL database query does not satisfy the criteria for cacheability, the method further comprising;

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

26. The method of claim 21, wherein the step of comparing comprises the step of evaluating a regular expression defined by the database administrator against the first SQL database query.

27. The method of claim 21, 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; and

determining that the second SQL database query is an SQL write query, and if so, then forwarding the second SQL database query through a network port toward one or more of the servers in the plurality of database servers.

28. A computer readable medium having stored thereon in a non-transitory manner, a plurality of software code portions defining logic for performing the method of claim 21.

29. A computer readable medium having stored thereon in a non-transitory manner, a plurality of software code portions defining logic for performing the method of claim 23.

30. A computer readable medium having stored thereon in a non-transitory manner, a plurality of software code portions defining logic for performing the method of claim 25.

31. A computer readable medium having stored thereon in a non-transitory manner, a plurality of software code portions defining logic for performing the method of claim 27.

32. 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, accompanied by authentication credentials of one or more users making the 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, wherein each of the authentication credentials includes a user identifier, and the user identifier is distinct from the query made by the user;

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

comparing the first SQL database query, which is distinct from the user identifier, against criteria for cacheability defined by a database administrator; and

where the criteria are satisfied,

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

33. The method of claim 32, 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 toward the second client system without caching the second response data.

34. The method of claim 32, 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.

35. The method of claim 32, further comprising the steps of:

maintaining the cache memory contents in RAM; and

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

36. The method of claim 35, 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.

37. The method of claim 32, wherein the step of cacheing 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.

38. The method of claim 37, wherein;

the criteria for cacheability include a plurality of cacheability rules, and

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.

39. A computer readable medium having stored thereon in a non-transitory manner, a plurality of software code portions defining logic for performing the method of claim 38.

40. 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, accompanied by authentication credentials of one or more users making the 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,

means 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, wherein each of the authentication credentials includes a user identifier, and the user identifier is distinct from the query made by the user;

means to determine, in dependence upon content of the first SQL database query and database authentication credentials of a user making the query, whether the first SQL database query is one for which valid response data is already cached in the cache memory;

means to return the response data from the cache memory if so, and if not then to forward the first SQL database query through a network port toward one or more of the servers in the plurality of database servers;

and, where the first SQL database query has been forwarded toward a particular server;

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

means to forward the response data through a network port toward a first one of the client systems;

means to compare the first SQL database query, which is distinct from the user identifier, against criteria for cacheability defined by a database administrator; and

means to, in dependence on the comparison, cache the response data in the cache memory in association with an indication of the first SQL database query.

41. 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, accompanied by authentication credentials of one or more users making the 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, wherein each of the authentication credentials includes a user identifier, and the user identifier is distinct from the query made by the user, the apparatus comprising:

a cache memory;

means 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;

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

means to compare the first SQL database query, which is distinct from the user identifier, against criteria for cacheability defined by a database administrator; and

means to, where the criteria are satisfied, cache the response data in the cache memory in association with an indication of the first SQL database read query and database authentication credentials of a user making the query.