An external storage based analytical database system oriented to hybrid main memory optimization

Abstract

The application discloses a hybrid main memory optimization-oriented external storage-based analytical database system, which adopts a hybrid main memory storage structure of a persistent memory NVM and a volatile memory DRAM and comprises a Buffer unit configured for data caching, wherein the Buffer unit further comprises a first cache module configured for caching data pages read by scan operation and a second cache module configured for caching data pages read by point query operation; and a Heap unit configured for storing intermediate data structures, wherein the Heap unit further comprises a first sub-storage module configured as NVM for placing intermediate data structures and a second sub-storage module configured as DRAM for placing intermediate data structures; and the first sub-storage module and the second sub-storage module store the intermediate data structures according to a preset placement strategy. The application optimizes the external storage-based analytical database system by using the NVM in a server machine with a DRAM-NVM hybrid main memory and an external storage, thereby improving the system performance.

Description

TECHNICAL FIELD

[0001] The present application relates to a database system, in particular to an external storage-based analytical database system using NVM to extend the DRAM main memory. BACKGROUND

[0002] With the development of business intelligence and other needs, analytical database systems are widely used in life and production. Such database systems usually need to analyze a large amount of data, so typical analytical database systems are based on external storage, that is, data is stored in external storage (for example, solid state drive SSD or hard disk HDD), a buffer pool is established in memory, and data to be processed is read into the memory buffer pool to complete data processing operations. Typical analytical operations include scanning, filtering, joining, clustering, statistics, sorting, etc., which require a large amount of memory. NVM, as a new type of memory hardware with large capacity, persistence, support for byte access, and similar performance to DRAM, can be used to extend the main memory capacity to obtain better analytical operation performance.

[0003] The prior art discloses how to optimize transactional database systems and memory-based analytical database systems using NVM. Optimizing transactional database systems using NVM mainly integrates NVM into the execution engine of the memory transactional database system, thereby reducing log overhead and speeding up crash recovery. Or integrate NVM into the cache of the external storage transactional database system, thereby expanding the cache size to reduce I / O. However, transactional database systems mainly support transaction processing type loads, with characteristics of high concurrency, high throughput, single transaction accessing a small amount of data, and high write ratio. The load of analytical database systems is mainly complex query processing operations, with fewer concurrent queries, single query accessing a large amount of data, and mainly read-only access. Therefore, analytical database systems and transactional database systems have completely different load characteristics and need to be optimized using different techniques. Optimizing memory-based analytical database systems using NVM: various data structure DRAM-NVM placement problems in memory data table columnar storage are disclosed, but data in these studies is in the memory data structure and not read from external storage, which has very different characteristics from external storage-based analytical database systems. Or disclose the DRAM-NVM placement problem of input and output data of memory hash join operation and sorting operation, but these studies do not consider external storage-based query processing algorithms. In addition, these studies do not distinguish the characteristics of the various data structures used by the query processing operators.

[0004] For external storage-based analytical database systems, there are obvious differences in load characteristics, data structures, and operation algorithms from the prior art, so there is an urgent need for new methods and techniques to fully utilize NVM main memory to improve system performance. SUMMARY

[0005] In order to solve the above problems, the application provides a hybrid main memory optimization based on external storage analysis data system, which uses NVM to optimize the data cache and execution engine of the external storage based analysis database system in a server machine with DRAM-NVM hybrid main memory and external storage, thereby improving the performance of the system.

[0006] In order to achieve the above purpose, the application provides a hybrid main memory optimization based on external storage analysis database system, which uses a hybrid main memory storage structure of persistent memory NVM and volatile memory DRAM, and contains:

[0007] The Buffer unit is configured for data cache and contains:

[0008] The first cache module is configured for caching data pages read by scan operation;

[0009] The second cache module is configured for caching data pages read by point query operation;

[0010] The Heap unit is configured for storing intermediate data structures and contains:

[0011] The first sub-storage module is configured as NVM for placing intermediate data structures;

[0012] The second sub-storage module is configured as DRAM for placing intermediate data structures;

[0013] The first sub-storage module and the second sub-storage module store intermediate data structures according to a preset placement strategy.

[0014] Preferably, the placement strategy includes an on-demand hybrid placement strategy, which contains: preferentially placing the intermediate data structures in the second sub-storage module, and placing the intermediate data structures in the first sub-storage module when the remaining space of the second sub-storage module is smaller than the space required by the placed intermediate data structures.

[0015] Preferably, the placement strategy further comprises: a hybrid placement strategy of intermediate data structure type awareness, comprising: determining query characteristics and characteristics of intermediate data structure types, wherein the query characteristics are a space occupation graph of the query, and the characteristics of the intermediate data structure types are a unit influence rate of each type of intermediate data structure placed in the NVM on the query performance; calculating a peak point of memory usage in the space occupation graph; if the memory usage of the peak point exceeds the available space size of the DRAM, determining the space occupied by each type of intermediate data structure in the memory usage of the peak point; taking the unit influence rate from small to large as the NVM placement rule, and placing the intermediate data structure in the first sub-storage module, and each NVM placement rule indicates an intermediate data structure type and a partial occupancy ratio placed in the NVM.

[0016] Preferably, the hybrid placement strategy of intermediate data structure type awareness further comprises: if the memory usage of the peak point does not exceed the available space size of the DRAM, placing the intermediate data structure in the second sub-storage module.

[0017] Preferably, the first sub-storage module and the second sub-storage module store intermediate data structures according to a preset placement strategy, comprising: for a current query, in a case where the query characteristics and the characteristics of the intermediate data structure types are known, using the hybrid placement strategy of intermediate data structure type awareness to store the intermediate data structure in the first sub-storage module and the second sub-storage module; and in a case where neither the query characteristics nor the characteristics of the intermediate data structure types are known, using the on-demand hybrid placement strategy to store the intermediate data structure in the first sub-storage module and the second sub-storage module.

[0018] Preferably, the first sub-storage module and the second sub-storage module store intermediate data structures according to a preset placement strategy, further comprising: if the second sub-storage module is insufficient in space to store the intermediate data structure conforming to the hybrid placement strategy of intermediate data structure type awareness, using the on-demand hybrid placement strategy to store the intermediate data structure in the first sub-storage module and the second sub-storage module.

[0019] Preferably, the space occupation graph refers to a time series of memory space occupation of each type of intermediate data structure changing over time when a given query is executed.

[0020] Preferably, for a query type with a query frequency exceeding a preset frequency, the space occupation graph is obtained by recording historical information of query execution.

[0021] Preferably, the unit influence rate refers to, for a given query, an increase ratio of query running time when each GB of the type of intermediate data structure is placed in the NVM compared with when all are placed in the DRAM.

[0022] Preferably, the unit influence rate is estimated by using a predictor based on a multi-layer neural network; the input of the predictor includes the proportion of the intermediate data structure space to be predicted in the total memory consumption in a query and the proportion of the query running time to the memory consumption of multiple main operations; the multiple main operations include connection, clustering, sorting and external storage query algorithm.

[0023] Preferably, the intermediate data structure refers to a temporary data structure established by a query processing operation operator to complete a specific function of query processing; the specific function includes a hash table function in a hash-based operation operator, a sorting function in a sorting-based operation operator and the like; the temporary data structure allocates, creates and uses space in the query processing process, and releases the space when the query processing is completed.

[0024] From the above scheme, the advantages of the present application are as follows:

[0025] In the server machine with DRAM-NVM hybrid main memory and external storage, the present application optimizes the external storage-based analytical database system based on NVM. On the one hand, the data cache capacity is significantly improved by using NVM to optimize the data cache, more data pages are cached in the main memory, and the I / O is reduced, and the throughput of point query is greatly improved by the cache structure separating point query and scan operation. On the other hand, the execution engine of the database is optimized by using NVM, and part of the intermediate data structure is selectively placed in NVM, which greatly reduces the use of external storage query processing algorithm, thereby significantly improving the performance of complex analytical query. BRIEF DESCRIPTION OF DRAWINGS

[0026] Figure 1 is the structure diagram of the external storage-based analytical database system optimized by the hybrid main memory of the present application;

[0027] Figure 2 is the DHP strategy flow chart of the present application;

[0028] Figure 3 is the IHP strategy flow chart of the present application;

[0029] Figure 4 is Figure 1 the specific structure diagram of the system;

[0030] Figure 5 is the structure diagram of the predictor based on the multi-layer neural network of the present application;

[0031] Figure 6 is the comparison diagram of the experimental results of the present application;

[0032] Among them, the reference signs are:

[0033] 1, 1'- analytical database system;

[0034] 10-Buffer unit;

[0035] 100-first cache module;

[0036] 101-second cache module;

[0037] 11-Heap unit;

[0038] 110-first sub-storage module;

[0039] 111-second sub-storage module;

[0040] S100-S102, S200-S204-steps. DETAILED DESCRIPTION

[0041] The inventor mainly considered the characteristics of the external storage-based analytical database and the performance difference between NVM and DRAM in the research. Therefore, the application proposes an effective method of integrating NVM into the database cache and the execution engine, and constructs an analytical database system using hybrid memory to improve the overall performance of the database.

[0042] In order to make the above features and effects of the application more clear and easy to understand, the following embodiments are specifically described below, and the detailed description is made in conjunction with the accompanying drawings.

[0043] As Figure 1 The system 1 adopts a hybrid main memory storage structure (not shown in the figure) of persistent memory NVM and volatile memory DRAM, and includes a Buffer unit 10 and a Heap unit 11.

[0044] In this embodiment, the Buffer unit 10 is configured for data caching, and further includes a first cache module 100 and a second cache module 101. The first cache module 100 is configured for caching data pages read by scan operation. The second cache module 101 is configured for caching data pages read by point query operation.

[0045] Specifically, the first cache module 100 can be in NVM or DRAM, and the second cache module 101 can also be in NVM or DRAM. The NVM expands the capacity of the buffer in the main memory, reduces I / O overhead by caching more data in the main memory, and further uses a cache structure that separates point queries and scan operations to reduce the impact of scan operations on point queries. That is, the entire data cache space is divided into two separate caches, which are used to cache data pages read by point queries and scan operations, respectively, so as to avoid the replacement of data pages read by point queries from the cache by a large number of data pages read by scan operations, thereby reducing the interference of scan operations on point queries and improving the cache hit rate and performance of point queries.

[0046] The mixed DRAM and NVM space is used to cache data pages, and the increase in cache capacity can significantly reduce I / O and improve query processing performance. The performance difference caused by the placement of specific DRAM or NVM is small, so a simplified design can be used without the need to specially distinguish whether the data pages are placed in DRAM or NVM.

[0047] In this embodiment, the Heap unit 11 is configured to store intermediate data structures, and further includes a first sub-storage module 110 and a second sub-storage module 111. The first sub-storage module 110 is configured as NVM for placing intermediate data structures, and the second sub-storage module 111 is configured as DRAM for placing intermediate data structures. The first sub-storage module 110 and the second sub-storage module 111 store intermediate data structures according to a predetermined placement strategy. That is, some intermediate data structures are selectively placed in NVM to reduce I / O operations caused by external storage query processing algorithms, thereby improving system performance.

[0048] In this embodiment, the intermediate data structure refers to a temporary data structure established by a query processing operator to complete a specific function. The specific function includes a hash table function in a hash-based operator and a sorting function in a sorting-based operator. The temporary data structure allocates, creates, and uses space during query processing, and releases its space when query processing is completed.

[0049] In this embodiment, the predetermined placement strategy includes an on-demand hybrid placement strategy DHP (On-Demand Hybrid Placement) and an intermediate data structure type-aware hybrid placement strategy IHP (IST-aware Hybrid Placement). Hereinafter, the DHP strategy and the IHP strategy are referred to as DHP strategy and IHP strategy.

[0050] As shown in FIG. 1, the DHP strategy includes the following steps: Figure 2 The DHP strategy flowchart includes:

[0051] S100: preferentially placing the intermediate data structure in the second sub-storage module 111;

[0052] S101: when the remaining space of the second sub-storage module 111 is less than the space required by the placed intermediate data structure;

[0053] S102: placing the intermediate data structure in the first sub-storage module 110.

[0054] As Figure 3 shown is an IHP policy flowchart, including:

[0055] S200: determining the query feature and the feature of the intermediate data structure type, wherein the query feature is the space occupancy graph of the query, and the feature of the intermediate data structure type is the unit influence rate of placing each type of intermediate data structure in the NVM on the query performance;

[0056] S201: calculating the peak point of the memory usage in the space occupancy graph;

[0057] S202: judging whether the memory usage of the peak point exceeds the available space size of the DRAM,

[0058] S203: if yes, determining the space occupied by each type of intermediate data structure in the memory usage of the peak point; and taking the unit influence rate from small to large as the NVM placement rule, and placing the intermediate data structure in the first sub-storage module 110, wherein each NVM placement rule indicates the intermediate data structure type and the partial occupancy ratio placed in the NVM.

[0059] S204: if no, placing the intermediate data structure in the second sub-storage module 111.

[0060] In this embodiment, the space occupancy graph refers to the time sequence of the memory space occupancy of each type of intermediate data structure changing with time when a given query is executed. For the query type whose query frequency exceeds a preset frequency, i.e. the frequently occurring query type, the space occupancy graph is obtained by recording the historical information of query execution.

[0061] In this embodiment, the unit influence rate refers to the increase proportion of the query running time compared with placing all in the DRAM when placing each GB of the type of intermediate data structure into the NVM for a given query, i.e. the influence of placing each GB into the NVM on the performance.

[0062] In this embodiment, the unit influence rate is estimated by using a predictor based on a multi-layer neural network; the inputs of the predictor include the intermediate data structure space size to be predicted in a query and the proportion in the total memory usage of the query, and the running time of the query and the memory usage proportion of four main operations (connection, clustering, sorting and external storage query algorithm).

[0063] In this embodiment, the system 1 uses the DHP strategy as the basic placement strategy and uses the IHP strategy as the advanced placement strategy. For a current query, it is determined whether the query characteristics and the intermediate data structure are known.

[0064] In the case where the query characteristics and the characteristics of the intermediate data structure type are known, the IHP strategy is used to store the intermediate data structure in the first sub-storage module 110 or the second sub-storage module 111.

[0065] In the case where neither the query characteristics nor the characteristics of the intermediate data structure type are known, the DHP strategy is used to store the intermediate data structure in the first sub-storage module 110 or the second sub-storage module 111.

[0066] In this embodiment, the system 1 further includes: if the second sub-storage module 111 is insufficient in space to store the intermediate data structure conforming to the IHP strategy, then the DHP strategy is used to store the intermediate data structure in the first sub-storage module 110 or the second sub-storage module 111.

[0067] Specifically, as Figure 4 A specific structure diagram of a hybrid main memory-oriented optimization-based external storage-based analytical database system 1' according to an embodiment of the present application is shown. The system 1' uses a representative open-source analytical database system DuckDB. DuckDB is an external storage-based analytical database that uses advanced analytical database technologies such as PAX storage, vector-based volcano execution engine, and query-in parallel execution. Data is mainly stored in the Checkpoint file in the external storage, and the newly added data is cached in the memory and recorded by the WAL log. The database periodically merges the newly added data and the main data to form a new Checkpoint file. The system includes data buffer pool, query execution engine, query optimizer and other modules in the memory.

[0068] In the system architecture after DuckDB integrates NVM, the main components using NVM are Buffer and Heap, which correspond to the data cache and the memory space of the storage intermediate data structure of the execution engine in the content of the present application. In DuckDB, the data page is called Block and is cached in Buffer; the query processing intermediate data structure is called IST and is placed in Heap.

[0069] The Block data in the storage layer is cached in the Buffer using a hybrid memory of DRAM and NVM. In the experiment, a configuration of 30% DRAM and 70% NVM can achieve a performance close to that of 100% DRAM. In addition, the Buffer space is divided into two parts, which are used to cache the Blocks read by the scan operation (calling the Scan function) and the point query operation (calling the Fetch function) respectively. The two types of operations correspond to two LRU queues, and when the cache space of an operation is full, it will only evict Blocks from its own queue. Experiments show that under the mixed load of OLAP queries and point queries, the implementation can increase the throughput of point queries by 1.35-1.94 times, while the throughput of OLAP queries is almost unaffected.

[0070] The IST generated in the query processing process is placed in the Heap using a hybrid memory of DRAM and NVM. The IST placed in the NVM can be directly read and written without being loaded into the DRAM first. The IHP strategy selects the type of IST with the least performance impact in the query optimization stage and generates a series of placement rules to place in the NVM, so as to make the peak point in the space occupation graph not exceed the size of the DRAM in the Heap. During query execution, the database actively places part of the IST in the NVM according to the placement rules generated by the IHP.

[0071] As Figure 5 The structure diagram of the predictor in the embodiment of the application is shown in the figure, and the performance impact degree predictor of the NVM placement of the IST is a multi-layer neural network. The inputs of the left half part of the network are the size of the IST type and the proportion in the total memory consumption of the query, the query running time, and the memory consumption proportions of the four main operations (join, clustering, sorting, and external storage query algorithm) in sequence; the inputs of the right half part are one-hot encoding of the IST type. The output of the network is the predicted performance impact degree. The loss function for network training is the mean square error of the predicted value and the true value of the performance impact degree.

[0072] The TPC-H (SF=300) is used by the application to evaluate the above design scheme. The experiment covers three different hardware configurations, and the number of CPU cores is 20, 25 and 30 in sequence, and each CPU core is equipped with 2GB of DRAM and 8GB of NVM. The comparison objects include the original DuckDB, the DuckDB integrated with NVM in the Buffer, and the DuckDB integrated with NVM in the Buffer and the Heap.

[0073] As Figure 6The experimental results of the application are shown, which report the total execution time of TPC-H and the execution time of the top 5 queries with the largest memory usage under each hardware configuration. When the DRAM is less than 60GB, the DuckDB without integrating NVM cannot execute Q17. The experiment shows that after integrating the results of the application, the execution time of the entire TPC-H can be shortened by 1.36-1.54 times, and the execution of a single query can be accelerated by 2.71 times (Q18).

[0074] In summary, the application optimizes an analytical database system based on external storage by using NVM in a server machine with a DRAM-NVM hybrid main memory and external storage. On the one hand, the data cache is optimized by using NVM, so that the data cache capacity is significantly improved, more data pages are cached in the main memory, and I / O is reduced. At the same time, the cache structure of the separation of point query and scan operation greatly improves the throughput of point query. On the other hand, the database execution engine is optimized by using NVM, and part of the intermediate data structure is selectively placed in NVM, which greatly reduces the use of external storage query processing algorithm, thereby significantly improving the performance of complex analytical queries.

[0075] Of course, the application can have other various embodiments, and those skilled in the art can make various corresponding changes and modifications according to the application without departing from the spirit and essence of the application. However, these corresponding changes and modifications should all belong to the protection scope of the claims attached to the application.

Claims

1. A hybrid main memory oriented optimized disk-based analytical database system, characterized in that, A hybrid main memory storage structure using persistent memory NVM and volatile memory DRAM, comprising: a buffer unit configured for data caching, comprising: a first cache module configured for caching data pages read by scan operations; a second cache module configured for caching data pages read by point query operations; a heap unit configured for storing intermediate data structures, comprising: a first sub-storage module configured for the NVM to store intermediate data structures; a second sub-storage module configured for the DRAM to store intermediate data structures; the first sub-storage module and the second sub-storage module store the intermediate data structures according to a preset placement strategy; the placement strategy comprises a hybrid placement strategy with awareness of intermediate data structure types, comprising: determining query characteristics and characteristics of intermediate data structure types, wherein the query characteristics are a spatial occupancy graph of the query, and the characteristics of the intermediate data structure types are a unit influence rate of placing each type of intermediate data structure in the NVM on query performance; calculating a peak point of memory usage in the spatial occupancy graph; if the memory usage of the peak point exceeds the available space size of the DRAM, determining the space occupied by each type of intermediate data structure in the memory usage of the peak point; using the unit influence rate from small to large as the placement rule of the NVM, and placing the intermediate data structures in the first sub-storage module, and each placement rule of the NVM indicates the intermediate data structure type and the proportion of placement in the NVM.

2. The hybrid main memory optimization-oriented external storage-based analytical database system according to claim 1, wherein the placement strategy comprises: an on-demand hybrid placement strategy, comprising: preferentially placing the intermediate data structures in the second sub-storage module, placing the intermediate data structures in the first sub-storage module when the remaining space of the second sub-storage module is smaller than the space required for placing the intermediate data structures.

3. The hybrid main memory optimization-oriented external storage-based analytical database system according to claim 1, wherein the hybrid placement strategy with awareness of intermediate data structure types further comprises: if the memory usage of the peak point does not exceed the available space size of the DRAM, placing the intermediate data structures in the second sub-storage module.

4. The hybrid main memory optimization-oriented external storage-based analytical database system according to claim 1 or 2, wherein the first sub-storage module and the second sub-storage module store the intermediate data structures according to a preset placement strategy, comprising: for a current query, in a case where the query characteristics and the characteristics of the intermediate data structure types are known, using the hybrid placement strategy with awareness of intermediate data structure types to store the intermediate data structures in the first sub-storage module and the second sub-storage module; in a case where neither the query characteristics nor the characteristics of the intermediate data structure types are known, using the on-demand hybrid placement strategy to store the intermediate data structures in the first sub-storage module and the second sub-storage module. 5.The hybrid main memory oriented optimization based on disk analytic database system of claim 4, wherein, the first sub-storage module and the second sub-storage module store the intermediate data structure according to a preset placement strategy, and further comprising: if the second sub-storage module is insufficient to store the intermediate data structure conforming to the hybrid placement strategy of the type-awareness of the intermediate data structure, then the on-demand hybrid placement strategy is adopted to store the intermediate data structure in the first sub-storage module and the second sub-storage module. 6.The hybrid main memory oriented optimization based on disk analytic database system of claim 1, wherein, the space occupancy graph refers to a time series of memory space occupancy of each type of the intermediate data structure over time when a given query is executed. for a query type with a query frequency exceeding a preset frequency, the space occupancy graph is obtained by recording historical information of query execution. 8.The hybrid main memory oriented optimization based on disk analytic database system of claim 1, wherein, the unit impact rate refers to an increase proportion of query running time when each GB of the intermediate data structure is placed in the NVM compared with being placed in the DRAM for a given query.

7. The hybrid main memory oriented optimized disk-based analytical database system as claimed in claim 6, wherein, 9.The hybrid main memory oriented optimization based on disk analytic database system of claim 8, wherein, the unit impact rate is estimated by a predictor based on a multi-layer neural network. the input of the predictor includes a space size of the intermediate data structure to be predicted in a query and a proportion in total memory usage, and a query running time and a memory usage proportion of a plurality of main operations including connection, clustering, sorting and disk query algorithm. 10.The hybrid main memory oriented optimization based on disk analytic database system of claim 1, wherein, the intermediate data structure refers to a temporary data structure established by a query processing operation operator to complete a specific function of query processing. the specific function includes a hash table function in a hash-based operation operator and a sorting function in a sorting-based operation operator. the temporary data structure allocates, creates and uses space in the query processing process, and releases the space when the query processing is completed. ​ ​ ​ ​ ​

Images