Efficient sparse large object data migration method
By calculating the field properties of data records and setting reasonable number of threads, it is divided into batch insertion and stream copy mode writing, the problem of low data migration efficiency of sparse large objects is solved, and efficient data migration and resource utilization is achieved. It is suitable for Multiple databases.
Patent Information
- Application Number
- CN202411146767.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-21
- Publication Date
- 2024-12-13
AI Technical Summary
The existing sparse large object data migration method is single and the migration efficiency is low, resulting in high resource usage, large network bandwidth consumption and long processing time.
By calculating the field properties of each data record, setting a reasonable data buffer size and number of threads, it is divided into batch insertion and stream replication mode writing, and putting it into different task queues according to the data size, maximizing the available memory of the system and improving the The amount of concurrency to read, cache and write.
It improves the migration efficiency of sparse large object data, reduces resource overhead, improves the operation efficiency of data writing to the target library, and supports a variety of database and data migration operations.
Smart Images
Figure CN119127838A_ABST
Abstract
Description
Technical Field
[0001] The invention belongs to the field of big data, and in particular relates to an efficient sparse large object data migration method. Background Art
[0002] In the era of big data, the expression of information includes structured data with a clear format, or unstructured data without a fixed format such as documents, audio and video files, social media posts, and semi-structured data between structured and unstructured, such as json, xml files, emails, log files, etc. The data type of multimodal fusion presents sparse large object data of uneven size. For example, email data, attachments (files, pictures, etc.) exist in a large object field. Most emails have no attachments (0MB), a few have small attachments (0-100MB), and very few have large attachments (100-1000MB), forming a sparse data table. Sparse large object data has rich and diverse expression forms and great application value of deep mining, but it also occupies a lot of resources, resulting in high recovery costs, network bandwidth and IO consumption during network transmission, and high processing time of parsing, data storage, data migration, etc., which will lead to a decline in application performance.
[0003] The present invention proposes an efficient sparse large object data migration method. According to the available memory of the system, the concurrent amount of large object data operations (reading, storing, and writing) covering multi-modal and unevenly sized large objects such as structured, unstructured, and semi-structured is reasonably set to maximize the utilization of the available memory space of the system. At the same time, the physical storage format of the large object field is fully utilized, and the targeted data writing mode is reasonably selected, so as to improve the efficiency of writing large object data into the target library. If it is used in combination with the high-performance data replication method of a specific database, the effect will be better, such as PostgreSQL's binary copy (COPY...WITHBINARY) and SQL Server's batch copy (SqlBulkCopy). Summary of the invention
[0004] 1. Technical issues to be resolved
[0005] The technical problem to be solved by the present invention is how to provide an efficient sparse large object data migration method to solve the problems of single sparse large object data migration mode and low migration efficiency.
[0006] (II) Technical solution
[0007] In order to solve the above technical problems, the present invention proposes an efficient sparse large object data migration method, which includes the following steps:
[0008] S1. Calculate the space required to cache a row of data records based on the field attributes of each data record, estimate the minimum memory required for batch record operations as the data buffer size, and set the data buffer size as the threshold; estimate the reasonable number of threads for data reading, data caching, and data writing based on the nature of the task;
[0009] S2. Start the main thread to manage the read and write thread pools;
[0010] S3, the reading thread reads the source data, which is sparse large object data, and determines whether the data size of the source data exceeds the threshold;
[0011] If the threshold is not exceeded, the data is put into the task buffer. When the buffer data reaches the threshold, the buffer is submitted and inserted into the task queue in batches.
[0012] If the data size of the source data read reaches or exceeds the threshold, the data is submitted to the stream replication task queue;
[0013] S4. Write threads are divided into two categories: batch insert write threads and stream replication mode write threads, which respectively perform batch insert and stream replication mode write operations on the data of the batch insert task queue and the stream replication task queue.
[0014] (III) Beneficial effects
[0015] The present invention proposes an efficient sparse large object data migration method. The beneficial effects of the present invention are as follows: in view of the current actual situation where more and more multimodal data coexist, the characteristics of sparse large object data are comprehensively considered, and the problem of low operation efficiency caused by conventional single migration operation for structured data is broken through, and the concurrency of sparse large object data reading, caching and writing is improved; at the same time, for the writing of batch data and single large object data, unnecessary resource overhead is minimized, and the operation efficiency of data writing to the target library is further improved. In terms of implementation logic, the data reading interface is unified, supports multiple databases, and data migration, data comparison, etc. can be reused, and supports different databases (versions) to define data reading implementation. BRIEF DESCRIPTION OF THE DRAWINGS
[0016] Figure 1 A flowchart of an efficient sparse large object data migration method of the present invention;
[0017] Figure 2 This is an example diagram of the source data table structure;
[0018] Figure 3 This is a schematic diagram of the read, store and write buffers;
[0019] Figure 4 The figure is a workflow diagram of the efficient sparse large object data migration method of the present invention. DETAILED DESCRIPTION
[0020] In order to make the purpose, content and advantages of the present invention more clear, the specific implementation methods of the present invention are further described in detail below in conjunction with the drawings and examples.
[0021] The technical problem to be solved by the present invention is: the single migration mode of sparse large object data and low migration efficiency. By reasonably allocating data reading, data caching, and data writing to memory, reasonably setting the number of concurrent threads, and adopting different modes of write operations, the system available memory can be maximized to improve efficiency.
[0022] The present invention discloses an efficient sparse large object data migration method, which mainly improves the operational efficiency of database table data migration containing large object fields. First, the size of a row of data is calculated according to the table structure, and a reasonable data buffer size and a size threshold for loading large object data into memory are set accordingly, as well as a reasonable number of reading, caching and writing threads; second, the size of the read source data is compared with the set data threshold, and different task queues are placed according to the result; third, different writing modes are used for data in different task queues to write data into the target database. Aiming at the characteristics of sparse large object data, the present invention proposes an efficient data migration method, which maximizes the use of the system's available memory to increase the concurrency of data operations (reading, caching, writing), thereby improving the efficiency of data migration into the warehouse.
[0023] Its high efficiency is reflected in the following aspects: Since most values of sparse large object data are empty or small, resulting in uneven size distribution, data is cached by pre-reading (reading is usually faster than writing), and data is directly obtained from memory when writing (memory access speed is much higher than disk or network), and written to the target database by batch insertion (binary copy and other higher-performance data copying methods), maximizing the use of system available memory to increase operation concurrency, and making full use of the physical mechanism of large object field storage to adopt a more efficient writing method, thereby improving data migration efficiency.
[0024] The technical solution of the present invention is: an efficient sparse large object data migration method, the method comprising the following steps:
[0025] S1. Calculate the space required to cache a row of data records based on the field attributes of each data record, estimate the minimum memory required for batch record operations (reading, caching, writing) as the data buffer size, and set the data buffer size as the threshold; estimate the reasonable number of threads for data reading, data caching, and data writing based on the nature of the task.
[0026] S2. Start the main thread to manage the read and write thread pools;
[0027] S3, the reading thread reads the source data, which is sparse large object data, and determines whether the data size of the source data exceeds the threshold;
[0028] If the threshold is not exceeded, the data is put into the task buffer. When the buffer data reaches the threshold, the buffer is submitted and inserted into the task queue in batches.
[0029] If the data size of the source data read reaches or exceeds the threshold, the data is submitted to the stream replication task queue;
[0030] S4. Write threads are divided into two categories: batch insert write threads and stream replication mode write threads, which respectively perform batch insert and stream replication mode write operations on the data of the batch insert task queue and the stream replication task queue.
[0031] In the step S1, the field size of each data record is calculated, and the field size of other fields except the large object type field is calculated according to the maximum value of its field type, and then the storage space size required for caching a row of data records is calculated. According to the storage space size required for a row of data records and the number of data rows written in batches, the data buffer size required for the read and write buffers and task queues is estimated as a threshold;
[0032] Data migration is an IO-intensive task. The number of threads for reading, caching, and writing is calculated based on the CPU configuration.
[0033] In step S2, the main thread is started to set the reading thread and writing thread counts, and the reading thread and writing thread are initialized. When the reading thread and writing thread counts are both 0, the program ends.
[0034] In step S3, the reading thread reads the source data (sparse large object data) and determines whether the data has been read. If the data has been read, the reading thread count is reduced by 1; if the data has not been read, it is determined whether the data size exceeds the threshold:
[0035] Data that reaches or exceeds the threshold is submitted to the stream replication task queue;
[0036] Data that does not exceed the threshold is put into the task buffer. If the data is added to the existing data size in the buffer and reaches the threshold, it is submitted for batch insertion into the task queue. If it does not reach the threshold, the source data continues to be read.
[0037] In step S4, the write thread determines whether the task queue is empty. If the read thread count is 0 when it is empty, the write thread count is reduced by 1 and the thread ends; when the stream copy task queue is not empty, the stream copy mode write thread performs a StreamCopy write operation on the stream copy task queue data; when the batch insert task queue is not empty, the batch insert write thread performs a BatchInsert write operation on the batch insert task queue data.
[0038] Embodiment 1:
[0039] Step 1: Calculate the field size of each data record. Except for large object type fields, calculate the field size according to the maximum value of the field type. For the task type of data migration, calculate the number of reading, caching, and writing threads based on the CPU configuration.
[0040] like Figure 2 As shown in the figure, table table1 is set, which contains 8 fields from col1 to col8, among which col6 is a character large object (clob), col7 and col8 are binary large objects (blob). The memory size required to buffer a row of data is the sum of the data sizes of all fields.
[0041] Since the data types of different programming languages may occupy different amounts of memory, Java (64-bit JVM, JVM parameter: -XX:+UseCompressedOops) is used as an example for calculation.
[0042] When the data of the large object type field (clob / blob) can be loaded into the memory, as shown in Table 1, the clob type corresponds to the String type, and the blob type corresponds to the byte[] (byte array). The safe storage space size for caching a row of data in the table is: 16+(24+10*2)+(24+20*2)+(24+16*2)+40+(24+m1)+(32+m2)+(32+m3)=308+m1+m2+m3 bytes;
[0043] When the data of the large object type field (clob / blob) cannot be loaded into the memory, as shown in Table 2, both clob and blob types correspond to InputStream (input stream object), so the safe storage space size for caching a row of data in the table is: 16+(24+10*2)+(24+20*2)+(24+16*2)+40+32+32+32=316 bytes.
[0044] Table 1 LOB type field data can be loaded into memory
[0045] Fields type Java Data Types Memory occupied (bytes) col1 integer Integer 16 col2 char(10) String 24+10*2＝44 col3 varchar(20) String 24+20*2＝64 col4 char(16) String 24+16*2＝56 col5 decimal(16, 2) BigDecimal 40 col6 clob String 24+m1 (m1 is the number of characters) col7 blob byte[] 32+m2 (m2 is the length of the array) col8 blob byte[] 32+m3 (m3 is the length of the array)
[0046] Table 2 LOB type field data cannot be loaded into memory
[0047]
[0048]
[0049] This can be used to estimate the data buffer size required for read, write buffers, and task queues. Usually the size of a batch write ( Figure 2 The recommended number of data rows n in the table is 50 to 100, and the minimum space of the corresponding data buffer is 15800 to 31600 bytes. Then, additional data cache space is added to the large object field based on the available memory to make full use of the memory. To simplify the calculation, the data buffer size, that is, the threshold used below, can be directly set to 10MB or 100MB.
[0050] Data migration is an IO-intensive task. The calculation formula for concurrent thread settings is: number of threads = number of CPU cores / (1-blocking coefficient), where the blocking coefficient is generally between 0.8 and 0.9. If it is 0.9, the corresponding number of threads is: dual-core CPU: 2 / (1-0.9)=20; 64-core 2-way CPU: 64*2 / (1-0.9)=1280, etc. Figure 3 As shown, X is set to be the number of reading threads, Y is the number of writing threads in streaming replication mode, Z is the number of writing threads for batch insertion, M is the maximum number of tasks in the batch insertion task queue, and N is the maximum number of tasks in the streaming replication task queue. The following must be satisfied: (X+Y+Z+M+N)*buffer size < system available memory.
[0051] The main thread, read thread, and write thread work as follows Figure 4 shown.
[0052] Step 2: Main thread workflow
[0053] The main thread sets the read thread and write thread counts, initializes the read thread and write thread, and determines whether the read thread and write thread counts are both 0 (the read thread and write thread perform count maintenance). If they are 0, the main thread program ends, otherwise the loop continues to determine.
[0054] Step 3: Data reading thread workflow
[0055] The reading thread reads the source data (sparse large object data, such as files, historical table data, etc.). If the data is read, the reading thread count is reduced by 1. If the data is not read, the read data size is determined:
[0056] Data with a size not exceeding the threshold is put into the buffer until the buffer data size reaches the threshold and then submitted to the batch insertion task queue;
[0057] If the read data size reaches or exceeds the threshold, it is directly put into the stream replication task queue. The length of the task queue is set as needed, and the principle is to maximize it within the limited memory range.
[0058] Step 4: Data writing thread workflow
[0059] The writing thread uses the batch insert (BatchInsert) writing thread and the stream copy (StreamCopy) mode writing thread to write the task queue data into the database.
[0060] In batch data operations, for data containing various types of large object fields, batch insertion makes full use of the physical storage format closest to the actual large object field, without parsing and conversion, directly loading binary data or file system into the database table, disabling indexes and constraints before inserting batch data to reduce the resource overhead occupied by additional checks and constraints, and further improving the efficiency of batch data writing. There is only one piece of data in the stream replication task queue, and what is transmitted is an input stream object, which is suitable for accurate data insertion.
[0061] The batch insert write thread determines whether the batch insert task queue is empty. If it is not empty, it reads the task and performs the write operation. If it is empty and the read thread count is 0, the write thread count is reduced by 1 and the writing ends. If the read thread count is not 0, it continues to read the batch insert task queue.
[0062] The stream replication mode writer thread determines whether the stream replication task queue is empty. If it is not empty, it reads the task and performs the write operation. If it is empty and the read thread count is 0, the write thread count is reduced by 1 and the writing ends. If the read thread count is not 0, it continues to read the stream replication task queue.
[0063] Embodiment 2:
[0064] An efficient sparse large object data migration method includes the following steps:
[0065] 1. Calculate the number of records to be read in a batch of sparse large object data to be written, and calculate the memory required for single-threaded data reading, caching, and writing to the target database as the data size threshold. Combined with the task type for sparse large object data operations, calculate the number of threads for reading, caching, and writing operations.
[0066] 2. Read the source data to be written to the target database through the reading thread. If the size of the source data reaches or exceeds the threshold, it is submitted to the stream replication task queue. If the size of the source data does not exceed the threshold, it is submitted to the batch insertion task queue after the buffer accumulates to the threshold.
[0067] 3. The write threads are divided into two categories, which perform different write operations on the data in the two types of task queues.
[0068] In the step 1, the size of all fields of a row of sparse large object data is calculated, and the memory required for a batch data reading, caching, and writing operation is calculated as the data threshold in combination with the number of rows read in batches; the number of threads for the read / write operation and the size of the task queue are calculated in combination with the migration task type of the sparse large object data (typically IO intensive) to ensure that the required memory does not exceed the available memory of the system.
[0069] In the step 2, the reading thread reads the source data (sparse large object data) and determines whether the read source data exceeds the threshold determined in the step 1.
[0070] If the threshold is reached or exceeded, the data is put into the stream replication task queue;
[0071] If the threshold is not exceeded, the read data is placed in the task buffer until the buffer data size reaches the threshold, and the task buffer is submitted for batch insertion into the task queue.
[0072] In the step three, two types of writing threads are used to write large object data into the target database. For the data inserted into the task queue in batches, the batch insert (BatchInsert) mode is used to write the batch data in the task queue into the target database. For the large object field in text mode, batch insert can use the specific data format provided by the target database to store data, thereby improving the storage and reading speed; in binary mode, batch insert directly loads binary data into the database table, reducing the resource overhead of parsing and conversion. For the data in the stream replication task queue, the stream replication (StreamCopy) mode is used to directly insert the input stream object (a single data that reaches the threshold) into the target database.
[0073] The beneficial effects of the present invention are as follows: in view of the current actual situation where more and more multimodal data coexist, the characteristics of sparse large object data are comprehensively considered, and the problem of low operational efficiency caused by conventional single migration operation for structured data is broken through, and the concurrency of reading, caching and writing of sparse large object data is improved; at the same time, for the writing of batch data and single large object data, unnecessary resource overhead is minimized, and the operational efficiency of writing data to the target library is further improved. In terms of implementation logic, the data reading interface is unified, supports multiple databases, and data migration, data comparison, etc. can be reused, and supports different databases (versions) to define data reading implementation.
[0074] The above is only a preferred embodiment of the present invention. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the technical principles of the present invention. These improvements and modifications should also be regarded as the scope of protection of the present invention.
Claims
1. An efficient sparse large object data migration method, characterized in that: The method comprises the following steps: S1. Calculate the space required to cache a row of data records based on the field attributes of each data record, estimate the minimum memory required for batch record operations as the data buffer size, and set the data buffer size as the threshold; estimate the reasonable number of threads for data reading, data caching, and data writing based on the nature of the task; S2. Start the main thread to manage the read and write thread pools; S3, the reading thread reads the source data, which is sparse large object data, and determines whether the data size of the source data exceeds the threshold; If the threshold is not exceeded, the data is put into the task buffer. When the buffer data reaches the threshold, the buffer is submitted and inserted into the task queue in batches. If the data size of the source data read reaches or exceeds the threshold, the data is submitted to the stream replication task queue; S4. Write threads are divided into two categories: batch insert write threads and stream replication mode write threads, which respectively perform batch insert and stream replication mode write operations on the data of the batch insert task queue and the stream replication task queue.
2. The efficient sparse large object data migration method according to claim 1, characterized in that: In step S1, the field size of each data record is calculated, and the field size of other fields except the large object type field is calculated according to the maximum value of their field type, and then the storage space size required for caching a row of data records is calculated. According to the storage space size required for a row of data records and the number of data rows written in batches, the data buffer size required for the read and write buffers and task queues is estimated as a threshold.
3. The efficient sparse large object data migration method according to claim 2, characterized in that: When the programming language is Java, for character large object clob and binary large object blob, when the data of the large object type field clob / blob can be loaded into the memory, the clob type corresponds to the String type, occupying 24+m1 bytes of memory, where m1 is the number of characters; the blob type corresponds to the byte array byte[], occupying 32+m2 bytes of memory, where m2 is the length of the array; when the data of the large object type field clob / blob cannot be loaded into the memory, both the clob and blob types correspond to the InputStream input stream object, occupying 32 bytes of memory space.
4. The efficient sparse large object data migration method according to claim 2, characterized in that: In the step S1, data migration is an IO-intensive task, and the number of threads for reading, caching and writing is calculated in combination with the CPU configuration; in the setting of concurrent threads, the calculation formula is: number of threads = number of CPU cores / (1-blocking coefficient), and X is set to be the number of reading threads, Y is the number of writing threads in the stream replication mode, Z is the number of batch insertion writing threads, M is the maximum number of tasks in the batch insertion task queue, and N is the maximum number of tasks in the stream replication task queue, which must satisfy: (X+Y+Z+M+N)*buffer size<system available memory.
5. The efficient sparse large object data migration method according to claim 4, characterized in that: The blocking coefficient is between 0.8 and 0.
9.
6. The efficient sparse large object data migration method according to any one of claims 1 to 5, characterized in that: The step S2 includes: starting the main thread, setting the reading thread and writing thread counts, and initializing the reading thread and writing thread. When the reading thread and writing thread counts are both 0, the program ends.
7. The efficient sparse large object data migration method according to claim 6, characterized in that: The step S3 includes: a reading thread reads source data, the source data is sparse large object data, and determines whether the data has been read. If the data has been read, the reading thread count is reduced by 1; if the data has not been read, it is determined whether the data size exceeds a threshold: Data that reaches or exceeds the threshold is submitted to the stream replication task queue; Data that does not exceed the threshold is put into the task buffer. If the data is added to the existing data size in the buffer and reaches the threshold, it is submitted for batch insertion into the task queue. If it does not reach the threshold, the source data continues to be read.
8. The efficient sparse large object data migration method according to claim 7, characterized in that: The step S4 includes: the write thread determines whether the task queue is empty. If the read thread count is 0 when it is empty, the write thread count is reduced by 1 and the thread ends; when the stream copy task queue is not empty, the stream copy mode write thread performs a StreamCopy write operation on the stream copy task queue data; when the batch insert task queue is not empty, the batch insert write thread performs a BatchInsert write operation on the batch insert task queue data.
9. The efficient sparse large object data migration method according to claim 8, characterized in that: The batch insert write thread determines whether the batch insert task queue is empty. If it is not empty, it reads the task and performs the write operation. If it is empty and the read thread count is 0, the write thread count is reduced by 1 and the writing ends. If the read thread count is not 0, it continues to read the batch insert task queue.
10. The efficient sparse large object data migration method according to claim 8, characterized in that: The stream replication mode writer thread determines whether the stream replication task queue is empty. If it is not empty, it reads the task and performs the write operation. If it is empty and the read thread count is 0, the write thread count is reduced by 1 and the write operation ends. If the read thread count is not 0, it continues to read the stream replication task queue.