A high-frequency database writing method and device based on multi-threaded asynchronous

Through multi-threaded asynchronous write method and cache queue mechanism, the problem of low efficiency of existing database APIs when writing high frequency and large amounts of data is solved, and efficient and timely data writing and system stability are achieved, which is suitable for finance and High-frequency data scenarios such as the Internet of Things.

CN118467591BActive Publication Date: 2024-11-05DOLPHINDB INC (CN)
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202410917781.4
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-07-10
Publication Date
2024-11-05
Estimated Expiration
2044-07-10

AI Technical Summary

Technical Problem

Existing database APIs are inefficient when writing high-frequency and large amounts of data, causing client processes to wait for MySQL communication and data insertion operations, resulting in data being unable to be written in time, there is a performance bottleneck, and it does not support the merge of multiple data into one. Request efficient write.

Method used

Using multi-threaded asynchronous high-frequency database writing method, by creating multi-threaded table writer objects, setting cache queues and write thresholds, implementing asynchronous data insertion, supporting concurrent writing of partition tables, providing data compression and disconnection repetition Connection mechanism to ensure timely data writing and system robustness.

Benefits of technology

It improves database writing efficiency, reduces data writing delay and database load, provides real-time monitoring and disconnection and reconnection functions, ensures timely write data in high-frequency scenarios and improves system stability.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118467591B_ABST
    Figure CN118467591B_ABST
Patent Text Reader

Abstract

The present invention discloses a high-frequency database writing method and device based on multi-threaded asynchronous, which is as follows: a multi-threaded table writer object is created and initialized; a table structure to be written into a database is obtained, a group of threads and corresponding cache queues are created, each thread is connected to a database system and monitors the state of a corresponding cache queue, and a number threshold of a single expected write of the cache queue and a time threshold of a maximum write interval are set; data is asynchronously inserted through a multi-threaded table writer object; a table type and a partition type are determined according to the table structure, and for a partition table, data of different partition types are inserted into different cache queues; a thread is executed cyclically, and based on the number threshold and time threshold, a write condition of a cache queue is triggered, and data in the cache queue is sent to a database system through a thread for data insertion, and the cache queue is cleared. Thus, writing efficiency is improved, data writing delay is reduced, and database load is reduced.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The invention belongs to the field of computer databases, and in particular relates to a high-frequency database writing method and device based on multi-threaded asynchronous operation. Background Art

[0002] At present, big data technology has been widely used in finance, Internet of Things and other industries, and the writing of massive data is the basis of big data processing and analysis. In practical applications, how to write a large amount of real-time data into the database in a timely, accurate and complete manner is a very challenging task.

[0003] At present, each mainstream database has launched its own API to perform data write query operations. Take the use of the open source library mariadb-connector-c to write data to MySQL as an example. When writing data, the user needs to call the mysql_query interface to execute an SQL statement, and this interface is a synchronous interface, that is, after the request is issued, the thread calling the interface will suspend waiting and will not continue to execute subsequent codes until MySQL completes the data insertion and returns the execution result. The mysql_query interface will return, and then the user needs to obtain the execution result by calling other interfaces, such as mysql_store_result. This method is very inefficient when writing high-frequency data (such as transactions on the stock exchange in financial scenarios, and sensor measurement data in the Internet of Things scenario), because the client process needs to bear the time overhead of communicating with MySQL and inserting data in MySQL, which will cause the data to be unable to be written to the database in time and accumulate in the client, which will cause some other problems. Therefore, in the prior art, there is a certain performance bottleneck for high-frequency and large-scale write data in the database system.

[0004] At the same time, writing data is a complex task for the database. For example, it needs to go through the process of parsing SQL statements, locking related tables, executing write operations, logging, committing transactions, etc. It is much faster to execute the operation of writing 10,000 rows of data once than to execute the operation of writing one row 10,000 times. It would be much more efficient if the API supports merging multiple data into one request to initiate writing. However, the APIs provided by the current mainstream databases do not support this function. Summary of the invention

[0005] In order to solve the shortcomings of the prior art and achieve the purpose of improving the high-frequency and large-volume writing performance of the database system, the present invention adopts the following technical solutions:

[0006] A high-frequency database writing method based on multi-threaded asynchronous operation includes the following steps:

[0007] Step S1: Create a multi-threaded table writer MTW object and initialize it; obtain the table structure to be written to the database, create a group of threads and corresponding cache queues, each thread is connected to the database system dolphindb and monitors the status of the corresponding cache queue, and sets the number threshold of the cache queue's single expected write and the time threshold of the maximum write interval; these two parameters are used to limit the frequency of inserting data, and the condition for triggering writing is: when the number of cached data reaches the number threshold or the time from the last write reaches the time threshold;

[0008] Set the number of threads and partition column names. If it is a partitioned table, you can set multi-threaded writing;

[0009] If the parameters provided when creating the MTW object are incorrect, an exception is thrown to notify the user;

[0010] Set the compression method. When bandwidth is limited, enable data compression to save bandwidth.

[0011] If the TCP connection between the background worker thread and the database system dolphindb server is disconnected due to network or other reasons, MTW will automatically try to reconnect and log in, and then continue to transmit data, ensuring that data will not be lost due to network problems, thereby improving the robustness of the write system.

[0012] Step S2: insert data asynchronously through the multi-threaded table writer MTW object; determine the table type and partition type according to the table structure, and for the partition table, insert data of different partition types into different cache queues to wait for the background worker thread to process the data for subsequent concurrent writing, because different partitions in the database can be written concurrently without interfering with each other, and the asynchronous method allows users to not wait in the main thread, which can improve work efficiency; compared with the prior art where partitions can only be distinguished by time, thus requiring each piece of data to contain time, the present invention only needs to specify the partition column and temporarily store the data in different cache queues according to the partition rule;

[0013] Step S3: loopingly executing the thread, triggering the write condition of the cache queue based on the quantity threshold and the time threshold, sending the data in the cache queue to the database system dolphindb for data insertion through the thread, and clearing the cache queue.

[0014] In addition, if the insertion fails due to an exception in the database system dolphindb server (such as memory allocation failure, etc.), MTW will immediately terminate all data insertion operations. At this time, calling MTW's insert data interface will throw an exception. Users can obtain specific error information through the getStatus interface provided by the MTW class. And get all the data to be inserted and the data that failed to be inserted through the getUnwrittenData interface.

[0015] Furthermore, in the initialization of step S1, different types of partitioning schemes are set to insert data of different partition types into different cache queues in step S2. The partitioning schemes include range partitioning, hash partitioning, and value partitioning. Range partitioning creates a partition for each partition interval. Hash partitioning uses hash functions to operate on partition columns to facilitate the establishment of a specified number of partitions. Value partitioning creates a partition for each value of the partition column.

[0016] Furthermore, in the initialization of step S1, the table structure information includes the data type of each column, which is used to verify the incoming data in step S2. If there is a mismatch, the insert interface will return false to notify the user. If there is a match, the verified legal data will be inserted.

[0017] Furthermore, in step S2, for the memory table, since the memory table does not support concurrent writing of data, only one cache queue and one background thread are used to insert the data into the cache queue.

[0018] Furthermore, in step S3, the process of thread loop execution includes the following steps:

[0019] Step S3.1: Determine whether the number of caches in the cache queue reaches the number threshold, if so, proceed to step S3.3, otherwise, proceed to step S3.2;

[0020] Step S3.2: Determine whether the time interval between the current time and the last insertion reaches the time threshold. If so, proceed to step S3.3, otherwise, return to step S3.1. By setting a time threshold, a submission will be made in time when the time comes, thus avoiding the situation where the amount of data in a partition is small and the data may not be submitted for a long time due to not considering the time, thus failing to ensure the timeliness of data entry.

[0021] Step S3.3: Send all the data in the cache queue to the database system dolphindb server for data insertion, clear the cache queue, and return to step S3.1; sending the cached data as a whole to the database system dolphindb at one time can improve the efficiency of insertion, because inserting multiple records in batches at one time is much faster than inserting single records one by one multiple times.

[0022] Furthermore, in the step S1, a callback function is set to notify the user of the result of data insertion; the step S3 also includes step S3.4: determining whether a callback function is set, and if so, notifying the customer of the result of data insertion through the callback function, and returning to step S3.1.

[0023] Furthermore, in step S3, a reasonable combination of the time threshold and the quantity threshold can meet a variety of work scenarios: when the real-time requirement for data writing is high, by reducing the time threshold and the quantity threshold, the data can be written to the database in time, thereby reducing the write latency; when the real-time requirement for data writing is not high, by increasing the time threshold and the quantity threshold, more data can be written in batches at one time, thereby reducing the load on the database.

[0024] Furthermore, the multi-threaded table writer MTW object counts the number of data inserted by each thread, the number of data in the cache queue, and the overall number of successfully inserted data, the number of data to be inserted, and the number of data that failed to be inserted, and provides an interface to the client to return these data for monitoring the state of the multi-threaded table writer MTW object. Real-time acquisition of status, such as the number of data waiting to be inserted, the number of data successfully inserted, the number of data failed to be inserted, and the specific reason for the failure when the insertion fails, so as to try to insert again.

[0025] Furthermore, in step S3, if the user has completed inserting data and is ready to destroy the multi-threaded table writer MTW object, the multi-threaded table writer MTW provides an interface for waiting for all data to be sent. By calling the interface for waiting for all data to be sent, the thread that destroys the multi-threaded table writer MTW object will be suspended and will not return until all background working threads have completed inserting data in all cache queues. After the function returns, the multi-threaded table writer MTW object can be safely destroyed.

[0026] A high-frequency database writing device based on multi-threaded asynchronous operation includes a memory and one or more processors. The memory stores executable code. When the one or more processors execute the executable code, they are used to implement the high-frequency database writing method based on multi-threaded asynchronous operation.

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

[0028] The present invention uses an asynchronous method to save users from waiting in the main thread, thereby improving work efficiency; through the reasonable combination of time threshold and quantity threshold, it can reduce data writing delay and database load in various work scenarios; through batch insertion, the cached data is sent to the database system as a whole at one time, thereby improving insertion efficiency; at the same time, real-time monitoring of write status and disconnection and reconnection functions are also provided to improve the robustness of the database system. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] Figure 1 is a flow chart of a method in an embodiment of the present invention.

[0030] Figure 2 It is a flowchart of creating and initializing a multi-threaded table writer MTW object in an embodiment of the present invention.

[0031] Figure 3 It is a flow chart of data insertion in an embodiment of the present invention.

[0032] Figure 4 It is a flowchart of the cyclic execution of the working thread in an embodiment of the present invention.

[0033] Figure 5 Schematic diagram of the structure of the device in the embodiment of the present invention. DETAILED DESCRIPTION

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

[0035] like Figure 1 As shown, a high-frequency database writing method based on multi-threaded asynchronous is provided in each API interface of the database system dolphindb. The MultithreadedTableWriter (hereinafter referred to as MTW) class is provided. By calling several methods provided by this class, data can be conveniently written to the database system dolphindb in a multi-threaded asynchronous manner. Different from the synchronous interface in the prior art, asynchronous returns immediately after calling the method without waiting for the task to be completed. The execution result will be notified to the user through a callback function or other methods. The specific writing method includes the following steps:

[0036] Step S1: Create a multi-threaded table writer MTW object and initialize it; obtain the table structure to be written to the database, create a group of threads and corresponding cache queues, each thread is connected to the database system dolphindb, set the number threshold of the cache queue's single expected write and the time threshold of the maximum write interval;

[0037] The user must first create a multi-threaded table writer MTW object and specify the following parameters:

[0038] (1) Set the IP address, port number, user name, and password to connect to the specified dolphindb server and log in;

[0039] (2) Set the database name and database table name, and the user specifies the table into which the data is to be inserted;

[0040] (3) Setting the number threshold of a single expected write and the time threshold of the maximum write interval (in seconds) are optional parameters. These two parameters are used to limit the frequency of inserting data. The triggering condition for writing is: when the number of cached data reaches the number threshold or the time from the last write reaches the time threshold. In the embodiment of the present invention, the default values ​​are 1s and 0.01s respectively.

[0041] (4) Setting the number of threads and partition column name is an optional parameter. If it is a partitioned table, multi-threaded writing can be set. In the embodiment of the present invention, the default values ​​are 1 and an empty string.

[0042] (5) Set the compression method, which is an optional parameter. When the bandwidth is limited, data compression can be enabled. It is not enabled by default.

[0043] (6) Set the callback function, which is an optional parameter. The user will be notified through the callback whether the data insertion is successful or failed.

[0044] If the parameters provided when creating an MTW object are incorrect, such as an inaccessible IP address or a non-existent table name, an exception will be thrown to notify the user.

[0045] The initialization process of the MTW object includes the following steps:

[0046] (1) Connect to the database system dolphindb and log in;

[0047] (2) Obtain the table structure information of the table to be written based on the filled-in database name and table name; including the data type of each column for subsequent data verification, as well as the partition column and partition type of the table for subsequent data classification;

[0048] (3) Set the partitioning scheme. Currently supported partitioning schemes include range partitioning, hash partitioning, and value partitioning. Range partitioning creates a partition for each partition interval; hash partitioning uses hash functions to operate on partition columns to facilitate the establishment of a specified number of partitions; value partitioning creates a partition for each value of the partition column. Different partitions in the database can be written concurrently without interfering with each other;

[0049] (4) Initialize resources based on the thread number parameter N, create N background worker threads and N cache queues. Each thread will connect to dolphindb separately and is responsible for monitoring the status of a cache queue. The process is as follows: Figure 2 shown.

[0050] Step S2: insert data asynchronously through the multi-threaded table writer MTW object; determine the table type according to the table structure, and for a partitioned table, insert data of different partitions into different cache queues to wait for background worker threads to process the data for subsequent concurrent writing, because different partitions in the database can be written concurrently without interfering with each other;

[0051] Users can insert data asynchronously through the insert interface provided by the MTW class. When calling this interface, the incoming data will first be checked for parameters, such as whether the quantity, type, etc. match the table structure. If they do not match, the insert interface will return false to notify the user; if the data is legal, the data will be inserted into the cache queue to wait for the background worker thread to process the data. The logic of inserting the queue is as follows:

[0052] (1) If the table to be inserted is a partitioned table, since data in different partitions can be written concurrently at the same time, the data in different partitions should be inserted into different cache queues according to the partition type (value partition, range partition, and hash partition). This ensures that data in different partitions enter different cache queues for subsequent concurrent writing, such as Figure 3 shown.

[0053] (2) If the table to be inserted is a memory table, since the memory table does not support concurrent data writing, only one cache queue and one background thread will be used. At this time, the data can be inserted into the cache queue.

[0054] Step S3: loopingly executing the thread, triggering the write condition of the cache queue based on the quantity threshold and the time threshold, sending the data in the cache queue to the database system dolphindb for data insertion through the thread, and clearing the cache queue.

[0055] The background worker thread will always be in a loop, such as Figure 4 As shown, it includes the following steps:

[0056] (1) The cycle starts;

[0057] (2) Determine whether the number of caches in the cache queue has reached the number threshold. If so, proceed to step (4); otherwise, proceed to step (3);

[0058] (3) Determine whether the time interval between the current time and the last insertion time reaches the time threshold. If so, proceed to step (4); otherwise, return to step (1);

[0059] (4) All data in the cache queue are sent to the database system dolphindb server for data insertion, and the cache queue is cleared;

[0060] (5) Determine whether the user has set a callback function. If so, notify the client of the result of data insertion through the callback function and return to step (1).

[0061] The MTW object will count the number of data inserted by each thread, the number of data in the cache queue, the overall number of successfully inserted data, the number of data to be inserted, and the number of data that failed to be inserted, and provide an interface to the client to return these data to monitor the MTW status.

[0062] If the user has finished inserting data and is ready to destroy the MTW object, MTW provides an interface to wait for all data to be sent. When this interface is called, the thread will be suspended and will not return until all background working threads have sent all data in the cache queue. After this function returns, the MTW object can be safely destroyed.

[0063] If the TCP connection between the background worker thread and the database system dolphindb server is disconnected due to network or other reasons, MTW will automatically try to reconnect and log in, and then continue data transmission to ensure that data will not be lost due to network problems.

[0064] If the insertion fails due to an exception in the database system dolphindb server (such as memory allocation failure, etc.), MTW will immediately terminate all data insertion operations. At this time, calling the MTW data insertion interface will throw an exception. Users can obtain specific error information through the getStatus interface provided by the MTW class. And get all the data to be inserted and the data that failed to be inserted through the getUnwrittenData interface.

[0065] Corresponding to the aforementioned embodiment of a high-frequency database writing method based on multi-thread asynchronous operation, the present invention further provides an embodiment of a high-frequency database writing device based on multi-thread asynchronous operation.

[0066] See also Figure 5 A high-frequency database writing device based on multi-threaded asynchrony provided in an embodiment of the present invention includes a memory and one or more processors. An executable code is stored in the memory. When the one or more processors execute the executable code, it is used to implement a high-frequency database writing method based on multi-threaded asynchrony in the above embodiment.

[0067] An embodiment of a high-frequency database writing device based on multi-threaded asynchronous operation of the present invention can be applied to any device with data processing capabilities, and the device with data processing capabilities can be a device or apparatus such as a computer. The device embodiment can be implemented through software, or through hardware or a combination of software and hardware. Taking software implementation as an example, as a device in a logical sense, it is formed by the processor of any device with data processing capabilities in which it is located reading the corresponding computer program instructions in the non-volatile memory into the internal memory for execution. From a hardware perspective, if Figure 5 As shown, it is a hardware structure diagram of any device with data processing capability where a high-frequency database writing device based on multi-threaded asynchronous is located in the present invention, except Figure 5 In addition to the processor, memory, network interface, and non-volatile memory shown, any device with data processing capabilities in which the apparatus in the embodiments is located may also include other hardware, generally based on the actual functions of the device with data processing capabilities, which will not be described in detail.

[0068] The implementation process of the functions and effects of each unit in the above-mentioned device is specifically described in the implementation process of the corresponding steps in the above-mentioned method, and will not be repeated here.

[0069] For the device embodiment, since it basically corresponds to the method embodiment, the relevant parts can refer to the partial description of the method embodiment. The device embodiment described above is only schematic, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of the present invention. Ordinary technicians in this field can understand and implement it without paying creative work.

[0070] An embodiment of the present invention further provides a computer-readable storage medium on which a program is stored. When the program is executed by a processor, a high-frequency database writing method based on multi-threaded asynchronous operation in the above embodiment is implemented.

[0071] The computer-readable storage medium may be an internal storage unit of any device with data processing capability described in any of the aforementioned embodiments, such as a hard disk or a memory. The computer-readable storage medium may also be an external storage device of any device with data processing capability, such as a plug-in hard disk, a smart media card (SMC), an SD card, a flash card, etc. equipped on the device. Furthermore, the computer-readable storage medium may also include both an internal storage unit and an external storage device of any device with data processing capability. The computer-readable storage medium is used to store the computer program and other programs and data required by any device with data processing capability, and may also be used to temporarily store data that has been output or is to be output.

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

Claims

1. A high-frequency database writing method based on multi-threaded asynchronous operation, characterized in that The steps include: Step S1: Create a multi-threaded table writer object and initialize it; obtain the table structure to be written to the database, create a group of threads and corresponding cache queues, each thread is connected to the database system and monitors the status of the corresponding cache queue, set the number threshold of the cache queue's single expected write and the time threshold of the maximum write interval; set the number of threads and partition column names; During initialization, different types of partitioning schemes are set to insert data of different partition types into different cache queues in step S2. The partitioning schemes include range partitioning, hash partitioning, and value partitioning. Range partitioning creates a partition for each partition interval. Hash partitioning uses a hash function to operate on the partition column to establish a specified number of partitions. Value partitioning creates a partition for each value of the partition column. During initialization, the table structure information includes the data type of each column, which is used to verify the incoming data in step S2, and then insert the verified legal data; Step S2: inserting data asynchronously through a multi-threaded table writer object; determining the table type and partition type according to the table structure, and for a partitioned table, inserting data of different partition types into different cache queues to wait for background worker threads to process the data for subsequent concurrent writing; Step S3: loopingly executing the thread, triggering the write condition of the cache queue based on the quantity threshold and the time threshold, sending the data in the cache queue to the database system for data insertion through the thread, and clearing the cache queue; The thread loop execution process includes the following steps: Step S3.1: Determine whether the number of caches in the cache queue reaches the number threshold, if so, proceed to step S3.3, otherwise, proceed to step S3.2; Step S3.2: Determine whether the time interval between the current time and the last insertion reaches the time threshold. If so, proceed to step S3.3; otherwise, return to step S3.1; Step S3.3: Send all the data in the cache queue to the database system for data insertion, clear the cache queue, and return to step S3.

1.

2. According to claim 1, a high-frequency database writing method based on multi-threaded asynchronous operation is characterized in that: In step S2, for the memory table, a cache queue and a thread are used to insert data into the cache queue.

3. The high-frequency database writing method based on multi-threaded asynchronous operation according to claim 1, characterized in that: In the step S1, a callback function is set to notify the user of the result of data insertion; the step S3 also includes step S3.4: determining whether a callback function is set, and if so, notifying the customer of the result of data insertion through the callback function, and returning to step S3.

1.

4. The high-frequency database writing method based on multi-threaded asynchronous operation according to claim 1, characterized in that: In step S3, by reducing the time threshold and the quantity threshold, the data can be written into the database in time, and by increasing the time threshold and the quantity threshold, more data can be written in batches at one time.

5. The high-frequency database writing method based on multi-threaded asynchronous operation according to claim 1, characterized in that: The multi-threaded table writer object counts the amount of data inserted by each thread, the amount of data in the cache queue, and the overall amount of successfully inserted data, the amount of data to be inserted, and the amount of data that failed to be inserted, and returns these data for monitoring the state of the multi-threaded table writer object.

6. The high-frequency database writing method based on multi-threaded asynchronous according to claim 1, characterized in that: In step S3, if the user has finished inserting data and is ready to destroy the multi-threaded table writer object, the multi-threaded table writer provides an interface for waiting for all data to be sent. By calling the interface for waiting for all data to be sent, the thread that destroys the multi-threaded table writer object will be suspended, and the multi-threaded table writer object will not be destroyed until the data in all cache queues has been inserted.

7. A high-frequency database writing device based on multi-threaded asynchronous operation, characterized in that: It comprises a memory and one or more processors, wherein the memory stores executable codes, and when the one or more processors execute the executable codes, they are used to implement a high-frequency database writing method based on multi-threaded asynchronous operation as described in any one of claims 1 to 6.