Optimization method, device, equipment and product for data table sharding

Abstract

The present invention discloses a method, device, equipment and product for optimizing data table sharding, including: when the operating load of any data node where a data table shard is located is greater than a first set threshold, the number of shards of the data table is redetermined according to the maximum data carrying capacity of the data table and the data node; when the product of the frequency and time of cross-shard JOIN operations in the data table reaches a second set threshold, the sharding key of the data table is redetermined; the method, device, equipment and product for optimizing data table sharding disclosed by the present invention can dynamically adjust the sharding of the data table at any time according to changes in the data volume and request mode, avoid data skew or excessive load on a data node, and improve the overall operating performance of the database.

Description

Technical Field

[0001] The present invention belongs to the field of databases, and in particular relates to a method, device, equipment and product for optimizing data table sharding. Background Art

[0002] With the rapid development of cloud computing and distributed technology, business development based on distributed databases is becoming more and more popular. Under existing technical conditions, the static data table sharding method used by traditional distributed databases may lead to performance bottlenecks and load imbalance, especially when the data volume and request mode change. At the same time, the traditional distributed database sharding key needs to be determined when the table is built, so it is difficult to meet the needs of different business system loads and dynamic changes in data volume, resulting in the failure to improve the performance of the database, and even performance degradation and resource waste. Summary of the invention

[0003] In view of this, the present invention aims to overcome the defects in the prior art and proposes an optimization method, device, equipment and product for data table sharding.

[0004] To achieve the above object, the technical solution of the present invention is achieved as follows:

[0005] In a first aspect, the present invention discloses a method for optimizing data table sharding, comprising:

[0006] When the running load of any data node where the data table shard is located is greater than the first set threshold, the number of shards of the data table is re-determined according to the maximum data carrying capacity of the data table and the data node;

[0007] When the product of the frequency and time of cross-shard JOIN operations in the data table reaches the second set threshold, the shard key of the data table is re-determined;

[0008] Re-optimize the sharding of the data table according to the re-determined number of shards of the data table and / or the re-determined sharding key of the data table.

[0009] In another embodiment of the present invention, when the operating load of any data node where the data table shard is located is greater than the first set threshold, it includes: any one of the disk IO utilization rate, CPU utilization rate and network bandwidth utilization rate of the data node is greater than 80%.

[0010] In another embodiment of the present invention, when the operating load of any data node where the data table shard is located is greater than a first set threshold, the number of shards of the data table is re-determined according to the maximum data carrying capacity of the data table and the data node, including:

[0011] Get the total number of rows in the data table and the amount of disk space occupied;

[0012] Obtaining the minimum value of the number of data rows stored when all data nodes reach the set resource usage rate as the first maximum data capacity;

[0013] Obtaining the minimum value of the rated storage capacity of all data nodes with a set ratio as the second maximum data capacity;

[0014] The ratio of the total number of rows to the first maximum data capacity is rounded off as the first shard number of the data table, and the ratio of the occupied disk space capacity to the second maximum data capacity is rounded off as the second shard number of the data table. The first shard number or the second shard number is used as the re-determined shard number of the data table.

[0015] In another embodiment of the present invention, the data node reaches a set resource usage rate, including: the data node reaches any one of a set disk IO usage rate, a set CPU usage rate, and a set memory usage rate.

[0016] In another embodiment of the present invention, when the product of the frequency and time of cross-shard JOIN operations in a data table reaches a second set threshold, the shard key of the data table is re-determined, including: obtaining all SQL statements within a time period that meet the set conditions as key SQL statements, the frequency and time of cross-shard JOIN operations are the frequency and time of cross-shard JOIN operations in the key SQL statements; the set conditions are used to determine that the data node is in a high-load operating state.

[0017] In another embodiment of the present invention, re-determining the shard key of the data table includes: using the primary key of the data table as the shard key.

[0018] In another embodiment of the present invention, re-sharding the data table according to the re-determined number of shards of the data table and / or the re-determined sharding key of the data table includes:

[0019] Generate a snapshot of the data table at the current time t, and synchronize it to the data table created with the re-determined number of shards of the data table and / or the re-determined shard key of the data table, create a write-ahead log starting at time t, and forward the write-ahead log to the new shard of the data table for replay.

[0020] In a second aspect, the present invention discloses a device for optimizing data table sharding, the device comprising:

[0021] A shard number determination module, used to re-determine the number of shards of the data table according to the maximum data carrying capacity of the data table and the data node when the operating load of any data node where the data table shard is located is greater than a first set threshold;

[0022] A shard key determination module, used to re-determine the shard key of the data table when the product of the frequency and time of cross-shard JOIN operations in the data table reaches a second set threshold;

[0023] The re-sharding module is used to re-optimize the sharding of the data table according to the re-determined number of shards of the data table and / or the re-determined sharding key of the data table.

[0024] In a third aspect, the present invention discloses an electronic device, comprising: one or more processors; a storage device for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors execute the above method.

[0025] In a fourth aspect, the present invention discloses a computer program product, including a computer program, which implements the above method when executed by a processor.

[0026] Compared with the prior art, the present invention has the following advantages:

[0027] The present invention discloses a method, device, equipment and product for optimizing data table sharding, including: when the operating load of any data node where a data table shard is located is greater than a first set threshold, the number of shards of the data table is redetermined according to the maximum data carrying capacity of the data table and the data node; when the product of the frequency and time of cross-shard JOIN operations in the data table reaches a second set threshold, the sharding key of the data table is redetermined; the method, device, equipment and product for optimizing data table sharding disclosed by the present invention can dynamically adjust the sharding of the data table at any time according to changes in the data volume and request mode, avoid data skew or excessive load on a data node, and improve the overall operating performance of the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] The accompanying drawings, which constitute a part of the present invention, are used to provide a further understanding of the present invention. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute improper limitations on the present invention.

[0029] In the attached picture:

[0030] Figure 1 A schematic diagram of an application scenario of a data table sharding optimization method according to an embodiment of the present invention;

[0031] Figure 2 A schematic diagram of a data table sharding optimization method according to an embodiment of the present invention;

[0032] Figure 3 A schematic diagram of data node load status of a method for optimizing data table sharding according to an embodiment of the present invention;

[0033] Figure 4A schematic diagram of a device for optimizing data table sharding according to an embodiment of the present invention;

[0034] Figure 5 A schematic diagram of an electronic device for optimizing data table sharding according to an embodiment of the present invention. DETAILED DESCRIPTION

[0035] It should be noted that, in the absence of conflict, the embodiments of the present invention and the features in the embodiments may be combined with each other.

[0036] In the description of the present invention, it should be further explained that the terms "first", "second", etc. are used for descriptive purposes only and should not be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Thus, a feature defined as "first", "second", etc. may explicitly or implicitly include one or more of the features. In the description of the present invention, unless otherwise specified, "plurality" means two or more.

[0037] Figure 1 The present invention discloses an application scenario diagram of a data table sharding optimization method, device, equipment and product, which is used to shard a data table and store it in different data nodes. Under the existing technical conditions, the static data table sharding method used by traditional distributed databases may lead to performance bottlenecks and load imbalance, especially when the data volume and request mode change. At the same time, the traditional distributed database sharding key needs to be determined when the table is built, so it is difficult to meet the needs of different business system loads and dynamic changes in data volume, resulting in the performance of the database not being improved, and even performance degradation and resource waste.

[0038] The present invention discloses a method, device, equipment and product for optimizing data table sharding, which can redetermine the number of shards of a data table according to the maximum data carrying capacity of the data table and the data node; when the product of the frequency and time of cross-shard JOIN operations in the data table reaches a second set threshold, redetermine the sharding key of the data table; and dynamically adjust the sharding of the data table at any time according to changes in the data volume and request mode, thereby avoiding data skew or excessive load on a data node and improving the overall operating performance of the database.

[0039] The present invention will be described in detail below with reference to the accompanying drawings and in conjunction with embodiments.

[0040] In one embodiment of the present invention, Figure 2 As shown, a method for optimizing data table sharding includes:

[0041] Step S201: when the operating load of any data node where the data table shard is located is greater than a first set threshold, the number of shards of the data table is re-determined according to the maximum data carrying capacity of the data table and the data node;

[0042] In this embodiment, when the operating load of any data node where the data table shard is located is greater than the first set threshold, it includes: any one of the disk IO utilization rate, CPU utilization rate and network bandwidth utilization rate of the data node is greater than 80%.

[0043] In this embodiment, if Figure 3 As shown in the figure, if any of the disk IO usage, CPU usage, and network bandwidth usage of the data node is greater than 80%, it means that the data table sharding needs to be adjusted.

[0044] In this embodiment, the maximum data carrying capacity of the data node is represented by obtaining the minimum value of the number of data rows stored when all data nodes reach the set resource utilization rate, or by obtaining the minimum value of the rated storage capacity of all data nodes at a set ratio;

[0045] The maximum data carrying capacity represents the maximum amount of data that can be stored in a data table shard in a data node.

[0046] In this embodiment, the data node reaches the set resource usage rate, including: the data node reaches any one of a set disk IO usage rate, a set CPU usage rate, and a set memory usage rate.

[0047] Step S202: when the product of the frequency and time of cross-shard JOIN operations in the data table reaches a second set threshold, the shard key of the data table is re-determined;

[0048] In this embodiment, all SQL statements within a time period that meet the set conditions are obtained as key SQL statements, and the frequency and time of cross-shard JOIN operations are the frequency and time of cross-shard JOIN operations in the key SQL statements; the set conditions are used to determine whether the data node is in a high-load operating state.

[0049] In this embodiment, the setting conditions are as follows:

[0050] Statistics are collected for a business cycle of all data nodes, for example, the CPU usage, memory usage, and disk IO usage within 24 hours. The minimum values ​​of the time periods when the CPU usage, memory usage, and disk IO usage are higher than 90% are taken, and recorded as threshold A, threshold B, and threshold C respectively. The SQL statements executed in the time period when each data node exceeds any of the thresholds A, B, and C are obtained. The obtained SQL statements are further sorted in descending order according to the total execution time and execution frequency, and the SQL statements with the top 5% of total execution time and execution frequency are obtained as key SQL statements.

[0051] In this embodiment, re-determining the shard key of the data table includes: using the primary key of the data table as the shard key.

[0052] In this embodiment, when the shard key is a composite key, you can also consider:

[0053] Choose a field that is relevant to the application query pattern as the shard key, such as a field that appears frequently in the WHERE or HAVING clause.

[0054] Select fields that are not updated or are updated very infrequently as shard keys;

[0055] Select a unique index as the sharding key. If there are multiple unique indexes, consider combining multiple unique indexes.

[0056] Select high-cardinality fields as shard keys to ensure that data is evenly distributed across shards, thereby improving query performance.

[0057] Step S203: re-optimize the sharding of the data table according to the re-determined number of shards of the data table and / or the re-determined sharding key of the data table.

[0058] Based on the previous embodiment, in another implementation of the present invention, when the operating load of any data node where the data table shard is located is greater than the first set threshold, the number of shards of the data table is re-determined according to the maximum data carrying capacity of the data table and the data node, including:

[0059] Get the total number of rows in the data table and the amount of disk space occupied;

[0060] Obtaining the minimum value of the number of data rows stored when all data nodes reach the set resource usage rate as the first maximum data capacity;

[0061] In this embodiment, the resource utilization rate is set to 5%, which means that the resource utilization rate of a shard of a single data table occupying a single data node is set to a maximum of 5%.

[0062] In this embodiment, the total number of rows is represented as TotalTableNumber, the occupied disk space capacity is represented as TotalTableSize, and the first maximum data capacity is represented as MaxNodeNumCapacity;

[0063] Obtaining the minimum value of the rated storage capacity of all data nodes with a set ratio as the second maximum data capacity;

[0064] In this embodiment, the ratio is set to 5% of the rated storage capacity, and the second maximum data capacity is expressed as MaxNodeSizeCapacity;

[0065] In this embodiment, the ratio is set to 5% of the rated storage capacity, which means that the shards of a single data table are set to occupy a maximum of 5% of the storage capacity of a single data node.

[0066] The ratio of the total number of rows to the first maximum data capacity is rounded off as the first shard number of the data table, and the ratio of the occupied disk space capacity to the second maximum data capacity is rounded off as the second shard number of the data table. The first shard number or the second shard number is used as the re-determined shard number of the data table.

[0067] The first shard number is represented as NumberOfShards1, and the calculation process is as follows:

[0068]

[0069] The second number of shards is represented by NumberOfShards2, and the calculation process is as follows:

[0070]

[0071] In this embodiment, NumberOfShards1 focuses on shard optimization from the perspective of load, and NumberOfShards2 focuses on shard optimization from the perspective of capacity.

[0072] In another embodiment of the present invention, re-sharding the data table according to the re-determined number of shards of the data table and / or the re-determined sharding key of the data table includes:

[0073] Generate a snapshot of the data table at the current time t, and synchronize it to the data table created with the re-determined number of shards of the data table and / or the re-determined shard key of the data table, create a write-ahead log starting at time t, and forward the write-ahead log to the new shard of the data table for replay.

[0074] In this embodiment, the re-sharding optimization of the data table is performed in a time period when there are fewer write operations to the data table.

[0075] In another embodiment of the present invention, the data table is re-sharded and optimized. The specific process is as follows:

[0076] Create a distributed logical replica table based on the re-determined number of shards and / or the re-determined shard key of the data table. Except for the table name and the field where the shard key is located, the other properties of the table are consistent with those of the data table to be optimized. The data table is logically marked as needing to be distributed to different shards.

[0077] Generate a snapshot of the data table at the current time t and synchronize it to the distributed logical replica table. During the synchronization, capture the write-ahead logs generated by all insert, update, and delete data, and forward them to each shard of the distributed logical replica table. When the snapshot synchronization of the data table is completed, the write-ahead logs are replayed on the new shards.

[0078] When the write-ahead log on each new shard is replayed to be close to the optimized data table, the database adds a write lock to the optimized data table to prevent data from being written. When the distributed logical replica table is completely consistent with the optimized data table, the names of the distributed logical replica table and the optimized data table are exchanged.

[0079] like Figure 4 As shown, the present invention also discloses a data table sharding optimization device, comprising:

[0080] The shard number determination module 301 is used to re-determine the number of shards of the data table according to the maximum data carrying capacity of the data table and the data node when the operating load of any data node where the data table shard is located is greater than a first set threshold;

[0081] A shard key determination module 302, configured to re-determine a shard key of a data table when the product of the frequency and time of cross-shard JOIN operations in the data table reaches a second set threshold;

[0082] The re-sharding module 303 is used to re-optimize the sharding of the data table according to the re-determined number of shards of the data table and / or the re-determined sharding key of the data table.

[0083] The present invention also discloses an electronic device, such as Figure 5 As shown, an embodiment is disclosed, which is a block diagram of an electronic device suitable for optimizing the above-mentioned data table sharding.

[0084] The electronic device 40 of this embodiment includes a processor 401, which can perform various appropriate actions and processes according to the program stored in the ROM 402 or the program loaded from the storage part 408 into the RAM 403. The processor 401 may include, for example, a general-purpose microprocessor, an instruction set processor and / or a related chipset and / or a dedicated microprocessor, etc. The processor 401 may also include an onboard memory for caching purposes. The processor 401 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of the present invention.

[0085] In RAM403, various programs and data required for the operation of electronic device 40 are stored. Processor 401, ROM402 and RAM403 are connected to each other through bus 404, and processor 401 performs various operations of the method flow according to the embodiment of the present invention by executing the program in ROM402 and / or RAM403. It should be noted that the program can also be stored in one or more memories other than ROM402 and RAM403, and processor 401 can also perform various operations of the method flow according to the embodiment of the present invention by executing the program stored in one or more memories.

[0086] According to an embodiment of the present invention, the electronic device 40 may further include an I / O interface 405, which is also connected to the bus 404. The electronic device 40 may further include one or more of the following components connected to the I / O interface 405: an input portion 406 including a keyboard, a mouse, etc.; an output portion 407 including a cathode ray tube, a liquid crystal display, and a speaker; a storage portion 408 including a hard disk, etc.; and a communication portion 409 including a network interface card such as a LAN card, a modem, etc. The communication portion 409 performs communication processing via a network such as the Internet. A drive 4010 is also connected to the I / O interface 405 as needed. A removable medium 4011, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 4010 as needed, so that a computer program read therefrom is installed into the storage portion 408 as needed.

[0087] Embodiments of the present invention also include a computer program product.

[0088] The computer program product includes a computer program, which contains program codes for executing the method provided by the embodiment of the present invention. When the computer program product runs on an electronic device, the program codes are used to enable the electronic device to implement the method provided by the embodiment of the present invention.

[0089] In one embodiment, the computer program may rely on a tangible storage medium such as an optical storage device or a magnetic storage device. In another embodiment, the computer program may also be transmitted and distributed in the form of a signal on a network medium. The program code included in the computer program may be transmitted using any appropriate network medium, including but not limited to: wireless, wired, etc., or any suitable combination of the above.

[0090] According to an embodiment of the present invention, the program code for executing the computer program provided by the embodiment of the present invention can be written by any combination of one or more programming languages, and specifically, these computing programs can be implemented using high-level process and / or object-oriented programming languages. Programming languages ​​include but are not limited to programming languages ​​such as Java, C++, python, C language or similar. The program code can be executed completely on the user computing device, partially on the user device, partially on the remote computing device, or completely on the remote computing device or server. In the case of a remote computing device, the remote computing device can be connected to the user computing device through any type of network, including a local area network or a wide area network, or can be connected to an external computing device.

[0091] The flowcharts and block diagrams in the accompanying drawings illustrate the possible architecture, functions and operations of the systems, methods and computer program products according to various embodiments of the present invention. In this regard, each box in the flowchart or block diagram may represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of the code contains one or more executable instructions for implementing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box may also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flowchart, and the combination of boxes in the block diagram or flowchart, can be implemented with a dedicated hardware-based system that performs the specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions. It can be understood by those skilled in the art that the features recorded in the various embodiments and / or claims of the present invention can be combined and / or combined in various ways, even if such a combination or combination is not explicitly recorded in the present invention. In particular, without departing from the spirit and teaching of the present invention, the features described in the various embodiments and / or claims of the present invention may be combined and / or combined in a variety of ways. All of these combinations and / or combinations fall within the scope of the present invention.

[0092] The embodiments of the present invention are described above. However, these embodiments are only for the purpose of illustration, and are not intended to limit the scope of the present invention. Although the embodiments are described above, this does not mean that the measures in the various embodiments cannot be used in combination. The scope of the present invention is limited by the attached claims and their equivalents. Without departing from the scope of the present invention, those skilled in the art may make various substitutions and modifications, which should all fall within the scope of the present invention.

Claims

1. A data table sharding optimization method, characterized in that: include: When the running load of any data node where the data table shard is located is greater than the first set threshold, the number of shards of the data table is re-determined according to the maximum data carrying capacity of the data table and the data node; When the product of the frequency and time of cross-shard JOIN operations in the data table reaches the second set threshold, the shard key of the data table is re-determined; Re-optimize the sharding of the data table according to the re-determined number of shards of the data table and / or the re-determined sharding key of the data table; When the operating load of any data node where the data table shard is located is greater than the first set threshold, the number of shards of the data table is re-determined according to the maximum data carrying capacity of the data table and the data node, including: Get the total number of rows in the data table and the amount of disk space occupied; Obtaining the minimum value of the number of data rows stored when all data nodes reach the set resource usage rate as the first maximum data capacity; Obtaining the minimum value of the rated storage capacity of all data nodes with a set ratio as the second maximum data capacity; The ratio of the total number of rows to the first maximum data capacity is rounded off as the first shard number of the data table, the ratio of the occupied disk space capacity to the second maximum data capacity is rounded off as the second shard number of the data table, and the first shard number or the second shard number is used as the re-determined shard number of the data table; The re-determining the shard key of the data table includes: using the primary key of the data table as the shard key.

2. The method for optimizing data table fragmentation according to claim 1, characterized in that: When the operating load of any data node where the data table shard is located is greater than the first set threshold, it includes: any one of the disk IO utilization rate, CPU utilization rate and network bandwidth utilization rate of the data node is greater than 80%.

3. The method for optimizing data table fragmentation according to claim 2, characterized in that: The data node reaches the set resource usage rate, including: the data node reaches any one of a set disk IO usage rate, a set CPU usage rate and a set memory usage rate.

4. The method for optimizing data table fragmentation according to claim 1, characterized in that: When the product of the frequency and time of the cross-shard JOIN operation in the data table reaches a second set threshold, the shard key of the data table is re-determined, including: obtaining all SQL statements within a time period that meets the set conditions as key SQL statements, the frequency and time of the cross-shard JOIN operation are the frequency and time of the cross-shard JOIN operation in the key SQL statements; the set conditions are used to determine that the data node is in a high-load operation state.

5. The method for optimizing data table fragmentation according to claim 1, characterized in that: The re-optimizing the sharding of the data table according to the re-determined number of shards of the data table and / or the re-determined sharding key of the data table includes: Generate a snapshot of the data table at the current time t, and synchronize it to the data table created with the re-determined number of shards of the data table and / or the re-determined shard key of the data table, create a write-ahead log starting at time t, and forward the write-ahead log to the new shard of the data table for replay.

6. A data table sharding optimization device, characterized in that: The device includes: A shard number determination module is used to re-determine the number of shards of the data table according to the maximum data carrying capacity of the data table and the data node when the operating load of any data node where the data table shard is located is greater than a first set threshold; obtain the total number of rows of the data table and the occupied disk space capacity; The minimum value of the number of data rows stored when all data nodes reach the set resource utilization rate is obtained as the first maximum data capacity; the minimum value of the rated storage capacity of all data nodes with a set ratio is obtained as the second maximum data capacity; The ratio of the total number of rows to the first maximum data capacity is rounded off as the first shard number of the data table, the ratio of the occupied disk space capacity to the second maximum data capacity is rounded off as the second shard number of the data table, and the first shard number or the second shard number is used as the re-determined shard number of the data table; A shard key determination module, used to re-determine the shard key of the data table when the product of the frequency and time of cross-shard JOIN operations in the data table reaches a second set threshold; Use the primary key of the data table as the shard key; The re-sharding module is used to re-optimize the sharding of the data table according to the re-determined number of shards of the data table and / or the re-determined sharding key of the data table.

7. An electronic device, characterized in that: include: one or more processors; A storage device for storing one or more programs, wherein when the one or more programs are executed by one or more processors, the one or more processors are caused to execute the method according to any one of claims 1 to 5.

8. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the method according to any one of claims 1 to 5 is implemented.

Images