A method and device for dynamically adjusting the number of deployment shards

Abstract

A method and device for dynamically adjusting the number of deployed shards, relating to the field of shard deployment technology, the method comprising: obtaining the size of a hash cache in a cluster, and obtaining data information involved in calculation according to disk monitoring data; calculating and obtaining the optimal number of deployed shards according to the size of the hash cache and the data information; and dynamically adjusting the number of deployed shards according to the optimal number of deployed shards. The present invention can output the optimal number of deployed shards, avoid performance degradation caused by disk problems, and improve the performance of business execution.

Description

Technical Field

[0001] The present invention relates to the field of shard deployment technology, and in particular to a method and device for dynamically adjusting the number of deployed shards. Background Art

[0002] In an MPP distributed system, when DBAs deploy MPP clusters, they usually use a single-shard deployment method for ease of operation, that is, one computing node and one primary shard. When the amount of data is small or the server memory is large and the number of concurrent queries is small, this deployment method will not cause obvious performance abnormalities. However, when performing associated queries or aggregate queries on tables with large amounts of data, the MPP cluster will put all the data of the primary shard into the memory and split it according to the number of CPU cores or threads. When the amount of data on the primary shard is large, the splitting time will be very long. In addition, when the amount of data is too large to fit in the memory, the data will be written to the disk. Once disk reading and writing are involved, the gap between disk reading and writing performance and memory reading and writing performance is large, which will significantly affect query performance. This is also a disadvantage of single-shard deployment. Summary of the invention

[0003] The present invention aims to solve at least one of the technical problems existing in the related art. To this end, the present invention provides a method and device for dynamically adjusting the number of deployment shards.

[0004] The present invention provides a method for dynamically adjusting the number of deployment shards, comprising:

[0005] S1: Get the size of the hash cache in the cluster and obtain the data information involved in the calculation based on the disk monitoring data;

[0006] S2: Calculate the optimal number of deployment shards according to the hash cache size and the data information;

[0007] S3: Dynamically adjust the number of deployed shards according to the optimal number of deployed shards.

[0008] According to a method for dynamically adjusting the number of deployment shards provided by the present invention, step S1 specifically includes:

[0009] When there is no SQL history data in the disk monitoring data, collecting first data information;

[0010] When there is sql historical data in the disk monitoring data, the second data information is collected.

[0011] According to a method for dynamically adjusting the number of deployment shards provided by the present invention, the first data information includes:

[0012] The total number of data rows, maximum column width, and column base value of the table involved in the calculation;

[0013] The second data information includes:

[0014] The number of currently deployed shards and the size of data flushed to disk.

[0015] According to a method for dynamically adjusting the number of deployment shards provided by the present invention, step S2 specifically includes:

[0016] When the data information is the first data information, the optimal number of deployment shards is the first optimal number of deployment shards;

[0017] When the data information is the second data information, the optimal number of deployment shards is the second optimal number of deployment shards.

[0018] According to a method for dynamically adjusting the number of deployment shards provided by the present invention, the expression of the first optimal number of deployment shards is:

[0019]

[0020] in, is the first optimal number of shards to deploy, is the column base value involved in the calculation, is the total number of data rows in the table involved in the calculation, is the hash buffer size, The maximum column width of the data columns involved in the calculation;

[0021] The expression of the second optimal number of deployment shards is:

[0022]

[0023] in, is the second best number of shards to deploy, is the size of the data flushed to disk, The number of shards currently deployed.

[0024] According to a method for dynamically adjusting the number of deployment shards provided by the present invention, step S3 specifically includes:

[0025] When the number of deployment shards needs to be adjusted, the number of deployment shards is dynamically adjusted according to the optimal number of deployment shards;

[0026] When the number of deployed shards needs to be maintained, the data information is adjusted to maintain the number of deployed shards.

[0027] According to a method for dynamically adjusting the number of deployment shards provided by the present invention, the step of dynamically adjusting the number of deployment shards according to the optimal number of deployment shards specifically includes:

[0028] When any data information value in the data information changes, the optimal number of deployed shards after the change is calculated, and the optimal number of deployed shards after the change is used as the number of deployed shards after dynamic adjustment.

[0029] According to a method for dynamically adjusting the number of deployment shards provided by the present invention, the step of adjusting data information to maintain the number of deployment shards specifically includes:

[0030] When the total number of data rows increases, other data information remains unchanged, and the number of deployed shards needs to be maintained, the size of the hash cache area is increased;

[0031] When the maximum column width increases, other data information remains unchanged, and the number of deployed shards needs to be maintained, the size of the hash cache area is increased.

[0032] According to a method for dynamically adjusting the number of deployment shards provided by the present invention, when the size of the hash cache area cannot be increased, the number of deployment shards after dynamic adjustment is the optimal number of deployment shards.

[0033] The present invention also provides a device for dynamically adjusting the number of deployment shards, which is used to execute a method for dynamically adjusting the number of deployment shards as described in any one of the above items, comprising:

[0034] Collection module: used to obtain the size of the hash cache in the cluster and obtain the data information involved in the calculation based on the disk monitoring data;

[0035] Calculation module: used to calculate the optimal number of deployment shards according to the hash cache size and the data information collected by the collection module;

[0036] Adjustment module: used to dynamically adjust the number of deployment shards according to the optimal number of deployment shards calculated by the calculation module.

[0037] The present invention provides a method and device for dynamically adjusting the number of deployed shards, which enables data to be calculated in memory to the greatest extent, and calculates the optimal number of deployed shards in different ways according to whether SQL running data exists in disk data and the size of disk data. The optimal number of deployed shards obtained by calculation is subsequently adjusted according to whether the number of shards needs to be adjusted, and the number of deployed shards or the hash buffer is adjusted accordingly, which can minimize the impact of the disk on performance and solve the problem of performance degradation when the data volume is large, the memory is small, and the concurrency is high. Generally speaking, the present invention can automatically output the optimal number of deployed shards according to the characteristics of MPP, and minimize the impact of the disk on performance, so that user services can be completed more quickly, and performance degradation caused by disk problems can be avoided, thereby improving the performance of business execution.

[0038] Additional aspects and advantages of the present invention will be given in part in the following description and in part will be obvious from the following description, or will be learned through practice of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] In order to more clearly illustrate the technical solutions in the present invention or the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0040] Figure 1 It is a flowchart of a method for dynamically adjusting the number of deployment shards provided by an embodiment of the present invention.

[0041] Figure 2 It is a schematic diagram of the structure of a device for dynamically adjusting the number of deployment shards provided in an embodiment of the present invention.

[0042] Reference numerals:

[0043] 100, acquisition module; 200, calculation module; 300, adjustment module. DETAILED DESCRIPTION

[0044] In order to make the purpose, technical scheme and advantages of the present invention clearer, the technical scheme of the present invention will be clearly and completely described below in conjunction with the drawings in the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in the field without creative work are within the scope of protection of the present invention. The following embodiments are used to illustrate the present invention, but cannot be used to limit the scope of the present invention.

[0045] In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "example", "specific example", or "some examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the embodiment of the present invention. In this specification, the schematic representations of the above terms do not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner. In addition, those skilled in the art may combine and combine the different embodiments or examples described in this specification and the features of the different embodiments or examples, without contradiction.

[0046] Combine the following Figure 1 to Figure 2 Embodiments of the present invention are described.

[0047] like Figure 1 As shown, the present invention provides a method for dynamically adjusting the number of deployment shards, comprising:

[0048] S1: Get the size of the hash cache in the cluster and obtain the data information involved in the calculation based on the disk monitoring data;

[0049] Wherein, step S1 specifically includes:

[0050] When there is no SQL history data in the disk monitoring data, collecting first data information;

[0051] The first data information includes:

[0052] The total number of data rows, maximum column width, and column base value of the table involved in the calculation.

[0053] Furthermore, in this stage, if there is no disk monitoring data during the actual operation of SQL, that is, the first time SQL is run, the hash buffer size in the database is obtained, the total number of data rows in the table participating in the calculation in the database cluster is obtained, the maximum value of the memory space occupied by the data columns participating in the calculation (maximum column width), and the number of different values ​​of the columns participating in the calculation (column cardinality) are obtained.

[0054] When there is SQL history data in the disk monitoring data, collecting second data information;

[0055] The second data information includes:

[0056] The number of currently deployed shards and the size of data flushed to disk.

[0057] Furthermore, if there is disk monitoring data during the actual operation of SQL, that is, SQL is not running for the first time, then the number of currently deployed shards, the size of data written to disk during the actual operation of SQL, and the size of the hash buffer are obtained. If the size of the data written to disk is 0, it means that the current sharding settings are normal. Otherwise, if the size of the data written to disk is not 0, it means that additional shards need to be added.

[0058] S2: Calculate the optimal number of deployment shards according to the hash cache size and the data information;

[0059] Wherein, step S2 specifically includes:

[0060] When the data information is the first data information, the optimal number of deployment shards is the first optimal number of deployment shards;

[0061] When the actual operation monitoring data of SQL does not appear in the disk monitoring data, we use the optimal deployment shard number calculation method 1 to calculate the number of shards, that is, the optimal deployment shard number = min (column cardinality, total number of table data rows / (hash buffer size / maximum column width)). It should be noted that [total number of table data rows / (hash buffer size / maximum column width)] needs to be rounded up. The specific expression is as follows.

[0062] The expression of the first optimal number of deployment shards is:

[0063]

[0064] in, is the first optimal number of shards to deploy, is the column base value involved in the calculation, is the total number of data rows in the table involved in the calculation, is the hash buffer size, The maximum column width of the data columns involved in the calculation.

[0065] For query statements, the premise for obtaining the best performance is that all the data involved in the calculation is in memory, reducing the number of disk reads and writes to 0. That is, by setting the number of shards, the data is divided according to the hash value and placed in the corresponding shards. When the data of a shard can just fit into the hash buffer, the best performance can be obtained. The number of rows that can be accommodated in the hash buffer = the size of the hash buffer / the maximum column width. Therefore, the optimal number of deployed shards is greater than or equal to the total number of data rows / (hash buffer size / maximum column width).

[0066] At the same time, considering the impact of computing time, one shard has one computing time, so the total computing time is proportional to the number of shards. The more data shards there are, the more total computing time there is. Therefore, considering the two dimensions of disk read and write cost and total computing time, the optimal number of deployed shards = total number of data rows / (hash buffer size / maximum column width), and the result is rounded up.

[0067] Because the data distribution on which shard is obtained by calculating the hash value of the corresponding column data, if the column cardinality is small, more shards cannot be applied to multiple shards. Therefore, from the three dimensions of disk read and write cost, total calculation time, and column cardinality, the above optimal deployment shard number calculation formula 1 is given, that is, the first optimal deployment shard number .

[0068] When the disk monitoring data contains the actual operation monitoring data of SQL, we use the second method to calculate the number of optimal deployment shards to calculate the number of shards, that is, the optimal number of deployment shards = min (column cardinality, (data size on disk / hash buffer size+1)*current number of deployment shards). It should be noted that [(data size on disk / hash buffer size+1)*current number of deployment shards] needs to be rounded up. The specific expression is as follows.

[0069] When the data information is the second data information, the optimal number of deployment shards is the second optimal number of deployment shards;

[0070] The expression of the second optimal number of deployment shards is:

[0071]

[0072] in, is the second best number of shards to deploy, is the size of the data flushed to disk, The number of shards currently deployed.

[0073] For the above formula, the size of the data flushed to disk is not 0, which means that the hash buffer is not large enough to hold all the data, so the number of shards needs to be increased. The optimal number of deployed shards is greater than or equal to (the size of the data flushed to disk / the size of the hash buffer+1)*the number of currently deployed shards.

[0074] At the same time, considering the impact of computing time, one shard has one computing time, so the total computing time is proportional to the number of shards. The more data shards there are, the more total computing time there is. Therefore, considering the two dimensions of disk read and write cost and total computing time, the optimal number of deployed shards = (data size on disk / hash buffer size + 1) * the current number of deployed shards, and the result is rounded up.

[0075] Because the data is distributed on which shard is obtained by calculating the hash value of the corresponding column data, if the column cardinality is small, it cannot be applied to multiple shards even if there are many shards. At the same time, considering the impact of computing time, one shard is one computing time, so the total computing time is proportional to the number of shards. The more data shards there are, the more total computing time there is. Therefore, considering the two dimensions of disk read and write cost and total computing time, the above-mentioned optimal deployment shard number calculation formula 2 is given, which is the second optimal deployment shard number .

[0076] S3: Dynamically adjust the number of deployed shards according to the optimal number of deployed shards.

[0077] Wherein, step S3 specifically includes:

[0078] When the number of deployment shards needs to be adjusted, the number of deployment shards is dynamically adjusted according to the optimal number of deployment shards;

[0079] The step of dynamically adjusting the number of deployment shards according to the optimal number of deployment shards specifically includes:

[0080] When any data information value in the data information changes, the optimal number of deployed shards after the change is calculated, and the optimal number of deployed shards after the change is used as the number of deployed shards after dynamic adjustment.

[0081] That is, according to the calculation method 1 or 2 for the optimal number of shards for deployment, when one or more of the column cardinality, the total number of data rows in the table, the hash buffer, the maximum column width, and the size of the data stored on the disk change, the optimal number of shards for deployment is automatically adjusted.

[0082] When the number of deployed shards needs to be maintained, adjust the data information to maintain the number of deployed shards;

[0083] The step of adjusting the data information to maintain the number of deployed shards specifically includes:

[0084] When the total number of data rows increases, other data information remains unchanged, and the number of deployed shards needs to be maintained, the size of the hash cache area is increased;

[0085] When the maximum column width increases, other data information remains unchanged, and the number of deployed shards needs to be maintained, the size of the hash cache area is increased.

[0086] Among them, when the size of the hash cache area cannot be increased, the dynamically adjusted number of deployment shards is the optimal number of deployment shards.

[0087] That is to say, if you want to maintain the optimal number of shards unchanged, when the total number of data rows increases, you can increase the hash buffer size; when the total number of data rows remains unchanged and the maximum column width increases, you can also increase the hash buffer size; when the hash buffer cannot be increased again, you need to calculate the optimal number of shards after the data increases according to the formula.

[0088] like Figure 2 As shown, the present invention also provides a device for dynamically adjusting the number of deployment shards, which is used to execute a method for dynamically adjusting the number of deployment shards as described in any one of the above items, including:

[0089] Collection module 100: used to obtain the size of the hash buffer in the cluster and obtain the data information involved in the calculation according to the disk monitoring data;

[0090] Calculation module 200: used to calculate the optimal number of deployment shards according to the hash cache size and the data information collected by the collection module 100;

[0091] The adjustment module 300 is used to dynamically adjust the number of deployment shards according to the optimal number of deployment shards calculated by the calculation module 200.

[0092] The following describes a method for dynamically adjusting the number of deployment shards provided by the present invention through a specific embodiment.

[0093] The optimal number of deployment shards is calculated by formula 1, which is the first optimal number of deployment shards. The specific examples and corresponding implementation steps are as follows:

[0094] (1) Obtain the hash buffer setting size by querying the database. Assume that the hash buffer is 1GB.

[0095] (2) Query the database to obtain the total number of data rows, maximum column width, and column cardinality of the table. Assume that the total number of data rows in the queried table is 10 million, the maximum column width is 1 KB, and the column cardinality is 20;

[0096] (3) Obtain the optimal number of shards according to the calculation formula: optimal number of shards = min(total number of data rows / (hash buffer size / maximum column width)) = min(20,10 million / (1000*1000 / 1)) = 10, so the optimal number of shards for deployment = 10;

[0097] (4) When the column cardinality, data volume, hash buffer, or maximum column width changes, the calculation of the optimal number of deployment shards is automatically triggered. When the total number of data rows is 20 million, if the column cardinality, hash buffer, and maximum column width remain unchanged, the optimal number of deployment shards should be changed to 20. If you want to keep the optimal number of deployment shards unchanged at 10, you can increase the hash buffer and change the hash buffer to 2GB.

[0098] The second best deployment shard number is calculated by the formula 2, which is the second best deployment shard number. The specific examples and corresponding implementation steps are as follows:

[0099] (1) Obtain the hash buffer setting size by querying the database. Assume that the hash buffer is 1GB.

[0100] (2) Get the size of the data written to the disk during the actual SQL operation, assuming it is 3 GB, and the number of shards currently deployed, assuming it is 4, and the column cardinality is 20;

[0101] (3) The optimal number of shards to be deployed = min(column cardinality, (data size on disk / hash buffer size + 1) * current number of shards to be deployed)) = min(20, (3G / 1G+1) * 4) = 16, so the optimal number of shards to be deployed = 16;

[0102] (4) When the column cardinality and the size of the data stored on disk are not zero, the calculation of the optimal number of shards is automatically triggered.

[0103] The present invention provides a method and device for dynamically adjusting the number of deployed shards. First, the hash buffer size of the cluster is obtained, and then the number of data rows involved in the calculation and the maximum column width, column cardinality, number of shards and disk file size are obtained. Subsequently, the optimal number of deployed shards is output according to different calculation formulas based on the monitoring data of whether SQL is running. Finally, the optimal number of deployed shards is adjusted in real time according to demand, which can solve the problems of large data volume, small memory, high concurrency and performance degradation. The method can automatically output the optimal number of deployed shards according to the characteristics of MPP, minimize the impact of the disk on performance, enable user services to be completed more quickly, and improve user satisfaction.

[0104] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for dynamically adjusting the number of deployment shards, characterized in that: include: S1: Get the size of the hash cache in the cluster and obtain the data information involved in the calculation based on the disk monitoring data; When there is no SQL history data in the disk monitoring data, collecting first data information; The first data information includes: The total number of data rows, maximum column width, and column base value of the table involved in the calculation; When there is SQL history data in the disk monitoring data, collecting second data information; The second data information includes: The number of currently deployed shards and the size of data flushed to disk; S2: Calculate the optimal number of deployment shards according to the hash cache size and the data information; When the data information is the first data information, the optimal number of deployment shards is the first optimal number of deployment shards; The expression of the first optimal number of deployment shards is: in, is the first optimal number of shards to deploy, is the column base value involved in the calculation, is the total number of data rows in the table involved in the calculation, is the hash buffer size, The maximum column width of the data columns involved in the calculation; When the data information is the second data information, the optimal number of deployment shards is the second optimal number of deployment shards; The expression of the second optimal number of deployment shards is: in, is the second best number of shards to deploy, is the size of the data flushed to disk, The number of shards currently deployed; S3: Dynamically adjust the number of deployed shards according to the optimal number of deployed shards.

2. A method for dynamically adjusting the number of deployment shards according to claim 1, characterized in that: Step S3 specifically includes: When the number of deployment shards needs to be adjusted, the number of deployment shards is dynamically adjusted according to the optimal number of deployment shards; When the number of deployed shards needs to be maintained, the data information is adjusted to maintain the number of deployed shards.

3. A method for dynamically adjusting the number of deployment shards according to claim 2, characterized in that: The step of dynamically adjusting the number of deployment shards according to the optimal number of deployment shards specifically includes: When any data information value in the data information changes, the optimal number of deployed shards after the change is calculated, and the optimal number of deployed shards after the change is used as the number of deployed shards after dynamic adjustment.

4. A method for dynamically adjusting the number of deployment shards according to claim 2, characterized in that: The step of adjusting the data information to maintain the number of deployed shards specifically includes: When the total number of data rows increases, other data information remains unchanged, and the number of deployed shards needs to be maintained, the size of the hash cache area is increased; When the maximum column width increases, other data information remains unchanged, and the number of deployed shards needs to be maintained, the size of the hash cache area is increased.

5. A method for dynamically adjusting the number of deployment shards according to claim 3, characterized in that: When the size of the hash cache area cannot be increased, the dynamically adjusted number of deployment shards is the optimal number of deployment shards.

6. A device for dynamically adjusting the number of deployment shards, used to execute a method for dynamically adjusting the number of deployment shards as claimed in any one of claims 1 to 5, characterized in that: include: Collection module: used to obtain the size of the hash cache in the cluster and obtain the data information involved in the calculation based on the disk monitoring data; When there is no SQL history data in the disk monitoring data, collecting first data information; The first data information includes: The total number of data rows, maximum column width, and column base value of the table involved in the calculation; When there is SQL history data in the disk monitoring data, collecting second data information; The second data information includes: The number of currently deployed shards and the size of data flushed to disk; Calculation module: used to calculate the optimal number of deployment shards according to the hash cache size and the data information collected by the collection module; When the data information is the first data information, the optimal number of deployment shards is the first optimal number of deployment shards; The expression of the first optimal number of deployment shards is: in, is the first optimal number of shards to deploy, is the column base value involved in the calculation, is the total number of data rows in the table involved in the calculation, is the hash buffer size, The maximum column width of the data columns involved in the calculation; When the data information is the second data information, the optimal number of deployment shards is the second optimal number of deployment shards; The expression of the second optimal number of deployment shards is: in, is the second best number of shards to deploy, is the size of the data flushed to disk, The number of shards currently deployed; Adjustment module: used to dynamically adjust the number of deployment shards according to the optimal number of deployment shards calculated by the calculation module.

Images