A high-storage query processing system based on equipment digital twin data

Abstract

The present invention relates to a high storage query processing system based on equipment digital twin data, including: a MySQL storage module for receiving query or insert requests and generating corresponding execution plans; a query optimization module for obtaining a query execution plan tree, characterizing the query execution plan tree and inputting it into a query optimization model, obtaining the best execution plan and setting an operation timestamp, and controlling the storage optimization of the model; a RocksDB and MySQL interface module for performing SQL data and key-value pair conversion; a RocksDB storage engine module for performing data query or insertion operations after receiving operation information; a storage optimization module for reading current state information and performing characterization processing, and predicting the optimal merge operation through a storage optimization network model. The present invention realizes the maximum saving of storage space for massive data streams, not only maintaining extremely high storage efficiency, but also ensuring rapid response to data reading.

Description

Technical Field

[0001] The present invention relates to the technical field of storage optimization, and in particular to a high-storage query processing system based on equipment digital twin data. Background Art

[0002] Equipment digital twin is an innovative technology application. It realizes intelligent management and optimization of the entire life cycle of equipment (including design, manufacturing, operation and maintenance, scrapping and other stages) by closely connecting physical equipment with digital models in virtual space. This technology makes full use of multi-source information such as physical models, sensor data, and operation history to build a digital image in virtual space that is highly consistent with physical equipment, namely digital twin. The data generated by equipment digital twin is of extremely high value and has a wide range of uses. First, these data support equipment status monitoring and remote operation and maintenance. By collecting and analyzing equipment operation data in real time, enterprises can promptly detect potential faults and predict maintenance needs, thereby avoiding unplanned downtime and improving production efficiency and equipment utilization. Second, these data provide an important basis for the optimal design of equipment. Designers can use digital twins for virtual testing and verification, evaluate the impact of different design schemes on equipment performance, and thus select the best solution to reduce design costs and time. In addition, equipment digital twin data can also be used in the field of training and education. By simulating real scenarios, it helps operators become familiar with equipment operation processes and improve their skills.

[0003] In the field of equipment digital twins, the KV (key-value pair) storage format has become the preferred solution for data storage due to its efficiency, flexibility, and scalability. It can quickly respond to the update requirements of real-time data in digital twin models, and achieve fast data writing and retrieval through a simple key-value pair structure. KV storage does not require pre-defining complex data table structures, and can flexibly adapt to the ever-changing data requirements in the process of equipment digital twins, providing strong support for the dynamic adjustment and optimization of models. At the same time, its strong scalability ensures that as the scale of equipment digital twins continues to expand, data storage capabilities can be seamlessly expanded to meet storage requirements under large data volumes. These advantages together constitute the unique charm of KV in data storage in the field of equipment digital twins.

[0004] RocksDB is a high-performance, embedded key-value storage database, which was improved and extended by Facebook based on Google's LevelDB. RocksDB uses the Log-Structured Merge-Tree (LSM-Tree) structure, which makes it perform well when handling write operations. LSM-Tree reduces the number of disk writes by writing data to memory and periodically writing data in batches to disk, thereby improving write performance. RocksDB supports multi-core parallel computing, which means that it can make full use of the power of modern multi-core processors to further improve read and write performance. The technical features of RocksDB include support for data compression, write batching, and efficient read-write amplification control. Data compression can significantly reduce the use of disk space while maintaining high read and write performance. Write batching reduces the overhead of each write operation by merging multiple write operations into one transaction. Read-write amplification control reduces the number of actual read and write operations and improves overall performance by optimizing data structures and algorithms. The storage advantage of RocksDB lies in its high-throughput write operations and low-latency read operations, which is particularly suitable for application scenarios that require high-concurrency reads and writes and large-scale data storage. It has excellent performance in disk space utilization and storage performance, and can effectively support applications such as cache systems, message queues, and time series databases. In addition, RocksDB provides a wealth of configuration options and tuning parameters, allowing developers to optimize according to specific application requirements to achieve optimal performance. In short, RocksDB has become an ideal choice for modern high-performance storage systems with its excellent technical features and storage advantages.

[0005] In complex application scenarios with twin data storage, although RocksDB is known for its high performance and scalability, its read-write amplification problem has become a challenge that cannot be ignored. RocksDB is a high-performance key-value storage engine based on the LSM-Tree (Log-Structured Merge-Tree) architecture. Its write operation will first write data to the WAL (Write Ahead Log) and the MemTable in memory. When the MemTable reaches a certain threshold, its content will be flushed to the disk to generate an SST (Sorted String Table) file. These SST files are stored in layers according to the key value range of the data and merged in the background to optimize query performance. During the data writing process, since the data needs to be copied to multiple SST files and merged in the background, this may cause the amount of data actually written to the disk to be much larger than the amount of data written by the user, that is, write amplification. This mechanism brings about the problem of read-write amplification. In the process of high-concurrency data writing, RocksDB has a significant write amplification phenomenon, that is, the amount of data actually written to the disk far exceeds the amount of data written by the user, which leads to a waste of disk space and increases the burden of disk I / O. More disk space is used to store redundant data, which is particularly disadvantageous in equipment twin data storage, because equipment twin systems usually need to store a large amount of historical data and real-time data, and the storage write amplification will grow geometrically at this time. In addition, read-write amplification may also cause fluctuations in read performance. Due to the merging and hierarchical structure of SST files, read operations may need to cross multiple levels and files to retrieve data, increasing the complexity and latency of queries. Summary of the invention

[0006] In order to solve the problems existing in the above-mentioned prior art, the purpose of the present invention is to propose an efficient storage and query processing solution for equipment digital twin data, targeting the massive, real-time device data stream generated by digital twin technology and the high read-write amplification problem of RocksDB under high concurrency. The solution aims to solve the read-write amplification problem of RocksDB, maximize the savings of storage space, and maintain extremely high storage efficiency while ensuring rapid response to data reading.

[0007] To achieve the above object, the present invention provides the following solutions:

[0008] A high-storage query processing system based on equipment digital twin data, comprising:

[0009] A MySQL storage module, configured to receive a query processing request or a data insertion request, and when receiving the query processing request, obtain an execution plan for the query processing request; when receiving the data insertion request, generate an execution plan for the data insertion request;

[0010] A query optimization module, configured to obtain a query execution plan tree when receiving the query processing request, input the query execution plan tree into a query optimization model, obtain an optimal query plan tree, generate an execution plan for the query processing request based on the optimal query plan tree, and set a timestamp flag when receiving the query processing request;

[0011] The RocksDB and MySQL interface module is used to, when receiving the execution plan of the query processing request, convert the execution plan of the query processing request into a data query operation, convert the key-value pair data, obtain the row data, send the row data to the MySQL storage module, and generate a query result; when receiving the execution plan of the data insertion request, convert the execution plan of the data insertion request into a data insertion operation;

[0012] A RocksDB storage engine module, for, when receiving a data query operation, performing data query according to the data query operation and generating the key-value pair data, and when receiving a data insert operation, performing data insert according to the data insert operation;

[0013] The storage optimization module is used to obtain current storage status information according to the timestamp mark, input the current storage status information into the storage optimization model, obtain the optimal layer storage merger operation cost, and perform storage optimization through the optimal layer storage merger operation cost.

[0014] Furthermore, the query optimization module includes:

[0015] A query plan monitoring and interception unit, used to receive the query processing request, detect the query processing event type, and thus obtain a query execution plan tree;

[0016] A custom optimization tree exploration and setting unit is used to set the optimize_switch parameter setting set as the optimization tree exploration direction, optimize the query execution plan tree using the MySQL optimizer, and generate a corresponding optimization exploration query plan;

[0017] The query plan tree information extraction unit is used to extract information from the optimized exploration query plan, convert the information extraction result into an optimized exploration tree in JSON format, and collect the optimized exploration trees to form an optimized forest in JSON format.

[0018] Furthermore, the query optimization module also includes:

[0019] A query plan tree feature extraction unit is used to parse the table information of the optimization forest, perform feature processing according to the original logical structure of the query execution plan tree, and introduce the optimize_switch state feature to generate an optimization forest feature tensor; the feature processing is: the feature of the query plan node is used as the first dimension, the query plan tree is used as the second dimension, and the query plan forest is used as the third dimension, and when the feature vectors are connected, it is determined that the dimensions of each feature vector are consistent;

[0020] The neural network processing unit is used to input the optimized forest feature tensor into the query optimization model for cost prediction, obtain the best query plan tree, and generate an execution plan for the query processing request based on the best query plan tree.

[0021] Furthermore, the query optimization module also includes:

[0022] A query experience storage unit, used to save JSON format information, query time and optimize_switch parameter setting set, generate a training set, back-propagate the training set, train the query optimization model, and optimize the parameters of the query optimization model;

[0023] The network model retraining unit is used to manage the amount of experience stored in the query experience storage unit, determine whether the amount of experience stored in the query experience storage unit reaches a preset threshold, and if so, retrain the query optimization model.

[0024] Furthermore, the query plan tree information extraction unit includes:

[0025] A query plan tree information extraction subunit is used to extract information from the query execution plan tree; wherein the extracted information includes: cardinality information and latency information of each node of the query execution plan tree;

[0026] The json format conversion subunit is used to obtain the execution time and row cardinality of each table in the query execution plan tree according to the cardinality information and latency information of each node in the query execution plan tree, and compile it into json format information, traverse the query plan sequentially from the root node, recursively call the conversion function, and output the json format plan tree information.

[0027] Furthermore, the storage optimization module includes:

[0028] A storage optimization model starting unit, used for updating the timestamp mark during the data query operation or the data insertion operation, starting the storage optimization operation threshold, and obtaining the current storage state information when the timestamp mark reaches the storage optimization operation threshold;

[0029] A query plan time receiving unit, used for obtaining the execution time of the data query operation when receiving the data query operation;

[0030] The storage status information acquisition unit is used to collect the current SSTable storage information and SSTable status information when receiving a data query operation, that is, the number and size of SSTable data results, the size and number of data blocks in the SSTable data results, the number of deletion marks, and the overlap information.

[0031] Furthermore, the storage optimization module also includes:

[0032] A storage state feature extraction unit is used to read the stored experience records from the experience record table, use the SSTable storage information and SSTable state information in the experience records as one-dimensional feature vectors, and use the execution time of the data query operation as an edge extension feature for feature processing to generate a storage state feature tensor; the feature processing is: use the storage state feature of each layer as the first dimension, the number of SSTable layers as the second dimension, and when the feature vectors are connected, determine that the dimensions of each feature vector are consistent;

[0033] The neural network processing unit is used to input the storage state feature tensor into the storage optimization model to obtain the merging cost of each layer of current storage.

[0034] Furthermore, the storage optimization module also includes:

[0035] The Merge operation unit is used to select the minimum merging cost of each storage layer according to the predicted merging costs of multiple storage layers, and use the LUR strategy to merge the data blocks of the SSTable data structure in the storage layer with the data blocks of the SSTable data structure in the next layer involving the same data;

[0036] Optimize the experience storage unit to record the execution time, SSTable storage information and SSTable status information of the data query operation, merge them into tuples, insert them into the experience record table, and generate a training set;

[0037] The network model retraining unit is used to manage the amount of experience stored in the optimization experience storage unit, determine whether the amount of experience stored in the optimization experience storage unit reaches a preset threshold, and if so, perform a retraining operation on the query optimization model.

[0038] The beneficial effects of the present invention are:

[0039] This invention innovatively proposes a high-storage query processing system based on equipment digital twin data. The system cleverly combines the deep optimization strategy of the query plan tree, the integration of the advantages of relational databases and key-value databases, and the refined operation of SSTable merging technology, aiming to fundamentally weaken the built-in write amplification effect of RocksDB, thereby significantly improving the storage and reading performance of the system.

[0040] In the face of the challenge of massive, real-time device data streams generated by digital twin technology, the present invention can not only maximize the savings in storage space, but also ensure rapid response to data reading while maintaining extremely high storage efficiency. Through this series of precisely designed optimization measures, the present invention not only solves the performance bottleneck problem faced by traditional databases when processing such data, but also opens up a new and efficient path for data management in the field of equipment digital twins. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] In order to more clearly illustrate the technical solutions in this embodiment or the prior art, the drawings required for use in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0042] Figure 1 This is an architecture diagram of a high-storage query processing system based on equipment digital twin data in this embodiment;

[0043] Figure 2 This is a flowchart of processing query requests in this embodiment;

[0044] Figure 3 The processing insert request flow chart of this embodiment;

[0045] Figure 4 This is a query optimization model framework diagram of this embodiment;

[0046] Figure 5 This is a flow chart of the query optimization model of this embodiment;

[0047] Figure 6 This is a storage optimization model framework diagram of this embodiment;

[0048] Figure 7 This is a flow chart of the storage optimization model of this embodiment. DETAILED DESCRIPTION

[0049] The following will be combined with the drawings in this embodiment to clearly and completely describe the technical solution in this embodiment. Obviously, the described embodiment is only a part of the embodiment of the present invention, not all of the embodiments. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.

[0050] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, the present invention is further described in detail below with reference to the accompanying drawings and specific embodiments.

[0051] The present embodiment discloses a high-storage query processing system based on equipment digital twin data, including: a MySQL storage module, which is used to receive a query processing request or a data insertion request, and when a query processing request is received, an execution plan of the query processing request is obtained; when a data insertion request is received, an execution plan of the data insertion request is generated; a query optimization module, which is used to obtain a query execution plan tree when a query processing request is received, input the query execution plan tree into a query optimization model, obtain an optimal query plan tree, and generate an execution plan for the query processing request according to the optimal query plan tree, and when a query processing request is received, a timestamp flag is set; a RocksDB and MySQL interface module, which is used to input the query execution plan tree into a query optimization model when a query processing request is received, obtain an optimal query plan tree, and generate an execution plan for the query processing request according to the optimal query plan tree, and when a query processing request is received, a timestamp flag is set; and a RocksDB and MySQL interface module, which is used to input the query execution plan tree into a query optimization model when a query processing request is received. The execution plan of the request is converted into a data query operation, and the key-value pair data is converted to obtain row data, and the row data is sent to the MySQL storage module to generate query results; when the execution plan of the data insertion request is received, the execution plan of the data insertion request is converted into a data insertion operation; the RocksDB storage engine module is used to perform data query according to the data query operation when a data query operation is received, and generate key-value pair data; when a data insertion operation is received, the data is inserted according to the data insertion operation; the storage optimization module is used to obtain the current storage status information according to the timestamp mark, input the current storage status information into the storage optimization model, obtain the optimal layer storage merge operation cost, and perform storage optimization through the optimal layer storage merge operation cost.

[0052] Specifically, the main solution of this embodiment is: obtain the query plan tree generated by the SQL query statement; set the query optimization tree exploration direction; extract the query optimization tree feature vector; predict the query cost of each optimization tree in the query optimization forest; generate an execution plan for the optimization tree with the smallest query cost and convert it into a RocksDB operation and send it to the RocksDB storage engine; obtain the query plan execution time and SSTable storage and status information; trigger the storage optimization operation under low I / O operation of the device; extract the storage status information feature vector; predict the storage optimization cost of each layer of SSTable; and perform the layer merge operation with the smallest storage optimization cost.

[0053] In this embodiment, the following is explained with equipment digital twin data as the processing subject:

[0054] In complex application scenarios equipped with twin data storage, although RocksDB is known for its high performance and scalability, its read and write amplification problem has become a challenge that cannot be ignored.

[0055] RocksDB is a high-performance key-value storage engine based on the LSM-Tree (Log-Structured Merge-Tree) architecture. Its write operation will first write data to the WAL (Write Ahead Log) and the MemTable in memory. When the MemTable reaches a certain threshold, its content will be flushed to the disk to generate an SST (Sorted String Table) file.

[0056] These SST files are stored in layers according to the key value range of the data and merged in the background to optimize query performance. During the data writing process, since the data needs to be copied to multiple SST files and merged in the background, this may cause the amount of data actually written to the disk to be much larger than the amount of data written by the user, which is called write amplification.

[0057] This mechanism brings about the problem of read-write amplification. In the process of high-concurrency data writing, RocksDB has a significant write amplification phenomenon, that is, the amount of data actually written to the disk far exceeds the amount of data written by the user, which leads to a waste of disk space and increases the burden of disk I / O. More disk space is used to store redundant data, which is particularly disadvantageous in equipment twin data storage, because equipment twin systems usually need to store a large amount of historical data and real-time data, and the storage write amplification will grow geometrically at this time. In addition, read-write amplification may also cause fluctuations in read performance. Due to the merging and hierarchical structure of SST files, read operations may need to cross multiple levels and files to retrieve data, increasing the complexity and latency of queries.

[0058] The present invention provides a high-storage query processing system based on equipment digital twin data. The system cleverly integrates the deep optimization strategy of the query plan tree, the integration of the advantages of relational database and key-value database, and the refined operation of SSTable merging technology, effectively and fundamentally weakening the built-in write amplification effect of RocksDB and significantly improving the storage and reading performance of the system.

[0059] Based on this, this embodiment provides a high-storage query processing system based on equipment digital twin data, such as Figure 1 shown.

[0060] In this embodiment, the system architecture of the optimization device model includes:

[0061] MySQL storage system. The MySQL version should be 8.0 or later and deployed on the Linux system.

[0062] In this embodiment, the functions of the MySQL storage system include: receiving data query and data insertion requests from digital twin devices; processing data query requests and generating a query plan tree; and generating a corresponding execution plan based on the operation request type.

[0063] It should be noted that when installing the above MySQL system, the plugin.h plug-in file should be included.

[0064] Query optimization model, the query optimization model interface is written in C language. The following is the pseudo code of the model:

[0065] static int rewrite_query_notify(MYSQL_THD thd, mysql_event_class_tevent_class,

[0066] const void* event) {

[0067] if (event_class == MYSQL_AUDIT_QUERY_CLASS) {

[0068] const struct mysql_event_query* query_event =

[0069] (const struct mysql_event_query*)event;

[0070] if (query_event->event_subclass == MYSQL_AUDIT_QUERY_STMT_

[0071] PREPARED&&var_enable_bao) {

[0072] if (determine whether it is an explanation statement) {

[0073] Declare and initialize variables;

[0074] Join query optimization model;

[0075] for (int i = 0; i <var_bao_num_arms + 1; i++) {

[0076] Set optimize_switch parameters;

[0077] Generate an optimized execution plan based on the parameters;

[0078] }

[0079] Get storage engine cache information;

[0080] Send json information to the optimization model;

[0081] Read optimization results;

[0082] }

[0083] }

[0084] }

[0085] }

[0086] In this embodiment, the query optimization model functions include: obtaining the query plan tree generated by the MySQL built-in query optimizer; setting the query optimization tree exploration direction and selecting the optimize_switch parameter setting set; generating the query plan tree under each parameter setting set; extracting the query optimization forest and each optimization tree feature tensor; predicting the query cost of each optimization tree in the query optimization forest; and selecting the query plan tree with the smallest query optimization cost.

[0087] It should be noted that the module uses the MySQL audit plug-in to obtain the internal execution status of MySQL and notify the execution of optimization operations; and sets the optimization operating system variables to control the life status of the module within MySQL.

[0088] In actual applications, after the module is installed, it needs to be compiled and linked to achieve the expansion of MySQL.

[0089] RocksDB and MySQL interface module, which has been implemented by the Facebook team and can be downloaded from GitHub.

[0090] In this embodiment, the functions of the RocksDB and MySQL interface module include: when executing data query or data insertion operations, converting the execution plan generated by MySQL into corresponding RocksDB operations; when reading data in the RocksDB storage engine, converting key-value pair data into row data that MySQL can recognize.

[0091] It should be noted that after downloading the RocksDB engine interface, you need to first download and install the built-in requirement module, and then compile and install the interface module.

[0092] RocksDB storage engine. In this embodiment, the RocksDB storage engine functions include: actually storing the data generated by the digital twin, and performing hierarchical storage according to the key value range of the data.

[0093] It should be noted that RocksDB's hierarchical storage mechanism is the root cause of the write amplification problem. The lower the level, the more redundant data will be stored, resulting in a large waste of disk space and a reduction in data reading speed.

[0094] Storage optimization model. It should be noted that the storage optimization model is written in Python interpreter, and the version is 3.9 or above. The following is the pseudo code of the model:

[0095] class OptimizeStorager:

[0096] def __init__(self):

[0097] Initializing member variables

[0098] def load(self, path):

[0099] Model path loading;

[0100] def save(self, path):

[0101] Model path save;

[0102] def fit(self, x, y):

[0103] Characterize sample datasets;

[0104] Process the data set in batches;

[0105] Initialize the network model;

[0106] Set model hyperparameters;

[0107] Set up the model optimizer;

[0108] Set the model loss function;

[0109] for epoch in range(100):

[0110] Train the model;

[0111] If the accuracy is up to standard:

[0112] Break

[0113] else:

[0114] Output information;

[0115] def predict(self, x):

[0116] Data characterization processing;

[0117] Call the model forward function to predict;

[0118] Invokes reverse feature processing.

[0119] In this embodiment, the storage optimization model functions include: obtaining query plan execution time and SSTable storage and status information; triggering storage optimization operations under low I / O operations of the device; extracting storage status information feature vectors; predicting the storage optimization cost of each layer of SSTable; and performing layer merge operations with the lowest storage optimization cost.

[0120] It should be noted that when the device performs a query operation, it records the execution time of the query operation and the timestamp when the operation is executed, and sends them to the storage optimization model; when the device performs an insert operation, it records and updates the timestamp when the operation is executed, and then sends it to the storage optimization model.

[0121] Based on the device architecture diagram of this embodiment, please refer to Figure 2 , Figure 2 This is a flow chart of query request processing by a device in an embodiment of the device optimization method of the present invention.

[0122] In this embodiment, the process of processing the query request by the device includes steps S101 to S108:

[0123] Step S101: The MySQL storage system receives a query request;

[0124] Step S102: the query optimization model intercepts the query plan tree;

[0125] Step S103: The query optimization model rewrites the query plan tree.

[0126] It should be noted that rewriting the query plan tree requires that the parameter setting set be set in advance, and the MySQL query optimizer needs to be re-invoked to generate a query plan tree after the setting set is written.

[0127] Step S104: the MySQL storage system sends an execution plan;

[0128] RocksDB and MySQL interface module step S105: RocksDB and MySQL interface module operation conversion;

[0129] RocksDB and MySQL interface module Step S106: RocksDB storage engine executes the query.

[0130] It should be noted that at this point, the data processing operation has been transferred to the RocksDB storage engine for processing. If a query operation is performed, it will first try to find the target key-value pair in the MemTable in memory. If the target key-value pair is not found in the MemTable, the read operation will then search in the BlockCache. If the target key-value pair is not found in the BlockCache, the read operation will turn to the SSTable file on the disk for query, and it may be necessary to traverse the SSTable file in multiple levels to find the target key-value pair.

[0131] Step S107: RocksDB and MySQL interface module result conversion;

[0132] RocksDB and MySQL interface module Step S108: The MySQL storage system returns the result.

[0133] The above is the overall process of the device processing data query embodiment. The query optimization processing is mainly reflected in the optimization of the query plan. By optimizing the query plan parameter part, the data query time is reduced.

[0134] Based on the device architecture diagram of this embodiment, please refer to Figure 3 , Figure 3 This is a flow chart of processing insertion requests by a device in an embodiment of the device optimization method of the present invention.

[0135] In this embodiment, the process of processing the insertion request by the device includes steps S109 to S115:

[0136] RocksDB and MySQL interface module Step S109: The MySQL storage system receives the insert request;

[0137] RocksDB and MySQL interface module step S110: query optimization model to set timestamp flag.

[0138] It should be noted that the recorded flag is the timestamp of the current execution operation, and intensive flag update operations will represent the current operation state of the device, that is, it is in a high-concurrency I / O operation.

[0139] Step S111: the MySQL storage system sends an execution plan;

[0140] Step S112: RocksDB and MySQL interface module operation conversion;

[0141] Step S113: The RocksDB storage engine performs insertion.

[0142] It should be noted that the storage mechanism of RocksDB is that data is first written to WAL to ensure data persistence, and then the data is inserted into MemTable. When MemTable reaches a certain size, a new SSTable file is generated. The SSTable files on the disk are organized into multiple levels (Level-0, Level-1,...), and the number and size of data files at each level gradually increase, and the sorting method gradually tends to be completely sorted by key.

[0143] Step S114: Check whether the flag reaches the threshold.

[0144] It should be noted that the reduction in the flag update frequency will indicate that the device has left the high-concurrency I / O operation mode. At this time, the storage optimization operation of the execution device will not have a significant impact on the device query storage operation.

[0145] Step S115: If the optimization conditions are met, the storage optimization model performs storage optimization.

[0146] The above is the overall process of the device processing data insertion embodiment. The storage optimization processing is mainly reflected in optimizing the storage hierarchy structure, mainly by reducing the write amplification problem from the root.

[0147] Furthermore, the query optimization module includes: a query plan monitoring and interception unit, which is used to receive query processing requests, detect query processing event types, and thus obtain a query execution plan tree; a custom optimization tree exploration and setting unit, which is used to set the optimize_switch parameter setting set as the optimization tree exploration direction, optimize the query execution plan tree using the MySQL optimizer, and generate a corresponding optimization exploration query plan; a query plan tree information extraction unit, which is used to extract information from the optimization exploration query plan, convert the information extraction result into an optimization exploration tree in json format, and collect each optimization exploration tree to form an optimization forest in json format.

[0148] Furthermore, the query optimization module also includes: a query plan tree feature extraction unit, which is used to parse the table information of the optimization forest, perform feature processing according to the original logical structure of the query execution plan tree, and introduce the optimize_switch state feature to generate an optimization forest feature tensor; the feature processing is: the feature of the query plan node is used as the first dimension, the query plan tree is used as the second dimension, and the query plan forest is used as the third dimension, and when the feature vectors are connected, the size of the dimensions of each feature vector is determined to be consistent; a neural network processing unit, which is used to input the optimization forest feature tensor into the query optimization model for cost prediction, obtain the best query plan tree, and generate an execution plan for the query processing request based on the best query plan tree.

[0149] Furthermore, the query optimization module also includes: a query experience storage unit, which is used to store json format information, query time and optimize_switch parameter setting set, generate a training set, back-propagate the training set, train the query optimization model, and optimize the parameters of the query optimization model; a network model retraining unit, which is used to manage the number of experience storages in the query experience storage unit, determine whether the number of experience storages in the query experience storage unit reaches a preset threshold, and if it reaches the preset threshold, retrain the query optimization model. In this embodiment, the target format adopts the json format.

[0150] Furthermore, the query plan tree information extraction unit includes: a query plan tree information extraction sub-unit, which is used to extract information from the query execution plan tree; wherein the extracted information includes: cardinality information and latency information of each node of the query execution plan tree; a json format conversion sub-unit, which is used to obtain the execution time and row cardinality of each table in the query execution plan tree according to the cardinality information and latency information of each node of the query execution plan tree, and write it into json format information, traverse the query plan sequentially from the root node, recursively call the conversion function, and output json format plan tree information.

[0151] Specifically, based on the device architecture diagram of this embodiment, please refer to Figure 4 , Figure 4 This is a device query optimization model framework diagram of an embodiment of the device optimization method of the present invention.

[0152] In this example, the model structure of the query optimization model includes:

[0153] RocksDB and MySQL interface module: SQL query plan monitoring and interception module. In this embodiment, the RocksDB and MySQL interface module functions include: accessing the MySQL storage system, monitoring the internal operation execution status; and obtaining the query plan tree generated by the internal optimizer when executing the query operation.

[0154] It should be noted that the module audits the occurrence of query events through event_notify and sets MySQL system variables through the st_mysql_sys_var structure.

[0155] Query plan tree information extraction module. In this embodiment, the query plan tree information extraction module functions include: obtaining the execution time and row cardinality of the nodes in the plan tree, and writing them into json format information; traversing the query plan tree from the root node in pre-order order, recursively calling the conversion function, and outputting the plan tree information in json format.

[0156] It should be noted that module conversion can filter out useless information and improve operational efficiency.

[0157] Query optimization model startup module. In this embodiment, the query optimization model startup module functions include: starting the query optimization service model; receiving the query plan tree json format information to be optimized;

[0158] It should be noted that the optimization model can be set as a separate service and deployed in other places outside the storage device to improve the performance of the storage device.

[0159] Model function management module. In this embodiment, the functions of the model function management module include: controlling the forward prediction and reverse fitting operations of the query optimization model.

[0160] Customized optimization tree exploration and optimize_switch setting module. In this embodiment, the functions of the customized optimization tree exploration and optimize_switch setting module include: customizing the parameter state set according to the correlation between optimize_switch parameters; parameter setting is the optimization tree exploration direction, and regenerating the query plan tree of each parameter set according to the parameter setting.

[0161] It should be noted that the step of regenerating the query plan forest includes sending the generated parameter set to the MySQL query optimizer for parameter setting, regenerating the query optimization tree under each parameter setting, passing through the query plan tree information extraction module again, and returning it to the query optimization model.

[0162] Query plan feature extraction module. In this embodiment, the query plan feature extraction module includes: parsing the query plan tree or optimization forest for node information; performing feature processing according to the original logical structure of the tree or forest; inserting optimize_switch state features to generate query plan tree or optimization forest feature tensors;

[0163] It should be noted that during the characterization process, the characteristics of the query plan node are used as the first dimension, the query plan tree is used as the second dimension, and the query plan forest is used as the third dimension. When the feature vectors are connected, the dimensions of each feature vector are kept consistent.

[0164] Neural network processing module. In this embodiment, the functions of the neural network processing module include: creating a deep learning neural network model and initializing parameters; inputting the optimized forest feature tensor, predicting the best query plan tree and optimizing tree settings through model convolution, pooling and full connection; training the collected query experience training set, and tuning the model parameters through back propagation;

[0165] It should be noted that when performing the feature convolution operation, the features are collected along the third dimension of the feature tensor, and a feature vector composed of the features of each planned tree in the optimization forest will be obtained.

[0166] Query experience storage module. In this embodiment, the query experience storage module functions include: recording data query results, including SQL statements, query time, optimize_switch parameter records, and generating training sets;

[0167] Network model retraining module. In this embodiment, the network model retraining module functions include: managing the number of experience storages, and determining whether to perform a retraining operation on the neural network model based on the number of records.

[0168] In this embodiment, a query optimization model is provided. The model adopts the DQN method, combines reinforcement learning with deep learning, and uses some low-correlation experience to strengthen learning to improve the parameters of the deep neural network model to achieve a relatively ideal optimization model. This model writes a MySQL audit plug-in and intercepts the MySQL query plan tree to optimize the plan tree, so as to achieve the effect of improving data reading speed.

[0169] Based on the query optimization model framework diagram of the device in this embodiment, please refer to Figure 5 , Figure 5 This is a flow chart of a device query optimization model in an embodiment of the device optimization method of the present invention.

[0170] In this embodiment, the model processing query optimization process includes steps S201 to S212:

[0171] Step S201: Check whether the query optimization model is started.

[0172] It should be noted that the query optimization model is independently deployed as a service. Checking whether the query optimization model is started includes checking whether the optimization model service is started and whether the MySQL system variables are set to start.

[0173] Step S202: Start the model and receive information.

[0174] It should be noted that after starting the model, it continuously monitors information through the socket.

[0175] Step S203: intercepting the query plan;

[0176] Step S204: remove redundant information and send it to the model;

[0177] Step S205: Select an operation according to the received information.

[0178] It should be noted that step S202 sets the operation type information in json format, connects it with the query plan tree in json format and sends it to the information receiving module, and step S205 selects the operation to be executed according to the received operation type information.

[0179] Step S206: Check whether the operation type is a prediction operation or a fitting operation;

[0180] Step S207: generating an optimized forest;

[0181] Step S208: extracting optimized forest features;

[0182] Step S209: Model prediction.

[0183] It should be noted that the result of model prediction is the feature vector of the query optimization forest. Each feature value is the query cost feature of each query optimization tree. The optimal query plan tree and its parameter settings are obtained according to the index of the minimum value in the feature vector.

[0184] Step S210: experience storage;

[0185] RocksDB and MySQL interface module Step S211: Check whether the experience number reaches a threshold.

[0186] It should be noted that by setting the threshold, the model parameter update is automatically called, eliminating the need for manual call execution operations.

[0187] Step S212: training the model.

[0188] The above is the overall process of the device query optimization model implementation example, which optimizes the query speed by auditing the occurrence of query events, optimizing the parameter settings in the query plan, and rewriting the query plan tree.

[0189] Furthermore, the storage optimization module includes: a storage optimization model startup unit, which is used to update the timestamp flag, start the storage optimization operation threshold, and obtain the current storage status information when the timestamp flag reaches the storage optimization operation threshold; a query plan time receiving unit, which is used to obtain the execution time of the data query operation when a data query operation is received; a storage status information acquisition unit, which is used to collect the SSTable storage information and SSTable status information in the current situation when a data query operation is received, that is, the number and size of SSTable data results, the size and number of data blocks in the SSTable data results, the number of deletion marks, and the overlap information.

[0190] Furthermore, the storage optimization module also includes: a storage state feature extraction unit, which is used to read the stored experience records from the experience record table, use the SSTable storage information and SSTable state information in the experience record as a one-dimensional feature vector, and use the execution time of the data query operation as an edge extension feature for feature processing to generate a storage state feature tensor; the feature processing is: use the storage state feature of each layer as the first dimension, the number of SSTable layers as the second dimension, and when the feature vectors are connected, determine that the dimensions of each feature vector are consistent; a neural network processing unit, which is used to input the storage state feature tensor into the storage optimization model to obtain the merging cost of each current storage layer.

[0191] Furthermore, the storage optimization module also includes: a Merge operation unit, which is used to select the minimum merging cost of each storage layer according to the predicted merging costs of multiple storage layers, and use the LUR strategy to merge the data blocks of the SSTable data structure in the storage layer with the data blocks of the SSTable data structure in the next layer involving the same data; an optimized experience storage unit, which is used to record the execution time, SSTable storage information and SSTable status information of the data query operation, and merge them into tuples, insert them into the experience record table, and generate a training set; a network model retraining unit, which is used to manage the number of experience storages in the optimized experience storage unit, and determine whether the number of experience storages in the optimized experience storage unit reaches a preset threshold. If the preset threshold is reached, the query optimization model is retrained.

[0192] Specifically, based on the device architecture diagram of this embodiment, please refer to Figure 6 , Figure 6 This is a framework diagram of a device storage optimization model in an embodiment of the device optimization method of the present invention.

[0193] In this embodiment, the model structure of the storage optimization model includes modules:

[0194] RocksDB and MySQL interface module: storage optimization model startup module.

[0195] In this embodiment, the functions of the RocksDB and MySQL interface modules include: when the device performs data query operations or data insertion operations, setting and updating the timestamp record flag; setting a threshold for starting storage optimization operations, and when the timestamp flag reaches the threshold, calling the device storage optimization operation.

[0196] Query plan time receiving module. In this embodiment, the query plan time receiving module functions include: when the device executes a data query operation, the module is called to collect the execution time of the device data query operation.

[0197] It should be noted that the main function of this module is notified through the audit_null_notify function in the MySQL audit plug-in.

[0198] Model operation management module. In this embodiment, the model operation management module functions include: controlling the forward prediction and reverse fitting operations of the storage optimization model.

[0199] Storage state feature extraction module. In this embodiment, the functions of the storage state feature extraction module include: reading the stored experience records from the optimization experience table; recording the storage information and state information of each layer of SSTable as a one-dimensional feature vector, connecting the feature vectors of each layer; setting edge extension for the connected feature tensor, and recording the query time as the edge extension feature.

[0200] It should be noted that during the featurization process, the storage state characteristics of each layer are used as the first dimension, the number of SSTable layers is used as the second dimension, and when the feature vectors are connected, the dimensions of each feature vector are kept consistent.

[0201] Neural network processing module. In this embodiment, the functions of the neural network processing module include: creating a deep learning neural network model and initializing parameters; inputting the storage state feature tensor, and predicting the cost of the current storage state layer merge operation through model convolution, pooling and full connection; and tuning the model parameters through back propagation based on the training set set of collected storage optimization experience;

[0202] It should be noted that at the end of the neural network model, a fully connected layer is set, and the connection vector is the estimated cost of each layer merge. When data moves from Level-0 to Level-1, assuming that Level-0 has N files, the size of each file is M, and the time complexity of the merge is approximately O(N * M); in the merge from Level-N to Level-N+1, assuming that the number of SSTable files in Level-N is N, the number of files in Level-N+1 is N', the size of each file is M, and the time complexity of the merge is approximately O(N * log(N')).

[0203] SSTable storage information and status information acquisition module. In this embodiment, the functions of the SSTable storage information and status information acquisition module include: when the query plan time receiving module is called, the module is called to collect the storage information and status information of the current SSTable through the RocksDB third-party tool;

[0204] Optimization experience storage module. In this embodiment, the functions of the optimization experience storage module include: recording the device storage optimization results and query time and storage status results, including data query time, SSTable storage information, SSTable status information, and generating a training set.

[0205] It should be noted that when the device performs a data query operation, it records the execution time of the operation and sends a signal to the SSTable storage information and status information acquisition module; after receiving the signal, the SSTable storage information and status information acquisition module calls the RocksDB built-in function rocksdb::DB::GetProperty and the sst_dump third-party tool to obtain the device storage status information; the obtained execution time information and device storage status are merged into a tuple, which are inserted into the experience record table together.

[0206] Network model retraining module. In this embodiment, the network model retraining module functions include: managing the number of experience storages, and determining whether to perform a retraining operation on the neural network model based on the number of records;

[0207] Merge operation module: In this embodiment, the functions of the Merge operation module include: selecting the minimum layer storage merging cost according to the predicted layer storage merging cost, and performing the Merge operation of the layer.

[0208] In this embodiment, a storage optimization model is provided. The model also uses the DQN method to combine reinforcement learning with deep learning. It uses some low-correlation experience to improve the parameters of the deep neural network model, thereby optimizing storage efficiency while optimizing reading efficiency. The model uses RocksDB built-in functions and third-party tools to implement the Merge operation on SSTable to optimize device storage.

[0209] Based on the storage optimization model framework diagram of the device in this embodiment, please refer to Figure 7 , Figure 7 This is a flow chart of the device storage optimization model of the device optimization method embodiment of the present invention.

[0210] In this embodiment, the model processing storage optimization process includes steps S301 to S311:

[0211] Step S301: Check whether the storage optimization model is started;

[0212] Step S302: Start the model.

[0213] It should be noted that the storage model can be set as a separate service and deployed in other places outside the storage device to improve the performance of the storage device.

[0214] Step S303: Check whether there is a query operation;

[0215] Step S304: If there is a query operation, obtain SSTable information;

[0216] Step S305: experience storage;

[0217] Step S306: Check whether the experience number reaches a threshold.

[0218] It should be noted that by setting the threshold, the model parameter update is automatically called, eliminating the need for manual call execution operations.

[0219] Step S307: If the number of experiences reaches the threshold, the model is trained.

[0220] Step S308: Check whether the optimization triggering condition is met.

[0221] It should be noted that data query and data insertion operations performed by the device will affect the I / O performance of the device, so the settings will update the timestamp flag to avoid the high I / O processing stage.

[0222] Step S309: extracting feature information;

[0223] Step S310: Model prediction.

[0224] It should be noted that the result of the model prediction is the feature vector of the storage optimization cost of each layer. Each eigenvalue is the cost feature of the SSTable merge operation at each layer. The layer number of the Merge operation is obtained according to the index number of the minimum value in the feature vector.

[0225] Step S311: Execute operation.

[0226] The above is the overall process of the device storage optimization model implementation example. By recording the SSTable storage status information, predicting the cost of each layer of merge operations, and reducing the SSTable size, the effect of reducing device write amplification can be achieved.

[0227] This embodiment discloses a high-storage query processing system based on equipment digital twin data. The system enters the Mysql storage system through query or insert request and generates the corresponding execution plan; constructs a query optimization model, obtains the query execution plan tree through the audit plug-in, characterizes the query plan, and predicts the optimal execution plan through the query optimization network model; converts SQL data and key-value pairs through the RocksDB and Mysql interface modules; the RocksDB storage engine receives the operation information and performs data query or insertion operation; finally, calls the storage optimization model, reads the current state information and characterizes it, and predicts the optimal merge operation through the storage optimization network model. It realizes the maximum saving of storage space, and while maintaining extremely high storage efficiency, it can also ensure the rapid response of data reading.

[0228] The embodiments described above are only descriptions of the preferred embodiments of the present invention and are not intended to limit the scope of the present invention. Without departing from the design spirit of the present invention, various modifications and improvements made to the technical solutions of the present invention by ordinary technicians in this field should all fall within the protection scope determined by the claims of the present invention.

Claims

1. A high-storage query processing system based on equipment digital twin data, characterized in that: include: A MySQL storage module, used for receiving a query processing request or a data insertion request, and when receiving the query processing request, obtaining an execution plan of the query processing request; When receiving the data insertion request, generating an execution plan for the data insertion request; A query optimization module, configured to obtain a query execution plan tree when receiving the query processing request, input the query execution plan tree into a query optimization model, obtain an optimal query plan tree, generate an execution plan for the query processing request based on the optimal query plan tree, and set a timestamp flag when receiving the query processing request; The RocksDB and MySQL interface module is used to convert the execution plan of the query processing request into a data query operation when receiving the execution plan of the query processing request, convert the key-value pair data, obtain the row data, send the row data to the MySQL storage module, and generate the query result; When receiving the execution plan of the data insertion request, converting the execution plan of the data insertion request into a data insertion operation; A RocksDB storage engine module, for, when receiving a data query operation, performing data query according to the data query operation and generating the key-value pair data, and when receiving a data insert operation, performing data insert according to the data insert operation; The storage optimization module is used to obtain current storage status information according to the timestamp mark, input the current storage status information into the storage optimization model, obtain the optimal layer storage merger operation cost, and perform storage optimization through the optimal layer storage merger operation cost.

2. The high-storage query processing system based on equipment digital twin data according to claim 1, characterized in that: The query optimization module includes: A query plan monitoring and interception unit, used to receive the query processing request, detect the query processing event type, and thus obtain a query execution plan tree; A custom optimization tree exploration and setting unit is used to set the optimize_switch parameter setting set as the optimization tree exploration direction, optimize the query execution plan tree using the MySQL optimizer, and generate a corresponding optimization exploration query plan; The query plan tree information extraction unit is used to extract information from the optimized exploration query plan, convert the information extraction result into an optimized exploration tree in JSON format, and collect the optimized exploration trees to form an optimized forest in JSON format.

3. The high-storage query processing system based on equipment digital twin data according to claim 2 is characterized in that: The query optimization module also includes: A query plan tree feature extraction unit is used to parse the table information of the optimization forest, perform feature processing according to the original logical structure of the query execution plan tree, and introduce the optimize_switch state feature to generate an optimization forest feature tensor; the feature processing is: the feature of the query plan node is used as the first dimension, the query plan tree is used as the second dimension, and the query plan forest is used as the third dimension, and when the feature vectors are connected, it is determined that the dimensions of each feature vector are consistent; The neural network processing unit is used to input the optimized forest feature tensor into the query optimization model for cost prediction, obtain the best query plan tree, and generate an execution plan for the query processing request based on the best query plan tree.

4. The high-storage query processing system based on equipment digital twin data according to claim 3 is characterized in that: The query optimization module also includes: A query experience storage unit, used to save JSON format information, query time and optimize_switch parameter setting set, generate a training set, back-propagate the training set, train the query optimization model, and optimize the parameters of the query optimization model; The network model retraining unit is used to manage the amount of experience stored in the query experience storage unit, determine whether the amount of experience stored in the query experience storage unit reaches a preset threshold, and if so, retrain the query optimization model.

5. The high-storage query processing system based on equipment digital twin data according to claim 2, characterized in that: The query plan tree information extraction unit comprises: A query plan tree information extraction subunit is used to extract information from the query execution plan tree; wherein the extracted information includes: cardinality information and latency information of each node of the query execution plan tree; The target format conversion subunit is used to obtain the execution time and row cardinality of each table in the query execution plan tree according to the cardinality information and latency information of each node in the query execution plan tree, and compile it into target format information, traverse the query plan sequentially from the root node, recursively call the conversion function, and output the target format plan tree information.

6. The high-storage query processing system based on equipment digital twin data according to claim 1, characterized in that: The storage optimization module includes: A storage optimization model startup unit, used for updating a timestamp mark during a data query operation or a data insertion operation, starting a storage optimization operation threshold, and acquiring the current storage state information when the timestamp mark reaches the storage optimization operation threshold; A query plan time receiving unit, used for obtaining the execution time of the data query operation when receiving the data query operation; The storage status information acquisition unit is used to collect the current SSTable storage information and SSTable status information when receiving a data query operation, that is, the number and size of SSTable data results, the size and number of data blocks in the SSTable data results, the number of deletion marks, and the overlap information.

7. The high-storage query processing system based on equipment digital twin data according to claim 6, characterized in that: The storage optimization module also includes: A storage state feature extraction unit is used to read the stored experience records from the experience record table, use the SSTable storage information and SSTable state information in the experience records as one-dimensional feature vectors, and use the execution time of the data query operation as an edge extension feature for feature processing to generate a storage state feature tensor; the feature processing is: use the storage state feature of each layer as the first dimension, the number of SSTable layers as the second dimension, and when the feature vectors are connected, determine that the dimensions of each feature vector are consistent; The neural network processing unit is used to input the storage state feature tensor into the storage optimization model to obtain the merging cost of each layer of current storage.

8. The high-storage query processing system based on equipment digital twin data according to claim 7, characterized in that: The storage optimization module also includes: The Merge operation unit is used to select the minimum merging cost of each storage layer according to the predicted merging costs of multiple storage layers, and use the LUR strategy to merge the data blocks of the SSTable data structure in the storage layer with the data blocks of the SSTable data structure in the next layer involving the same data; Optimize the experience storage unit to record the execution time, SSTable storage information and SSTable status information of the data query operation, merge them into tuples, insert them into the experience record table, and generate a training set; The network model retraining unit is used to manage the amount of experience stored in the optimization experience storage unit, determine whether the amount of experience stored in the optimization experience storage unit reaches a preset threshold, and if so, perform a retraining operation on the query optimization model.

Images