Resource group management method and system based on query cost prediction

Abstract

The invention relates to a resource group management method and system based on query cost prediction. The method comprises the steps of data acquisition and preprocessing; query cost prediction: inputting an XGBoost-TF-IDF algorithm, training a query cost prediction model, then inputting the trained query cost prediction model, and predicting cluster resources needing to be consumed by a query task submitted by a user; cluster configuration: optimizing a distribution mode of the memory pool, then configuring cluster resource groups into an instant task resource group and a batch processing task resource group, and respectively optimizing resource configuration of the instant task resource group and the batch processing task resource group; and resource group classification optimization: according to the prediction cost of the query task, marking the query task as an instant task or a batch processing task, distributing the query task to a corresponding resource group, and carrying out dynamic resource expansion and limitation on the resource group by utilizing the prediction cost of the query task. According to the invention, the memory resources of the cluster are reasonably allocated, and the query efficiency and stability of the query cluster are improved.

Description

Technical Field

[0001] The present invention relates to the field of big data computing technology, and in particular to a resource group management method and system based on query cost prediction. Background Art

[0002] In today's growing multi-source heterogeneous and multi-modal big data environment, distributed query engines are becoming more important. Presto is a distributed SQL query engine with an MPP architecture that aims to provide a simple, scalable and high-performance way to query and analyze large-scale data sets. Since Presto is a query engine designed based on full-memory computing, the allocation of cluster memory resources will directly affect the operating efficiency of query tasks. Therefore, a resource group mechanism (Resource Groups) is provided in its core service to achieve efficient management and resource isolation of query tasks from the perspective of overall resource allocation. In addition, Presto retains various performance indicators of historical query tasks, which can help us analyze query performance bottlenecks and optimize cluster resource configuration, providing users with fast query services for massive multi-source heterogeneous data.

[0003] However, Presto's resource group mechanism still has many problems. From the perspective of resource isolation, in a shared Presto cluster, large query tasks will occupy more resources and are more likely to affect other tasks, resulting in resource contention and performance degradation. From the perspective of resource management, the resource group's demand for resources changes with the addition and completion of different query tasks. The Presto cluster cannot adjust the scale and resource allocation of the resource group according to the actual operation situation, making it difficult to maximize resource utilization.

[0004] Therefore, in response to the problems of inflexible resource allocation and poor isolation effect faced by Presto, how to dynamically optimize the resource management and resource isolation of Presto resource groups according to the cluster runtime situation in a multi-tenant environment to improve the utilization of cluster resources and query task execution efficiency and achieve cluster load balancing is an important issue faced in big data scenarios. Summary of the invention

[0005] In view of the above problems, the present invention proposes a resource group management method based on query cost prediction.

[0006] The present invention aims to obtain the relevant performance indicators of the historical query tasks of the Presto engine and save them as a training data set; then clean the data set and input it into the algorithm for training to predict the cost of the query tasks submitted by the user; according to the amount of resources consumed by the query tasks, the query tasks are distributed to different resource groups and specific optimization is performed for the resource groups. Using the present invention, different types of query tasks can be optimized, resource management and resource isolation can be dynamically implemented, and the resource utilization of the cluster and the query efficiency of the tasks can be improved.

[0007] The present invention also proposes a resource group management system based on query cost prediction, which is used to implement the above-mentioned resource group management method based on query cost prediction.

[0008] Terminology explanation:

[0009] 1. Presto: A distributed SQL query engine for querying large-scale data. Its goal is to perform fast and interactive query analysis in large-scale data warehouses, supporting multiple data sources and data formats;

[0010] 2. XGBoost algorithm: XGBoost (eXtreme Gradient Boosting) is an ensemble learning algorithm based on gradient boosting decision trees. It has a wide range of applications and excellent performance in the field of machine learning. Its model specifically used to handle regression problems is called XGBRegressor;

[0011] 3. TF-IDF: TF-IDF (Term Frequency-Inverse Document Frequency) is used to evaluate the importance of a word to a document set or a document in a corpus. Its value increases in direct proportion to the number of times a word appears in a document, but decreases in inverse proportion to the frequency of its occurrence in the corpus.

[0012] 4. General Memory Pool: The General Memory Pool is used to allocate memory for each query task when it is running. This is the memory pool used by most queries when they are running, and is responsible for meeting the memory requirements during query execution;

[0013] 5. Reserved Memory Pool: The Reserved Memory Pool is used to reserve memory for large jobs that may be triggered suddenly. When the general memory pool is full but the reserved memory pool is empty, Presto will move the most memory-consuming queries from the general pool to the reserved pool for execution;

[0014] 6. Resource Group Mechanism: Resource Groups is an admission control and workload management mechanism. Presto controls the overall query load of the cluster from the perspective of resource allocation. The resource group mechanism ensures that groups that use more than their allocated quota are penalized, thereby preventing any group from exceeding its quota limit;

[0015] 7. Presto Web Service: The Presto service provides a Web UI interface for checking and monitoring the current running status of the Presto cluster and displaying a list of query tasks that have been run. Each query task contains all query performance indicators. You can obtain JSON data containing performance indicators by accessing the corresponding API.

[0016] The technical solution of the present invention is:

[0017] A resource group management method based on query cost prediction, comprising:

[0018] Data collection and preprocessing: Collect various performance indicators of historical query tasks, select some relevant performance indicators as the initial data set, and preprocess the data in the initial data set;

[0019] Query cost prediction: The preprocessed data set is input into the XGBoost-TF-IDF algorithm to train the query cost prediction model. The query tasks submitted by the user are then input into the trained query cost prediction model to predict the cluster resources required for the query tasks submitted by the user.

[0020] Cluster configuration: optimize the allocation method of the memory pool, configure the cluster resource group into an immediate task resource group and a batch task resource group, and optimize the resource configuration of the immediate task resource group and the batch task resource group respectively;

[0021] Resource group classification optimization: According to the predicted cost of query tasks, query tasks are marked as immediate tasks or batch tasks and distributed to the corresponding resource groups. The predicted cost of query tasks is used to dynamically expand and restrict the resource capacity of resource groups.

[0022] Preferably, according to the present invention, various performance indicators of historical query tasks are collected, some relevant performance indicators are selected as initial data sets, and preprocessing operations are performed on the data in the initial data sets; including:

[0023] Obtain the JSON data of each query task through the Presto Web service, parse the JSON data to obtain the performance indicators of each task, and store them persistently;

[0024] Filter some relevant performance indicators, including: SQL query task user_sql, actual execution time elapsed_time, process processor time cpu_time, process output data volume output_data, and maximum memory usage peak_memory;

[0025] Data cleaning includes: processing missing values, duplicate values, and outliers on the filtered initial data set, deleting records with missing data, complete duplicates, or outliers; and standardizing the storage units and time units of the data set.

[0026] Preferably, according to the present invention, the data set after the preprocessing operation is input into the XGBoost-TF-IDF algorithm, the query cost prediction model is trained, and then the query task submitted by the user is input into the trained query cost prediction model; including:

[0027] Use TF-IDF to extract text features and convert text data into numerical features; including:

[0028] Calculate the word frequency TF of each word in a single document; calculate the inverse document frequency IDF of each word in the entire document collection; multiply TF and IDF to get the TF-IDF value as the weight of each word; construct a feature vector, that is, a numerical feature, and use the TF-IDF value of each word as a dimension of the feature vector;

[0029] Input the numerical features into the XGBRegressor algorithm, use the gradient descent method to optimize the loss function, and continuously add new regression trees;

[0030] The preprocessed data set is input into the XGBoost-TF-IDF algorithm, and the query cost prediction model is trained and saved. The query tasks submitted by the user are matched from recent to far in time to see if there are the same historical query tasks. If so, the cost information of the most recent query task is output. If not, it is input into the saved query cost prediction model to predict the cost information consumed by the query task.

[0031] Preferably, according to the present invention, the allocation mode of the memory pool is optimized, and the cluster resource group is configured as an immediate task resource group and a batch task resource group, and the resource configuration of the immediate task resource group and the batch task resource group is optimized respectively; including:

[0032] Disable the reserved memory pool of each Worker node in the Presto cluster and allocate all memory to the general memory pool to improve the utilization of memory resources;

[0033] Configure the Presto cluster resource group as an immediate task resource group and a batch task resource group, and then optimize the resource configuration of the resource group according to the characteristics of different query tasks, including:

[0034] For the immediate task resource group with fast task execution speed and low resource consumption, the maximum resource limit that each task can consume before being terminated is reduced;

[0035] For batch processing task resource groups that have slow task execution speed and occupy a lot of resources, increase the maximum resource limit that each task can consume before being terminated.

[0036] Further preferably, the reserved memory pool of each Worker node of the Presto cluster is disabled, and all memory is allocated to the general memory pool to improve the utilization of memory resources; including:

[0037] Set the experimental.reserved-pool-enabled configuration parameter to false to disable the reserved memory pool;

[0038] Set the query.low-memory-killer.policy configuration parameter to total-reservation-on-blocked-nodes. When the general memory pool is full, the largest query task in the Blocked state is terminated.

[0039] Further preferably, for an immediate task resource group with fast task execution speed and small resource occupation, the maximum resource upper limit that can be consumed by each task in the group before being terminated is reduced; including:

[0040] Set the name configuration parameter to adhoc_task and the name of the resource group to the immediate task resource group;

[0041] Set the softMemoryLimit configuration parameter to 60%, which is the percentage of the maximum available memory of the resource group to the total memory;

[0042] Set the perQueryLimits.executionTimeLimit configuration parameter to 2h to specify the maximum execution time of the query task;

[0043] Set the perQueryLimits.totalMemoryLimit configuration parameter to 5G to specify the maximum memory that a query task can consume;

[0044] Set the perQueryLimits.cpuTimeLimit configuration parameter to 12h to specify the maximum CPU time that a query task can use.

[0045] Further preferably, for a batch processing task resource group with slow task execution speed and high resource occupation, the maximum resource upper limit that can be consumed by each task before being terminated is increased; including:

[0046] Set the name configuration parameter to batch_task, and the name of the resource group is the batch task resource group;

[0047] Set the softMemoryLimit configuration parameter to 40%, which is the percentage of the maximum available memory of the resource group to the total memory;

[0048] Set the perQueryLimits.executionTimeLimit configuration parameter to 24h to specify the maximum execution time of the query task;

[0049] Set the perQueryLimits.totalMemoryLimit configuration parameter to 50G to specify the maximum memory that a query task can consume;

[0050] Set the perQueryLimits.cpuTimeLimit configuration parameter to 120h to specify the maximum CPU time that a query task can use.

[0051] Preferably, according to the present invention, the query task is marked as an immediate task or a batch task and distributed to a corresponding resource group according to the predicted cost of the query task; including:

[0052] Arrange the predicted query costs in descending order, select the first few data and calculate the average values ​​respectively, including the average processor time occupied by the process cpu_time_avg, the average data volume output by the process output_data_avg, and the average maximum memory usage peak_memory_avg. Take the sum of the ratios of the predicted value to the average value, that is, cpu_time_pre / cpu_time_avg+peak_memory_pre / peak_memory_avg+output_data_pre / output_data_avg. If the sum of the ratios of the predicted value to the average value is greater than 1, mark the corresponding query task as a batch task by specifying the Client Tags property of Presto Cli. Otherwise, mark the task as an immediate task, and distribute different tasks to the corresponding resource group through the selector rule of the resource group.

[0053] Preferably, according to the present invention, the predicted cost of the query task is used to dynamically expand and limit the resource capacity of the resource group; including:

[0054] For the immediate task resource group, the upper limit of concurrent task execution is increased in the initial stage of the cluster. As the resource consumption of the resource group increases, the resource capacity of the immediate task resource group is dynamically expanded and restricted according to the predicted total resource consumption and actual total resource consumption of the resource group, including:

[0055] When query tasks of a subgroup of a resource group start to queue but the predicted total resource consumption preMemory does not reach the threshold softMemoryLimit, the concurrency of the resource group is increased; when the predicted total resource consumption preMemory of a subgroup exceeds the threshold softMemoryLimit, an attempt is made to apply for resource expansion. If there are idle resources, the weights of each subgroup are calculated and allocated; when the predicted total resource consumption of a subgroup does not exceed the threshold and there are no idle resources, the resource group that exceeds the initial threshold and occupies resources of other subgroups is restricted, and new queries are not allowed to be executed. Resources are released after the query is completed, and the upper limit of total resource occupancy is reduced to the initial threshold.

[0056] For batch task resource groups, in the initial stage of the cluster, only a single task is allowed to run in the resource group, and other tasks enter the queue. Tasks are selected from the queue for execution based on the predicted resource consumption of the task and the available resources of the resource group, and the upper limit of concurrent execution of tasks is gradually increased, including:

[0057] Before executing a new task, first determine whether the current resource group has idle resources. If so, traverse the queue in the order of time when it enters the queue and select tasks whose predicted resource consumption is less than the idle resource amount; if there are no idle resources or there are idle resources but not enough to execute any task in the queue, apply for resource expansion in the order of queue time; if resource expansion fails due to insufficient idle resources, limit the resource upper limit of all subgroups until the initial threshold is reached.

[0058] A computer device comprises a memory and a processor, wherein the memory stores a computer program and the processor executes the steps of the resource group management method based on query cost prediction.

[0059] A computer-readable storage medium stores a computer program, which, when executed by a processor, implements the steps of the resource group management method based on query cost prediction.

[0060] A resource group management system based on query cost prediction, comprising:

[0061] The data collection and preprocessing module is configured to: collect various performance indicators of historical query tasks, select some relevant performance indicators as initial data sets, and perform preprocessing operations on the data in the initial data sets;

[0062] The query cost prediction module is configured to: input the preprocessed data set into the XGBoost-TF-IDF algorithm to train the query cost prediction model, and then input the query task submitted by the user into the trained query cost prediction model to predict the cluster resources required for the query task submitted by the user;

[0063] The cluster configuration module is configured to: optimize the allocation method of the memory pool, configure the cluster resource group into an immediate task resource group and a batch task resource group, and optimize the resource configuration of the immediate task resource group and the batch task resource group respectively;

[0064] The resource group classification optimization module is configured to: mark the query tasks as immediate tasks or batch tasks and distribute them to the corresponding resource groups according to the predicted costs of the query tasks, and dynamically expand and restrict the resources of the resource groups using the predicted costs of the query tasks.

[0065] The beneficial effects of the present invention are:

[0066] The present invention proposes a method of using a cost prediction algorithm to analyze the resource consumption index of the historical query tasks provided by the Presto engine, predict the query cost of the tasks submitted by the user, divide the tasks into different types of tasks according to the query cost, and then divide the resource group of the Presto cluster into an immediate task resource group and a batch task resource group, and optimize the resource expansion and restriction respectively. Through this method, different query tasks can be distributed to the corresponding resource groups, resource expansion and restriction can be dynamically realized, the function of Presto cluster resource isolation can be enhanced, the memory resources of the cluster can be reasonably allocated, and the query efficiency and stability of the query cluster can be improved. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0068] Figure 1 It is a schematic diagram of a resource group management system based on query cost prediction of the present invention;

[0069] Figure 2 Configure a diagram for a resource group;

[0070] Figure 3 Optimize the flow diagram for the immediate resource group;

[0071] Figure 4 Optimize the flow diagram for batch resource groups. DETAILED DESCRIPTION

[0072] The technical solution of the present invention is further described in detail below in conjunction with the accompanying drawings.

[0073] Example 1

[0074] A resource group management method based on query cost prediction, comprising:

[0075] Data collection and preprocessing: Collect various performance indicators of historical query tasks, select some relevant performance indicators as the initial data set, and preprocess the data in the initial data set;

[0076] Query cost prediction: The preprocessed data set is input into the XGBoost-TF-IDF algorithm to train the query cost prediction model. The query tasks submitted by the user are then input into the trained query cost prediction model to predict the cluster resources required for the query tasks submitted by the user.

[0077] Cluster configuration: optimize the allocation method of the memory pool, configure the cluster resource group into an immediate task resource group and a batch task resource group, and optimize the resource configuration of the immediate task resource group and the batch task resource group respectively;

[0078] Resource group classification optimization: According to the predicted cost of query tasks, query tasks are marked as immediate tasks or batch tasks and distributed to the corresponding resource groups. The predicted cost of query tasks is used to dynamically expand and restrict the resource capacity of resource groups.

[0079] Example 2

[0080] The resource group management method based on query cost prediction according to Embodiment 1 is different in that:

[0081] Collect various performance indicators of historical query tasks, select some relevant performance indicators as the initial data set, and perform preprocessing operations on the data in the initial data set; including:

[0082] Obtain the JSON data of each query task through the Presto Web service, parse the JSON data to obtain the performance indicators of each task and store them persistently; obtain the ID set of historical query tasks through the Presto system table "system.runtime.queries", use the ID set to obtain the JSON data of each query task from the Presto Web service, parse them one by one to obtain all the performance indicators of each task and store them persistently in a CSV file;

[0083] Filter some relevant performance indicators, including: SQL query task user_sql, actual execution time elapsed_time, process processor time cpu_time, process output data volume output_data and maximum memory usage peak_memory. The filtered indicators can reflect the resource consumption of query tasks.

[0084] Data cleaning includes: processing missing values, duplicate values, and outliers on the filtered initial data set, deleting records with missing data, complete duplicates, or outliers; standardizing the storage units and time units of the data set, converting the units into bytes and milliseconds.

[0085] Input the preprocessed data set into the XGBoost-TF-IDF algorithm, train the query cost prediction model, and then input the query task submitted by the user into the trained query cost prediction model; including:

[0086] TF-IDF is used to extract text features and convert text data into numerical features. These features can reflect the importance and distribution of words in the text, including:

[0087] Calculate the word frequency TF of each word in a single document; calculate the inverse document frequency IDF of each word in the entire document collection; multiply TF and IDF to get the TF-IDF value as the weight of each word; construct a feature vector, that is, a numerical feature, and use the TF-IDF value of each word as a dimension of the feature vector;

[0088] The numerical features are input into the regression version of XGBoost, the XGBRegressor algorithm, and the loss function is optimized using the gradient descent method. New regression trees are continuously added to gradually improve the prediction ability of the XGBRegressor model, including:

[0089] (1) Initializing the model: Before starting the iteration, the model starts with a simple prediction (such as the mean) as the initial prediction value;

[0090] (2) Iteratively adding trees: In each iteration, XGBoost adds a new tree to try to correct the prediction error of the model in the previous iteration. The process includes calculating residuals, building trees, and updating the model:

[0091] Calculate the residual: For each sample, calculate the difference between the predicted value of the current model and the true value. This difference is called the residual;

[0092] Building a tree: Use the residual to build a new decision tree. XGBoost uses a greedy algorithm to select the best split point, that is, to select the split point that can minimize the objective function.

[0093] Update the model: add the newly constructed tree to the model and adjust the weight of the new tree according to the learning rate to avoid overfitting;

[0094] (3) Objective function and regularization: XGBoost's objective function includes not only the loss function but also the regularization term. The regularization term usually includes the tree complexity penalty term, such as the tree depth and the number of leaf nodes, which helps to control the complexity of the model and prevent overfitting.

[0095] (4) Stop condition: The iteration process stops when the preset number of iterations is reached or the performance of the model is no longer significantly improved.

[0096] In summary, the XGBoost-TF-IDF algorithm combines the advantages of TF-IDF in text feature extraction and the powerful performance of XGBoost in regression prediction, with higher speed, accuracy and interpretability.

[0097] The preprocessed data set is input into the XGBoost-TF-IDF algorithm, and the query cost prediction model is trained and saved. The query tasks submitted by the user are matched from recent to distant in time to see if there are the same historical query tasks. If so, the cost information of the most recent query task is output. If not, it is input into the saved query cost prediction model to predict the cost information consumed by the query task. Cost information, such as: estimated execution time elapsed_time_pre, process processor time cpu_time_pre, process output data volume output_data_pre, and maximum memory usage peak_memory_pre.

[0098] Optimize the allocation method of the memory pool, configure the cluster resource group into an immediate task resource group and a batch task resource group, and optimize the resource configuration of the immediate task resource group and the batch task resource group respectively; including:

[0099] Disable the reserved memory pool of each Worker node in the Presto cluster and allocate all memory to the general memory pool to improve the utilization of memory resources;

[0100] Set the experimental.reserved-pool-enabled configuration parameter to false to disable the reserved memory pool;

[0101] Set the query.low-memory-killer.policy configuration parameter to total-reservation-on-blocked-nodes. When the general memory pool is full, the largest query task in the Blocked state is terminated.

[0102] like Figure 2 As shown in the figure, configure the Presto cluster resource group as an immediate task resource group and a batch task resource group, and then optimize the resource configuration of the resource group according to the characteristics of different query tasks, including:

[0103] For the immediate task resource group with fast task execution speed and low resource consumption, the maximum resource limit that each task can consume before being terminated is reduced;

[0104] For batch task resource groups that have slow task execution speed and occupy a lot of resources, increase the maximum resource limit that each task can consume before being terminated.

[0105] Disable the reserved memory pool of each Worker node in the Presto cluster and allocate all memory to the general memory pool to improve the utilization of memory resources; including:

[0106] Set the experimental.reserved-pool-enabled configuration parameter to false to disable the reserved memory pool;

[0107] Set the query.low-memory-killer.policy configuration parameter to total-reservation-on-blocked-nodes. When the general memory pool is full, the largest query task in the Blocked state is terminated.

[0108] For the immediate task resource group with fast task execution speed and low resource consumption, reduce the maximum resource limit that each task in the group can consume before being terminated; including:

[0109] Set the name configuration parameter to adhoc_task and the name of the resource group to the immediate task resource group;

[0110] Set the softMemoryLimit configuration parameter to 60%, which is the percentage of the maximum available memory of the resource group to the total memory;

[0111] Set the perQueryLimits.executionTimeLimit configuration parameter to 2h to specify the maximum execution time of the query task;

[0112] Set the perQueryLimits.totalMemoryLimit configuration parameter to 5G to specify the maximum memory that a query task can consume;

[0113] Set the perQueryLimits.cpuTimeLimit configuration parameter to 12h to specify the maximum CPU time that a query task can use.

[0114] For batch processing task resource groups that have slow task execution speed and occupy a lot of resources, increase the maximum resource limit that each task can consume before being terminated; including:

[0115] Set the name configuration parameter to batch_task, and the name of the resource group is the batch task resource group;

[0116] Set the softMemoryLimit configuration parameter to 40%, which is the percentage of the maximum available memory of the resource group to the total memory;

[0117] Set the perQueryLimits.executionTimeLimit configuration parameter to 24h to specify the maximum execution time of the query task;

[0118] Set the perQueryLimits.totalMemoryLimit configuration parameter to 50G to specify the maximum memory that a query task can consume;

[0119] Set the perQueryLimits.cpuTimeLimit configuration parameter to 120h to specify the maximum CPU time that a query task can use.

[0120] Based on the predicted cost of the query task, the query task is marked as an immediate task or a batch task and distributed to the corresponding resource group; including:

[0121] Arrange the predicted query costs in descending order, select the first several data (the first 30% of the data) and calculate the average values ​​respectively, including the average processor time occupied by the process cpu_time_avg, the average data volume output by the process output_data_avg, and the average maximum memory usage peak_memory_avg. Take the sum of the ratios of the predicted value to the average value, that is, cpu_time_pre / cpu_time_avg+peak_memory_pre / peak_memory_avg+output_data_pre / output_data_avg. If the sum of the ratios of the predicted value to the average value is greater than 1, it means that the task consumes a certain resource or the overall resources more. Then, by specifying the Client Tags attribute of Presto Cli, mark the corresponding query task as a batch task. Otherwise, mark the task as an immediate task, for example, "Presto Cli--client-tags batch_task"; distribute different tasks to the corresponding resource groups through the selector rules of the resource group. For example ""selectors":[{"clientTags":["batch_task"],"group":"global.batch_task.*"}]".

[0122] Use the predicted cost of query tasks to dynamically expand and limit resource capacity of resource groups; including:

[0123] like Figure 3 As shown in the figure, for the instant task resource group, the group pursues more efficient use of memory resources in view of the characteristics of the instant query task, which has a short actual execution time, low resource usage, and a large number of tasks; in the initial stage of the cluster, the upper limit of concurrent task execution is increased. As the resource consumption of the resource group increases, the resource capacity of the instant task resource group is dynamically expanded and restricted according to the predicted total resource consumption and the actual total resource consumption of the resource group, including:

[0124] In the initial state, the subgroups in the immediate task resource group do not set the upper limit of the number of query tasks that can be run concurrently (hardConcurrencyLimit); according to the needs of different resource groups, the maximum amount of memory that can be used by the resource group (softMemoryLimit) and the soft limit of the number of concurrency (softConcurrencyLimit) are set. When the resources exceed the limit, the new query task will enter the queue state.

[0125] When the query tasks of the subgroups of the resource group start to queue but the predicted total resource consumption preMemory (the total predicted resource consumption of all tasks in the group) does not reach the threshold softMemoryLimit, the concurrency of the resource group is increased by 20%, that is, softConcurrencyLimit*1.2, to improve resource utilization; when the predicted total resource consumption preMemory of the subgroups of the resource group exceeds the upper limit of the threshold softMemoryLimit, try to expand the resource capacity. If there are idle resources, calculate the weight of each subgroup. Considering the priorities of different groups and preventing a certain group from being too many, the weight formula is "(preMemory-realMemory)*originalSML*{1-(currentSML-originalSML)}", where realMemory is the actual resource consumption of the current resource group, originalSML is the value of the initial softMemoryLimit of the cluster, currentSML is the value of the current softMemoryLimit of the cluster, and (currentSML-originalSML) is the current actual excess ratio; then the remaining available resources are allocated according to the weight of each subgroup;

[0126] If there are no idle resources, all subgroups that exceed the initial resource threshold originalSML are restricted, and softMemoryLimit is set to originalSML. New queries are not allowed to be executed until the resource consumption drops to the initial threshold originalSML or the actual consumption reaches the upper limit and the above resource expansion operation is attempted;

[0127] like Figure 4 As shown in the figure, for the batch task resource group, the batch query task has the characteristics of long actual execution time, high resource usage, and small number of tasks. This group pursues the stability of task execution. In the initial stage of the cluster, the resource group only allows a single task to run, and other tasks enter the queue. According to the predicted resource consumption of the task and the available resources of the resource group, tasks are selected from the queue for execution and the upper limit of concurrent task execution is gradually increased, including:

[0128] Before executing a new task, first determine whether there are idle resources in the current resource group (softMemoryLimit > currentSML). If there are, traverse the queued queue in the order of the time when the tasks entered the queued queue queuedQueries and select the tasks whose predicted resource consumption is less than the idle resource amount (preMemory < softMemoryLimit - currentSML). Then, set the priority of the first eligible query task to the highest through the session session properties, and modify the parallelism number to the current parallelism number plus one (softConcurrencyLimit + 1), and execute the task with the highest priority in the queued queue;

[0129] If there are no idle resources or there are idle resources but not enough to execute any task in the queue, apply for resource expansion in the order of the queue time; if the resource expansion fails due to insufficient idle resources, limit the resource upper limit of all subgroups until the initial threshold originalSML is reached;

[0130] When the predicted resource consumption of the resource group exceeds 90% of the total available resource softMemoryLimit, limit the resource group with the highest resource consumption in the subgroup from executing new tasks; when the actual resource consumption exceeds 90%, limit all subgroups from executing new tasks;

[0131] The restrictions on the resource group will only make the scheduler try to prevent the start of new queries, but will not force the stop of the running queries.

[0132] Embodiment 3

[0133] A computer device includes a memory and a processor. The memory stores a computer program, and the processor executes the steps of the resource group management method based on query cost prediction described in Embodiment 1 or 2.

[0134] Embodiment 4

[0135] A computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, it implements the steps of the resource group management method based on query cost prediction described in Embodiment 1 or 2.

[0136] Embodiment 5

[0137] A resource group management system based on query cost prediction, as Figure 1 shown, includes:

[0138] A data collection and preprocessing module, configured to: collect various performance indicators of historical query tasks, screen out some relevant performance indicators as the initial data set, and perform preprocessing operations on the data in the initial data set;

[0139] The query cost prediction module is configured to: input the preprocessed data set into the XGBoost-TF-IDF algorithm to train the query cost prediction model, and then input the query task submitted by the user into the trained query cost prediction model to predict the cluster resources required for the query task submitted by the user;

[0140] The cluster configuration module is configured to: optimize the allocation method of the memory pool, configure the cluster resource group into an immediate task resource group and a batch task resource group, and optimize the resource configuration of the immediate task resource group and the batch task resource group respectively;

[0141] The resource group classification optimization module is configured to: mark the query tasks as immediate tasks or batch tasks and distribute them to the corresponding resource groups according to the predicted costs of the query tasks, and dynamically expand and restrict the resources of the resource groups using the predicted costs of the query tasks.

[0142] Obviously, the examples listed in the specific implementation are only some examples of the present invention, not all examples. Based on the examples of the present invention, all other examples obtained by technicians without creative work should fall within the protection scope of the present invention.

Claims

1. A resource group management method based on query cost prediction, characterized in that: include: Data collection and preprocessing: Collect various performance indicators of historical query tasks, select some relevant performance indicators as the initial data set, and preprocess the data in the initial data set; Query cost prediction: The preprocessed data set is input into the XGBoost-TF-IDF algorithm to train the query cost prediction model. The query tasks submitted by the user are then input into the trained query cost prediction model to predict the cluster resources required for the query tasks submitted by the user. Cluster configuration: optimize the allocation method of the memory pool, configure the cluster resource group into an immediate task resource group and a batch task resource group, and optimize the resource configuration of the immediate task resource group and the batch task resource group respectively; Resource group classification optimization: According to the predicted cost of query tasks, query tasks are marked as immediate tasks or batch tasks and distributed to the corresponding resource groups. The predicted cost of query tasks is used to dynamically expand and restrict the resource capacity of resource groups.

2. A resource group management method based on query cost prediction according to claim 1, characterized in that: Collect various performance indicators of historical query tasks, select some relevant performance indicators as the initial data set, and perform preprocessing operations on the data in the initial data set; including: Obtain the JSON data of each query task through the Presto Web service, parse the JSON data to obtain the performance indicators of each task, and store them persistently; Filter some relevant performance indicators, including: SQL query task user_sql, actual execution time elapsed_time, process processor time cpu_time, process output data volume output_data, and maximum memory usage peak_memory; Data cleaning includes: processing missing values, duplicate values, and outliers on the filtered initial data set, deleting records with missing data, complete duplicates, or outliers; and standardizing the storage units and time units of the data set.

3. The resource group management method based on query cost prediction according to claim 1, characterized in that: Input the preprocessed data set into the XGBoost-TF-IDF algorithm, train the query cost prediction model, and then input the query task submitted by the user into the trained query cost prediction model; including: Use TF-IDF to extract text features and convert text data into numerical features; including: Calculate the word frequency TF of each word in a single document; calculate the inverse document frequency IDF of each word in the entire document collection; multiply TF and IDF to get the TF-IDF value as the weight of each word; construct a feature vector, that is, a numerical feature, and use the TF-IDF value of each word as a dimension of the feature vector; Input the numerical features into the XGBRegressor algorithm, use the gradient descent method to optimize the loss function, and continuously add new regression trees; The preprocessed data set is input into the XGBoost-TF-IDF algorithm, and the query cost prediction model is trained and saved. The query tasks submitted by the user are matched from recent to far in time to see if there are the same historical query tasks. If so, the cost information of the most recent query task is output. If not, it is input into the saved query cost prediction model to predict the cost information consumed by the query task.

4. The resource group management method based on query cost prediction according to claim 1, characterized in that: Optimize the allocation method of the memory pool, configure the cluster resource group into an immediate task resource group and a batch task resource group, and optimize the resource configuration of the immediate task resource group and the batch task resource group respectively; including: Disable the reserved memory pool of each Worker node in the Presto cluster and allocate all memory to the general memory pool to improve the utilization of memory resources; Configure the Presto cluster resource group as an immediate task resource group and a batch task resource group, and then optimize the resource configuration of the resource group according to the characteristics of different query tasks, including: For the immediate task resource group with fast task execution speed and low resource consumption, the maximum resource limit that each task can consume before being terminated is reduced; For batch task resource groups that have slow task execution speed and occupy a lot of resources, increase the maximum resource limit that each task can consume before being terminated.

5. A resource group management method based on query cost prediction according to claim 4, characterized in that: Disable the reserved memory pool of each Worker node in the Presto cluster and allocate all memory to the general memory pool to improve the utilization of memory resources; including: Set the experimental.reserved-pool-enabled configuration parameter to false to disable the reserved memory pool; Set the query.low-memory-killer.policy configuration parameter to total-reservation-on-blocked-nodes. When the general memory pool is full, the largest query task in the Blocked state is terminated.

6. The resource group management method based on query cost prediction according to claim 4, characterized in that: For the immediate task resource group with fast task execution speed and low resource consumption, reduce the maximum resource limit that each task in the group can consume before being terminated; including: Set the name configuration parameter to adhoc_task and the name of the resource group to the immediate task resource group; Set the softMemoryLimit configuration parameter to 60%, which is the percentage of the maximum available memory of the resource group to the total memory; Set the perQueryLimits.executionTimeLimit configuration parameter to 2h to specify the maximum execution time of the query task; Set the perQueryLimits.totalMemoryLimit configuration parameter to 5G to specify the maximum memory that a query task can consume; Set the perQueryLimits.cpuTimeLimit configuration parameter to 12h to specify the maximum CPU time that a query task can use.

7. A resource group management method based on query cost prediction according to claim 4, characterized in that: For batch processing task resource groups that have slow task execution speed and occupy a lot of resources, increase the maximum resource limit that each task can consume before being terminated; including: Set the name configuration parameter to batch_task, and the name of the resource group is the batch task resource group; Set the softMemoryLimit configuration parameter to 40%, which is the percentage of the maximum available memory of the resource group to the total memory; Set the perQueryLimits.executionTimeLimit configuration parameter to 24h to specify the maximum execution time of the query task; Set the perQueryLimits.totalMemoryLimit configuration parameter to 50G to specify the maximum memory that a query task can consume; Set the perQueryLimits.cpuTimeLimit configuration parameter to 120h to specify the maximum CPU time that a query task can use.

8. The resource group management method based on query cost prediction according to claim 1, characterized in that: Based on the predicted cost of the query task, the query task is marked as an immediate task or a batch task and distributed to the corresponding resource group; including: Arrange the predicted query costs in descending order, select the first few data and calculate the average values ​​respectively, including the average processor time occupied by the process cpu_time_avg, the average data volume output by the process output_data_avg, and the average maximum memory usage peak_memory_avg. Take the sum of the ratios of the predicted value to the average value, that is, cpu_time_pre / cpu_time_avg+peak_memory_pre / peak_memory_avg+output_data_pre / output_data_avg. If the sum of the ratios of the predicted value to the average value is greater than 1, mark the corresponding query task as a batch task by specifying the Client Tags property of Presto Cli. Otherwise, mark the task as an immediate task, and distribute different tasks to the corresponding resource group through the selector rule of the resource group.

9. The resource group management method based on query cost prediction according to claim 1, characterized in that: Use the predicted cost of query tasks to dynamically expand and limit resource capacity of resource groups; including: For the immediate task resource group, the upper limit of concurrent task execution is increased in the initial stage of the cluster. As the resource consumption of the resource group increases, the resource capacity of the immediate task resource group is dynamically expanded and restricted according to the predicted total resource consumption and actual total resource consumption of the resource group, including: When query tasks of a subgroup of a resource group start to queue but the predicted total resource consumption preMemory does not reach the threshold softMemoryLimit, the concurrency of the resource group is increased; when the predicted total resource consumption preMemory of a subgroup exceeds the threshold softMemoryLimit, an attempt is made to apply for resource expansion. If there are idle resources, the weights of each subgroup are calculated and allocated; when the predicted total resource consumption of a subgroup does not exceed the threshold and there are no idle resources, the resource group that exceeds the initial threshold and occupies resources of other subgroups is restricted, and new queries are not allowed to be executed. Resources are released after the query is completed, and the upper limit of total resource occupancy is reduced to the initial threshold. For batch task resource groups, in the initial stage of the cluster, only a single task is allowed to run in the resource group, and other tasks enter the queue. Tasks are selected from the queue for execution based on the predicted resource consumption of the task and the available resources of the resource group, and the upper limit of concurrent execution of tasks is gradually increased, including: Before executing a new task, first determine whether the current resource group has idle resources. If so, traverse the queue in the order of time when it enters the queue and select tasks whose predicted resource consumption is less than the idle resource amount; if there are no idle resources or there are idle resources but not enough to execute any task in the queue, apply for resource expansion in the order of queue time; if resource expansion fails due to insufficient idle resources, limit the resource upper limit of all subgroups until the initial threshold is reached.

10. A resource group management system based on query cost prediction, characterized in that: include: The data collection and preprocessing module is configured to: collect various performance indicators of historical query tasks, select some relevant performance indicators as initial data sets, and perform preprocessing operations on the data in the initial data sets; The query cost prediction module is configured to: input the preprocessed data set into the XGBoost-TF-IDF algorithm to train the query cost prediction model, and then input the query task submitted by the user into the trained query cost prediction model to predict the cluster resources required for the query task submitted by the user; The cluster configuration module is configured to: optimize the allocation method of the memory pool, configure the cluster resource group into an immediate task resource group and a batch task resource group, and optimize the resource configuration of the immediate task resource group and the batch task resource group respectively; The resource group classification optimization module is configured to: mark the query tasks as immediate tasks or batch tasks and distribute them to the corresponding resource groups according to the predicted costs of the query tasks, and dynamically expand and restrict the resources of the resource groups using the predicted costs of the query tasks.

Images