CN-122029529-A - High performance resource and job scheduling

CN122029529ACN 122029529 ACN122029529 ACN 122029529ACN-122029529-A

Abstract

The computer-implemented method, system, and computer program product include program code executing on a processor that obtains a query from a source via a query interface. Program code analyzes the query to obtain attributes of the query. The program code applies a trained classifier to the attributes to classify the queries into limited categories based on predicted usage of a given resource type exceeding a predetermined threshold and an expected execution duration of each query. The program code determines which of the resources can effectively execute each of the one or more queries based on the limited categories of the one or more queries and the resource configuration including the database service. Program code schedules each of the one or more queries for execution by the determined resource.

Inventors

SUN SHENGYAN
ZHAO XUQIN
JIANG PENGHUI
XU JIAN
ZHANG XUELIAN
WANG TINGTING

Assignees

国际商业机器公司

Dates

Publication Date: 20260512
Application Date: 20240926
Priority Date: 20231027

Claims (20)

1. A computer-implemented method for scheduling query execution by a database service based on predicted workload characteristics of one or more queries, the method comprising: obtaining, by one or more processors, the one or more queries from one or more sources via a query interface; analyzing, by the one or more processors, the one or more queries to obtain attributes of the one or more queries; applying, by the one or more processors, a trained classifier to the attributes to classify the one or more queries into finite categories based on predicted usage of a given resource type of a plurality of resource types exceeding a predetermined threshold and an expected execution duration of each of the one or more queries; Determining, by the one or more processors, which of the resources is capable of efficiently executing each of the one or more queries based on the limited category of the one or more queries and a resource configuration of the resources including the database service, and Scheduling, by the one or more processors, each of the one or more queries for execution by the determined resource.
2. The computer-implemented method of claim 1, wherein the determining comprises matching a portion of the one or more queries having the predicted usage of the given resource type that exceeds the predetermined threshold to a given resource comprising the given resource type, wherein the given resource comprises a capacity of a query that executes the expected execution duration of the portion of queries.
3. The computer-implemented method of claim 1, wherein the attributes of the one or more queries are selected from the group consisting of statistics from a catalog, interpretation information, a parser tree, tokenized SQL text data, and a source selected from the one or more sources.
4. The computer-implemented method of claim 1, further comprising: Training, by the one or more processors, a classifier to generate the trained classifier, the training comprising: Obtaining, by the one or more processors, query attributes of a dataset comprising training data queries and corresponding tags, wherein the corresponding tags indicate an initial classification of each training data query or are based on preprocessing the training data queries of the training data queries; Identifying, by the one or more processors, relevant features in the query attributes of the training data queries, the relevant features indicating use of the given resource type exceeding the predetermined threshold or execution duration of each of the training data queries, and The classifier is trained by the one or more processors to identify the relevant features in an incoming query, wherein the one or more queries include the incoming query.
5. The computer-implemented method of claim 4, wherein the query attributes of the training data query are selected from the group consisting of table statistics, access paths, SQL text, and query sources from the one or more sources.
6. The computer-implemented method of claim 5, wherein the query attributes comprise statistics and the statistics are selected from the group consisting of cardinality, number of pages, active blocks, and overflow numbers.
7. The computer-implemented method of claim 5, wherein the query attributes comprise access paths and the access paths are selected from the group consisting of parallelism, ordering, tablespace scanning, join, and aggregation.
8. The computer-implemented method of claim 5, wherein the query attributes comprise SQL text and the attributes are selected from the group consisting of participation of a function and whether SQL is read-only.
9. The computer-implemented method of claim 5, wherein the query attributes comprise a query source and the query source is selected from the group consisting of business intelligence, online transaction processing, machine learning, flow analysis, data science, and data application.
10. The computer-implemented method of claim 1, wherein applying the trained classifier comprises outputting a query tag and a priority for each of the one or more queries.
11. The computer-implemented method of claim 10, wherein the query tag indicates the predicted use of the given resource type that exceeds the predetermined threshold and the expected execution duration of each query.
12. The computer-implemented method of claim 11, wherein the given resource type is selected from the group consisting of a central processing unit, memory, and input/output.
13. The computer-implemented method of claim 11, wherein the scheduling further comprises: Generating, by the one or more processors, the database service as an asymmetric database service, wherein the resources comprising the database service comprise a configuration, and The resource is marked by the one or more processors based on the configuration, wherein the marking indicates compatibility with query execution requirements indicated by the query marking of the one or more queries.
14. The computer-implemented method of claim 13, wherein the resources are distributed across at least two nodes.
15. The computer-implemented method of claim 13, wherein a portion of the resources comprise micro-services.
16. The computer-implemented method of claim 1, wherein the scheduling comprises: Determining, by the one or more processors, whether the resource of the database service has capacity to execute the one or more queries; Queuing each of the one or more queries for execution by the determined resource based on determining that the resource of the database service has the capacity, and Based on determining that the resource of the database service does not have the capacity, increasing a capacity of the determined resource of the database service and queuing each of the one or more queries for execution by the determined resource.
17. The computer-implemented method of claim 1, further comprising: Each of the one or more queries is executed by the one or more processors based on the schedule.
18. A computer system for scheduling query execution by a database service based on predicted workload characteristics of one or more queries, the computer system comprising: Memory, and One or more processors in communication with the memory, wherein the computer system is configured to perform a method comprising: obtaining, by the one or more processors, the one or more queries from one or more sources via a query interface; analyzing, by the one or more processors, the one or more queries to obtain attributes of the one or more queries; applying, by the one or more processors, a trained classifier to the attributes to classify the one or more queries into finite categories based on predicted usage of a given resource type of a plurality of resource types exceeding a predetermined threshold and an expected execution duration of each of the one or more queries; Determining, by the one or more processors, which of the resources is capable of effectively executing each of the one or more queries based on the limited category of the one or more queries and a resource configuration of the resources including the database service, and Scheduling, by the one or more processors, each of the one or more queries for execution by the determined resource.
19. The computer system of claim 18, wherein the determining comprises matching a portion of the one or more queries having the predicted use of the given resource type that exceeds the predetermined threshold to a given resource comprising the given resource type, wherein the given resource comprises a capacity of a query that executes the expected execution duration of the portion of queries.
20. A computer program product for scheduling query execution by a database service based on predicted workload characteristics of one or more queries, the computer program product comprising: one or more computer-readable storage media and program instructions collectively stored on the one or more computer-readable storage media, the program instructions being readable by at least one processing circuit to: obtaining, by one or more processors, the one or more queries from one or more sources via a query interface; analyzing, by the one or more processors, the one or more queries to obtain attributes of the one or more queries; applying, by the one or more processors, a trained classifier to the attributes to classify the one or more queries into finite categories based on predicted usage of a given resource type of a plurality of resource types exceeding a predetermined threshold and an expected execution duration of each of the one or more queries; Determining, by the one or more processors, which of the resources is capable of effectively executing each of the one or more queries based on the limited category of the one or more queries and a resource configuration of the resources including the database service, and Scheduling, by the one or more processors, each of the one or more queries for execution by the determined resource.

Description

High performance resource and job scheduling Background The present invention relates generally to the field of workload scheduling in database systems, and more particularly to optimizing system performance based on improved workload balancing techniques. A database management system (DBMS) handles various query types, each having its own characteristics that affect the performance of the system. Resource allocation, such as Central Processing Units (CPUs), memory, and disks, varies based on whether the query is CPU-intensive, memory-intensive, or input/output (I/O) intensive. Traditionally, load balancing of database servers relies on basic metrics like server idleness to determine where to direct queries or transactions. This approach may lead to resource congestion, especially when multiple I/O-intensive queries are sent to the same server in a short time frame, resulting in a slow response. In addition, when the server is occupied with I/O operations, server resources may be wasted, causing the processor to remain idle while waiting for data processing. The challenges of load balancing in database systems are exacerbated in more complex computing environments. These challenges arise in cloud environments or large-scale distributed computing clusters. Two current approaches to address workload balancing in database systems are known as scaling up (vertical scaling) and lateral scaling (horizontal scaling). Scaling up involves adding resources such as CPUs, memory and storage devices to a single node, extending its capacity vertically to enhance computing or storage capacity. Scaling up can be done quickly with relatively simple operations, but as computing or storage requirements increase, hardware resources increase to a limit. For example, a single node becomes expensive, costly to maintain, and creates a potential single point of failure. Meanwhile, by adding node resources, the computing and storage requirements are transversely met, and the address distributed environment is transversely expanded. The reduction in cost and increase in efficiency of computing and storage units enables the creation of so-called supercomputing centers (e.g., private and public cloud platform solutions) using low-end business systems and distributed technologies. However, the lateral expansion is only a scaled-up version, but on a larger scale. Thus, lateral expansion introduces challenges such as huge input, high energy consumption, and increased cost of use. Furthermore, there are strong computing, storage and bandwidth capabilities at the edge nodes of everyday life that highlight the problem of free or uneven resource utilization. Artificial Intelligence (AI) refers to the intelligence exhibited by a machine. Artificial Intelligence (AI) research includes search and mathematical optimization, neural networks, and probabilities. Artificial Intelligence (AI) solutions involve features derived from research in a variety of different scientific and technological disciplines of computer science, mathematics, psychology, linguistics, statistics and neuroscience. Machine learning has been described as a learning field that gives computers the ability to learn without explicit programming. Disclosure of Invention The shortcomings of the prior art are overcome and additional advantages are provided through the provision of a computer-implemented method for scheduling query execution by a database service based on predicted workload characteristics of one or more queries. The method may include obtaining, by one or more processors, the one or more queries from one or more sources via a query interface, analyzing, by the one or more processors, the one or more queries to obtain attributes of the one or more queries, applying, by the one or more processors, a trained classifier to the attributes to classify, by the one or more processors, the one or more queries into limited categories based on predicted usage of a given resource type of a plurality of resource types exceeding a predetermined threshold and an expected execution duration of each of the one or more queries, determining, by the one or more processors, which of the resources can efficiently execute each of the one or more queries based on the limited categories of the one or more queries and a resource configuration of resources including the database service, and scheduling, by the one or more processors, the each of the one or more queries for execution. The shortcomings of the prior art are overcome and additional advantages are provided through the provision of a computer program product for scheduling query execution by a database service based on predicted workload characteristics of one or more queries. The computer program product includes a storage medium readable by one or more processors and instructions for execution by the one or more processors to perform the method. The method includes, for example, obtaining, by one or more processors, the one or m