Storage method and device for providing ordered access for time series data
By partitioning and partial indexing, block indexing, dynamic sorting and data reorganization modules by acquisition device, the inefficiency of traditional databases when processing timing data is solved, and efficient orderly data access and write performance improvements are achieved.
Patent Information
- Application Number
- CN202411120153.X
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-15
- Publication Date
- 2025-01-03
AI Technical Summary
When traditional databases process massive time-series data, it is difficult to achieve efficient storage and orderly access, resulting in low query and aggregation efficiency. Especially in the Internet of Things industry, the writing efficiency of time-series data is affected by the index structure, and traditional indexes will bring about Additional overhead.
Using partition data files by acquisition equipment, establish partial indexes and block indexes, dynamic sorting modules and data reorganization modules, and determine whether the storage engine provides orderly data or sorts by the computing engine through query optimizer, reducing the impact of indexes on writing and improving Write performance.
The simplest and most effective data approximation and writing method is realized, which significantly improves the writing performance, reduces the overhead of index writing to data, and improves the efficiency of timing data processing.
Smart Images
Figure CN119248767A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of database technology, and specifically provides a storage method and device for providing ordered access to time series data. Background Art
[0002] With the development of IoT technology, IoT devices and application data are growing rapidly, and the demand for efficient storage, management and analysis of the massive data generated by IoT devices is also increasing.
[0003] Compared with the data processed by traditional databases, the data generated by IoT devices and applications has obvious time series data processing characteristics, such as:
[0004] (1) Data always contains timestamp attributes and is usually generated frequently and stably in chronological order;
[0005] (2) Huge amounts of data. For example, a sensor generates about 1.4M data in a day. A factory with tens of millions of sensors can generate 500T of data in a year. In emerging industries such as the Internet of Vehicles and digital energy, managing hundreds of millions or even billions of devices is becoming a trend.
[0006] (3) Data is updated and deleted less frequently;
[0007] (4) Queries usually use time as a filtering condition, and aggregate analysis is usually performed on the device and time dimensions.
[0008] Since traditional data processing technologies are difficult to efficiently store and analyze such large-scale data, time series data processing has emerged as a specialized data processing technology and has developed rapidly. In addition to the Internet of Things industry, time series data processing technology has also been widely used in systems monitoring, stock trading and other industries.
[0009] In time series data processing scenarios, a large amount of data access uses the time dimension as a filtering condition or aggregation condition. For example, in IoT device monitoring scenarios, the system will periodically obtain the latest generated data; in time series data display applications, the obtained data will be required to be arranged from new to old; in time series analysis scenarios, the time dimension will be aggregated at certain time intervals, etc. In these scenarios, if the time series data obtained by the computing engine from the storage engine is ordered by time, data processing will be much more efficient. The reasons are as follows:
[0010] (1) Many time series data queries require results to be sorted by time (for example, ORDER BY TIME_COL). If the time series data obtained by the computing engine from the storage engine is already sorted, there is no need to sort it again;
[0011] (2) For queries that require aggregation in the time dimension, the aggregation efficiency on ordered data sets is much higher than that on unordered data sets;
[0012] (3) For queries that require the result set to be sorted by time (for example, ORDER BY TIME_COL) and have a quantity limit (for example, LIMIT 20), if the data provided by the storage engine is already in order, the early exit mechanism can be enabled.
[0013] In summary, the time series data storage engine provides a time-sorted data set, which is effective in improving the efficiency of time series data processing. Therefore, many time series database storage engines also provide this function. There are mainly the following ways to implement this capability:
[0014] (1) Time series data is stored in a time-ordered data structure. Many databases choose to use LSM or LSM-like structures so that data can be returned in chronological order when accessed;
[0015] (2) Ordered access to data is achieved through auxiliary data structures such as indexes. Some time series databases based on traditional relational database architecture provide ordered data access by creating indexes on timestamp columns;
[0016] (3) In addition, the data storage engine can first sort the data and then return the results to the computing engine. However, due to the low efficiency of this method, few databases adopt it.
[0017] However, the characteristic of time series data is that it is generated in time. In most cases, time series data is written into the database in chronological order. In other words, sequential write storage is the most efficient way to write data and can maintain the validity of data in most cases.
[0018] Using traditional index structures will cause a lot of additional overhead for data writing. Even LSM, a storage structure that supports high-performance writing, has a certain amount of additional overhead compared to the sequential append writing method. Summary of the invention
[0019] The present invention aims at addressing the deficiencies of the above-mentioned prior art and provides a storage method for providing ordered access to time series data with strong practicality.
[0020] A further technical task of the present invention is to provide a storage device that is rationally designed, safe and applicable and provides ordered access to time series data.
[0021] The technical solution adopted by the present invention to solve its technical problem is:
[0022] A storage method for providing ordered access to time series data has the following steps:
[0023] S1, partition data files by acquisition device;
[0024] S2, out-of-order data index;
[0025] S3, dynamic sorting module;
[0026] S4, data reorganization module;
[0027] S5. Query optimizer.
[0028] Further, in step S1, a group of acquisition devices that share the same file or a group of files constitute a data partition, by pre-allocating a space for each acquisition device in the file or a group of files, and continuously appending and writing the time series data collected by the acquisition device into the pre-allocated space;
[0029] The space allocated for a device in a data partition consists of fixed-size data storage units, which are called database blocks.
[0030] When the data block pre-allocated for this acquisition device is about to be full, a new data block is pre-allocated to the storage device.
[0031] Furthermore, in step S2, it includes:
[0032] (1) Only partial indexes are created for out-of-order data;
[0033] (2) Index data blocks rather than data records.
[0034] Further, in step (1), the out-of-order data index is to index the data within the time range having out-of-order data;
[0035] In step (2), it is assumed that in most cases the time series data in the system is ordered within a data block, and only out of order between data blocks. The index item of the out of order index in the system is the data block.
[0036] Furthermore, in step S3, in most cases, the method of writing new blocks into the disordered data is adopted to ensure that the data in the data block is in order. In special cases, it is necessary to add a dynamic sorting function to sort the data in the data block with disordered data before returning it to the upper engine.
[0037] The determination of whether there is out-of-order data in a data block is completed by adding an out-of-order mark in the data block, and the mark is initialized to False; when out-of-order data is written to a data block, the mark is set to True.
[0038] Furthermore, in step S4, it includes:
[0039] S4-1, local data reorganization;
[0040] S4-2. Global data reorganization.
[0041] Furthermore, in step S4-1, the local data reorganization is responsible for reorganizing the disordered data in the data block, that is, sorting the data and writing it back to the original data block;
[0042] In step S4-2, when there are too many index items in the partial index, it means that there is a lot of disorder between blocks, and global reorganization is required, that is, all data is reordered and written back to the original data file.
[0043] Furthermore, in step S5, the optimizer determines whether the ordered data is provided by the storage or sorted by the sorting operator of the computing engine according to the following statistical information:
[0044] (1) The number of blocks that are out of order: the number of blocks in which the data in a data block is out of order relative to the previously written data blocks;
[0045] (2) The number of disordered data in the block: that is, the number of data blocks with disordered data in the data block;
[0046] (3) Total number of blocks: the total number of data blocks;
[0047] The above statistics are used to calculate the cost of providing sorted data in the storage engine and compare it with the cost of sorting in the computation engine.
[0048] A storage device for providing ordered access to time series data, comprising: at least one memory and at least one processor;
[0049] The at least one memory is used to store a machine-readable program;
[0050] The at least one processor is used to call the machine-readable program to execute a storage method that provides ordered access to time series data.
[0051] Compared with the prior art, the storage method and device for providing ordered access to time series data of the present invention have the following outstanding beneficial effects:
[0052] The present invention adopts the simplest and most effective data append writing method to realize time series data writing, and its writing performance is much better than that of a storage system based on an ordered data structure. BRIEF DESCRIPTION OF THE DRAWINGS
[0053] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.
[0054] Attached Figure 1 It is a flowchart of ordered data access in a storage method for providing ordered access to time series data;
[0055] Attached Figure 2 It is a flowchart of establishing an index for out-of-order data in a storage method that provides ordered access to time series data;
[0056] Attached Figure 3 It is a schematic diagram of data files partitioned by acquisition device in a storage method that provides ordered access to time series data;
[0057] Attached Figure 4 It is a schematic diagram of out-of-order data indexing in a storage method that provides ordered access to time series data. DETAILED DESCRIPTION
[0058] In order to enable those skilled in the art to better understand the solution of the present invention, the present invention is further described in detail below in conjunction with specific implementation methods. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.
[0059] A best embodiment is given below:
[0060] like Figure 1-2 As shown, a storage method for providing ordered access to time series data in this embodiment has the following steps:
[0061] S1, partition data files by acquisition device;
[0062] Since time series data is generated in time, in most cases time series data will be written into the database in chronological order. There are three main situations in which time series data cannot be written in chronological order:
[0063] (1) Different time settings and acquisition frequencies of different acquisition devices will cause the time sequence of time series data to be disordered when it arrives at the time series database;
[0064] (2) A collection device failure or application failure causes the data for a certain period of time to be written into the database some time after it is generated;
[0065] (3) Other reasons, such as application errors, etc.
[0066] The first reason is the main reason why the time series data cannot be written in chronological order. To address this problem, a method of partitioning storage by acquisition device can be adopted. This can be done by allocating one or a group of files to each acquisition device, and continuously appending the time series data collected by the acquisition device to the file; or by pre-allocating a space for each acquisition device in one or a group of files, and continuously appending the time series data collected by the acquisition device to the pre-allocated space. The present invention adopts the latter, and a group of acquisition devices sharing the same or a group of files constitute a data partition.
[0067] To further optimize write efficiency, the space allocated for a device in a data partition can be composed of fixed-size data storage units, which can be called data blocks to further improve efficiency.
[0068] like Figure 3 As shown, the data storage method based on the partition of the acquisition device stores the data of the six acquisition devices into two data partitions respectively. Each partition contains the data of three acquisition devices, which are stored in one file. Within the data partition, this storage method pre-allocates one or more data blocks for the data storage of each acquisition device. When the timing data of a certain acquisition device is written, the data block allocated to it will be found for data appending and writing. When the data block pre-allocated for this acquisition device is about to be full, a new data block is pre-allocated to the storage device.
[0069] S2, out-of-order data index;
[0070] Index is a common data structure that supports ordered access to data. However, due to the very high write throughput requirements for time series data, the overhead of traditional indexes on data writing is unacceptable. This paper proposes two innovations to reduce the performance impact of indexes on write and update operations:
[0071] (1) Only partial index is created for disordered data;
[0072] A partial index is an index that is created only on part of the data. In traditional databases, a partial index is an index created for data in a table that meets certain conditions.
[0073] The out-of-order data index proposed in the present invention does not define the data to be indexed according to specific conditions, but indexes the data within the time range with out-of-order data.
[0074] For example, data generated from "2024-07-01 09:00:00" to "2024-07-01 12:00:00" is continuously written into a certain time series table, and then data at "2024-07-0111:56:00" is written. In the system of the present invention, the above data will be written sequentially into the same file. When the data generated at "2024-07-01 11:56:00" is written, it is considered to be out-of-order data because its time is earlier than "2024-07-01 12:00:00", and an index will be created for the out-of-order data between "2024-07-01 11:56:00" and "2024-07-01 12:00:00". If after writing the corresponding data, the subsequent data written is later than "2024-07-01 11:56:00" but earlier than "2024-07-01 12:00:00", that is, the subsequent data belongs to a certain index time range, then the subsequent data will also be added to the partial index. If after writing the data corresponding to "2024-07-01 11:56:00", the subsequent data written is later than "2024-07-01 12:00:00", then the subsequent data will not affect the partial index.
[0075] In most scenarios, time series data is written in order, so partial indexes greatly reduce the storage space required for indexes and greatly reduce the impact of indexes on data writing and updating performance.
[0076] (2) Indexing data blocks rather than data records;
[0077] Data inserted due to application or device failure is often partially ordered. For example, when a device fails between "2024-07-01 09:00:00" and "2024-07-01 12:00:00", when the application re-enters this part of data into the database, the three hours of data re-entered into the database are often ordered. However, before this data is written, other written data is already later than "2024-07-01 12:00:00", so this data is considered out of order.
[0078] In view of this situation, the system proposed by the present invention assumes that in most cases, the time series data in the system is ordered within a data block, and only out of order between data blocks. Therefore, the index items of the out-of-order index in the system can be data blocks instead of data records. Block index can further reduce the storage space required for the index and reduce the impact of the index on data writing and updating performance.
[0079] Based on the above optimization, assuming that there are 1 billion records in a time series data table, the traditional indexing method needs to index 1 billion records, that is, 1 billion index items, and a 5-6-level B-tree structure is required to implement the index structure. Assuming that there is 1% disordered data in the table, and the data block size supporting the table is 1000 records per block, the system proposed by the present invention only needs 1,000,000,000*1% / 1000=1000 index items. Only a 2-level B-tree or even a simple array structure is needed to implement the index.
[0080] Since the block index is different from the record index, the index content of the block index is a time range, so the same block may belong to multiple index items. However, due to the characteristics of time series data discussed above, by writing new blocks in disordered data, the probability of the same block belonging to multiple index items is very small, and its overhead can be almost ignored.
[0081] like Figure 4 As shown in FIG. 1 , two out-of-order situations occur during the writing of sequential data blocks. In situation 1, the data in the time range of “10:01” to “10:20” is written after the data in the time range of “11:00” to “11:00” is written, but there is no overlap between these data blocks.
[0082] Case 2 is that the data in the time range "11:11-11:22" is written after the data in the time range "11:40" is written, and the data overlaps with the previously written data blocks. In this case, the out-of-order index will cover the data in the time range "10:01-11:40", create an index key value based on the data range of the data block, and point the index item to all data blocks that have an intersection with the key value of the index item.
[0083] S3, dynamic sorting module;
[0084] According to the characteristics of time series data, in most cases, especially after the method of "writing out-of-order data into a new block" is adopted, the data in the data block is in order. However, it is still unavoidable to have out-of-order data in the data block. For this case, it is necessary to add a dynamic sorting function to sort the data in the data block with out-of-order data before returning it to the upper-level engine. Since the amount of data in the data block is small, its sorting overhead is also small, and it basically does not affect the early exit mechanism.
[0085] To determine whether there is out-of-order data in a data block, you can add an out-of-order flag in the data block. The flag is initialized to False; when out-of-order data is written to a data block, the flag is set to True.
[0086] S4, data reorganization module;
[0087] include:
[0088] S4-1, local data reorganization;
[0089] Although the probability of disorder in a data block is very low, it is still an unnecessary overhead for repeated data access. The local data reorganization module is responsible for reorganizing the disordered data in the data block, that is, sorting the data and writing it back to the original data block.
[0090] S4-2, global data reorganization;
[0091] When there are too many index items in a partial index, it means that there is a lot of disorder between blocks, and global reorganization is required, that is, all data is reordered and written back to the original data file.
[0092] The above-mentioned "writing back the original data block" and "writing back the original data file" are not necessary. In order to increase data availability, the methods of "writing a new data block and switching" and "writing a new data file and switching" can also be used. Both are known methods and will not be described here.
[0093] S5, query optimizer;
[0094] The above methods are applicable to most time series data usage scenarios. However, there are still some scenarios where application errors or special application logic may cause more data disorder.
[0095] The optimizer proposed in the present invention can decide whether to provide ordered data by storage or by the sorting operator of the computing engine based on the following statistical information:
[0096] (1) The number of blocks that are out of order: the number of blocks in which the data in a data block is out of order relative to the previously written data blocks;
[0097] (2) The number of disordered data in the block: that is, the number of data blocks with disordered data in the data block;
[0098] (3) Total number of blocks: the total number of data blocks.
[0099] The above statistics can be used to calculate the cost of providing sorted data in the storage engine and compare it with the cost of sorting in the computation engine.
[0100] Based on the above method, a storage device for providing ordered access to time series data in this embodiment includes: at least one memory and at least one processor;
[0101] The at least one memory is used to store a machine-readable program;
[0102] The at least one processor is used to call the machine-readable program to execute a storage method that provides ordered access to time series data.
[0103] The above-mentioned specific implementations are only specific cases of the present invention. The patent protection scope of the present invention includes but is not limited to the above-mentioned specific implementations. Any technical solutions that comply with the claims of the present invention and any appropriate changes or substitutions made by ordinary technicians in the relevant technical field shall fall within the patent protection scope of the present invention.
[0104] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.
Claims
1. A storage method for providing ordered access to time series data, characterized in that: The steps are as follows: S1, partition data files by acquisition device; S2, out-of-order data index; S3, dynamic sorting module; S4, data reorganization module; S5. Query optimizer.
2. A storage method for providing ordered access to time series data according to claim 1, characterized in that: In step S1, a group of acquisition devices that share the same file or a group of files constitute a data partition, by pre-allocating a space for each acquisition device in the file or a group of files, and continuously appending and writing the time series data collected by the acquisition device into the pre-allocated space; The space allocated for a device in a data partition consists of fixed-size data storage units, which are called database blocks. When the data block pre-allocated for this acquisition device is about to be full, a new data block is pre-allocated to the storage device.
3. A storage method for providing ordered access to time series data according to claim 2, characterized in that: In step S2, it includes: (1) Only partial indexes are created for out-of-order data; (2) Index data blocks rather than data records.
4. The storage method for providing ordered access to time series data according to claim 3, characterized in that: In step (1), the out-of-order data index is to index the data within the time range with out-of-order data; In step (2), it is assumed that in most cases the time series data in the system is ordered within a data block, and only out of order between data blocks. The index item of the out of order index in the system is the data block.
5. The storage method for providing ordered access to time series data according to claim 4, characterized in that: In step S3, in most cases, the method of writing new blocks into out-of-order data is adopted to ensure that the data in the data block is in order. In special cases, it is necessary to add a dynamic sorting function to sort the data in the data block with out-of-order data before returning it to the upper-layer engine. The determination of whether there is out-of-order data in a data block is completed by adding an out-of-order mark in the data block, and the mark is initialized to False; when out-of-order data is written to a data block, the mark is set to True.
6. A storage method for providing ordered access to time series data according to claim 5, characterized in that: In step S4, it includes: S4-1, local data reorganization; S4-2. Global data reorganization.
7. The storage method for providing ordered access to time series data according to claim 6, characterized in that: In step S4-1, local data reorganization is responsible for reorganizing the disordered data in the data block, that is, sorting the data and writing it back to the original data block; In step S4-2, when there are too many index items in the partial index, it means that there is a lot of disorder between blocks, and global reorganization is required, that is, all data is reordered and written back to the original data file.
8. The storage method for providing ordered access to time series data according to claim 7, characterized in that: In step S5, the optimizer decides whether to provide ordered data by the storage or to use the sorting operator of the computing engine according to the following statistical information: (1) The number of blocks that are out of order: the number of blocks in which the data in a data block is out of order relative to the previously written data blocks; (2) The number of disordered data in the block: that is, the number of data blocks with disordered data in the data block; (3) Total number of blocks: the total number of data blocks; The above statistics are used to calculate the cost of providing sorted data in the storage engine and compare it with the cost of sorting in the computation engine.
9. A storage device for providing ordered access to time series data, characterized in that: include: at least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is configured to call the machine-readable program to execute the method according to any one of claims 1 to 8.