A method and system for supporting multi-thread efficient loading of large files into a database

Abstract

This invention discloses a method and system for efficient multi-threaded large file import, belonging to the field of data storage and processing technology. To address the problems of low efficiency, uneven resource utilization, lack of breakpoint resumption capability, poor data consistency, and weak storage adaptability in existing large file import methods, this invention achieves high-speed and reliable large file import in distributed or centralized storage systems through a dynamic adaptive segmentation algorithm, a multi-threaded collaborative scheduling mechanism, distributed progress management, and multi-level data verification. This invention can improve the efficiency of large file import, balance hardware resource utilization, ensure data consistency, and support adaptation to various storage systems and large-scale expansion.

Description

Technical Field

[0001] This invention belongs to the field of data storage and processing technology, specifically relating to a method and system for supporting efficient multi-threaded data import of large files. Background Technology

[0002] With the deepening of digital transformation, the amount of file data generated by various industries is growing exponentially, and the demand for storage of large files (usually referring to files exceeding 1GB) is becoming increasingly urgent. For example, 4K / 8K video footage in the film and television industry, high-resolution remote sensing data in the meteorological field, user behavior log archiving in internet companies, and transaction record backup in the financial industry all require efficient storage of GB or even TB-level large files into backend storage systems to support subsequent data processing and business analysis.

[0003] Traditional methods for importing large files into the database primarily rely on single-threaded I / O operations or simple file splitting and multi-threaded transmission, resulting in the following core technical bottlenecks. First, inefficient data import: When reading files and writing them to the storage system in a single thread, limitations imposed by disk I / O bandwidth, network transmission rates (if the storage system is distributed), or the write performance of storage nodes prevent full utilization of hardware resources such as CPU, memory, and network. This can lead to GB-level files taking hours to import, severely impacting business process efficiency. Second, uneven resource utilization: Even with multi-threading, a lack of a scientific file splitting strategy (such as fixed-size splitting) can easily result in the coexistence of "hot threads" (threads processing excessively large data blocks) and "idle threads" (threads processing excessively small data blocks). This leads to an unbalanced allocation of CPU and memory resources, preventing the full realization of the advantages of multi-threading.

[0004] Furthermore, traditional methods lack the ability to resume interrupted transmissions during the large file import process: if a network interruption, node crash, or hardware failure occurs, the transmission must be restarted from the beginning, and the data blocks that have already been imported cannot be reused, leading to duplicated work and wasted resources, especially for TB-level files. During multi-threaded parallel writing, data consistency is difficult to guarantee: without a unified progress management and verification mechanism, data block loss, disordered order (such as the file's logical structure depending on the data block order), or data corruption (such as bit errors during transmission) can easily occur, resulting in files that cannot be read correctly or whose content is distorted after importation.

[0005] Furthermore, different storage systems (such as HDFS, S3-compatible object storage, and MySQL clusters) have varying write performance characteristics (e.g., single-node write bandwidth, concurrent connection limits). Traditional methods typically use fixed thread counts and data block sizes, making it impossible to dynamically adjust strategies based on storage system performance, resulting in poor adaptability. Existing solutions sometimes improve I / O efficiency by increasing the file buffer, but this only alleviates local bottlenecks. While some distributed file systems (such as HDFS) natively support multi-replica parallel write interfaces, they are not optimized for multi-threaded partitioning and data insertion of large single files. Other methods rely on third-party tools (such as rsync) for incremental transfers, but these are only suitable for file update scenarios and cannot solve the efficiency and consistency issues of the initial data insertion.

[0006] Therefore, there is an urgent need for a method that can combine dynamic file segmentation, multi-threaded collaborative scheduling, breakpoint resume, data consistency verification and storage system adaptation for efficient storage of large files, in order to break through the bottlenecks of traditional technology and meet the needs of modern business for fast and reliable storage of large files. Summary of the Invention

[0007] The purpose of this invention is to propose a method and system that supports efficient multi-threaded insertion of large files into the database, in order to solve the technical problems of low efficiency, uneven resource utilization, lack of breakpoint resume, poor data consistency and weak storage adaptability in existing large file insertion methods. This invention aims to achieve high-speed and reliable insertion of large files into distributed or centralized storage systems, and to achieve balanced utilization of hardware resources, abnormal recovery capabilities and adaptability to multiple storage systems.

[0008] To achieve the above objectives, the present invention adopts the following technical solution.

[0009] A method for efficiently inserting large files into a database using multiple threads includes the following steps: 1) Obtain the write bandwidth, single-threaded input / output time, and available computing resources and memory capacity of the source file server for the target storage system; 2) Divide the large file to be stored into multiple data blocks, and calculate the initial number of blocks based on the maximum write bandwidth of a single node of the storage system, the single-thread input / output time, the number of available CPU cores and the available memory capacity. Adjust the number of blocks based on the size of a single data block, the memory threshold and the single-thread time to determine the optimal number and size of data blocks. 3) Set up multi-threaded collaborative scheduling for reading, transmitting, and writing threads to sequentially read data blocks from the source file, transmit them over the network to the target storage system, and write them to the storage. At the same time, write the processing status and metadata of each stage into a distributed schedule. 4) Implement distributed progress management, record the processing status of each data block in real time, and only reprocess the unfinished data blocks when an abnormal interruption occurs, thus realizing breakpoint resume. 5) Perform sequential checks at the block level, file level, and logical order to verify the integrity of data blocks and files, and re-execute the processing flow for abnormal data blocks; 6) Perform storage adaptation based on the target storage system type, and complete the reading, transmission and writing of data blocks through a unified interface to achieve seamless switching between heterogeneous storage systems.

[0010] Furthermore, the read thread in step 3) which is coordinated by multiple threads includes: obtaining data block metadata from the read queue, reading the data block with the corresponding offset in the source file, calculating the temporary hash value of the data block, updating the distributed progress table status, and then writing the data block and metadata into the transmission queue. The multi-threaded collaborative scheduling of the transmission thread includes: retrieving data blocks from the transmission queue, transmitting the data blocks to the target storage system via the network, updating the distributed schedule status after the transmission is completed, and writing the data blocks and metadata to the write queue. The write thread, which is coordinated and scheduled by multiple threads, includes: retrieving data blocks from the write queue, writing the data blocks to the target storage system, reading the written data blocks to recalculate the hash value, comparing it with the temporary hash value and updating the distributed progress table status. If they are inconsistent, the data blocks are added back to the read queue for further processing.

[0011] Furthermore, in step 4), the distributed progress management includes an initialization phase, a progress update phase, an exception interruption handling phase, and a completion phase. In the initialization phase, a progress table is created and all data blocks are set to pending status. In the progress update phase, the status is updated atomically after each thread completes its processing step. In the exception interruption handling phase, only data blocks in the pending or failed status are reprocessed during recovery. In the completion phase, the overall file status is updated after all data blocks are in the successful status.

[0012] Furthermore, in step 5), block-level verification includes calculating and comparing the hash value of the data block after reading and writing are completed; file-level verification includes calculating the file hash value and comparing it with the source file after concatenating all data blocks in the original order; and logical order verification includes further parsing of files that depend on the order or structure to verify the integrity of the logical structure.

[0013] A system that supports efficient multi-threaded insertion of large files into the database includes: The large file preprocessing module is used to obtain the write performance parameters of the target storage system and the available computing resources and memory capacity of the source file server, and to provide the required parameters for the dynamic segmentation module and the multi-threaded scheduling module. The dynamic segmentation module is used to divide the files to be imported into the database into multiple data blocks based on the write bandwidth of the target storage system, the single-thread input / output time, and the available CPU cores and memory capacity of the source file server, and dynamically adjust the number and size of the data blocks to ensure load balancing of each thread. The multi-threaded scheduling module is used to set up read threads, transmission threads, and write threads to read data blocks from the source file, transmit them over the network to the target storage system, and write them to the storage. At the same time, the status and metadata of each processing stage are written to the distributed schedule table. The distributed progress management module is used for distributed progress management. It records the processing status of each data block in real time. When an abnormal interruption occurs, it only reprocesses the unfinished data blocks to achieve breakpoint resumption. The multi-level verification module is used to sequentially verify the integrity of data blocks and files at the block level, file level, and logical order, and to reprocess abnormal data blocks. The storage adaptation module is used to adapt storage according to the target storage system type and provides a unified interface for reading, transmitting and writing data blocks to achieve seamless switching between heterogeneous storage systems.

[0014] Furthermore, the dynamic segmentation module is used to increase or decrease the number of data blocks and recalculate the size of each data block based on the comparison results of the size of a single data block with the memory threshold and the processing time of a single thread, so as to determine the optimal segmentation scheme.

[0015] Furthermore, the multi-threaded scheduling module is used for: In the reading thread, data block metadata is obtained from the reading queue, the data block corresponding to the offset of the source file is read, a temporary hash value is calculated, and the distributed progress table status is updated before the data block and metadata are written to the transmission queue. In the transmission thread, data blocks are retrieved from the transmission queue, transmitted to the target storage system via the network, and after the transmission is completed, the distributed schedule status is updated and the data blocks and metadata are written to the write queue. In the write thread, data blocks are retrieved from the write queue, written to the target storage system, the written data blocks are read, the hash value is recalculated and compared with the temporary hash value, the distributed progress table status is updated according to the comparison result, and if they are inconsistent, the data blocks are added back to the read queue for reprocessing.

[0016] Furthermore, the distributed progress management module is used to execute the initialization phase, progress update phase, exception interruption handling phase, and completion phase. In the initialization phase, a progress table is created and all data blocks are set to pending status. In the progress update phase, the status is updated atomically after each thread completes its processing step. In the exception interruption handling phase, only data blocks in the pending or failed status are reprocessed during recovery. In the completion phase, the overall file status is updated after all data blocks are in the successful status.

[0017] Furthermore, the multi-level verification module is used to perform block-level verification to compare data block hash values, perform file-level verification to concatenate data blocks in their original order to calculate file hash values ​​and compare them with the source file, and perform logical order verification to verify the integrity of the file's logical structure that depends on the order or structure.

[0018] Furthermore, the storage adaptation module provides a unified data block read / write interface and path management interface, shielding the underlying differences between different storage systems, and dynamically selecting the target storage system according to the configuration.

[0019] The present invention has achieved the following beneficial effects.

[0020] 1. This invention achieves high-speed data entry for large files in distributed or centralized storage systems through a dynamic adaptive segmentation algorithm and a multi-threaded collaborative scheduling mechanism. The data entry time for GB-level files can be reduced to 1 / 5 to 1 / 3 of that of traditional single-threaded methods, and the efficiency improvement for TB-level files is even more significant.

[0021] 2. This invention achieves balanced resource utilization by dynamically adjusting threads and data blocks based on storage performance and hardware resources, stabilizing CPU utilization at 70%~85%, and improving memory and network bandwidth utilization by more than 30%.

[0022] 3. This invention achieves powerful breakpoint resumption capability by reprocessing only incomplete data blocks through distributed progress management, significantly reducing the repeated transmission time after abnormal interruption of large files.

[0023] 4. This invention uses a multi-level data verification mechanism to verify the entire process of reading, transmitting and writing data blocks, ensuring that the files entering the database are completely consistent with the source files, and reducing the data corruption rate to below 0.001%.

[0024] 5. This invention encapsulates different storage system interfaces through a storage adaptation module, supporting multiple storage targets such as HDFS, S3, and MySQL. Switching can be done without modifying the core logic, and its adaptability is superior to traditional methods.

[0025] 6. The core components of this invention are designed with a modular approach, supporting horizontal expansion and capable of handling the ingestion of petabyte-level data, demonstrating excellent scalability and adaptability. Attached Figure Description

[0026] Figure 1 This is a system architecture diagram for supporting efficient multi-threaded insertion of large files into the database according to the present invention.

[0027] Figure 2 This is a flowchart of the method for supporting efficient multi-threaded insertion of large files into the database according to the present invention.

[0028] Figure 3This is a sequence diagram of large file import into the database in the embodiment. Detailed Implementation

[0029] To make the various technical features, advantages, or effects of the present invention more apparent and understandable, detailed descriptions are provided below through embodiments.

[0030] This embodiment provides a system that supports efficient multi-threaded insertion of large files into the database, such as... Figure 1 As shown, the system includes a large file preprocessing module, a dynamic segmentation module, a multi-threaded scheduling module, a distributed progress management module, a multi-level verification module, and a storage adaptation module. These modules work collaboratively to form a complete multi-threaded large file ingestion process. This system can dynamically adjust the file segmentation strategy and the number of threads based on storage system performance, implement breakpoint resumption through a distributed progress table, and ensure data integrity by combining hash verification and sequential consistency verification, thereby achieving efficient, reliable, and adaptive large file ingestion.

[0031] 1. Large file preprocessing module.

[0032] The large file preprocessing module is used to obtain the write performance parameters of the target storage system and the available computing resources and memory capacity of the source file server before data is written to the database, and saves the measured parameters as a configuration file to provide a basis for subsequent dynamic data block segmentation and multi-threaded scheduling.

[0033] 2. Dynamic segmentation module.

[0034] The dynamic partitioning module adopts a storage performance-aware dynamic adaptive partitioning method. Compared with the traditional fixed-size partitioning strategy, this method can determine the optimal file partitioning scheme in real time according to the performance characteristics of different storage systems, so that the data block size processed by each thread matches the write capacity of the storage system, avoiding resource waste and uneven thread load.

[0035] Let the file to be inserted into the database be F, and its size be S (in bytes); the target storage system be T, whose pre-probing maximum write bandwidth per node is B (in MB / s), and the average time for single-threaded I / O operations is t (in ms); the number of available CPU cores in the system is C, and the available memory capacity is M (in GB). The goal of the partitioning is to divide the file F into K data blocks, each of size S. It satisfies the following constraints: (1) The sum of the sizes of all data blocks satisfies ; (2) The size of each data block does not exceed the memory cache threshold, i.e. The remaining 80% of memory is used for other parallel operations; (3) Time consumption for processing a single data block by a single thread The threads are basically balanced, with differences not exceeding 10%, in order to avoid hot threads.

[0036] The above dynamic adaptive segmentation method includes the following steps: Step 1: Storage performance pre-probe.

[0037] A temporary probe thread sends N (N≥5) test data blocks of different sizes (e.g., 16MB, 32MB, 64MB) to the storage system T, records the write time, and uses linear regression to calculate the storage system's maximum write bandwidth B and average I / O time t. Simultaneously, system monitoring information is acquired to determine the number of available CPU cores C and the available memory capacity M.

[0038] Step 2: Calculate the initial number of segments.

[0039] Calculate the initial number of threads based on hardware resources and storage performance. in, Used to avoid over-scheduling Reflects the maximum number of threads supported by memory and storage bandwidth (each thread must process at least...) (Size data blocks to reduce thread switching overhead).

[0040] Step 3: Dynamically adjust the data block size.

[0041] Calculate the initial data block size .

[0042] when If this occurs, it indicates that a single block of data exceeds the memory threshold, and the number of threads should be increased. And recalculate .

[0043] when Too small a time will cause single-threaded time consumption. If thread switching ratio is too high, reduce the number of threads to [a lower threshold]. And recalculate .

[0044] Finally, based on the above conditions, the optimal number of partitions K and the data block size s are determined (allowing the last data block size to be slightly smaller than s to satisfy the condition). ).

[0045] 3. Multi-threaded scheduling module.

[0046] The multi-threaded scheduling module constructs a scheduling strategy based on the "producer-consumer" model, decoupling the three stages of file reading, data transmission, and storage writing. It achieves collaborative processing through hierarchical task queues and combines dynamic thread pools to achieve adaptive load scheduling, avoiding multi-threaded resource contention.

[0047] (1) Task queue design.

[0048] The task queue is divided into three queues with the same priority based on the processing stage: File read queue: Stores the index of the data block to be read and its offset; Data transmission queue: Stores the content of read data blocks and their metadata; Storage write queue: Stores the data block content to be written to the target storage system and the target path.

[0049] (2) Thread role division.

[0050] a. Read Thread (R Thread): Retrieves tasks from the file read queue, reads local file data blocks according to the offset and size; calculates the temporary hash value (such as MD5) of the data block, and writes the data block and its metadata (including data block ID, offset, and temporary hash value) into the data transfer queue.

[0051] b. Transmission Thread (T Thread): If the storage system is a distributed system (such as HDFS), the thread retrieves tasks from the data transmission queue, transmits the data blocks to the target storage node via the network, and writes the transmitted data blocks and metadata into the storage write queue. If the storage system is local storage, this thread role can be merged with the read thread.

[0052] c. Write Thread (W Thread): Retrieves tasks from the storage write queue, writes data blocks to the specified storage path, records the write progress, and triggers the subsequent data verification process.

[0053] (3) Dynamic thread count adjustment.

[0054] The module adaptively adjusts the number of threads based on the backlog of tasks in the queue. For example, when the backlog of file read queues exceeds a threshold (e.g., more than 50 tasks), the number of R threads is automatically increased, up to a maximum of 1.2 times the number of CPU cores; when the storage write queue is backlogged, the number of W threads is increased to the maximum number of concurrent write connections allowed by the storage system; this is to avoid bottlenecks caused by a single processing stage and to achieve dynamic optimization of overall throughput.

[0055] 4. Distributed progress management module.

[0056] To address the issue of data recovery in the event of abnormal interruptions, the distributed progress management module introduces a distributed progress table (implemented based on Redis or ZooKeeper) and employs a breakpoint resume mechanism to record the processing status of each data block in real time. When the system recovers, only the unfinished data blocks need to be reprocessed; completed steps do not need to be re-executed.

[0057] (1) Distributed schedule structure.

[0058] Progress information is stored using a Redis hash table with the key name format: file:progress:{fileId}, where fileId is a unique identifier of the file to be added to the database (e.g., a hash value combining the file path and modification time).

[0059] The fields recorded in the hash table include: total_chunks (total number of data blocks), chunk_size (size of data blocks), status (overall file entry status, such as init, processing, completed, failed, etc.), chunk_{i}_status (processing status of the i-th data block, such as pending, reading, transmitting, writing, success, failed, etc.), chunk_{i}_hash (final verification hash value of the data block, which is recorded upon success), and last_update (last update time of the progress table).

[0060] (2) Resuming interrupted download process.

[0061] 1) Initialization phase: When the database is first entered, a progress table is created and initialized: total_chunks = K, chunk_size = s, status = init; all chunks_{i}_status = pending.

[0062] 2) Progress update phase: After completing the corresponding processing step, each thread updates the status of the corresponding data block in the progress table in an atomic manner: After the R thread completes the data block reading, it changes chunk_{i}_status from pending to reading and then to transmitting; after the T thread completes the network transmission, it updates it to writing; after the W thread completes the storage writing, it updates it to success and records the final verification hash value of the data block.

[0063] 3) Exception interruption handling: After the system restarts due to an exception (such as network interruption or thread crash), the module executes the following logic: If status = completed, it directly returns that the data was successfully inserted; if status = failed or last_update times out (e.g., 300 seconds), it marks all data blocks with the status of reading / transmitting / writing as failed; it rebuilds the task queue, adds only the data blocks with the status of pending or failed back to the task queue, and restarts each thread to execute.

[0064] 4) Completion phase: When all chunk_{i}_status = success, update the overall file status to completed; write all chunk_{i}_hash; the progress table is automatically cleaned up after a specified time (e.g., 7 days) according to the strategy, or persisted as needed.

[0065] 5. Multi-level verification module.

[0066] To ensure the consistency between the imported files and the source files and to avoid data loss, corruption, or damage, the multi-level verification module adopts a three-level verification mechanism of "block-level verification - file-level verification - logical sequence verification" to verify data integrity layer by layer.

[0067] (1) Block-level verification.

[0068] During the data block processing phase, bidirectional verification during reading and writing ensures the integrity and reliability of a single data block: After completing the reading of the data block, the R thread calculates the MD5 hash value of the data block and writes it to the distributed schedule table; after completing the writing of the data block, the W thread reads the data block from the storage system again, recalculates the MD5 hash value, and compares it with the value recorded in the schedule table; if the two hash values ​​are inconsistent, the data block status is marked as failed and it is re-added to the task processing flow.

[0069] (2) File-level verification.

[0070] After all data blocks have been written, the overall consistency of the file is confirmed through a comprehensive reconstruction: all data blocks are read by calling the storage system interface and assembled into a complete file according to the block order of the original file; the MD5 hash value of the assembled file is calculated and compared with the MD5 of the source file; if they match, the overall content of the file is confirmed to be correct; if they do not match, the abnormal data block is located according to the chunk_{i}_hash in the progress table, and the processing flow is re-executed for the located data block.

[0071] (3) Logical order verification.

[0072] For file types that depend on the order and structure of data blocks (such as video files, executable files, etc.), after the file-level verification passes, the logical structure of the file content is further verified: the corresponding file format parsing tool is called (for example, FFmpeg for video files and ELF parser for executable files) to check the structural integrity and parsability of the file; if the parsing is normal, the logical structure of the file is confirmed to be correct; if the parsing fails, the relevant data blocks are traced back and reprocessed.

[0073] 6. Storage adaptation module.

[0074] To adapt to different types of storage systems, this module adopts a storage adaptation layer design. It encapsulates the write logic of heterogeneous storage systems through a unified interface, eliminating the need for modifications to the core processing flow for specific storage types and enabling flexible switching of storage targets. The adaptation layer is responsible for: providing unified write, read, and path management interfaces; shielding the implementation details of underlying storage differences (such as local file systems, HDFS, object storage, etc.); supporting dynamic selection of the target storage system based on configuration; and maintaining consistent behavioral semantics across different storage environments.

[0075] This embodiment also provides a method for supporting efficient multi-threaded data import of large files, used to import an 18G 4K video file (filename video_20240904_18G.mp4, MP4 format, unencrypted) stored on the local disk of the source file server into the HDFS distributed storage system. The goal is to achieve the following while ensuring data consistency: (1) efficient dynamic segmentation and multi-threaded parallel writing of large files; (2) resume transmission from interruption due to network interruption or node failure during the data import process; (3) file-level consistency verification after data import is completed; the overall data import time does not exceed 60 minutes and the data corruption rate is 0%. The process is as follows: Figure 2 As shown.

[0076] Step 1: Storage performance pre-probing and dynamic partitioning calculation.

[0077] This step involves pre-testing HDFS write performance and available resources on the source file server to calculate the appropriate thread size and data block size for this data insertion task, thereby achieving dynamic adaptation of the partitioning scheme.

[0078] 1. Storage performance pre-probing Obtain HDFS performance metrics and source file server hardware resource information through a pre-probing script, including the following sub-steps: (1) Write performance probing: Six test data blocks of different sizes were sent to the HDFS cluster in sequence, with sizes of 32MB, 64MB, 128MB, 256MB, 512MB and 1024MB respectively, and the time taken for each write was recorded (unit: ms).

[0079] (2) Performance parameter estimation: Based on multiple sets of write results, linear regression analysis was performed to obtain the maximum write bandwidth of a single HDFS DataNode B = 80 MB / s; the average time of single-thread I / O operation t = 20 ms (including the time spent on connection establishment and metadata interaction).

[0080] (3) Obtaining available system resources: Obtain the current available computing resources of the source file server through the psutil library: available CPU cores C = 14; available memory M = 48 GB.

[0081] 2. Dynamic segmentation calculation.

[0082] Calculate the initial number of threads based on the system and storage performance parameters obtained from the probe. And determine the final number of partitions K and the data block size s.

[0083] (1) Calculation of the initial number of threads.

[0084] Calculate the maximum number of supported threads based on the constraints of CPU, memory, and storage bandwidth: Maximum number of threads based on CPU: ( (This exceeds the number of available cores, so we take 14).

[0085] Maximum number of threads based on memory: Maximum available data block size per thread Minimum acceptable size of data blocks ; (Sufficient memory, far exceeding CPU limitations).

[0086] Maximum number of threads based on storage bandwidth: .

[0087] Final initial number of threads: .

[0088] (2) Calculation of data block size.

[0089] The total file size is: .

[0090] Initial data block size: .

[0091] Constraint checks: Memory constraints: This satisfies the constraint of processing time balance: single-threaded processing time... The time difference among the 12 thread tasks is ≤0.1%, which meets the balance requirement.

[0092] Final partitioning scheme: number of partitions K=12; standard data block size s = 1536 MB; the last data block size is still 1536 MB. No additional adjustments are needed.

[0093] Step 2: Distributed schedule initialization.

[0094] To achieve unified scheduling and breakpoint recovery for the multi-threaded data insertion process, a distributed progress table for the target file is created atomically using Redis. In this embodiment, the progress table key name is generated based on the MD5 hash of the file path and modification time: fileId = md5(video_20240904_18G.mp4_path+modify_time) = a1b2c3d4e5f6a7b8c9d0e1f2a3b4c5d6.

[0095] 1. Initialize the data block state.

[0096] Write the initial status (pending) to each of the 12 data blocks one by one. An example command is as follows: `for i in 1..12; do; HSET file:progress:a1b2c3d4e5f6a7b8c9d0e1f2a3b4c5d6 chunk_${i}_status pending; done;`

[0097] 2. Set the schedule lifespan. Set the time-to-live (TTL) of the schedule to 604,800 seconds (7 days) to ensure sufficient time for recovery in case of anomalies: EXPIRE file:progress:a1b2c3d4e5f6a7b8c9d0e1f2a3b4c5d6 604800.

[0098] Step 3: Multi-threaded collaborative scheduling and data import.

[0099] The main data insertion program (implemented in Python) is launched, employing a producer-consumer model consisting of a read thread (R thread), a transfer thread (T thread), and a write thread (W thread) to execute the entire data insertion process. The thread configuration is as follows: Number of R threads: 4 (to avoid excessive contention for disk I / O); Number of threads: 6 (to match network bandwidth); W thread count: 12 (corresponding to the number of data blocks and matching the concurrent write capability of HDFS).

[0100] The entire process includes task queue initialization, coordinated execution of three types of threads, and dynamic thread adjustment in case of abnormal situations.

[0101] 1. Task queue initialization.

[0102] Create three thread-safe queues (based on queue.Queue) for passing data blocks and intermediate results across threads: read_queue: Stores metadata (chunk_id, offset, size_mb) for 12 data blocks; trans_queue: Stores data blocks (chunk_id, data, md5_temp) that have been read by the R thread. write_queue: Stores the data blocks (chunk_id, data, md5_temp, hdfs_path) that have been transferred by thread T.

[0103] 2. Execution flow of the read thread (R thread) (4 threads in parallel).

[0104] (1) Obtain the metadata of the data block to be processed from read_queue (e.g., chunk_id = 1, offset = 0, size_mb = 1536).

[0105] (2) Call open(' / data / source / video_20240904_18G.mp4', 'rb') to open the source file, and then... Locate the offset and read Bytes of data.

[0106] (3) Calculate the temporary MD5 value of the data block, for example: md5_temp = “f8a7b6c5d4e3f2a1b0c9d8e7f6a5b4c3”.

[0107] (4) Atomically update the Redis progress table, setting the data block status sequentially from reading to transmitting.

[0108] (5) Write (chunk_id, data, md5_temp) into trans_queue.

[0109] 3. Execution flow of the transmission thread (T thread) (6 threads in parallel).

[0110] (1) Obtain the data block to be transmitted from trans_queue.

[0111] (2) Generate the target HDFS path based on chunk_id, for example: hdfs: / / nn-001:9000 / user / video / 202409 / video_20240904_18G.mp4.chunk1.

[0112] (3) Establish a connection through pyhdfs.HdfsClient and write the data block to HDFS (enable Snappy compression to reduce network transmission volume).

[0113] (4) After the transmission is completed, the Redis progress table is updated atomically and the status is set to writing.

[0114] (5) Write (chunk_id, data, md5_temp, hdfs_path) into write_queue.

[0115] 4. Execution flow of the write thread (W thread) (12 threads in parallel).

[0116] (1) Get the data block to be written from write_queue.

[0117] (2) Call hdfs_client.write(hdfs_path, data, overwrite=True) to write the data block to HDFS.

[0118] (3) After writing, call hdfs_client.read(hdfs_path) to read the data block and recalculate the MD5: md5_check = hashlib.md5(read_data).hexdigest().

[0119] (4) Perform consistency check: If md5_check == md5_temp: Atomically update the progress table, mark the block as successful, and write chunk_1_hash = md5_check; If inconsistent: Mark the status of the data block as failed, and add it back to read_queue for reprocessing.

[0120] (5) Record the writing time and synchronize it to the main program in real time through the monitoring script (e.g., the writing time of chunk_id=1 is 18.9s).

[0121] 5. Dynamic thread adjustment (handling abnormal scenarios).

[0122] Eight minutes into the data ingestion process, the monitoring script detected a backlog of 5 data blocks in the write_queue (indicating that the processing speed of thread W is lower than the transmission speed of thread T). The system then implemented the following automatic adjustment strategy: (1) Detect and handle HDFS node anomalies: The write bandwidth of DataNode dn-002 was detected to drop to 40MB / s (disk I / O busy) through HDFS API, and the node was temporarily removed from the list of writable nodes.

[0123] (2) Adjust the number of threads: the number of W threads is increased from 12 to 14 (to accelerate writing by utilizing the remaining CPU cores); the number of T threads is reduced from 6 to 4 (to reduce writing pressure and suppress the continued expansion of write_queue accumulation).

[0124] (3) Recovery mechanism: After 3 minutes, the write_queue backlog is cleared, and the W thread and T thread are restored to 12 and 6 respectively.

[0125] Step 4: Verify breakpoint resume (simulate network interruption scenario).

[0126] 1. Inbound interruption triggered.

[0127] At the 30-minute mark of file import, a network interruption (simulating a fiber optic failure) is simulated between the source file server and the HDFS cluster. The system automatically triggers the resume download process.

[0128] 2. Interruption detection.

[0129] Both threads T and W detected network connection errors. The main program marked all data blocks (chunk_id=7, 8, 9) in the transmitting or writing state as failed.

[0130] Simultaneously update the Redis progress table: HSET file:progress:a1b2c3d4e5f6a7b8c9d0e1f2a3b4c5d6; chunk_7_status failed; chunk_8_status failed; chunk_9_status failed; last_update 1725402000.

[0131] 3. Network recovery and program restart.

[0132] Twenty minutes after network recovery, the main program first checks the Redis progress table upon startup. The check reveals that the file status is "init" (incomplete), and "last_update" has not timed out (only 20 minutes since the interruption, less than 300 seconds). Data blocks with statuses of "pending" or "failed" (chunk_id=7, 8, 9, 10, 11, 12) are then selected.

[0133] 4. Re-enter the data block into the database.

[0134] Add the filtered data block metadata back to the read_queue. Restart the R, T, and W threads, processing only incomplete data blocks. Data blocks that have been successfully added to the database (chunk_id=1~6) do not need to be reprocessed.

[0135] 5. Progress recovery and completion.

[0136] After 45 minutes, all data blocks were marked as successful. The main program updated the Redis progress table, marking the file status as complete: HSET file:progress:a1b2c3d4e5f6a7b8c9d0e1f2a3b4c5d6 statuscompleted.

[0137] The following is a specific example.

[0138] This embodiment uses the efficient multi-threaded import of a 10GB business log archive file into the HDFS distributed storage system as an example to fully illustrate the implementation of this invention. This scenario aims to solve the problems of low efficiency, uneven resource utilization, lack of breakpoint resumption, and difficulty in guaranteeing data consistency associated with traditional single-threaded import processes. It achieves high-speed and reliable import of 10GB files, with an import time of ≤40 minutes and a data corruption rate of 0%. Figure 3 As shown.

[0139] I. Environment and Prerequisites.

[0140] 1. Hardware and storage environment: The source file server and HDFS distributed storage cluster have been deployed.

[0141] 2. Core modules: The dynamic segmentation module, multi-threaded scheduling module, distributed progress management module, and multi-level verification module have been configured and integrated.

[0142] 3. Redis Cluster: Pre-set file progress hash table and data block status record key. The progress table TTL is set to 604800 seconds to ensure sufficient recovery time after an anomaly.

[0143] 4. Auxiliary tools and scripts: including storage performance pre-detection scripts, multi-threaded scheduling main program, and breakpoint resume recovery scripts.

[0144] 5. Information on the file to be added to the database: file size 10GB, TXT format, stored on the source file server path / data / source / logs / , file modification time is 2024-09-04 10:00:00, source file MD5 value is f0e1d2c3b4a5f6e7d8c9b0a1f2e3d4c5.

[0145] II. Warehousing Process.

[0146] Step 1: Storage performance pre-probe and parameter acquisition.

[0147] A Python pre-probing script was launched to send six different data blocks of 32MB, 64MB, 128MB, 256MB, 512MB, and 1024MB to the HDFS cluster, recording the write time (ms) for each operation. Core performance parameters were calculated using linear regression analysis: maximum write bandwidth per HDFS DataNode B = 80MB / s; average single-threaded I / O time t = 20ms (including connection establishment and metadata exchange time); and the available resources of the source file server were read using the psutil tool: CPU cores C = 12, memory capacity M = 32GB. The probing results were saved to performance_params.json for subsequent calculations by the dynamic partitioning module.

[0148] Step 2: Dynamic segmentation and generation of data block metadata.

[0149] The dynamic segmentation module determines the optimal segmentation scheme based on the performance parameters in performance_params.json, following a process of "pre-probing - initial calculation - dynamic adjustment": 1. Initial thread count calculation: based on CPU limitations: (Reserve 40% of CPU cores for other tasks); Based on memory limitations: maximum data block per thread Minimum size of a single data block ; Based on storage bandwidth: Initial number of threads .

[0150] 2. Data block size calculation: Standard data block size Memory verification: (Meets memory constraints); Time-balanced processing check: Single-threaded processing time (Time difference ≤ 0.1%); Dynamic adjustment: The size of the first 11 data blocks is set to 854MB, and the size of the 12th data block... Ensure the total size is consistent with the original file.

[0151] 3. Data block metadata generation: Generate a chunk_metadata.json file, which records the index (1~12), offset (0MB, 854MB, 1708MB...9394MB) and size (854MB for the first 11, 846MB for the 12th) of 12 data blocks.

[0152] Step 3: Initialize the distributed schedule.

[0153] 1. Initialize file unique identifier: Calculate the file unique identifier based on the file path and modification time: fileId = md5(file path + modification time) = d7e8f9a0b1c2d3e4f5a6b7c8d9e0f1a2.

[0154] 2. Basic Information Storage for the Progress Table: Initialize the file progress table using atomic operations performed by Redis CLI: HSETfile:progress:d7e8f9a0b1c2d3e4f5a6b7c8d9e0f1a2;total_chunks 12;chunk_size 895412224;status init;last_update 1725405000; where chunk_size represents the standard data block size (854MB corresponds to the number of bytes), status is initialized to init, and last_update is the current timestamp.

[0155] 3. Initialize data block status: Set the status of all 12 data blocks to pending: for i in 1..12; do; HSET file:progress:d7e8f9a0b1c2d3e4f5a6b7c8d9e0f1a2 chunk_${i}_status pending; done.

[0156] 4. Set the progress expiration time: EXPIRE file:progress:d7e8f9a0b1c2d3e4f5a6b7c8d9e0f1a2 604800; to ensure sufficient recovery time in case of abnormal situations.

[0157] Step 4: Multi-threaded scheduling and data block insertion execution.

[0158] 1. Thread Pool and Task Queue Initialization: Start the multi-threaded scheduling main program, and establish a hierarchical task queue and thread pool using a "producer-consumer" model; create thread-safe queues: read_queue: stores metadata for 12 data blocks; trans_queue: stores read data blocks and their corresponding metadata; write_queue: stores data blocks to be written to HDFS and their target paths. Configure the number of threads: 3 read threads (R threads); 6 transfer threads (T threads); 12 write threads (W threads).

[0159] 2. Read thread execution logic (taking chunk_id=1 as an example): Retrieve data block metadata from read_queue (chunk_id=1, offset_mb=0, size_mb=854). Open the source file / data / source / logs / log_20240904_10G.log, locate the offset using seek, and read... Byte data. Calculate the temporary MD5 hash of the data block: `md5_temp = hashlib.md5(data).hexdigest() = "a3b4c5d6e7f8a9b0c1d2e3f4a5b6c7d8"`. Atomic update of the Redis progress table: mark `chunk_1_status` as `reading`, and update it to `transmitting` after the read is complete. Place `(chunk_id=1, data, md5_temp)` into the `trans_queue`.

[0160] 3. Transmission thread execution logic (taking chunk_id=1 as an example): Retrieves a data block from the trans_queue and generates the HDFS target path: hdfs: / / nn-001:9000 / user / logs / 202409 / log_20240904_10G.log.chunk1. Establishes a connection using pyhdfs.HdfsClient(hosts='nn-001:50070') and enables Snappy compression for transmitting data blocks. After transmission, updates the progress table: chunk_1_status = writing, last_update = 1725405120. Adds (chunk_id=1, data, md5_temp, hdfs_path) to the write_queue.

[0161] 4. Write thread execution logic (taking chunk_id=1 as an example): Retrieve the data block from the write_queue and call hdfs_client.write(hdfs_path, data, overwrite=True) to write it to HDFS. Reread the data block in HDFS and calculate the MD5 checksum: md5_check=hashlib.md5(read_data).hexdigest(). After successful verification, update the Redis progress table: HSET file:progress:d7e8f9a0b1c2d3e4f5a6b7c8d9e0f1a2; chunk_1_status success; chunk_1_hash "a3b4c5d6e7f8a9b0c1d2e3f4a5b6c7d8"; last_update1725405180. Record the write time (approximately 10.5 seconds) and report it to the main program monitoring panel.

[0162] Step 5: Dynamic thread adjustment and exception handling.

[0163] 1. Thread backlog monitoring: At the 12-minute mark of data insertion, the main program monitoring detected a backlog of 4 data blocks in the write_queue. This was because the processing speed of thread W was lower than the transmission speed of thread T. The system triggered dynamic thread adjustment logic.

[0164] 2. HDFS Node Status Detection and Handling: By detecting the node status through the HDFS API, it was found that the write bandwidth of DataNode dn-003 dropped to 30MB / s (disk I / O is busy). The node was temporarily removed from the write node list to avoid further impact on the data entry efficiency.

[0165] 3. Dynamic adjustment of the number of threads: The number of write threads (W threads) is increased from 12 to 14 to utilize the idle CPU resources of the source file server; the number of transfer threads (T threads) is reduced from 6 to 4 to reduce the transfer rate and prevent further queue accumulation.

[0166] 4. Recovery and Callback: Five minutes after the adjustment, the write_queue backlog is cleared, and the main program automatically restores 12 W threads and 6 T threads to ensure the stability of subsequent processes.

[0167] Step 6: Verify and restore the resume function.

[0168] 1. Network Interruption Handling: At the 25-minute mark of the data transfer process, a fiber optic cable failure is simulated between the source file server and the HDFS cluster, triggering the resume transfer process. Threads T and W throw connection exceptions, and the main program marks data blocks chunk_9~chunk_12 in the transmitting or writing state as failed. The Redis progress table is updated: HSET file:progress:d7e8f9a0b1c2d3e4f5a6b7c8d9e0f1a2; chunk_9_status failed; chunk_10_status failed; chunk_11_status failed; chunk_12_status failed; last_update 1725406500.

[0169] 2. Recovery and Re-insertion: 15 minutes after network recovery, the main program checks the progress table and finds that the file status is "init" and "last_update" has not timed out. Data blocks chunk_9~chunk_12 with statuses of "pending" or "failed" are filtered and re-added to the read_queue; threads are restarted to process incomplete data blocks, ensuring that only data blocks that were not successfully inserted into the database are processed.

[0170] 3. Progress Update: Ten minutes after re-entry into the database, chunks 9 through 12 have all been processed. The main program updates the Redis progress table: HSET file:progress:d7e8f9a0b1c2d3e4f5a6b7c8d9e0f1a2;statuscompleted;last_update 1725407400.

[0171] Step 7: Multi-level data consistency verification.

[0172] 1. Block-level verification: When writing each data block, the W thread performs "read-after-write verification". All 12 data blocks pass the MD5 check and no retry is required.

[0173] 2. File-level verification: 12 data blocks are read sequentially through the HDFS API, concatenated into a complete file, and the global MD5 value is calculated: MD5 = "f0e1d2c3b4a5f6e7d8c9b0a1f2e3d4c5"; it is completely consistent with the MD5 of the source file, ensuring file integrity.

[0174] 3. Logical order verification: Use the Python fileinput module to read the input file, randomly select 100 lines of logs and compare them with the source file. If there is no disorder or distortion of the order, the logical consistency is confirmed.

[0175] Although the present invention has been disclosed above with reference to embodiments, it is not intended to limit the present invention. Appropriate modifications or equivalent substitutions made by those skilled in the art to the technical solutions of the present invention should be covered within the protection scope of the present invention, which is defined by the claims.

Claims

1. A method for efficiently inserting large files into a database using multi-threading, characterized in that, Includes the following steps: 1) Obtain the write bandwidth, single-threaded input / output time, and available computing resources and memory capacity of the source file server for the target storage system; 2) Divide the large file to be stored into multiple data blocks, and calculate the initial number of blocks based on the maximum write bandwidth of a single node of the storage system, the single-thread input / output time, the number of available CPU cores and the available memory capacity. Adjust the number of blocks based on the size of a single data block, the memory threshold and the single-thread time to determine the optimal number and size of data blocks. 3) Set up multi-threaded collaborative scheduling for reading, transmitting, and writing threads to sequentially read data blocks from the source file, transmit them over the network to the target storage system, and write them to the storage. At the same time, write the processing status and metadata of each stage into a distributed schedule. 4) Implement distributed progress management, record the processing status of each data block in real time, and only reprocess the unfinished data blocks when an abnormal interruption occurs, thus realizing breakpoint resume. 5) Perform sequential checks at the block level, file level, and logical order to verify the integrity of data blocks and files, and re-execute the processing flow for abnormal data blocks; 6) Perform storage adaptation based on the target storage system type, and complete the reading, transmission and writing of data blocks through a unified interface to achieve seamless switching between heterogeneous storage systems.

2. The method as described in claim 1, characterized in that, In step 3), the multi-threaded collaborative scheduling of the reading thread includes: obtaining data block metadata from the reading queue, reading the data block with the corresponding offset in the source file, calculating the temporary hash value of the data block, updating the distributed progress table status, and then writing the data block and metadata into the transmission queue. The multi-threaded collaborative scheduling of the transmission thread includes: retrieving data blocks from the transmission queue, transmitting the data blocks to the target storage system via the network, updating the distributed schedule status after the transmission is completed, and writing the data blocks and metadata to the write queue. The write thread, which is coordinated and scheduled by multiple threads, includes: retrieving data blocks from the write queue, writing the data blocks to the target storage system, reading the written data blocks to recalculate the hash value, comparing it with the temporary hash value and updating the distributed progress table status. If they are inconsistent, the data blocks are added back to the read queue for further processing.

3. The method as described in claim 1, characterized in that, Step 4) Distributed progress management includes an initialization phase, a progress update phase, an exception interruption handling phase, and a completion phase. In the initialization phase, a progress table is created and all data blocks are set to pending status. In the progress update phase, the status is updated atomically after each thread completes its processing step. In the exception interruption handling phase, only data blocks in the pending or failed status are reprocessed during recovery. In the completion phase, the overall file status is updated after all data blocks are in the successful status.

4. The method as described in claim 1, characterized in that, Step 5) Block-level verification includes calculating and comparing the hash value of the data block after reading and writing are completed; file-level verification includes calculating the file hash value and comparing it with the source file after concatenating all data blocks in the original order; logical order verification includes further parsing of files that depend on the order or structure to verify the integrity of the logical structure.

5. A system supporting efficient multi-threaded data import of large files, used to execute the method described in any one of claims 1-4, characterized in that, include: The large file preprocessing module is used to obtain the write performance parameters of the target storage system and the available computing resources and memory capacity of the source file server, and to provide the required parameters for the dynamic segmentation module and the multi-threaded scheduling module. The dynamic segmentation module is used to divide the files to be imported into the database into multiple data blocks based on the write bandwidth of the target storage system, the single-thread input / output time, and the available CPU cores and memory capacity of the source file server, and dynamically adjust the number and size of the data blocks to ensure load balancing of each thread. The multi-threaded scheduling module is used to set up read threads, transmission threads, and write threads to read data blocks from the source file, transmit them over the network to the target storage system, and write them to the storage. At the same time, the status and metadata of each processing stage are written to the distributed schedule table. The distributed progress management module is used for distributed progress management. It records the processing status of each data block in real time. When an abnormal interruption occurs, it only reprocesses the unfinished data blocks to achieve breakpoint resumption. The multi-level verification module is used to sequentially verify the integrity of data blocks and files at the block level, file level, and logical order, and to reprocess abnormal data blocks. The storage adaptation module is used to adapt storage according to the target storage system type and provides a unified interface for reading, transmitting and writing data blocks to achieve seamless switching between heterogeneous storage systems.

6. The system as described in claim 5, characterized in that, The dynamic segmentation module is used to increase or decrease the number of data blocks and recalculate the size of each data block based on the comparison results of the size of a single data block with the memory threshold and the processing time of a single thread, so as to determine the optimal segmentation scheme.

7. The system as described in claim 5, characterized in that, The multi-threaded scheduling module is used for: In the reading thread, data block metadata is obtained from the reading queue, the data block corresponding to the offset of the source file is read, a temporary hash value is calculated, and the distributed progress table status is updated before the data block and metadata are written to the transmission queue. In the transmission thread, data blocks are retrieved from the transmission queue, transmitted to the target storage system via the network, and after the transmission is completed, the distributed schedule status is updated and the data blocks and metadata are written to the write queue. In the write thread, data blocks are retrieved from the write queue, written to the target storage system, the written data blocks are read, the hash value is recalculated and compared with the temporary hash value, the distributed progress table status is updated according to the comparison result, and if they are inconsistent, the data blocks are added back to the read queue for reprocessing.

8. The system as described in claim 5, characterized in that, The distributed progress management module is used to execute the initialization phase, progress update phase, exception interruption handling phase, and completion phase. In the initialization phase, a progress table is created and all data blocks are set to pending status. In the progress update phase, the status is updated atomically after each thread completes its processing step. In the exception interruption handling phase, only data blocks in the pending or failed status are reprocessed during recovery. In the completion phase, the overall file status is updated after all data blocks are in the successful status.

9. The system as described in claim 5, characterized in that, The multi-level verification module is used to perform block-level verification to compare the hash values ​​of data blocks, perform file-level verification to concatenate data blocks in their original order to calculate the file hash value and compare it with the source file, and perform logical order verification to verify the integrity of the file's logical structure that depends on the order or structure.

10. The system as described in claim 5, characterized in that, The storage adaptation module provides a unified data block read / write interface and path management interface, shielding the underlying differences between different storage systems, and dynamically selecting the target storage system based on the configuration.

Images