A primary key storage engine and storage method supporting real-time update and ad hoc query

Through the LSM-Tree storage form and write-time merge solution, the data consistency and index support problems of the storage engine in real-time updates and complex queries are solved, and efficient data insertion and query performance is achieved. It is suitable for OLTP databases to DolphinDB Data analysis scenario.

CN118643042BActive Publication Date: 2024-11-22DOLPHINDB INC (CN)
View PDF 8 Cites 0 Cited by

Patent Information

Application Number
CN202411116849.5
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-08-15
Publication Date
2024-11-22
Estimated Expiration
2044-08-15

AI Technical Summary

Technical Problem

Existing storage engines are difficult to ensure data consistency and efficient index support when handling real-time data updates and complex queries. Especially when frequent insertion, update and deletion operations, they cannot meet the needs of OLTP databases and cannot effectively utilize non-primary key columns. indexing limits the flexibility of data analysis and processing.

Method used

The log structure merge tree (LSM-Tree) storage form is adopted to realize sequential write operations through the combination of memory and disk. The write-on-Write method is used to ensure the uniqueness of the primary key and reduce the repetition overhead during query. , and speed up queries by deleting bitmaps and custom indexes.

Benefits of technology

It realizes efficient real-time insertion and update, reduces the overhead of query deduplication, supports Ad Hoc query mode, balances write and query performance, and adapts to the data analysis needs of OLTP database to DolphinDB database.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118643042B_ABST
    Figure CN118643042B_ABST
Patent Text Reader

Abstract

The present invention discloses a primary key storage engine and storage method supporting real-time update and ad hoc query, adopts the storage form of log structure merge tree, performs sequential write operation, adopts the merge mode when writing to ensure the uniqueness of primary key, and introduces deletion bitmap, and newly written data is inserted into memory table, when the data in memory reaches the capacity threshold, the data is dropped to disk, at this time, in memory, by temporarily storing the primary key, temporarily storing part of the data for background execution of deletion bitmap update and query deduplication, each layer file has a corresponding deletion bitmap, updates the deletion mark of the corresponding file data when writing data, and filters the deleted or overwritten file data according to the deletion bitmap when querying data. While ensuring the uniqueness of primary key, the present invention effectively reduces the deduplication overhead during query, accelerates query performance through various custom indexes, and improves write performance by sequential write operation, thereby achieving a better balance between write and query performance.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The invention belongs to the technical field of database systems, and in particular relates to a primary key storage engine and a storage method that support real-time update and ad hoc query. Background Art

[0002] In modern data management, such as finance and the Internet of Things, the need for efficient, accurate, and flexible data processing is becoming increasingly important. To meet these needs, many companies need to transfer OLTP (Online Transaction Processing, used to manage data operations such as inserts, updates, and deletes in transactional applications) databases to DolphinDB databases for analysis through CDC (Change Data Capture, used to record and track changes in data in the database, such as real-time transmission of inserts, updates, and deletes to another system). OLTP databases usually set primary keys to ensure data consistency, while DolphinDB databases need to maintain the uniqueness of primary keys while bearing near-real-time, OLTP-like data insertion and update loads, as well as unfixed query patterns (such as Ad Hoc queries, an ad hoc query, usually used for temporary and undefined data query needs, which requires the database to have flexible query capabilities to adapt to diverse analysis needs).

[0003] However, existing technologies are difficult to fully meet these requirements. Although some systems can achieve efficient write and query performance, they have significant deficiencies in handling real-time data updates and complex queries. Many existing storage engines cannot guarantee data consistency and timeliness when facing frequent data insertion, update and deletion operations, and it is also difficult to provide efficient index support in queries on non-primary key columns. These limitations make it impossible to fully utilize real-time data for rapid decision-making and analysis during data analysis and processing. For example, the current distributed storage engine of the DolphinDB database has its own shortcomings in dealing with the above requirements. The current DolphinDB database mainly relies on TSDB and OLAP storage engines for analysis on primary key tables. Although the OLAP engine can maximize query performance, it is difficult to achieve real-time updates because data must be merged for each write. The TSDB (Time Series Database) storage engine performs well in write performance. When the deduplication strategy is LAST, it can meet the uniqueness requirements, but due to the high deduplication cost during query and the inability to use indexes on non-primary key columns, it is not suitable for Ad Hoc query modes that require a combination of multiple indexes. The IMOLTP engine stores all data in memory, which can achieve extreme writing and query performance, but is not suitable for scenarios with large amounts of data and analytical queries. Summary of the invention

[0004] In order to solve the shortcomings of the prior art, realize the uniqueness of the primary key, reduce the deduplication overhead during query, insert and update efficiently in real time, and accelerate the query, the present invention adopts the following technical solutions:

[0005] A primary key storage engine supporting real-time update and ad hoc query includes a memory and a disk. The memory includes a memory table MemTable and a temporary primary key Stashed Primary Key. The disk includes a pre-write log REDO, metadata META of the storage engine, and data STORAGE of the storage engine. The pre-write log REDO is used to ensure the atomicity and persistence of transactions. The write operation will first write data to the redo log and then enter the memory table. The metadata META of the storage engine includes a delete bitmap file. The data STORAGE of the storage engine is a multi-layer structure, and each layer includes a group of level files Level File.

[0006] The storage form of Log-Structured Merge-Tree (LSM-Tree, which improves write performance by writing data into memory first and then merging it to disk regularly) is adopted for sequential write operations to achieve efficient real-time insertion and update and ensure write performance. The uniqueness of the primary key is ensured by Merge-on-Write. Without affecting the write speed, the query does not need to read the file for deduplication and merge, thereby effectively reducing the deduplication overhead during query and realizing Ad Hoc query mode. The newly written data is inserted into the memory table. When the data in the memory reaches the capacity threshold, the data is written to the disk. Since the delete bitmap is updated in the background, in order to ensure the correctness of query deduplication, at this time, in the memory, the stashed primary key is used to temporarily store part of the data for the delete bitmap update and query deduplication executed in the background, so as not to affect the write delay of small transactions. Each level file has a corresponding delete bitmap delete. bitmap, updates the deletion mark of the corresponding file data (rows of each file) when writing data, and directly filters the deleted or overwritten file data (rows) according to the delete bitmap when querying data.

[0007] Furthermore, the metadata META of the storage engine also includes a metadata log meta.log of the meta information, which is used to store file meta information and deletion bitmap delete bitmap information of the primary key storage engine PKEY (Primary Key Storage Engine) to provide a snapshot of the meta information at a certain moment for query; the deletion bitmap delete bitmap file is stored in a multi-version manner to avoid conflicts between the query and write processes. Each time the deletion bitmap delete bitmap file is written to the disk, the largest flush ID of the data is used as the snapshot version. Each version is distinguished by the flush ID. The metadata log meta.log records the flush ID of the latest deletion bitmap delete bitmap of the current partition. Each query specifies the latest deletion bitmap delete bitmap file for deduplication by the metadata log meta.log.

[0008] Furthermore, after the memory data is written to the disk, the part of the data temporarily stored in the stashed primary key is the primary key column and the commit CID (commit ID) column. If the commit CID of the same primary key in the stashed primary key is larger, the corresponding data of the file (this row) needs to be marked as deleted.

[0009] Furthermore, the stashed primary key adopts a double buffer design, and its bottom layer consists of two buffers. Each buffer includes two vectors, namely the primary key column and the committed CID column. The double buffer design is used to handle the background delete bitmap update, data storage and concurrency control of query deduplication, which can eliminate the copy overhead of query deduplication.

[0010] Furthermore, the memory table MemTable includes a mutable memory table Mutable MemTable and multiple immutable memory tables Immutable MemTable, and the newly written data will be inserted into the memory table; the sequential write operation of the log structure merge tree LSM-Tree is to divide the write and update operations into memory insertion and file sequential write, and the memory insertion is written to the mutable memory table of the storage engine. When the amount of data in the mutable memory table reaches the capacity threshold, an immutable memory table is formed according to the primary key sorting; because the immutable memory table is ordered, the primary key point query is performed through binary search to reduce the complexity.

[0011] Furthermore, in order to better manage Level Files and identify the new and old order of data between Level Files, the file name of each Level File contains the level ID, flush ID and sequence number. The level ID is used to indicate which level the file logically belongs to. The flush ID indicates which disk flush the latest data contained in the file comes from. The sequence number sequence number indicates the sequence number of the Level File in the sorted run file to which it belongs. The sorted run file is a group of Level Files with no data overlap, which is the output of flush or data compaction. The multiple Level Files of the last layer do not overlap, thereby reducing the read amplification and space amplification of the last layer.

[0012] A primary key storage method supporting real-time update and ad hoc query adopts the storage form of log structure merge tree LSM-Tree. The uniqueness of the primary key is ensured by Merge-on-Write. While not affecting the writing speed, the query does not need to read the file for deduplication and merging, thereby effectively reducing the deduplication overhead during the query and realizing Ad Hoc query mode. The log structure merge tree LSM-Tree performs sequential write operations to achieve efficient real-time insertion and update and ensure write performance. The primary key uniqueness introduces a delete bitmap to update the deletion mark of the corresponding data (rows of each file) when the data is written. When querying the data, the corresponding data (rows) that are deleted or overwritten are directly filtered according to the delete bitmap. The update of the delete bitmap is executed in the background, thereby not affecting the write delay of small transactions. In addition, since the delete bitmap is updated in the background, in order to ensure the correctness of the query deduplication, when the data is stored on the disk, the stashed primary key is temporarily stored. key temporarily stores some data in memory for deleting bitmaps, updating bitmaps, and querying for duplicate removal.

[0013] Compared with the Merge-on-Read solution, Merge-on-Write sacrifices write performance because it needs to update the deletebitmap of each file. However, since the query of the Merge-on-Write solution does not need to read files for deduplication, it can effectively use non-primary key indexes to filter query results; compared with Copy-on-Write, it can support real-time update scenarios.

[0014] Furthermore, data writing includes the following steps:

[0015] Step S101: Write the write-ahead log to ensure transaction atomicity (WAL is called RedoLog in the PKEY storage engine);

[0016] Step S102: writing into the mutable memtable of the cache engine;

[0017] Step S103: When the amount of data in the mutable memtable reaches the capacity threshold, the memtable is sorted by primary key and forms an immutable memtable;

[0018] Step S104: When the data in the memory reaches the capacity threshold, the background flush is triggered to write the data in the memory to the disk as a level file. The data flushed this time will be stored in the stashed primary key of the memory as the basis for the next delete bitmap update and query deduplication;

[0019] Step S105: after flushing the disk, determine whether each layer needs to be merged according to the trigger condition, and then merge according to the file selection strategy;

[0020] The trigger condition is to check whether the amount of data in each layer exceeds the threshold, and the proportion of the number of deleted or overwritten rows in each layer to the total size. When there are multiple layers that meet the trigger condition, select the layer with the highest score to proceed to the next logic step;

[0021] The file selection strategy is to start from the oldest file in the selected layer, select overlapping sorted run files, and calculate the scores to make the best selection; depending on the trigger conditions, the purpose of the selection strategy tends to be different: either tending to choose the one with the most junk data, or tending to choose the one with the largest number of sorted run files.

[0022] Furthermore, after the disk flushing in step S104 is completed, it is checked whether the amount of stashed primary key data reaches the threshold value PKEYDeleteBitmapUpdateThreshold (the default value is 100MB), thereby triggering the background update of the delete bitmap. The update process includes the following steps:

[0023] Step S201: for each file, determine the data blocks to be merged according to the index zone map of the file;

[0024] Step S202: for each data block, check and update the delete bitmap through binary search. The stashed primary key will temporarily store the primary key column and the commit ID (CID) column of the data. If the commit CID of the same primary key in the stashed primary key is larger, the corresponding data (this row) of the file needs to be marked as deleted.

[0025] Step S203: write the delete bitmaps of all files into a delete bitmap file, and use the maximum flush ID in the stashed primary key as the version number of the delete bitmap.

[0026] Furthermore, the query process of the PKEY storage engine is similar to TSDB KeepLast. The difference is that the PKEY engine pushes the predicate condition to before deduplication and can use indexes such as bloom filters to speed up queries. The specific data query includes the following steps:

[0027] Step S301: Obtain memory data; fetch the required columns from the cache engine. If it is a primary key query, a binary search method will be used to search each memory table memtable;

[0028] Step S302: Obtain a metadata version snapshot; obtain level file information, delete bitmap information, and stashed primary key from the meta version;

[0029] Step S303: Obtain disk data; perform coarse filtering through various indexes, and construct a bitmap of each file to access the required data block; the filtering includes bloom filter filtering, zone map filtering and delete bitmap filtering;

[0030] The bloom filter filtering, when performing primary key point query or equal value query on the column with bloom filter index, will perform file-level bloom filter filtering on each level file to find all files that may have results;

[0031] The zone map filtering, when performing equal value or range query, will perform block-level filtering on each level file to find all blocks that meet the conditions;

[0032] The delete bitmap filtering, after the above filtering is completed, takes the intersection of the delete bitmap of the file and the bitmap recording the line number, and filters out the deleted and overwritten lines;

[0033] Step S304: Calculate the predicate condition results and filter out unnecessary rows;

[0034] Step S305: de-duplicate the query result, the cache engine, and the stashed primarykey;

[0035] Step S306: Return the query result.

[0036] The data update process is a data query and write after modification, or the data deletion process is a data query and write deletion mark.

[0037] The advantages and beneficial effects of the present invention are:

[0038] The present invention proposes that the primary key storage engine PKEY adopts the storage form of LSM-Tree, and uses the Merge-on-Write (merge occurs when writing) scheme to ensure the uniqueness of the primary key, thereby effectively reducing the deduplication overhead during query. In terms of write performance, the PKEY engine uses sequential write operations to achieve efficient real-time insertion and update. At the same time, the engine accelerates queries through various custom indexes, especially in queries of non-primary key columns, helping users configure better storage strategies in actual scenarios. Compared with traditional OLAP and TSDB engines, the PKEY engine has achieved a better balance between write and query performance, and can meet the complex needs of data analysis from the primary key table CDC in the OLTP database to the DolphinDB database. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] Figure 1 It is a schematic diagram of the structure of the storage engine in an embodiment of the present invention.

[0040] Figure 2 It is a schematic diagram of the layer file structure in an embodiment of the present invention.

[0041] Figure 3 It is a schematic diagram of the structure of deleting a bitmap file in an embodiment of the present invention.

[0042] Figure 4 It is a flow chart of the storage method in an embodiment of the present invention.

[0043] Figure 5 This is a comparison chart of the insertion time of the PKEY engine and TSDB in an embodiment of the present invention.

[0044] Figure 6 This is one of the query time comparison diagrams between the PKEY engine and TSDB in an embodiment of the present invention.

[0045] Figure 7 This is the second comparison diagram of query time consumption between the PKEY engine and TSDB in an embodiment of the present invention.

[0046] Figure 8 This is the third comparison diagram of query time consumption between the PKEY engine and TSDB in an embodiment of the present invention.

[0047] Fig. 9 This is the fourth comparison diagram of query time consumption between the PKEY engine and TSDB in the embodiment of the present invention.

[0048] Fig.10 This is the fifth comparison diagram of query time consumption between the PKEY engine and TSDB in the embodiment of the present invention. DETAILED DESCRIPTION

[0049] The specific implementation of the present invention is described in detail below in conjunction with the accompanying drawings. It should be understood that the specific implementation described here is only used to illustrate and explain the present invention, and is not used to limit the present invention.

[0050] 1. Primary key uniqueness:

[0051] 1.1. Mainstream solutions include Copy-on-Write, Merge-on-Read, and Merge-on-Write.

[0052] Copy-on-Write: When writing data, the conflicting parts of the original data are read from the disk and merged, and then the merged results are written to the disk. This solution can maximize query performance, and there is no need to consider ensuring the uniqueness of the primary key during the query process. However, since conflicting data must be merged every time it is written, the update cost is very high, so it is not suitable for scenarios with frequent updates.

[0053] Merge-on-Read: Data writing does not need to strictly consider uniqueness, and there is no need to check whether the new file overwrites the data rows of the old file. It can be written to the disk in a blind manner. When querying data, new files that may conflict are merged based on the query results, and the latest primary key data is retained to ensure the uniqueness of the primary key. Merge-on-Read has excellent writing performance because it does not need to check whether the new data written will overwrite the old data. However, since new files need to be read for deduplication and merging during query, there is an upper limit to the performance optimization of the query, such as the inability to filter data based on indexes of non-primary key columns.

[0054] Merge-on-Write: Introduces the concept of delete bitmap, updates the row deletion mark of each file when writing data, so that deleted or overwritten rows can be directly filtered out according to the delete bitmap during query. Compared with the Merge-on-Read solution, Merge-on-Write sacrifices write performance because it needs to update the delete bitmap of each file. However, since the query of the Merge-on-Write solution does not need to read files for deduplication, it can effectively use non-primary key indexes to filter query results. Compared with Copy-on-Write, it can support real-time update scenarios. In comprehensive comparison, Merge-on-Write is a solution that balances write and query performance.

[0055] 1.2. Primary key engine solution selection:

[0056] To achieve real-time insertion and updating, the PKEY storage engine adopts LSM-Tree design and guarantees writing performance through sequential writing.

[0057] To implement the Ad Hoc query mode, the Merge-on-Write solution is adopted, which does not affect the writing speed and makes the query not need to read the file for deduplication and merge. In addition, the PKEY engine can use various indexes to speed up the query. Slightly different from the existing representative system, the PKEY engine executes the update of the delete bitmap in the background so as not to affect the write latency of small transactions. Since the delete bitmap is updated in the background, in order to ensure the correctness of the query deduplication, the engine will maintain the stashed primary key in memory for deduplication.

[0058] 2. Storage engine design

[0059] 2.1. A primary key storage engine that supports real-time updates and ad hoc queries, with the following storage structure:

[0060] The data in the PKEY storage engine is stored in memory and disk respectively. Newly written data will be saved in the cache engine (Cache Engine) of the memory. When the amount of data in the memory reaches the threshold, the data will be saved on disk as a read-only level file (Level File). The data in both memory and disk are stored in columnar format. In order to support efficient primary key point query, data compression (compaction) and delete bitmap update, the immutable memtable (i.e., a memory table that can only be read but not written) in the memory and each Level File on the disk are sorted in the order of the primary key. After the new data is written, the storage engine will continue to update the delete bitmap in the background, thereby avoiding the overhead of reading files for deduplication when querying.

[0061] 2.1.1 Storage layout

[0062] like Figure 1 As shown, the PKEY storage engine data is divided into two parts: memory and disk, and consists of the following main components:

[0063] 1) MemTable: Newly written data will be inserted into the memtable. The memtable is divided into a mutable memtable and multiple immutable memtables.

[0064] 2) Stashed Primary Key: When the data in memory reaches the maximum capacity, the data will be stored on disk. At this time, the engine will temporarily store some data for subsequent delete bitmap updates and query deduplication.

[0065] 3) Write-ahead log REDO: used to ensure the atomicity and durability of transactions. The user's write operation will first write the data to the redo log and then enter the memory table.

[0066] 4) Storage engine metadata META: includes metadata log meta.log of meta information and multiple versions of delete bitmap files.

[0067] 5) Data storage of the storage engine: It is divided into four layers, and each layer stores multiple level files.

[0068] MemTable: The sequential write feature of LSM-Tree is to divide the write and update operations into two parts: memory insertion and file sequential writing. Memory insertion is to write to the variable memory table of the storage engine. When the data volume of the variable memory table reaches the capacity, it will be sorted according to the primary key to form an immutable memory table. In the PKEY engine, the Cache Engine maintains these two memory tables. Since the immutable memory table is ordered, the primary key point query can reduce the complexity through binary search.

[0069] Stashed Primary Key: When the memory data is flushed to disk, the engine will temporarily store the primary key and CID (commit ID) columns of the data for background updates of the delete bitmap and query deduplication. Stashed Primary Key uses a double buffer design, and its bottom layer consists of two buffers. Each buffer includes two vectors, namely the primary key column and the CID column. The double buffer design is used to handle background delete bitmap updates, data flushing, and concurrent control of query deduplication, which can eliminate the copy overhead of query deduplication.

[0070] Meta Log: Mainly stores the file meta information and delete bitmap information of the PKEY engine. Its function is to provide a snapshot of the meta information at a certain moment for the query:

[0071] 1) File meta information, including file path, index information, column types, etc., which is used to save file change operations such as partition recovery, transactions, and data storage.

[0072] 2) Delete bitmap information, including path and version number.

[0073] Delete Bitmap: In the PKEY engine, each layer file has a corresponding delete bitmap, which is used to specify whether a row of data in the file is deleted or overwritten. The delete bitmap is characterized by its small size. About 120KB can store the marks of 1 million rows of data. Therefore, a delete bitmap file of the PKEY storage engine can store the delete bitmaps of all layer files under the partition. The delete bitmap file is stored in a multi-version manner to avoid conflicts in the query and write processes. Specifically, each time a delete bitmap file is written to the disk, the largest flush ID of this data will be used as the snapshot version, and the Meta Log will specify the latest delete bitmap file.

[0074] Level File: Conceptually equivalent to SSTable in LevelDB. In terms of data file merging and compaction strategy, the PKEY storage engine adopts a lazy leveling design, that is, multiple Level Files in the first three layers may overlap, but multiple Level Files in the last layer will not overlap. The purpose is to reduce read amplification and space amplification in the last layer.

[0075] File Structure

[0076] Level Files: In order to better manage Level Files and identify the new and old order of data between Level Files, the engine designs the file name of each Level File to consist of three parts, namely level ID, flush ID and sequence number. The level ID indicates which layer the file logically belongs to, the flush ID indicates which disk flush the latest data contained in the file comes from, and the sequence number indicates the sequence number of the level file in the sorted run to which it belongs. A sorted run refers to a group of level files with no data overlap between these files. These files are the output of flush or compaction. For example: a file name 1-000000014-001 means that the level file exists in the second layer, contains data with a flushID of 14, and is the second file in the sorted run.

[0077] Level file consists of six parts, such as Figure 2 shown.

[0078] 1) File header: stores the file information, such as the maximum and minimum CID, column type information, primary key column information, etc.

[0079] 2) Data Block: Each data block stores up to 8192 rows of data. Each data block of the file and the data blocks within the file are sorted in the order of the primary key.

[0080] 3) Index Block: Currently supports Bloom Filter and Zone Map.

[0081] 4) Data Block Index: stores the column number, file offset, number of rows, and other information of all data blocks.

[0082] 5) Index Block Index: stores information such as the type, column number, file offset, etc. of all index blocks.

[0083] 6) File footer: stores the file offset of Data Block Index and Index Block Index.

[0084] The above six parts are all aligned to 4KB to improve the efficiency of the operating system accessing file blocks.

[0085] Delete Bitmap file: contains the delete bitmap of all Level Files in this partition. Its structure is as follows Figure 3 As shown;

[0086] 1) Bitmap: stores the delete bitmap of a file, which contains a 01 sequence, indicating whether a row is deleted or overwritten. For example: Figure 3 The first bitmap indicates that the second line, sixth line, etc. of the corresponding Level File have been deleted or overwritten.

[0087] 2) Footer: Stores the Level File information and file offset corresponding to each bitmap, including the level ID, flush ID and sequence number of the LevelFile.

[0088] The storage engine stores delete bitmap files in multiple versions to provide consistent data snapshots for queries, where each version is distinguished by the flushID in the file name. For example, delmap-000000014 is the delete bitmap of each level file with the flush data with flushID 14 as the latest data. meta.log will record the flush ID of the latest delete bitmap of the current partition, and each query will obtain the latest delete bitmap from the meta information for deduplication.

[0089] 2.2 A primary key storage method that supports real-time updates and ad hoc queries. The read and write process is as follows:

[0090] 2.2.1. Writing process

[0091] like Figure 4 As shown, from the perspective of data transmission, the writing process of the PKEY engine includes the following steps:

[0092] Step S101: Write redo: Write to the write-ahead log to ensure transaction atomicity. (In the PKEY storage engine, WAL is called RedoLog);

[0093] Step S102: Write to cache engine: When the redo log is written successfully, write to the mutable memtable of the cache engine;

[0094] Step S103: Forming an immutable memtable: When the amount of data in the mutable memtable reaches its capacity (8MB), the memtable is sorted by primary key and forms an immutable memtable;

[0095] Step S104: Background disk flushing: When the amount of data in the cache engine reaches the PKEYCacheEngineSize capacity (default 1GB), the PKEY engine will trigger the background disk flushing logic to write all the data in the memory to the disk as the first-level level file. At the same time, the data flushed this time will be stored in the stashed primary key as the basis for the next delete bitmap update and query deduplication;

[0096] Step S105: Background compaction: After each disk flush and compaction, the background thread determines whether each layer needs to be compacted based on the trigger conditions, and then performs compaction based on the file selection strategy.

[0097] 1) Trigger condition: Check whether the amount of data in each layer exceeds the threshold, and the proportion of the number of deleted or overwritten rows in each layer to the total size. When there are multiple layers that meet the trigger condition, the layer with the highest score will be selected for the next step of logic.

[0098] 2) File selection strategy: Starting from the oldest file in the layer, select overlapping sorted runs and calculate the scores to make the best selection. Depending on the triggering conditions, the selection strategy has different tendencies: either the selection with the most junk data or the selection with the largest number of sorted runs.

[0099] After the step S104 of the data transfer process is finished, the system checks whether the amount of stashed primary key data reaches the threshold value PKEYDeleteBitmapUpdateThreshold (default value 100MB), thereby triggering the background update of deletebitmap. The process of background update of deletebitmap includes the following steps:

[0100] Step S201: for each file, determine the data blocks to be merged according to the zone map of the file;

[0101] Step S202: For each data block, check and update the delete bitmap through binary search; if the CID of the same primary key in the stashed primary key is larger, then this line of the file needs to be marked as deleted;

[0102] Step S203: Write the delete bitmaps of all files into a delete bitmap file, and use the maximum flushID in the stashed primary key as the version number of the delete bitmap.

[0103] 2.2.2. Query process

[0104] The query process of the PKEY storage engine is similar to TSDB KeepLast, except that the PKEY engine pushes the predicate condition to before deduplication and can use indexes such as bloom filters to speed up queries. The specific query process includes the following steps:

[0105] Step S301: Get memory data: Get the required columns from the cache engine. If it is a primary key query, a binary search will be used to search each memtable;

[0106] Step S302: Get metadata version snapshot: get level file information, deletebitmap information and stashed primary key from meta version;

[0107] Step S303: Obtain disk data: perform coarse filtering through various indexes, and construct a bitmap of each file to access the required data block;

[0108] 1) Use bloom filter filtering: When performing primary key point query or equal value query on a column with bloom filter index, the bloom filter at the file level will be filtered on each level file to find all files that may contain results;

[0109] 2) Use zone map filtering: When performing equal value or range query, each level file will be filtered at the block level to find all blocks that meet the conditions;

[0110] 3) Filter using delete bitmap: After the above filtering is completed, the delete bitmap of the file and the bitmap recording the line number are intersected to filter out the deleted and overwritten lines;

[0111] Step S304: Calculate the predicate condition results and filter out unnecessary rows;

[0112] Step S305: De-duplicate the query result, cache engine, and stashed primary key;

[0113] Step S306: Return the query result.

[0114] 2.2.3. Update process

[0115] In the PKEY engine, an update can be considered as a data query and write, which includes the following steps:

[0116] Step S401: Query: perform a query based on the predicate condition.

[0117] Step S402: Modify: modify the above query results.

[0118] Step S403: Write: insert data according to the write process.

[0119] 2.2.4. Deletion process

[0120] In the PKEY engine, deletion can also be regarded as a data query and write, which includes the following steps:

[0121] Step S501: Query: perform a query based on the predicate condition.

[0122] Step S502: Write: insert data according to the write process, but the CID will be written as a negative number as a deletion mark.

[0123] 3. Storage engine comparison test

[0124] Existing storage engines can all guarantee the uniqueness of primary keys. The application scenarios of OLAP and IMOLTP are clear, but the comparison between PKEY and TSDB KeepLast is not clear enough. It is necessary to further clarify in what scenarios the primary key engine is better than TSDBKeepLast.

[0125] 3.1. Test scenario

[0126] Compare the performance of the two on a single node, simulate a single-partition order system scenario, write daily incremental imports, and query various ad hoc.

[0127] Environment configuration:

[0128] CPU: 12th Gen Intel(R) Core(TM) i5-12500

[0129] CPU cores: 12

[0130] Memory: 32G

[0131] Test version: DolphinDB release3.00.1

[0132] Database settings: default configuration, single machine single partition

[0133] Schema:

[0134] Data table for simulating order transactions:

[0135] Field Name Field Type illustrate id_str STRING Length 20B, primary key column id_date DATE Primary key columns addr SYMBOL Length 8B, analog address date DATE Simulation Date col1~col8 DOUBLE Simulating other data

[0136] Each row is 100B, and the primary key + CID column occupies 32B.

[0137] operate:

[0138] Simulate daily write + update + delete operations. First, perform incremental writes, then execute various queries multiple times, and count the time consumed for writes and queries.

[0139] 1) Full import: import 10 million rows of data at one time.

[0140] 2) Incremental writing: 1,000 rows are written in each batch, and all the data is repeated between each batch. This is 100 times a day, or 100,000 rows. The simulated insert lasts for 50 days, and the incremental writing is 5 million rows in total, or 500 MB.

[0141] 3) Query #1: Primary key point query, select * from pt where id_str = t_id_str and id_date = t_id_date.

[0142] 4) Query #2: in primary key set, select * from pt where id_str in t_id_str and id_date in t_id_date, there are 10,000 rows in the set.

[0143] 5) Query #3: Range query, select * from pt where date between begin_date and end_date, the query result is close to 30% of the total number of rows.

[0144] 6) Query #4: Address point query, select * from pt where addr = t_addr.

[0145] 7) Query #5: Combine point query and range query, select * from pt where addr = t_addr anddate between begin_date and end_date.

[0146] The pseudo code of the corresponding test script is as follows:

[0147] …

[0148] full_load()

[0149] for i in range(0..50):

[0150] incremental_write()

[0151] test_q1()

[0152] test_q2()

[0153] test_q3()

[0154] test_q4()

[0155] test_q5()

[0156] …

[0157] 3.2. Test results

[0158] 3.2.1. Write performance

[0159] As the number of days increases, the average time spent inserting the same amount of data is as follows: Figure 5 The horizontal axis represents the number of execution days, and the vertical axis corresponds to the running time in milliseconds.

[0160] It can be found that in this scenario, the write speed of the PKEY engine is close to that of TSDB, with an average difference of about 1.2ms.

[0161] 3.2.3. Query performance

[0162] As the number of days increases, the average query time for different scenarios under the same query and the same data is as follows: Figures 6 to 10 .

[0163] Query scenario 1: primary key point query select * from pt where id_str = t_id_str and id_date = t_id_date

[0164] Figure 6 middle:

[0165] 1) The P95 query time of PKEY is 11.14ms, and the P95 query time of TSDB is 6.55ms. The P95 query time of PKEY is 1.7 times that of TSDB.

[0166] 2) The PKEY engine has a peak time consumption on the first day. At this time, the file is just stored on the disk, and the query will obtain data from the file. At this time, the delete bitmap has not been updated, so the query result and the stashed primary key are deduplicated, resulting in slow query.

[0167] The query time of the PKEY engine gradually increased, with an average time of 5.64ms on the second day and an average time of 10.08ms on the fiftieth day. This is because the cache engine continues to grow, and no flush is triggered during these fifty days, causing the query to do a binary search in multiple memtables, increasing the query time. We can reduce PKEYCacheEngineSize to 100MB, so that a flush can be performed every 10 days, reducing the amount of data in memory.

[0168] The optimization of point query and deduplication of PKEY engine is in progress. The performance is expected to be on par with TSDB without time fluctuation.

[0169] Query scenario 2: in primary key set select * from pt where id_str in t_id_str andid_date in t_id_date

[0170] Figure 7 middle:

[0171] 1) The P95 query time of PKEY is 64.42ms, and the P95 query time of TSDB is 58.17ms. The P95 query time of PKEY is 1.1 times that of TSDB.

[0172] 2) The query time of the PKEY engine gradually increases. The average query time on the second day is 29.41ms, and the average query time on the fiftieth day is 66.46ms. This is because the cache engine continues to grow, resulting in a gradual increase in memory deduplication overhead. You can reduce PKEYCacheEngineSize to 100MB, clear the cache engine every 10 days, and reduce the query time.

[0173] Query scenario 3: range query select * from pt where date between begin_date and end_date

[0174] Figure 8 middle:

[0175] 1) The P95 of EY query is 2875.66ms, and the P95 of TSDB query is 2852.58ms. The two are basically the same.

[0176] Query scenario 4: Index column point query select * from pt where addr = t_addr

[0177] Fig. 9 middle:

[0178] 1) The P95 query time of PKEY is 123.46ms, and the P95 query time of TSDB is 1765.28ms. The query time of PKEY is about 6.99% of that of TSDB.

[0179] 2) The reason why the PKEY engine performs well in this scenario is that it can filter out level files that do not need to be read through bloomfilter, while also avoiding the overhead of reading files for deduplication.

[0180] Query scenario 5: Multiple condition query of primary key and index columns select * from pt where addr = t_addr and date between begin_date and end_date

[0181] Fig.10 middle:

[0182] 1) The P95 query time of PKEY is 36.18ms, and the P95 query time of TSDB is 1740.63ms. The query time of PKEY is about 2.08% of that of TSDB.

[0183] 2) The query time of the PKEY engine is relatively stable, with a range of 5ms, and does not change with the amount of data in memory.

[0184] The reason why the PKEY engine performs well in this scenario is that it can filter out level files that do not need to be read through bloomfilter, while also avoiding the overhead of reading files for deduplication.

[0185] 3.3 Conclusion

[0186] Compared with TSDB KeepLast, the PKEY engine:

[0187] 1) Under the load of incremental write, primary key point query, in primary key collection and range query, the performance of PKEY engine is similar to TSDBKeepLast.

[0188] 2) When querying index columns, the PKEY engine is significantly better than TSDB KeepLast.

[0189] 3) After the full import, the PKEY engine may experience a short increase in time consumption, mainly because the delete bitmap has not been completely updated. The deduplication logic will be optimized in subsequent versions.

[0190] In summary, with proper tuning and scenario selection, the PKEY engine can achieve performance comparable to or even better than TSDB KeepLast, especially in specific query scenarios such as index column point query.

[0191] 4. Conclusion

[0192] The primary key storage engine (PKEY) provides primary key uniqueness guarantee, supports real-time updates and efficient queries, and meets the needs of primary key table CDC from OLTP database to DolphinDB data analysis. Compared with TSDB and OLAP engines, PKEY can support near-real-time insertion and update while maintaining primary key uniqueness, and adapt to non-fixed query patterns. Compared with IMOLTP engines, it is more suitable for processing large amounts of data and analytical queries.

[0193] Compared with the OLAP engine, the PKEY engine can better support real-time updates, and is particularly suitable for data analysis needs written from the OLTP database to DolphinDB through the CDC method. Compared with the TSDB engine, PKEY can reduce deduplication overhead while maintaining the uniqueness of the primary key, and provide custom indexes on non-primary key columns to adapt to a variety of query modes.

[0194] When using PKEY, you need to set reasonable partition fields, primary key fields, and indexes according to the user's data characteristics and query scenarios to achieve the best performance of PKEY.

[0195] The above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some or all of the technical features thereof may be replaced by equivalents. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A primary key storage method that supports real-time updates and ad hoc queries, applied to a primary key storage engine including memory and disk, Features: The memory includes a memory table and a temporary primary key, and the disk includes metadata of the storage engine and data of the storage engine. The metadata of the storage engine includes a deletion bitmap file, and the data of the storage engine is a multi-layer structure, and each layer includes a group of layer files; The storage form of log structure merge tree is adopted to perform sequential write operation. The uniqueness of primary key is merged at write time. The newly written data is inserted into the memory table. When the data in the memory reaches the capacity threshold, the data is dropped to the disk. At this time, in the memory, the primary key is temporarily stored. Part of the data is used for the deletion bitmap update and query deduplication executed in the background. Each layer file has a corresponding deletion bitmap. The deletion mark of the corresponding file data is updated when the data is written. When the data is queried, the deleted or overwritten file data is filtered according to the deletion bitmap; the primary key storage method includes the following steps: Step S101: writing into the write-ahead log; Step S102: writing into a variable memory table; Step S103: When the amount of data in the mutable memory table reaches the capacity threshold, the memory table is sorted by primary key and forms an immutable memory table; Step S104: When the data in the memory reaches the capacity threshold, the background disk flushing is triggered to write the data in the memory to the disk as a layer of files, and the data flushed this time will be stored in the temporary primary key of the memory as the basis for the next deletion bitmap update and query deduplication; Step S105: after flushing the disk, determine whether each layer needs to be merged according to the trigger condition, and then merge according to the file selection strategy; The trigger condition is to check whether the amount of data in each layer exceeds a threshold, and the ratio of the number of deleted or overwritten rows in each layer to the total size. When there are multiple layers that meet the trigger condition, the layer with the highest score is selected; The file selection strategy is to start from the oldest file in the selected layer, select overlapping sorted running files, and calculate the scores to make the best selection; Data query includes the following steps: Step S301: Obtain memory data; fetch the required columns from the cache engine. If it is a primary key query, a binary search method will be used to search each memory table; Step S302: Obtain a metadata version snapshot; obtain layer file information, delete bitmap information, and temporarily store primary keys from the meta version; Step S303: Obtain disk data; filter by index, and construct a bitmap of each file to access the required data block; The filtering includes bloom filter filtering, zone map filtering and delete bitmap filtering; The bloom filter filtering, when performing primary key point query or equal value query on the column with bloom filter index, will perform file-level bloom filter filtering on each layer file to find all files that may have results; The zone map filtering, when performing equal value or range query, will perform block-level filtering on each layer file to find all blocks that meet the conditions; The delete bitmap filtering, after the above filtering is completed, takes the intersection of the deletion bitmap of the file and the bitmap recording the line number, and filters out the deleted and overwritten lines; Step S304: Calculate the predicate condition results and filter out unnecessary rows; Step S305: De-duplicate the query results, the cache engine, and the temporary primary key; Step S306: Return the query result.

2. A primary key storage method supporting real-time update and ad hoc query according to claim 1, Features: The metadata of the storage engine also includes a metadata log of meta information, which is used to store file meta information and deletion bitmap information of the primary key storage engine, and provide a snapshot of the meta information at a certain moment for query; the deletion bitmap file is stored in a multi-version manner, and each time a deletion bitmap file is written to the disk, the largest disk ID of the data is used as the snapshot version, and each version is distinguished by the disk ID. The metadata log records the disk ID of the latest deletion bitmap of the current partition, and each query specifies the latest deletion bitmap file for deduplication by the metadata log.

3. A primary key storage method supporting real-time update and ad hoc query according to claim 1, Features: After the memory data is written to the disk, the part of the data temporarily stored by the temporary primary key is the primary key and the submission ID. If the submission ID of the same primary key in the temporary primary key is larger, the corresponding data of the file needs to be marked as deleted.

4. A primary key storage method supporting real-time update and ad hoc query according to claim 1, Features: The temporary primary key adopts a double buffer design, and its bottom layer consists of two buffers. Each buffer includes two vectors, namely the primary key and the submission ID. The double buffer design is used to handle the background deletion bitmap update, data storage, and query deduplication concurrency control.

5. A primary key storage method supporting real-time update and ad hoc query according to claim 1, Features: The memory table includes a mutable memory table and an immutable memory table, and the newly written data will be inserted into the memory table; the sequential write operation of the log structure merge tree is to divide the write and update operations into memory insertion and file sequential write, and the memory insertion is written to the mutable memory table of the storage engine. When the amount of data in the mutable memory table reaches the capacity threshold, an immutable memory table is formed according to the primary key sorting; since the immutable memory table is ordered, the primary key point query is performed through binary search.

6. A primary key storage method supporting real-time update and ad hoc query according to claim 1, Features: The file name of each layer file contains the layer ID, disk flushing ID and sequence number. The layer ID is used to indicate which layer the file logically belongs to. The disk flushing ID indicates which disk flushing the latest data contained in the file comes from. The sequence number indicates the sequence number of the layer file in the sorting run file to which it belongs. The sorting run file is a group of layer files with no data overlap and is the output of disk flushing or data merging.

7. A primary key storage method supporting real-time update and ad hoc query according to claim 1, Features: After the disk flushing in step S104 is completed, it is checked whether the amount of temporarily stored primary key data reaches a threshold, thereby triggering a background update of the deletion bitmap. The update process includes the following steps: Step S201: for each file, determine the data blocks to be merged according to the index of the file; Step S202: For each data block, check and update the deletion bitmap through binary search. The temporary primary key will temporarily store the primary key and submission ID of the data. If the submission ID of the same primary key in the temporary primary key is larger, the corresponding data of the file needs to be marked as deleted; Step S203: Write the deletion bitmaps of all files into a deletion bitmap file, and use the maximum disk ID in the temporary storage as the version number of the deletion bitmap.

Citation Information

Patent Citations

  • Oracle database data processing method based on LSM tree

    CN110109927A

  • SSD-oriented time series data storage method and system based on LSMTree

    CN116414839A

  • Data processing method and device in hybrid transaction analysis system and electronic equipment

    CN117056427A

  • Data deduplication and management system based on log structure merging tree

    CN117406923A

  • Data storage method and device of data lake, storage medium and electronic equipment

    CN118152357A