Background

SinceVersion 1.2.2

DISTRIBUTED BY … BUCKETS auto

Users often set inappropriate buckets, leading to various problems. For now, it only works for olap tables

Node: This feature will be disabled when synchronized by CCR. If this table is copied by CCR, that is, PROPERTIES contains is_being_synced = true, it will be displayed as enabled in show create table, but will not actually take effect. When is_being_synced is set to false, these features will resume working, but the is_being_synced property is for CCR peripheral modules only and should not be manually set during CCR synchronization.

Implementation

In the past, when creating buckets, you had to set the number of buckets manually, but the automatic bucket projection feature is a way for Apache Doris to dynamically project the number of buckets, so that the number of buckets always stays within a suitable range and users don’t have to worry about the minutiae of the number of buckets. First, for the sake of clarity, this section splits the bucket into two periods, the initial bucket and the subsequent bucket; the initial and subsequent are just terms used in this article to describe the feature clearly, there is no initial or subsequent Apache Doris bucket. As we know from the section above on creating buckets, BUCKET_DESC is very simple, but you need to specify the number of buckets; for the automatic bucket projection feature, the syntax of BUCKET_DESC directly changes the number of buckets to “Auto” and adds a new Properties configuration.

  1. -- old version of the creation syntax for specifying the number of buckets
  2. DISTRIBUTED BY HASH(site) BUCKETS 20
  4. -- Newer versions use the creation syntax for automatic bucket imputation
  5. DISTRIBUTED BY HASH(site) BUCKETS AUTO
  6. properties("estimate_partition_size" = "100G")

The new configuration parameter estimate_partition_size indicates the amount of data for a single partition. This parameter is optional and if not given, Doris will take the default value of estimate_partition_size to 10GB. As you know from the above, a partitioned bucket is a Tablet at the physical level, and for best performance, it is recommended that the Tablet size be in the range of 1GB - 10GB. So how does the automatic bucketing projection ensure that the Tablet size is within this range? To summarize, there are a few principles.

  • If the overall data volume is small, the number of buckets should not be set too high
  • If the overall data volume is large, the number of buckets should be related to the total number of disk blocks, so as to fully utilize the capacity of each BE machine and each disk Initial bucketing projection Starting from the principle, it becomes easy to understand the detailed logic of the automatic bucket imputation function. First look at the initial bucketing

First, use the value of estimate_partition_size divided by 5 (calculated as a 5-to-1 data compression ratio in text format into Doris) to obtain the following result.

  • (, 100MB), then take N=1
  • [100MB, 1GB), then take N=2
  • (1GB, ), then one bucket per GB
  1. calculate the number of buckets M based on the number of BE nodes and the disk capacity of each BE node, where each BE node counts as 1 and each 50G of disk capacity counts as 1. Then the rule for calculating M is M = number of BE nodes ( one disk block size / 50GB) number of disk blocks For example, if there are 3 BEs, each with 4 500GB disks, then M = 3 (500GB / 50GB) 4 = 120
  2. Calculation logic to get the final number of buckets. First calculate an intermediate value x = min(M, N, 128). If x < N and x < the number of BE nodes, the final bucket is y, the number of BE nodes; otherwise, the final bucket is x.
  3. x = max(x, autobucket_min_buckets), 这里autobucket_min_buckets是在Config中配置的，默认是1

The pseudo-code representation of the above process is as follows

  1. int N = Compute the N value;
  2. int M = compute M value;
  4. int y = number of BE nodes;
  5. int x = min(M, N, 128);
  7. if (x < N && x < y) {
  8.   return y;
  9. }
  10. return x;

With the above algorithm in mind, let’s introduce some examples to better understand this part of the logic.

  1. case1:
  2. Amount of data 100 MB, 10 BE machines, 2TB * 3 disks
  3. Amount of data N = 1
  4. BE disks M = 10* (2TB/50GB) * 3 = 1230
  5. x = min(M, N, 128) = 1
  6. Final: 1
  8. case2:
  9. Data volume 1GB, 3 BE machines, 500GB * 2 disks
  10. Amount of data N = 2
  11. BE disks M = 3* (500GB/50GB) * 2 = 60
  12. x = min(M, N, 128) = 2
  13. Final: 2
  15. case3:
  16. Data volume 100GB, 3 BE machines, 500GB * 2 disks
  17. Amount of data N = 20
  18. BE disks M = 3* (500GB/50GB) * 2 = 60
  19. x = min(M, N, 128) = 20
  20. Final: 20
  22. case4:
  23. Data volume 500GB, 3 BE machines, 1TB * 1 disk
  24. Data volume N = 100
  25. BE disks M = 3* (1TB /50GB) * 1 = 60
  26. x = min(M, N, 128) = 63
  27. Final: 63
  29. case5:
  30. Data volume 500GB, 10 BE machines, 2TB * 3 disks
  31. Amount of data N = 100
  32. BE disks M = 10* (2TB / 50GB) * 3 = 1230
  33. x = min(M, N, 128) = 100
  34. Final: 100
  36. case 6:
  37. Data volume 1TB, 10 BE machines, 2TB * 3 disks
  38. Amount of data N = 205
  39. BE disks M = 10* (2TB / 50GB) * 3 = 1230
  40. x = min(M, N, 128) = 128
  41. Final: 128
  43. case 7:
  44. Data volume 500GB, 1 BE machine, 100TB * 1 disk
  45. Amount of data N = 100
  46. BE disk M = 1* (100TB / 50GB) * 1 = 2048
  47. x = min(M, N, 128) = 100
  48. Final: 100
  50. case 8:
  51. Data volume 1TB, 200 BE machines, 4TB * 7 disks
  52. Amount of data N = 205
  53. BE disks M = 200* (4TB / 50GB) * 7 = 114800
  54. x = min(M, N, 128) = 128
  55. Final: 200

As you can see, the detailed logic matches the principle. Subsequent bucketing projection The above is the calculation logic for the initial bucketing. The subsequent bucketing can be evaluated based on the amount of partition data available since there is already a certain amount of partition data. The subsequent bucket size is evaluated based on the EMA[1] (short term exponential moving average) value of up to the first 7 partitions, which is used as the estimate_partition_size. At this point there are two ways to calculate the partition buckets, assuming partitioning by days, counting forward to the first day partition size of S7, counting forward to the second day partition size of S6, and so on to S1.

  1. if the partition data in 7 days is strictly increasing daily, then the trend value will be taken at this time

There are 6 delta values, which are

  1. S7 - S6 = delta1,
  2. S6 - S5 = delta2,
  3. ...
  4. S2 - S1 = delta6

This yields the ema(delta) value. Then, today’s estimate_partition_size = S7 + ema(delta)

  1. not the first case, this time directly take the average of the previous days EMA

today’s estimate_partition_size = EMA(S1, … , S7) , S7)

According to the above algorithm, the initial number of buckets and the number of subsequent buckets can be calculated. Unlike before when only a fixed number of buckets could be specified, due to changes in business data, it is possible that the number of buckets in the previous partition is different from the number of buckets in the next partition, which is transparent to the user, and the user does not need to care about the exact number of buckets in each partition, and this automatic extrapolation will make the number of buckets more reasonable.

Description

When autobucket is enabled, the schema you see in show create table is also BUCKETS AUTO. If you want to see the exact number of buckets, you can do so by show partitions from ${table};.