Patent Application
20190325052
Embodiments of the invention relate to database systems and, more particularly, to a client-side cache in computer systems.
In computer systems, a cache refers to a fast-access storage mechanism (such as memory) that holds data so that future requests for that data may be served faster from cache. The data stored in the cache may be the result of an earlier computation, or the duplicate of data stored elsewhere. A data or data item of which a copy is stored in cache is referred to as a source data item. The copy of the source item in the cache is referred to as a cache copy.
Caches are particularly helpful when the original data is expensive to fetch or expensive to compute relative to retrieval from a cache. The process of managing a cache is referred to as cache management. Cache management includes retrieving copies of source data items and storing them in a cache, providing valid cache copies to clients that request copies of a source data item, and maintaining and optimizing the use of a cache. A cache management system may include modules which may be composed of specialized software dedicated to managing one or more caches and may be executed by clients of a cache or servers of the source data, or a combination thereof.
In the context of databases, database caching can substantially improve the efficiency and throughput of database operations/applications, e.g., while processing indexes, data dictionaries and frequently used subsets of data. Database caches greatly improve the scalability and performance of applications that access databases by caching frequently used data.
A client-side query cache is a cache that is located at the database client. There are numerous advantages provided by the client-side cache. For example, if the requested data is located at the cache on the client, the requested data may be retrieved from the cache and thus eliminate the cost and expense of sending the request to the server and receiving the response from the server to retrieve query results. Client machines may also be added horizontally to provide caching capabilities in client memory and reduce the expense of setting up additional servers in supporting caching query results. Furthermore, storage on the client side offers the benefit of not only having the queries closer to the client but also ensure that the client's most relevant query results are stored at that client.
However, storage of query results in a client-side cache may introduce problems pertaining to the correctness of the query results within that cache. Database systems often need to guarantee the validity of query results with respect to transactional consistency, and therefore even if the query results data is obtained from a client-side cache instead of a server, it is expected that cached data being retrieved should not violate the expected guarantees of validity and correctness. For any caches that exist on the server-side (as opposed to the client-side), the correctness of cached data is easily managed since the server can simultaneously execute transactional operations while invalidating data that is out-of-date within its own server-side cache. However, it is a significant challenge to maintain the correctness of data within client-side caches, given the large number of clients in modern database systems with each client having its own cache, and considering the diverse sets of cached data that exists within the caches of each respective client.
Therefore, there is a need for an improved method and mechanism to efficiently and effectively manage invalidations of cached data within database client-side caches.
Embodiments of the present invention provide a method, system, and a computer product for registering queries and various objects within a system that are tied to that query and tracking query registrations based on partition and sub partition ids, columns in select query, and bind variables. The data manipulation language commands (DMLs) will not invalidate all cached queries on tables, but only those queries with matching partition info, columns, and bind variables. As such, a fine grained invalidation based consistent client cache is described where cache invalidations are pruned by looking at partition information, column names, or bind values.
Further details of aspects, objects, and advantages of the invention are described below in the detailed description, drawings, and claims. Both the foregoing general description and the following detailed description are exemplary and explanatory. They are not intended to be limiting as to the scope of the invention.
The drawings illustrate the design and utility of embodiments of the present invention, in which similar elements are referred to by common reference numerals. In order to better appreciate the advantages and objects of embodiments of the invention, reference should be made to the accompanying drawings. However, the drawings depict only certain embodiments of the invention, and should not be taken as limiting the scope of the invention.
Embodiments of the present invention provide methods, systems, and a computer product for efficiently managing invalidations of cached data within database client-side caches.
Various embodiments are described hereinafter with reference to the figures. It should be noted that the figures are not necessarily drawn to scale. It should also be noted that the figures are only intended to facilitate the description of the embodiments, and are not intended as an exhaustive description of the invention or as a limitation on the scope of the invention. In addition, an illustrated embodiment need not have all the aspects or advantages shown. An aspect or an advantage described in conjunction with a particular embodiment is not necessarily limited to that embodiment and can be practiced in any other embodiments even if not so illustrated. Also, reference throughout this specification to “some embodiments” or “other embodiments” means that a particular feature, structure, material, or characteristic described in connection with the embodiments is included in at least one embodiment. Thus, the appearances of the phrase “in some embodiments” or “in other embodiments,” in various places throughout this specification are not necessarily referring to the same embodiment or embodiments.
As noted above, a significant challenge exists when attempting to maintain the correctness of data within client-side caches. One possible approach to address this problem is to perform table-based tracking of changes with respect to cached data in client-side caches. In this approach, as transactions are processed at the database server, specific queries may be identified which are reliant upon one or more tables that are changed by a given transaction. Any results-set data for those queries that are maintained at client-side caches can then be invalidated by identifying which of the queries identified as being reliant upon a changed dependency table is associated with cached results at a client.
The issue with this table-based tracking approach is that it operates at a relatively coarse level of invalidation. Consider the situation where an extremely large table was processed by an earlier query, and the results data for that query is now cached at a local client-side cache. Assume that a later transaction modified a very tiny portion of that extremely large table. Under the table-granularity invalidation approach described above, the entirety of that result set in the client-side cache would need to be invalidated, even though only a small portion of the underlying table that the query depends upon changed. In fact, it is even possible that the portion of the table that was changed was not even relied upon to produce the original results set, and hence the later transaction did not affect the content or validity of the cached results set.
Therefore, embodiments of the present invention provide an approach to implement fine-grained invalidation of client-side cached data that provides a much more accurate and efficient approach to identify the data in a client-side cache that needs to be invalidated. according to some embodiments, invalidation of client-side cached data is implemented by registering queries and various objects within a system that are tied to a given query and tracking query registrations based on partition and sub partition ids, columns in select query, and/or bind variables.
In
The Database Server 300 may access or consist of the database 306 to fulfill SQL query requests or DML commands from the Client 200. In one or more embodiments, the Database Server 300 could access or consist of a cluster of databases and within the cluster broadcast received transactions to the other database instances within the cluster. In response to SQL query requests, the Database Server 300 will return one or more result sets generated upon execution of the SQL query by the Database 306.
To identify query results that may need to be invalidated, the server 300 maintains a Registration Table 302 to track the queries for which results are cached at a client. Each entry in this table corresponds to a different query, and includes at least three columns: (a) a first column holding the identifier for a specific query; (b) a second column to track any underlying dependencies relied upon to generate the query results for the registered query, where at least some of the underlying dependencies are at a fine-grained level of granularity (e.g., at levels smaller than an entire table); and (c) a time/commit number of the last change to a dependent data/structure/object for that query.
As explained in more detail below, the dependencies tracked by the Registration Table 300 can be at a level of granularity smaller than the table level. For example, the Registration Table 300 may track specific partitions that are relied upon and/or associated by a given query. In addition, the Registration Table 300 may track one or more columns relied upon and/or associated with a query. As another example, some embodiments may track one or more bind variables associated with a query. It is noted that these examples of tracked fine-grained dependencies are merely illustrative, and other types of fine-grained dependency levels may be tracked as well according to various embodiments of the invention.
With regard to the time/commit number of the last change tracked in the Registration Table 300, it is noted that a record of the state of the database (e.g., a snapshot) can typically be created when a transaction is executed as of a given state of the database. For example, a System Commit Number (SCN) often refers to a unique number assigned at the time of commit of a transaction, where the number monotonically increases within the database system. For the purposes of illustration and not by way of limitation, the term “SCN” will be used throughout this document to refer to identifiers for such committed changes within a database system.
The database system also includes a Client Status Table 304 that tracks the last time a given client was notified of possible invalidations of query results data cached at that client. Each entry in this table corresponds to a specific client, and each row in the table includes at least three columns: (a) a first column holding the identifier for a given client; (b) a second column to keep track of query IDs related to the client; and (c) a third column that identifies the latest SCN check for the client.
At 207, the server 300 sends the query results back to the client 200. If the query is deemed “cacheworthy”, then the query is registered within the registration table 302. It is noted that not all query results are determined to have the correct criteria to be cached. For example, queries that are not likely to be repeated should not have its results cached. As another example, queries corresponding to results that are likely to be invalid very quickly should not be cached, e.g., where the queried tables undergo rapid/constant changes. For at least some of the entries within the registration table, the level of granularity for the registration is less than the granularity of a table (e.g., partition-based, column-based, bind variable based, or any other specific granularity smaller than a table).
If the query is deemed cacheworthy, then at 209, the query results sent by the database server are stored in a caching mechanism at the client (e.g., in a memory device located at the client). The client can thereafter continue to access those cached query results while they are still valid, instead of being required to repeatedly send the query to the server to be re-processed to re-generate the query results.
At a future point in time at 211, a request may be received by the server to update one or more objects within the database. For example, an update operation may be received by the database server from another client to update one or more rows within a table managed by the database server.
At 213, the server checks whether there are any registered queries that have been affected by the update operation (that was performed at 211). If so, then at 217, the server updates the affected entries within the registration table with the current SCN of the transaction that made the update.
At a later time at 222, the client may send a request/communication to the server. The general idea is that any request that is received at the server at 219 from the client will require a response that can be usefully utilized to “piggyback” additional invalidation information from the server to the client. Therefore, the request may pertain to any type of request for any topic or object of interest, whether or not the request is related to the data currently cached in the client-side cache—it does not matter since the response message is being used as a vehicle for notifying the client of invalidations without requiring a separate/dedicated roundtrip between the entities just for the invalidation message.
A check is made of the queries associated with the client in the client status table 304. For any such queries, a check is made within the registration table 302 as to whether any queries associated with the client has a SCN that is greater than the time of the last SCN check by the client. If so, then this indicates that since the last time the client communicated with the server, an intervening transaction has made a change to a dependent object for a query registered with the client. In this situation, at 221, when the server responds back to the client with the response to the client request, the response message will also include a piggybacked invalidation message for query results affected by the later update to a dependent object.
At 223, the client 200 receives the response and piggybacked invalidation message. Thereafter, at 225, the client will invalidate the cached results that are stored in the client-side cache identified in the invalidation message.
This portion of the document will now describe an approach to implement a consistent client-side cache for partition-based registrations of queries according to some embodiments of the invention.
Partitioning addresses key issues in supporting large database tables and indexes by decomposing them into smaller and more manageable pieces called partitions. SQL queries and DML, statements do not need to be modified to access partitioned tables. This allows partitioning to be transparent to an application. For partitioned table (or index) each partition is stored in its own data (or index) segment, with its own set of extents. A partitioned object typically has a partitioning key, which consists of one or more columns that determine the partition where each row is stored. A database may automatically perform insert, update, and delete operations to the appropriate partition using the partitioning key. For queries that have predicates on the partition key, the results can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique of partition pruning can improve performance by multiple orders of magnitude. Partitioning can use several different methods to distribute the data into partitions. HASH partitioning maps data to partitions by hashing the value of the partition key; RANGE partitioning associates a range or partition key values with each partition; and LIST partitioning associates a list of discrete partition key values with each partition. An object can be composite partitioned where it is partitioned by one key and data distribution method and then each partition is further subdivided into subpartitions using a second key and data distribution method.
However, if the determination is made that the query indeed corresponds to a subset of partitions, then the server prunes unused partitions at 305. At 307, the server executes the query against the remaining partitions. The server will obtain the result of the query at 309 and register the query in the registration table along with the depended-upon partition(s) at 311. During query registration, the system stores the subpartition IDs that the query is dependent on. When a DML operation occurs, the system is able to see the subpartition IDs mapped to those extent/segments where data impacted by DML resides in.
The set of partitions and subpartitions accessed by a query may be known when the query is compiled by the relational database management system (rdbms) and this is called static pruning. Alternatively, the set of partitions and subpartitions may not be known until the query is executed by the rdbms, and this is called “dynamic partition pruning.” For example, dynamic partition pruning happens when there is a query that contains a predicate which involve a subquery, where the system does not know the results of the subquery until the query is executed. In one embodiment, when a query uses dynamic pruning, the system tracks which partitions are accessed during query execution and uses those partition and subpartition IDs for query registration. In another embodiment, query registration stores the object IDs for the table as part of a coarse grain registration. If each partition or sub-partition has a unique object ID, the system can include these sub-partition object IDs also in the query registration.
If an end user drops or adds new partitions, in one embodiment, the system can visit all queries relying on that partitioned table and modify their dependent partition IDs. The system them sends the invalidations to all clients. In another embodiment, the system drops the registered queries forcing the client caches to come to the server to re-execute and re-register the queries.
As shown in
Next, the query 401 is executed against the remaining partition (T1-P1), as shown in
The server then registers query 401 corresponding to the partition in the registration table 414 and client status table 416 and sends the result of the query back to the client 200, as shown in
At 506, the server will then check the registration table to see if any registered queries are affected by the update. This action is performed to check whether there are any queries that have been registered which correspond to a partition that has been updated by the update operation. If there are no registered queries that are affected by the update then the process is finished at 508.
However, if there are any registered queries that are affected by the update, then the server will update the specific entries for those queries. In particular, at 508, the SCN corresponding to the update operation is included into the affected entries within the registration table.
Next, a determination is made whether there are any entries in the registration table affected by the update.
Then, at 706, the server checks the status table to identify any queries associated with the client. The server will also check the registration table whether, for any queries associated with the client, if incoming SCN is lower than the SCN recorded @ invalidation for the client's registered quer(ies). If the incoming SCN is not lower than the SCN recorded @ invalidation for the query then the server sends a response to the request to the client at 708.
However, if the incoming SCN is lower than the SCN recorded @ invalidation for the query then, this indicates that an update has been made to a dependent object for the query in question. Therefore, at 710, the server sends both: (1) response to request and (2) an invalidation message for the identified query. As such, at 712, the client knows to invalidate the cached result for that query.
As illustrated in
The server handles the request, as shown in
This portion of the document will now describe an approach to implement a consistent client-side cache for column-based registrations of queries according to some embodiments of the invention.
A determination is made whether the query is relied upon specific column(s) of the database table at 908. If the query does not rely upon specific column(s) of the database table, then the server 300 registers the query at the registration table 1014 without identifying any specific column(s) that are relied upon at 914. However, if a determination is made that the query indeed relies upon specific column(s) of the database table, then the server identifies the column(s) relied upon for query at 910 and registers the query along with information regarding the depended-upon column(s) at 912. During query registration, the system stores information pertaining to the specific column(s) of the table that the query is dependent on. As such, the next time a DML operation occurs, the system is able to see the specific column(s) mapped to those columns where data impacted by the DML resides in.
In
Next, a determination is made whether there are any entries in the registration table that were affected by the update.
As illustrated in
This portion of the document will now describe an approach to implement a consistent client-side cache for bind-value based registrations of queries according to some embodiments of the invention.
In
At a later point in time when a DML statement is sent to the server. In 1112, the server receives and executes an update for one or more database objects within the database. For example, one or more bind variables may be updated by the update operation. At 1114, the server checks the predicate/query tracking structure to determine whether any queries are affected. The server then determines if the update affected any registered queries at 1116. This action is performed to check whether there are any queries that have been registered which correspond to a bind variable that has been updated by the update operation. If there are no registered queries that are affected by the update then the process is finished at 1120. However, if there are any registered queries that are affected by the update, then the server identifies and invalidates query ID(s) affected by the update at 1118. In particular, the SCN corresponding to the update operation is included into the affected entries within the registration table.
As shown in
The server 300 then registers query 11201 corresponding to the bind-variable relied upon in registration table 1214 and client status table 1216, as shown in
When there are concurrent connections from the same client cache process to the database, it is possible that the invalidations re-invalidate the result set repeatedly. The system stores the snapshot SCN of cached result-set and, only if it is less than the invalidation SCN, does the system invalidate the result-set. Otherwise, the system ignores that invalidation.
Consider an example with: “SELECT*from foo; Cached with SCN=30.” If it is a multi-threaded or multi-session client, there can be three calls in parallel going to the server. Consider below the sequence of operations:
Call-1 returns with invalidation SCN 30;
Call-2 is processing on server;
Call-3 re-execute result-set and returns result-set with SCN=35;
Call-2 returns from server and tries to re-invalidate result-set with invalidation SCN 30.
If the system honors all invalidations from the server, then performance will be reduced by un-necessary invalidations. In this case, the system compares cached result-set SCN 35 and ignores invalidation with SCN 30. When client processes have hundreds of connections to a database, the gain by reducing unnecessary invalidation be vastly beneficial. In one embodiment, the above approaches can be combined. For example, a 2-level invalidation with fine-grain partition invalidation.
According to some embodiments of the invention, computer system 1500 performs specific operations by processor 1507 executing one or more sequences of one or more instructions contained in system memory 1508. Such instructions may be read into system memory 1508 from another computer readable/usable medium, such as static storage device 1509 or disk drive 1510. In alternative embodiments, hard-wired circuitry may be used in place of or in combination with software instructions to implement the invention. Thus, embodiments of the invention are not limited to any specific combination of hardware circuitry and/or software. In some embodiments, the term “logic” shall mean any combination of software or hardware that is used to implement all or part of the invention.
The term “computer readable medium” or “computer usable medium” as used herein refers to any medium that participates in providing instructions to processor 1507 for execution. Such a medium may take many forms, including but not limited to, non-volatile media and volatile media. Non-volatile media includes, for example, optical or magnetic disks, such as disk drive 1510. Volatile media includes dynamic memory, such as system memory 1508.
Common forms of computer readable media include, for example, floppy disk, flexible disk, hard disk, magnetic tape, any other magnetic medium, CD-ROM, any other optical medium, punch cards, paper tape, any other physical medium with patterns of holes, RAM, PROM, EPROM, FLASH-EPROM, any other memory chip or cartridge, or any other medium from which a computer can read.
In an embodiment of the invention, execution of the sequences of instructions to practice the invention is performed by a single computer system 1500. According to other embodiments of the invention, two or more computer systems 1500 coupled by communication link 1510 (e.g., LAN, PTSN, or wireless network) may perform the sequence of instructions required to practice the invention in coordination with one another.
Computer system 1500 may transmit and receive messages, data, and instructions, including program, i.e., application code, through communication link 1515 and communication interface 1514. Received program code may be executed by processor 1507 as it is received, and/or stored in disk drive 1510, or other non-volatile storage for later execution. A database 1532 in a storage medium 1531 may be used to store data accessible by the system 1500.
In the foregoing specification, the invention has been described with reference to specific embodiments thereof. It will, however, be evident that various modifications and changes may be made thereto without departing from the broader spirit and scope of the invention. For example, the above-described process flows are described with reference to a particular ordering of process actions. However, the ordering of many of the described process actions may be changed without affecting the scope or operation of the invention. The specification and drawings are, accordingly, to be regarded in an illustrative rather than restrictive sense.
