A Database Caching Optimization Method Based on S3 Cloud Storage

Abstract

This invention discloses a database caching optimization method based on S3 cloud storage, comprising the following steps: S1, the data source receives pushdown filtering and column pruning requests from the DBMS via API, and the Cache Connector integrates into the unmodified DBMS through this data source API; S2, the caches store column-formatted data in the local SSD, and the Cache MS receives queries from the Cache Connector via API, which contain predicate pushdown; S3, the Cache Server receives the pushdown predicate string from the Cache Connector and then converts it back to the internal AST. The Cache Server uniformly converts the AST to Disjunctive Normal Form (DNF). The Cache Server cache granularity is Region, and all query requests are represented by disjunction (OR) of conjunction (AND); S4, during Region request processing, the Cache Server searches locally whether there is a superset in the cache that matches the requested Region. First, it scans the Region cache for a match. If the query does not match, the file download manager may retrieve the file from the file cache; if not, it pulls the file from remote storage.

Description

Technical Field

[0001] This invention relates to the field of IT application technology, and in particular to a database caching optimization method based on S3 cloud storage. Background Technology

[0002] Amazon Simple Storage Service (Amazon S3) is an object storage service with industry-leading scalability, data availability, security, and performance. Developed by Amazon Web Services (AWS), the platform was first launched on March 14, 2006. Amazon S3 Intelligent Tiling provides 99.99999999999% durability and 99.9% availability. Management features allow users to optimize, configure, and organize access to their data to meet specific compliance, business, or organizational needs.

[0003] Amazon S3's existing caching solutions include: page or block-based file caching, materialized views, semantic caching, intermediate result caching, and middleware database caching. These methods suffer from several drawbacks. Manually creating views requires experienced DBAs (database administrators) with a good understanding of hot and cold data in the actual business logic. Otherwise, the created views may not only fail to improve query efficiency but could also increase query latency and resource consumption due to cache misses. Maintainability is also poor. Semantic caching and intermediate result caching solutions require integration with the existing DBMS (Database Management System), and the cached data content must be tailored to the specific SQL queries of different DBMSs, leading to significant development complexity and high demands on maintenance personnel. Furthermore, cache utilization is low. For low-granularity data block caching and data page caching, a large amount of cache space is needed to ensure a high cache hit rate and thus high query efficiency. Summary of the Invention

[0004] To overcome the shortcomings of existing technologies, this invention provides a database caching optimization method based on S3 cloud storage, which decouples the cache from the database management system, improves the versatility of the cache, generates adaptive caching to improve system usability and significantly improve cache utilization, saves bandwidth of remote storage and reduces query latency.

[0005] To address the aforementioned technical problems, this invention provides the following technical solution: a database caching optimization method based on S3 cloud storage, comprising the following steps:

[0006] S1. Big data systems (such as Spark and Presto) provide a data source API to support various data sources and formats. The data source receives pushdown filtering and column pruning requests from the DBMS through this API. Therefore, the data source can reduce the amount of data returned to the DBMS by processing this additional information. A lightweight DBMS-specific data source connector (CacheConnector) is integrated into the unmodified DBMS through this data source API.

[0007] S2. Caches store columnar data (such as Parquet data) in local SSDs. The Cache Management System (Cache MS) receives queries from the Cache Connector via API. These queries include predicate pushdown. The Cache Server caches different subsets of the data using the predicate pushdown, which are referred to here as data regions.

[0008] S3. The Cache Server receives the pushdown predicate string from the Cache Connector and then converts it back to the internal AST (Abstract Syntax Tree). The Cache Server converts the AST into Disjunctive Normal Form (DNF). In the disjunctive normal form, all joins (AND) are pushed down into the expression tree. Joins (AND) and disjoints (OR) are no longer interleaved. Each conjunctive normal form (AND) can be regarded as a single set hyperrectangle. Then the data region can be regarded as the disjunctive normal form (OR) of the hyperrectangle. The granularity of the Cache Server cache is the region. All query requests are represented by the disjunction (OR) of conjunctive (AND).

[0009] S4. During Region request processing, the Cache Server searches locally to see if there is a superset of the requested Region that matches in the Cache. First, it scans the Region cache to see if there is a match. If the query does not find a match, the file download manager may retrieve the file from the file cache. If not, it will pull the file from remote storage.

[0010] Furthermore, in step S2, Cache MS first checks the matcher. If a cache hit occurs, it returns a set of file paths from local storage. If no hit occurs, it provides two options:

[0011] 1) The DBMS directly uses the Cache Connector to process data stored remotely, and the Cache MS then downloads the data to the caches through the Cache Connector;

[0012] 2) The Cache MS application predicate pushdown downloads data from the remote location, stores the results in caches, and returns the path to the connector;

[0013] The contents of the cache can be filled by DBMS or Cache MS, but not every requested region is cached; an LRU-2-based algorithm is needed to determine this.

[0014] Furthermore, the Cache Server, acting as the storage layer of the DBMS, operates outside the DBMS and transmits information through socket connections and shared spaces (SSD, ramdisk) in the file system. During a file request, the DBMS and the Cache Server exchange information about the file and the required regions, and the Cache Server will prioritize trying to satisfy the request with cached files.

[0015] Furthermore, the API uses tree-structured strings to represent pushdown predicates. Since predicates are typically stored as ASTs in a DBMS, ASTs are used to construct string representations. Each individual item uses a tree-like syntax (left, right) that supports binary operators, unary operators, and literals, where literals are the leaf nodes of the tree. Binary operations are either combinations of multiple predicates (AND, OR) or combinations of atomic predicates (such as gt, lt, eq). Atomic predicates use the same binary syntax, with the left side representing the identifier and the right side representing the comparison value.

[0016] Furthermore, through conjunction and disjunction expressions, there are four types of relations between Regions: linear totality, equivalence, intersection, and partial totality.

[0017] Furthermore, in step S4, the cache matching strategy is as follows:

[0018] A. Ideally, one Region would suffice;

[0019] B. If a region cannot satisfy the requirements, the Cache MS will try to satisfy a single hyperrectangle, but this may require additional deduplication operations. For example, if regions A and B can satisfy a query, but regions A and B overlap but are not exactly the same, then a deduplication operation is required.

[0020] C. When multiple Region combinations meet the requirements, a greedy algorithm is used for matching. For a candidate list consisting of multiple Regions, each time a Region that can cover the most hyperrectangles is selected and then deduplication is performed.

[0021] Compared with the prior art, the beneficial effects that this invention can achieve are:

[0022] This invention is easy to use, requiring only a connector to serialize predicates, and is highly compatible with various DBMSs. It boasts high cache utilization and cache hit rate. Based on remote storage designs such as Amazon S3 that support predicate pushdown, the Region cache only caches the extraction of the super rectangles divided by the predicates, resulting in high cache granularity and low resource consumption. When a cache hit occurs, a greedy algorithm that satisfies as many regions as possible is used to deduplicate overlapping regions, thereby improving the cache hit rate and preventing frequent cache updates from impacting system performance. Attached Figure Description

[0023] Figure 1 This is an overall framework diagram of the present invention;

[0024] Figure 2 This is a diagram of the Cache MS composition of the present invention;

[0025] Figure 3 This is a process diagram of converting DNF and extracting a single hyperrectangle in this invention;

[0026] Figure 4 This is an example diagram of the present invention, showing a query consisting of two hyperrectangles and matching two storage areas;

[0027] Figure 5 This is a schematic diagram of the matching process and algorithm of the present invention; Detailed Implementation

[0028] To make the technical means, creative features, and achieved objectives and effects of this invention easier to understand, the invention is further described below with reference to specific embodiments. However, the following embodiments are merely preferred embodiments of this invention and not all of them. Other embodiments obtained by those skilled in the art based on the embodiments described herein without creative effort are all within the protection scope of this invention. Unless otherwise specified, the experimental methods in the following embodiments are conventional methods, and the materials and reagents used in the following embodiments are commercially available unless otherwise specified.

[0029] Example 1

[0030] Please refer to Figure 1 , Figure 2 As shown, this invention provides a database caching optimization method based on S3 cloud storage, comprising the following steps:

[0031] S1. Big data systems (such as Spark and Presto) provide a data source API to support various data sources and formats. The data source receives pushdown filtering and column pruning requests from the DBMS through this API. Therefore, the data source can reduce the amount of data returned to the DBMS by processing this additional information. A lightweight DBMS-specific data source connector (CacheConnector) is integrated into the unmodified DBMS through this data source API.

[0032] S2 and Caches store columnar data (such as Parquet data) in local SSDs. The Cache Management System (Cache MS) receives queries from the Cache Connector via API. These queries include predicate pushdown. The Cache Server caches different subsets of data using predicate pushdown, which are called data regions. Through conjunction and disjunction expressions, there are four relationships between regions: full containment, equivalence, intersection, and partial containment.

[0033] S3. The Cache Server receives the pushdown predicate string from the Cache Connector and then converts it back to the internal AST (Abstract Syntax Tree). The Cache Server converts the AST into Disjunctive Normal Form (DNF). In the disjunctive normal form, all joins (AND) are pushed down into the expression tree. Joins (AND) and disjoints (OR) are no longer interleaved. Each conjunctive normal form (AND) can be regarded as a single set hyperrectangle. Then the data region can be regarded as the disjunctive normal form (OR) of the hyperrectangle. The granularity of the Cache Server cache is the region. All query requests are represented by the disjunction (OR) of conjunctive (AND).

[0034] S4. During Region request processing, the Cache Server searches locally to see if there is a superset of the requested Region that matches in the Cache. First, it scans the Region cache to see if there is a match. If the query does not find a match, the file download manager may retrieve the file from the file cache. If not, it will pull the file from remote storage.

[0035] In step S2, Cache MS first checks the matcher. If a cache hit occurs, it returns a set of file paths from local storage. If no hit occurs, it provides two options:

[0036] 1) The DBMS directly uses the Cache Connector to process data stored remotely, and the Cache MS then downloads the data to the caches through the Cache Connector;

[0037] 2) The Cache MS application predicate pushdown downloads data from the remote location, stores the results in caches, and returns the path to the connector;

[0038] The contents of the cache can be filled by DBMS or Cache MS, but not every requested region is cached; an LRU-2-based algorithm is needed to determine this.

[0039] The Cache Server acts as the storage layer of the DBMS, operating outside the DBMS. It transmits information through socket connections and shared spaces (SSD, ramdisk) in the file system. During a file request, the DBMS and the Cache Server exchange information about the file and the required regions. The Cache Server will prioritize trying to satisfy the request with cached files.

[0040] The API uses tree-structured strings to represent pushdown predicates. Since predicates are typically stored as ASTs in a DBMS, ASTs are used to construct string representations. Each individual item uses a tree-like syntax (left, right) and supports binary, unary, and literal operators, with literals being the leaf nodes of the tree. Binary operations are either combinations of multiple predicates (AND, OR) or combinations of atomic predicates (such as gt, lt, eq). Atomic predicates use the same binary syntax, with the left side representing the identifier and the right side representing the comparison value.

[0041] In step S4, the cache matching strategy is as follows:

[0042] A. Ideally, one Region would suffice;

[0043] B. If a region cannot satisfy the requirements, the Cache MS will try to satisfy a single hyperrectangle, but this may require additional deduplication operations. For example, if regions A and B can satisfy a query, but regions A and B overlap but are not exactly the same, then a deduplication operation is required.

[0044] C. When multiple Region combinations meet the requirements, a greedy algorithm is used for matching. For a candidate list consisting of multiple Regions, each time a Region that can cover the most hyperrectangles is selected and then deduplication is performed.

[0045] In a specific embodiment of the present invention, Amazon S3 (Amazon Simple Storage Service) is a remote object storage service provided by Amazon, which dominates the field of remote object storage; a database management system (DBMS) is a large software program used to manipulate and manage databases for creating, using, and maintaining them; a view refers to a view in a computer database, which is a virtual table whose content is defined by a query; like a real table, a view contains a series of named columns and rows of data, but the view does not exist in the database as a stored set of data values; the row and column data come from the tables referenced by the query that defines the view, and are dynamically generated when the view is referenced; a materialized view is similar to a view, reflecting the result of a query, but unlike a view which only stores the SQL definition, a materialized view stores the data itself, and is therefore a materialized view;

[0046] The Cache Server is divided into two parts: 1) a lightweight DBMS-specific data source connector, hereinafter referred to as the Cache Connector; and 2) a Cache Management System, hereinafter referred to as the Cache MS. Modern big data systems (such as Spark and Presto) provide a data source API to support various data sources and formats. The data source receives pushdown filtering and column pruning requests from the DBMS through this API. Therefore, the data source can reduce the amount of data returned to the DBMS by processing this additional information. The Cache Connector integrates into the unmodified DBMS through this data source API. The Cache Connector can be considered a data source for the DBMS and a client for the Cache MS. Caches store column-formatted data (such as Parquet data) in local SSD C. The Cache MS receives queries from the Cache Connector through the API. These queries include predicate pushdown. The Cache MS first checks the matcher. If a cache hit occurs, it returns a set of file paths from local storage. If no hit occurs, two options are provided:

[0047] 1) The DBMS directly uses the Cache Connector to process data stored remotely, and the Cache MS then downloads the data to the caches through the Cache Connector;

[0048] 2) The Cache MS application predicate pushdown downloads data from the remote location, stores the results in caches, and returns the path to the connector;

[0049] Therefore, the content in the cache can be filled by DBMS or Cache MS, but not every requested region is cached, and an LRU-2-based algorithm is needed to determine this.

[0050] As mentioned above, the Cache Server's system architecture makes it suitable for any cloud analytics system.

[0051] 1) Users can customize cache replacement based on workload;

[0052] 2) Remote storage can be replaced; it is not necessary to use Amazon S3. However, Amazon S3 or similar cloud storage supports predicate pushdown and can provide better performance.

[0053] 3) A custom Cache Connector can be implemented for each DBMS using this system;

[0054] By using predicate pushdown, the Cache Server caches different subsets of data, called data regions (hereinafter referred to as Regions). A Region can be viewed as a view table or another form of semantic caching. Compared to traditional file caching, semantic caching has two advantages: First, it typically returns a more compact view to the DBMS, reducing further data processing and saving I / O and CPU costs. Second, a Region can be much smaller than the original file, resulting in better space utilization and a higher cache hit rate. The process of converting a DBMS client request into a Region request includes the following steps:

[0055] (1) API

[0056] The Cache Server acts as the storage layer of the DBMS, running outside the DBMS. It transmits information through socket connections and shared spaces (SSD, ramdisk) in the file system. During a file request, the DBMS and the Cache Server exchange information about the file and the required regions. The Cache Server will prioritize trying to satisfy the request with cached files.

[0057] In this invention, the API uses tree-structured strings to represent pushdown predicates. Since predicates are usually stored as ASTs (Abstract Syntax Trees) in DBMS, we traverse the AST to construct the string representation. Each individual item uses a tree-structured syntax (left, right), which can support binary operators, unary operators, and literals. Liters are the leaf nodes of the tree. Binary operations are either combinations of multiple predicates (AND, OR) or combinations of atomic predicates (such as gt, lt, eq). Atomic predicates use the same binary syntax, with the left side being the identifier and the right side being the comparison value.

[0058] (2) Cache MS receive conversion conversion

[0059] The Cache Server receives the pushdown predicate string from the Cache Connector and then converts it back to the internal AST. Because the processing of arbitrarily nested logical expressions is quite complex, the Cache Server converts the AST into Disjunctive Normal Form (DNF). In Disjunctive Normal Form, all ANDs are pushed down into the expression tree, and ANDs and ORs are no longer intertwined.

[0060] Each conjunctive normal form (AND) can be viewed as a single set hyperrectangle, and the data region mentioned above can be viewed as the disjunctive normal form (OR) of the hyperrectangle. Figure 3 The process of converting DNF and extracting a single hyperrectangle is shown;

[0061] The granularity of the Cache Server cache is Region, and all query requests are represented by AND and OR. However, the connections of different regions can be combined to satisfy the incoming region request. Some previous semantic caching efforts only considered using non-overlapping hyperrectangles. Although non-overlapping hyperrectangles can help reduce the complexity of the decision-making process, too many hyperrectangles due to the small granularity are not friendly to additional costs such as caching strategies. Here, we still choose region as the smallest granularity of cache.

[0062] (3) Region matching

[0063] Using conjunction and disjunction expressions, there are four relationships between Regions: linear containment, equivalence, intersection, and partial containment. The fundamental expression for containment is each conjunction, which is a hyperrectangle. The algorithm for determining the relationship between two regions (rx, ry) will be shown below:

[0064] Represents all r y The hyperrectangle can be in r x Find the superset;

[0065] rx∩ry≠0 means there is at least one r y The hyperrectangle can be in r x Find the intersection of the hyperrectangles;

[0066] (Partial superset) indicates that there is at least one r y The hyperrectangle can be in r x Find the superset;

[0067]

[0068] like Figure 4 The image shows an instance where a query consisting of two hyperrectangles matches two storage regions.

[0069] (4) Request matching process and algorithm

[0070] like Figure 5 As shown, it first scans the Region cache to see if it matches. If the query does not match, the file download manager may retrieve the file from the file cache. If not, it will pull the file from remote storage.

[0071] Cache matching strategy:

[0072] A. Ideally, one Region would suffice;

[0073] B. If a region cannot satisfy the requirements, the Cache MS will try to satisfy a single hyperrectangle, but this may require additional deduplication operations. For example, if regions A and B can satisfy a query, but regions A and B overlap but are not exactly the same, then a deduplication operation is required.

[0074] C. When multiple Region combinations meet the requirements, a greedy algorithm is used for matching. For a candidate list consisting of multiple Regions, each time a Region that can cover the most hyperrectangles is selected and then deduplication is performed.

[0075] This invention offers ease of use. Solutions like semantic caching or intermediate result caching typically require integration with existing DBMSs, and the cached data content must be tailored to the specific SQL queries of different DBMSs, leading to significant development challenges. In contrast, this invention only requires a connector to serialize predicates, making it highly compatible with various DBMSs. Furthermore, it boasts high cache utilization and cache hit rates. Semantic caching either caches only the final result, resulting in low utilization and resource waste, or it divides the cache region into non-repeating blocks, incurring high computational costs and consuming substantial computational resources with each cache update. This invention, based on a Region cache design using remote storage such as Amazon S3 that supports predicate pushdown, caches only the excerpts of the predicate-divided hyperrectangles, resulting in high granularity and low resource consumption. When a cache hit occurs, a greedy algorithm that satisfies multiple regions as much as possible is used to deduplicate overlapping regions, thus improving the cache hit rate and preventing frequent cache updates from impacting system performance.

[0076] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.

Claims

1. A database caching optimization method based on S3 cloud storage, characterized in that: Includes the following steps: S1, Big Data systems including Spark and Presto provide a data source API to support various data sources and formats. The data source receives pushdown filtering and column pruning requests from the DBMS through this API. Therefore, the data source can reduce the amount of data returned to the DBMS by processing this additional information. A lightweight DBMS-specific data source connector, Cache Connector, is integrated into the unmodified DBMS through this data source API. S2 and Caches store column-formatted data, including Parquet data, in local SSDs. The Cache Management System CacheMS receives queries from the Cache Connector via API. These queries include predicate pushdown, which is used by the CacheServer to cache different subsets of the data, referred to here as data regions. S3. The Cache Server receives the pushdown predicate string from the Cache Connector and then converts it back to its internal AST (Abstract Syntax Tree). The Cache Server converts the AST into Disjunctive Normal Form (DNF). In DNF, all AND connections are pushed down into the expression tree, and AND connections and OR connections are no longer interleaved. Each AND connection can be seen as a single set hyperrectangle, and the data region can be seen as the OR connection of the hyperrectangle. The granularity of the Cache Server cache is the Region, and all query requests are represented by the OR connection of AND connections. S4. During Region request processing, the Cache Server searches locally to see if there is a superset of the requested Region that matches in the Cache. First, it scans the Region cache to see if there is a match. If the query does not find a match, the file download manager may retrieve the file from the file cache. If not, it pulls the file from remote storage.

2. The database caching optimization method based on S3 cloud storage according to claim 1, characterized in that: In step S2, Cache MS first checks the matcher. If a cache hit occurs, it returns a set of file paths from local storage. If no hit occurs, it provides two options: 1) The DBMS directly uses the Cache Connector to process data stored remotely, and the Cache MS then downloads the data to the caches through the Cache Connector; 2) The Cache MS application predicate pushdown downloads data from the remote location, stores the results in caches, and returns the path to the connector; The cached content can be filled by a DBMS or Cache MS, but not every requested region is cached; an LRU-2-based algorithm is needed to determine this.

3. The database caching optimization method based on S3 cloud storage according to claim 1, characterized in that: The Cache Server acts as the storage layer of the DBMS, operating outside the DBMS. It transmits information through socket connections and shared spaces such as SSDs and ramdisks in the file system. During a file request, the DBMS and the Cache Server exchange information about the file and the required regions. The Cache Server will prioritize trying to satisfy the request with cached files.

4. The database caching optimization method based on S3 cloud storage according to claim 1, characterized in that: The API uses tree-structured strings to represent pushdown predicates. Since predicates are typically stored as ASTs in a DBMS, ASTs are used to construct string representations. Each individual item uses a tree-like syntax of left and right, supporting binary operators, unary operators, and literals, where literals are the leaf nodes of the tree. Binary operations are either combinations of multiple predicates or combinations of atomic predicates, including gt, lt, and eq. Atomic predicates use the same binary syntax, with the left side representing the identifier and the right side representing the comparison value.

5. The database caching optimization method based on S3 cloud storage according to claim 1, characterized in that: There are four types of relations between regions using conjunction and disjunction expressions: linear containment, equivalence, intersection, and partial containment.

6. The database caching optimization method based on S3 cloud storage according to claim 1, characterized in that: In step S4, the cache matching strategy is as follows: A. In the best-case scenario, one region is sufficient to meet the requirements; B. If a region cannot satisfy the requirements, the Cache MS will try to satisfy a single hyperrectangle, but this may require additional deduplication operations. For example, if regions A and B can satisfy a query, but regions A and B overlap but are not exactly the same, then a deduplication operation is required. C. When multiple Region combinations meet the requirements, a greedy algorithm is used for matching. For a candidate list consisting of multiple Regions, each time a Region that can cover the most hyperrectangles is selected and then deduplication is performed.

Images