Light-weight non-immediate disaster recovery OLAP database system based on DuckDB

Abstract

The invention relates to the technical field of databases, and discloses a DuckDB-based lightweight non-instant disaster recovery OLAP database system, which comprises a server node, a webpage interface web ui and a business service client, a duckdb service is deployed on the server node, and the access of the duckdb service is packaged by adopting an asp.net core framework; the webpage interface web ui and the duckdb service are interacted with each other in a REST API (Application Program Interface) form; and the business service client side client service runs on the server node, and the business service client side client service writes data into the duckdb service by adopting a one-way memory mapping file (MMF). The method has an efficient OLAP processing capability, remarkably improves the processing performance of a large amount of time series data in writing, storing and reading links, is used for storing, querying and analyzing the large amount of time series data, and is particularly suitable for application scenes needing to process the large amount of time series data, such as the Internet of Things and industrial monitoring.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a lightweight, non-real-time disaster recovery OLAP database system based on DuckDB. Background Technology

[0002] With the rapid development of information technology, the scale of data generation and application continues to expand, especially the volume and processing needs of time-series data (such as monitoring data and log data) have increased significantly, placing higher demands on the functionality and performance of database systems. As the core carrier of data storage and processing, database systems must simultaneously meet the requirements of high efficiency in multiple stages such as data writing, storage, query and analysis, as well as ease of deployment and maintenance.

[0003] To achieve rich functionality, current database products on the market typically integrate numerous components, resulting in a large overall system size. This not only consumes significant hardware resources (such as computing, memory, and storage resources) but also makes deployment cumbersome and places high demands on the operating environment. Regarding data processing capabilities, traditional Online Transaction Processing (OLTP) databases are primarily optimized for transactional operations, and their architecture is ill-suited for efficient processing of large volumes of time-series data. When faced with high-frequency writes, large-scale storage, and complex analytical queries (such as aggregation calculations and multi-dimensional statistics) of time-series data, traditional OLTP databases often exhibit insufficient performance, failing to meet the efficiency requirements of Online Analytical Processing (OLAP) scenarios. In terms of data reliability, existing database disaster recovery solutions typically rely on complex cluster architectures and real-time data synchronization mechanisms. This not only requires configuring multiple nodes to work collaboratively but also imposes strict requirements on network stability and transmission efficiency, leading to high costs for building and maintaining disaster recovery systems. In addition, traditional database systems are highly complex to operate and maintain. They have numerous configuration parameters and tightly coupled components, requiring professional database administrators (DBAs) to perform daily maintenance and troubleshooting, which further increases the cost of using the system. They are especially unsuitable for application scenarios that require lightweight operation and low maintenance costs.

[0004] Therefore, in view of the problems existing in the database system in terms of size, OLAP performance, disaster recovery mechanism and operation and maintenance complexity, there is an urgent need for a database system solution that can balance lightweight deployment, efficient analysis and processing, and simplified disaster recovery and operation and maintenance. Summary of the Invention

[0005] This invention provides a lightweight, non-real-time disaster recovery OLAP database system based on DuckDB, which has efficient OLAP processing capabilities, significantly improves the processing performance of large amounts of time-series data in the writing, storage, and reading stages, reduces deployment complexity through a lightweight deployment architecture, and simplifies operation and maintenance processes by relying on a non-real-time disaster recovery mechanism, greatly reducing the difficulty of operation and maintenance, and achieving a synergistic effect of high-performance data processing and simple operation and maintenance.

[0006] This invention provides a lightweight, non-real-time disaster recovery OLAP database system based on DuckDB, including a server node, a web interface (web UI), and a business service client. The server node deploys the duckdb service, which is accessed using the ASP.NET Core framework. The web UI interacts with the duckdb service via a REST API. The client service runs on the server node and writes data to the duckdb service using a one-way memory-mapped file (MMF).

[0007] Furthermore, the duckdb service deployed on the server node is an embedded duckdb database.

[0008] Furthermore, it also includes multiple data channels, which receive data transmitted by the business service client through a memory-mapped file (MMF), and aggregate similar data generated by different business service clients. Each data channel corresponds to one type of data. Furthermore, the data received concurrently by the data channel uses a spinlock mechanism to cache the data, forming a data buffer. When the amount of cached data reaches the specified buffer size, the data is appended to the data file corresponding to the data type in batches through the connection pool. Each data type corresponds to one or more data files.

[0009] Furthermore, when a new data type needs to be written, a new connection is created from the connection pool and a new data file is generated. When no data is written to the data file within a set time, the corresponding database connection is closed and the corresponding resources are reclaimed.

[0010] Furthermore, all data files are subject to preset expiration conditions. The data file is determined to have reached the preset expiration condition based on its creation time. When the data file reaches the preset expiration condition, an automatic deletion process (auto drop) is triggered to remove the expired data file.

[0011] Furthermore, the data in the data files is periodically backed up, and the data is split and compressed before the backup operation is performed. The processed data is then backed up to remote storage nodes, including AWS S3 object storage service and MinIO cluster storage service.

[0012] The beneficial effects of this invention are as follows: This invention significantly reduces system size and resource consumption through an embedded database and single-node deployment architecture, simplifies the deployment process, and eliminates the need for complex cluster configurations. Leveraging multi-channel caching and connection pooling, it optimizes the writing, storage, and analysis capabilities of time-series data, improving performance in OLAP scenarios. An automatic remote backup mechanism enables non-real-time disaster recovery, avoiding the complex clusters and real-time synchronization requirements of traditional disaster recovery solutions, thus reducing the construction and maintenance costs of the disaster recovery system. Simultaneously, the overall architecture is lightweight with clearly defined functional modules, reducing reliance on professional operations and maintenance personnel and significantly lowering operational complexity. It is suitable for scenarios requiring lightweight deployment, efficient analysis, and low operational costs. Attached Figure Description

[0013] Figure 1 This is a schematic diagram of the structure of the lightweight non-real-time disaster recovery OLAP database system based on DuckDB according to the present invention.

[0014] Figure 2 This is a schematic diagram of the structure of the data channel in this invention.

[0015] Figure 3 This is a schematic diagram of the connection pool structure in this invention.

[0016] Figure 4 This is a schematic diagram of the data file expiration deletion mechanism in this invention.

[0017] Figure 5 This is a schematic diagram of remote backup of data files in this invention.

[0018] The realization of the objective, functional features and advantages of the present invention will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0019] It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.

[0020] This invention provides a system data persistence layer, enabling it to perform OLAP capabilities. It offers highly efficient writing, storage, and reading of large amounts of time-series data, while also enabling lightweight deployment and non-real-time disaster recovery for easy maintenance. It addresses the limitation of DuckDB, as a single-machine embedded database, in supporting multiple applications providing external services. A complete data interaction framework is built to support the operation of all subsequent functional modules, while also meeting the needs of daily querying, maintenance, and high-frequency data writing. It is used for processing the storage, querying, and analysis of large amounts of time-series data, and is particularly suitable for applications such as the Internet of Things and industrial monitoring that require processing massive amounts of time-series data.

[0021] like Figure 1 As shown, this invention provides a lightweight, non-real-time disaster recovery OLAP database system based on DuckDB, including a server node, a web interface (web UI), and a business service client. The server node deploys an embedded DuckDB service (an open-source embedded relational database management system (RDBMS) focused on analytical OLAP scenarios, providing lightweight, high-performance, and easily integrated data analysis capabilities, running without a separate server process). Access to the DuckDB service is wrapped using the ASP.NET Core framework (ASP.NET Core is a cross-platform, open-source web application development framework used to build modern web applications, API interfaces, real-time communication services, etc.). The web interface interacts with the DuckDB service via a REST API. The business service client runs on the server node and writes data to the DuckDB service using a one-way memory-mapped file (MMF).

[0022] It should be noted that since DuckDB is a single-machine embedded database, it cannot provide external services to multiple programs, so it needs to be modified. Specifically, the ASP.NET Core framework is used to wrap access to DuckDB. The ASP.NET Core framework can be extended to provide any accessible functionality. This invention provides two data interaction methods, such as... Figure 1 As shown, specifically: Figure 1The structure shown depicts a single server node hosting the Duckdb service, whose core is an embedded Duckdb database. Two data interaction methods exist: 1. The Duckdb service provides a web interface, interacting with the service via a Representational State Transfer Application Programming Interface (REST API). This interaction is suitable for routine queries and maintenance. 2. Two business services, clientservice, also run on the server node, using the Duckdb service. These clientservices write data very frequently. To reduce inter-program communication costs, clientservice uses one-way MMF (Memory-Mapped File) technology to write data to the Duckdb service.

[0023] In one embodiment, the system further includes multiple data channels. Each data channel receives data transmitted by the client service via a memory-mapped file (MMF) and aggregates similar data generated by different client services. Each data channel corresponds to one type of data. In one embodiment, the data channel receives data concurrently and uses a spinlock mechanism to cache the data, forming a data buffer. When the amount of cached data reaches the specified buffer size, the data is appended to the data file corresponding to the data type in batches through the connection pool. Each data type corresponds to one or more data files.

[0024] It should be noted that the DuckDB service focuses solely on processing time-series data when receiving and writing data. While DuckDB itself supports concurrent read and write operations within the same process, to achieve a balance between memory utilization and write efficiency, and to realize high-concurrency, high-performance data reception and batch persistence, as shown below... Figure 2 As shown, the design is as follows: Data of the same type generated by different business service clients (such as client service1, client service2) is aggregated into the corresponding data channel. The channel can concurrently accept data and uses a high-performance spinlock mechanism to cache the data, forming a data buffer. Once the specified buffer size is reached, the data is batch-appended to the database's data files. The database's persistent files (i.e., persistent files are data files) also correspond one-to-one with data types (e.g., data 1 corresponds to data1.db).

[0025] In one embodiment, when a new data type needs to be written, a new connection is created from the connection pool and a new data file is generated. When no data is written to the data file within a set time, the corresponding database connection is closed and the corresponding resources are reclaimed.

[0026] It should be noted that DuckDB data services reuse the same connection resources for similar data, but considering performance and storage, the data is segmented and persisted based on time. The connection pool design is as follows: Figure 3 As shown, when a new data type needs to be written, a new connection is created from the connection pool and a new data file is generated; if no data is written to the data file within a certain period of time, the DuckDB service will close the connection and reclaim the corresponding resources. Combining performance and storage requirements, data is segmented and persisted according to a time dimension, in conjunction with the connection management mechanism.

[0027] In one embodiment, all data files are subject to an expiration condition. The expiration time of the data file is used to determine whether the data file has reached the preset expiration condition. When the data file reaches the preset expiration condition, an automatic deletion process (auto drop) is triggered to remove the expired data file.

[0028] It should be noted that over time, even with DuckDB's excellent data compression capabilities, large amounts of data can still result in very large data files. Therefore, it's necessary to configure a strategy to automatically delete expired data files, such as... Figure 4 As shown, the system automatically tracks the creation time and expiration conditions of all data files. When a data file reaches the preset expiration condition, it triggers an automatic deletion (auto drop) process to remove the expired data file (such as data1 <expiration date>.db), thereby controlling the size of the data file, avoiding storage bloat caused by the accumulation of massive time-series data, and reducing storage resource consumption.

[0029] In one embodiment, backup operations are performed periodically on the data in the data file. Before the backup operation is performed, the data is split and compressed, and the processed data is backed up to a remote storage node, including AWS S3 object storage service, MinIO cluster storage service, etc.

[0030] It should be noted that the system is configured to periodically perform remote backups of locally persisted data (data in data files, such as data1.db and data2.db). During backups, the data is further split and compressed to improve the reliability of transmission and storage. This not only enables disaster recovery but also expands the storage capacity of the invention. Figure 5 As shown.

[0031] The present invention will be further described in detail below using specific examples.

[0032] The implementation process of this invention is as follows: 1. Create a Docker static image using amd64 / buildpack-deps + asp.net core8.0.21. 2. Combine the program of this invention with the above static image to construct a DuckDB database service static image. 3. Run the container based on the static image. A performance comparison of write and read operations using this invention and PostgreSQL is performed, as follows: (1) The comparative test environment is as follows: Operating System: CentOS Linux release 7.9.2009 (Core) CPU: Intel(R) Xeon(R) Bronze 3204 CPU @ 1.90GHz Memory: 32GB DDR4 2133MT / s Hard Drive: 1TB HDD 7200RPM PostgreSQL: 13.8 Duckdb: 1.3.2 .NET: 8.0 (2) Test steps: ① Generate 1 million data points, divide them into 100 batches, write 10,000 data points to the database in each batch, and record the time taken each time for comparison; generate 10 million data points, divide them into 100 batches, write 100,000 data points to the database in each batch, and record the time taken each time for comparison.

[0033] ② Compare the storage size occupied.

[0034] (3) Test results: ① 100 batch writes, each writing 10,000 records: PostgreSQL average time per write: 469ms. The average time per write using the DuckDB service in this invention: 113ms.

[0035] ② 100 batch writes, each writing 100,000 records: PostgreSQL average time per write: 3548ms. The average time per write using the DuckDB service in this invention: 703ms.

[0036] ③ Space occupied (10,000,000 data entries): PostgreSQL: 965MB. DuckDB service in this invention: 104.51MB.

[0037] (4) Comparison of query efficiency: ① Efficiency comparison of simple functions: Executing `select max(occtime) from yiritech.onemorelog` respectively: PostgreSQL time: 1991ms; DuckDB service time: 68ms.

[0038] Executing `select min(occtime) from yiritech.onemorelog` took 2557ms for PostgreSQL and 79.3ms for the DuckDB service.

[0039] ② Data statistics and comparison based on time range (approximately 9,500,000 data points were counted within the time range): PostgreSQL time: 2191ms, DuckDB service time: 58.3ms.

[0040] ③ Query data based on range conditions and sort the data in multiple columns: select * from onemorelog where cardnum > 98654 and cardnum <99011order by occtime desc, cardnum asc limit 50000; PostgreSQL execution time: 1347.11ms; DuckDB service execution time: 576.22ms.

[0041] Based on the above comparison, this invention encapsulates the duckdb database, enabling it to possess the characteristics of a distributed database while retaining the original high write, query, and compression features of the duckdb database, transforming it from a single-process embedded database.

[0042] This invention significantly reduces system size and resource consumption through an embedded database and single-node deployment architecture, simplifies the deployment process, and eliminates the need for complex cluster configurations. Leveraging multi-channel caching and connection pooling, it optimizes the writing, storage, and analysis capabilities of time-series data, improving performance in OLAP scenarios. An automatic remote backup mechanism enables non-real-time disaster recovery, avoiding the complex clusters and real-time synchronization requirements of traditional disaster recovery solutions, thus reducing the construction and maintenance costs of the disaster recovery system. Simultaneously, the overall architecture is lightweight with clearly defined functional modules, reducing reliance on professional operations and maintenance personnel and significantly lowering operational complexity. It is suitable for scenarios requiring lightweight deployment, efficient analysis, and low operational costs.

[0043] It should be noted that, in this document, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, apparatus, article, or method that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, apparatus, article, or method. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, apparatus, article, or method that includes that element.

[0044] The above description is merely a preferred embodiment of the present invention and does not limit the patent scope of the present invention. Any equivalent structural or procedural transformations made based on the content of the present invention's specification and drawings, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of the present invention.

Claims

1. A lightweight, non-real-time disaster recovery OLAP database system based on DuckDB, characterized in that, This includes server nodes, web UI, and client services. The server node deploys the duckdb service, which is accessed using the ASP.NET Core framework. The web UI interacts with the duckdb service via a REST API. The client service runs on the server node and writes data to the duckdb service using a one-way memory-mapped file (MMF).

2. The lightweight, non-real-time disaster recovery OLAP database system based on DuckDB according to claim 1, characterized in that, The duckdb service deployed on the server node is an embedded duckdb database.

3. The lightweight, non-real-time disaster recovery OLAP database system based on DuckDB according to claim 1, characterized in that, It also includes multiple data channels, which receive data transmitted by the business service client through a memory-mapped file (MMF) and aggregate data of the same type generated by different business service clients. Each data channel corresponds to a data type.

4. The lightweight, non-real-time disaster recovery OLAP database system based on DuckDB according to claim 3, characterized in that, The data channel receives data concurrently and uses a spinlock mechanism to cache the data, forming a data buffer. When the amount of cached data reaches the specified buffer size, the data is appended to the data file corresponding to the data type in batches through the connection pool. Each data type corresponds to one or more data files.

5. The lightweight, non-real-time disaster recovery OLAP database system based on DuckDB according to claim 4, characterized in that, When a new data type needs to be written, a new connection is created from the connection pool and a new data file is generated. When no data is written to the data file within a set time, the corresponding database connection is closed and the corresponding resources are reclaimed.

6. The lightweight, non-real-time disaster recovery OLAP database system based on DuckDB according to claim 4, characterized in that, All data files are subject to preset expiration conditions. The system determines whether a data file has reached the preset expiration condition based on its creation time. When a data file reaches the preset expiration condition, an automatic deletion process (auto drop) is triggered to remove the expired data file.

7. The lightweight, non-real-time disaster recovery OLAP database system based on DuckDB according to claim 4, characterized in that, The data in the data file is backed up periodically. Before the backup operation is performed, the data is split and compressed, and the processed data is backed up to a remote storage node, including AWS S3 object storage service and MinIO cluster storage service.

Images