A partition table data loading method based on an openGauss database
By setting cache queues and partition thresholds for partitioned tables in the openGauss database, and dynamically managing the opening and closing of partitions, the problems of resource waste and slow loading speed are solved, and efficient partitioned table data loading is achieved.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- BEIJING VASTDATA TECH
- Filing Date
- 2022-11-21
- Publication Date
- 2026-05-15
AI Technical Summary
Existing technologies suffer from resource waste and slow loading speed during the loading of partitioned tables in the openGauss database. In particular, after the data is distributed to various partitions, partitions that have not been used for a long time remain open, resulting in wasted system resources and insufficient IO resources.
By creating a cache queue for each partition and setting thresholds for the cache queue and partitions, the system controls data writing to partitions and the opening and closing of partitions, including quantity and time thresholds. It employs an LRU ring management system to dynamically adjust the opening and closing of partitions.
It effectively saves IO resources, improves the loading speed of partitioned tables, reduces resource waste, and enables flexible and efficient data loading.
Smart Images
Figure CN115827692B_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of database loading technology, and in particular to a method for loading partitioned table data based on the openGauss database. Background Technology
[0002] like Figure 1 As shown, partitioned tables divide the data in a large table into many small subsets called partitions, a technique widely supported by modern databases. Based on their internal properties, partitioned tables can be categorized into range partitioned tables, list partitioned tables, and hash partitioned tables.
[0003] like Figure 2 As shown, typically, data generated by the loading thread is directly written to a large table, and the database determines which partition each piece of data should be written to based on rules. In practical applications, the data to be loaded may be inserted into any partition, or only some partitions, depending on the partitioning rules. Loading requires opening and accessing a variable number of partitions. When users import large amounts of data into the partitioned tables of the openGauss database, each partition may need to be opened, and some opened partitions may not receive any data for a long time. Opening a large number of partitions also wastes system resources. Furthermore, because the data is scattered across partitions, resulting in random disk access, the write volume to each individual partition is easily unsaturated, with each write generating a low number of I / O operations, still leading to wasted system resources.
[0004] Therefore, how to provide a high-speed loading method for partitioned tables in the openGauss database has become an urgent problem to be solved. Summary of the Invention
[0005] In view of this, in order to overcome the shortcomings of the prior art, the present invention provides a partition table data loading method based on the openGauss database. By re-centralizing the data scattered across the partitions and promptly closing partitions that have not received data for a long time, resource waste is avoided and the loading speed of the partition table is improved.
[0006] On one hand, this invention provides a method for loading partitioned table data based on the openGauss database, including:
[0007] Create a cache queue for each partition in the partition table;
[0008] The loading thread matches the data to the corresponding partition according to the rules and writes the data into the cache queue of the corresponding partition;
[0009] Set a threshold for the cache queue, check the cache queue in real time during the loading process, and control the writing of data in the cache queue to the partition according to the set threshold;
[0010] Set partition thresholds for partitions, and control the opening and closing of partitions based on these thresholds.
[0011] Furthermore, in the partition table data loading method based on the openGauss database of the present invention, the loading thread matches the partition to which the data belongs according to the rules, including: when the loading thread starts, obtaining the partition table rules from the system table, and matching the data to the corresponding partition according to the obtained partition table rules.
[0012] Furthermore, in the partition table data loading method based on the openGauss database of the present invention, setting thresholds for the cache queue includes: setting a quantity threshold and a time threshold for the cache queue.
[0013] Furthermore, in the partitioned table data loading method based on the openGauss database of the present invention, the writing of data from the cache queue to the partition is controlled according to a set threshold, including:
[0014] When the amount of data accumulated in the cache queue exceeds the threshold, the data in the cache queue will be written to the corresponding partition.
[0015] When the data accumulated in the cache queue exceeds the time threshold, the data in the cache queue is written to the corresponding partition.
[0016] Furthermore, in the partition table data loading method based on the openGauss database of the present invention, a partition threshold is set for the partition, and the opening and closing of the partition is controlled according to the partition threshold, including: setting a partition time threshold for the partition, and closing the partition when no data is written to the partition for a longer period of time than the partition time threshold.
[0017] Furthermore, in the partition table data loading method based on the openGauss database of the present invention, a partition threshold is set for the partition, and the opening and closing of the partition is controlled according to the partition threshold. This includes: setting a partition number threshold for the partition, and closing the excess partitions when the number of open partitions exceeds the partition number threshold, so as to control the number of partitions below the threshold.
[0018] Furthermore, in the partition table data loading method based on the openGauss database of the present invention, when the number of open partitions exceeds the partition number threshold, the excess partitions are closed to control the number of partitions below the threshold. This includes: using a doubly linked list to connect the opened partitions into an LRU ring; when the number of open partitions exceeds the partition number threshold, the partitions in the LRU ring are closed in descending order of unused time to control the number of partitions below the threshold.
[0019] On the other hand, the present invention provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed, it performs the steps of the above-described partition table data loading method based on the openGauss database.
[0020] Finally, the present invention also provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the above-described partition table data loading method based on the openGauss database.
[0021] The partition table data loading method based on the openGauss database of this invention has the following beneficial effects:
[0022] 1. By setting a cache queue and a threshold for the cache queue, the cache queue is checked in real time during the loading process. The writing of data in the cache queue to the partition is controlled according to the set threshold. This can save a lot of IO resources, flexibly configure multi-threaded data loading, and improve the loading speed of partition tables.
[0023] 2. By setting partition thresholds, the opening and closing of partitions can be controlled according to the partition thresholds, effectively reducing the number of partitions open at the same time and avoiding resource consumption caused by too many idle partitions.
[0024] 3. By leveraging the features of the openGauss database, flexible and efficient partitioned table data loading is achieved. Attached Figure Description
[0025] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0026] Figure 1 This is a schematic diagram of the structure of large tables and partitioned tables in the existing technology.
[0027] Figure 2 This is a schematic diagram illustrating the loading of data from a large table to a partitioned table in existing technologies.
[0028] Figure 3 This is a schematic flowchart illustrating a partition table data loading method based on the openGauss database, as exemplarily described in the first embodiment of the present invention.
[0029] Figure 4 This is a schematic flowchart illustrating a partition table data loading method based on the openGauss database, as exemplarily described in the second embodiment of the present invention.
[0030] Figure 5 This is a schematic flowchart illustrating a partition table data loading method based on the openGauss database according to an exemplary third embodiment of the present invention.
[0031] Figure 6 This is a schematic diagram illustrating the control of partitions based on a partition number threshold in a partition table data loading method based on the openGauss database, as exemplarily described in the third embodiment of the present invention. Detailed Implementation
[0032] The embodiments of the present invention will now be described in detail with reference to the accompanying drawings.
[0033] It should be noted that, in the absence of conflict, the following embodiments and features can be combined with each other; and, based on the embodiments of this disclosure, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this disclosure.
[0034] It should be noted that various aspects of embodiments within the scope of the appended claims are described below. It will be apparent that the aspects described herein can be embodied in a wide variety of forms, and any particular structure and / or function described herein is merely illustrative. Based on this disclosure, those skilled in the art will understand that one aspect described herein can be implemented independently of any other aspect, and two or more of these aspects can be combined in various ways. For example, any number of aspects set forth herein can be used to implement the device and / or practice the method. Additionally, this device and / or method can be implemented using structures and / or functionalities other than one or more of the aspects set forth herein.
[0035] Figure 3 This is a flowchart illustrating a partition table data loading method based on the openGauss database according to an exemplary first embodiment of the present invention, as shown below. Figure 3 As shown, the method in this embodiment includes:
[0036] Create a cache queue for each partition in the partition table;
[0037] The loading thread matches the data to the corresponding partition according to the rules and writes the data into the cache queue of the corresponding partition;
[0038] Set a threshold for the cache queue, check the cache queue in real time during the loading process, and control the writing of data in the cache queue to the partition according to the set threshold;
[0039] Set partition thresholds for partitions, and control the opening and closing of partitions based on these thresholds.
[0040] like Figure 3As shown, in this embodiment, the loading thread matches the partition to which the data belongs according to the rules, including: when the loading thread starts, it obtains the partition table rules from the system table, and matches the data to the corresponding partition according to the obtained partition table rules.
[0041] Figure 4 This is a flowchart illustrating a partition table data loading method based on the openGauss database according to an exemplary second embodiment of the present invention. Figure 3 A preferred embodiment of the method shown.
[0042] like Figure 4 As shown, in this embodiment, setting a threshold for the cache queue and controlling the writing of data from the cache queue to the partition based on the set threshold includes:
[0043] Set quantity and time thresholds for the cache queue. When the accumulated data in the cache queue exceeds the quantity threshold, write the data in the cache queue to the corresponding partition; when the accumulated data in the cache queue exceeds the time threshold, write the data in the cache queue to the corresponding partition.
[0044] In practical applications, the method of this embodiment sets quantity and time thresholds for the cache queue through a threshold setting module, performs queue checks on the cache queue according to the thresholds set by the threshold setting module, and controls the writing of data in the cache queue to the partition.
[0045] Figure 5 This is a flowchart illustrating a partition table data loading method based on the openGauss database according to an exemplary third embodiment of the present invention. Figure 3 A preferred embodiment of the method shown.
[0046] like Figure 5 As shown, in this embodiment, the method involves setting a partition threshold for a partition and controlling the opening and closing of the partition based on the partition threshold, including:
[0047] Set a partition time threshold for the partition. If no data is written to the partition for more than the partition time threshold, the partition is closed.
[0048] Set a threshold for the number of partitions. When the number of open partitions exceeds the threshold, close the excess partitions and keep the number of partitions below the threshold.
[0049] In practical applications, the method of this embodiment sets partition time thresholds and partition quantity thresholds for partitions through a threshold setting module, checks partitions according to the thresholds set by the threshold setting module, and controls the opening and closing of partitions based on the partition thresholds.
[0050] In practical applications, the method of this embodiment, such as Figure 6 As shown, when the number of open partitions exceeds the partition number threshold, the excess partitions are closed to keep the number of partitions below the threshold. This includes: using a doubly linked list to connect the open partitions into an LRU ring; when the number of open partitions exceeds the partition number threshold, the partitions in the LRU ring are closed in descending order of their unused time to keep the number of partitions below the threshold.
[0051] The LRU ring is used to track partition usage and can quickly determine which partitions should be closed based on a preset threshold. In this embodiment, the number of elements in the LRU ring is the partition count threshold, and all elements are sorted according to their last access time. The head element of the list is the most recently accessed partition, and the tail element is the earliest accessed partition. When a partition is already open, a new access moves it to the head of the list. When a new partition needs to be opened, the tail partition is closed, the new partition is opened, the tail element is added to the list, and it becomes the new head element; no elements need to be moved.
[0052] The present invention provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed, it performs the steps of the partition table data loading method based on the openGauss database of the present invention.
[0053] Finally, the present invention also provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of a partition table data loading method based on the openGauss database.
[0054] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.
Claims
1. A method for loading partitioned table data based on the openGauss database, characterized in that, The method includes: S1. Create a cache queue for each partition in the partition table; S2. The loading thread matches the partition to which the data belongs according to the rules, including: when the loading thread starts, it retrieves the partition table rules from the system table, matches the data to the corresponding partition according to the retrieved partition table rules, and writes the data into the cache queue of the corresponding partition; S3. Set thresholds for the cache queue, including: setting quantity and time thresholds for the cache queue; checking the cache queue in real time during loading, and controlling the writing of data in the cache queue to partitions according to the set thresholds, including: When the amount of data accumulated in the cache queue exceeds the threshold, the data in the cache queue will be written to the corresponding partition. When the data accumulated in the cache queue exceeds the time threshold, the data in the cache queue is written to the corresponding partition; S4. Set partition thresholds for partitions to control the opening and closing of partitions, including: Set a partition time threshold; if no data is written to a partition for a period of time exceeding the partition time threshold, the partition is closed. Set a threshold for the number of partitions. When the number of open partitions exceeds the threshold, close the excess partitions and keep the number of partitions below the threshold.
2. The method for loading partitioned table data based on the openGauss database according to claim 1, characterized in that, When the number of open partitions exceeds the partition number threshold, the excess partitions are closed to keep the number of partitions below the threshold. This includes using a doubly linked list to connect the open partitions into an LRU ring. When the number of open partitions exceeds the partition number threshold, the partitions in the LRU ring are closed in descending order of their unused time to keep the number of partitions below the threshold.
3. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program, which, when executed, performs the method as described in claim 1 or 2.
4. A computer device, characterized in that, The computer device includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the method of claim 1 or 2.