Method and system for generating distributed execution plan based on q-learning

Abstract

The application discloses a method and system for generating a distributed execution plan based on Q-learning and belongs to the technical field of distributed databases; the technical problem to be solved by the application is how to apply reinforcement learning to a database for generating an optimal distributed physical execution plan so as to improve query performance and response speed; and the technical scheme adopted is as follows: the method is as follows: generating a logical plan: generating a planNode logical plan node, and each SQL has a corresponding planNode; generating a distributed physical plan: generating a distributed physical plan according to the logical plan; specifically as follows: a query optimizer receives a SQL query, and a logical query plan is constructed according to the query statement; the logical query plan is converted into a distributed physical query plan; and a Q-learning algorithm is adopted for each candidate physical execution plan to generate an optimal agent selection and task allocation scheme.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of distributed database, in particular to a method and system for generating distributed execution plan based on Q-learning. BACKGROUND

[0002] In a database system, when a query request is received, the executor is responsible for parsing the SQL statement, generating an execution plan, and then implementing the query request step by step. Distributed database has distributed execution plan, and has higher scalability compared with traditional single-machine database. The SQL execution process is as follows:

[0003] The client connects the database through the network and sends the SQL statement. After receiving the query request, the database needs to parse the SQL statement, parse the text into structured data for program processing, generate a logical execution plan, and then convert it into a physical execution plan related to the physical storage structure of the data. The required data is retrieved from the target node, and the entire data query process is completed.

[0004] Reinforcement learning (RL) discusses how an agent can maximize the rewards it can obtain in a complex and uncertain environment. By perceiving the state of the environment in response to the action, better actions are guided to obtain the maximum return, which is called learning in interaction. Such learning method is called reinforcement learning.

[0005] Therefore, how to apply reinforcement learning to the database to generate the optimal distributed physical execution plan and improve the query performance and response speed is a technical problem to be solved. SUMMARY

[0006] The technical task of the present application is to provide a method and system for generating distributed execution plan based on Q-learning, to solve the problem of how to apply reinforcement learning to the database to generate the optimal distributed physical execution plan and improve the query performance and response speed.

[0007] The technical task of the present application is achieved in the following way, a method for generating distributed execution plan based on Q-learning, which is as follows:

[0008] Generating a logical plan: generating a planNode logical plan node, each SQL has a corresponding planNode;

[0009] Generating distributed physical plan: generating distributed physical plan according to logical plan; specifically as follows:

[0010] Query optimizer receives a SQL query, and constructs a logical query plan according to the query statement;

[0011] Convert logical query machine into distributed physical query plan: the optimizer generates a list of candidate physical execution plans, and each physical execution plan involves task allocation on one or more nodes;

[0012] For each candidate physical execution plan, the Q-learning algorithm is used to generate the optimal agent selection and task allocation scheme;

[0013] According to the agent selection and task allocation scheme, the utility function value of the physical execution plan is calculated, and the definition of the utility function may be different for different scenarios, such as execution time, resource utilization, etc.;

[0014] By comparing the utility function values of all candidate physical execution plans, the physical execution plan with the highest utility function value is selected as the scheme for executing the query;

[0015] Execute the best physical execution plan selected by the optimizer, coordinate the execution progress of each node in the distributed environment, and finally complete the query processing.

[0016] As preferred, generating logical plan specifically as follows:

[0017] Build Optimizer: Optimizer converts input expression tree into logically equivalent expression tree and outputs the expression tree with the lowest execution cost;

[0018] Build Outscope: used for construction process;

[0019] Build memo through Outscope; wherein, memo is a data structure for effectively storing query plan forest;

[0020] Optimize memo;

[0021] Build planNode: interface for executing query;

[0022] Build exeplan through planNode.

[0023] More preferably, the utility function value is related to task completion time, resource utilization and load balancing, specifically as follows:

[0024] According to the ratio of task completion time and deadline, the utility function value of completing the task is calculated, and the utility function value of completing the task is normalized; specifically:

[0025] completionUtility:=math.Max(0,(taskDeadline-taskExecutionTime) / taskDeadline);

[0026] Wherein, completionUtility represents a calculateUtility function; taskExecutionTime represents the actual completion time of the task; taskDeadline represents the deadline of the task;

[0027] According to the system resource utilization, the resource utilization utility function value is calculated, and the resource utilization utility function value is normalized; Specifically:

[0028] resourceUtility:=math.Max(0,1-resourceUtilization);

[0029] Wherein, resourceUtilization represents the utilization of system resources;

[0030] According to the agent load balancing, the load balancing utility function value is calculated, and the utility function value is smoothed by using the function, specifically:

[0031] loadBalanceUtility:=math.Exp(-agentLoadBalance)

[0032] Wherein, agentLoadBalance represents the load balancing of each agent;

[0033] The utility function is weighted and averaged according to the weight, and the overall utility function value is calculated, specifically:

[0034] utility:=0.6*completionUtility+0.3*resourceUtility+0.1*loadBalanceUtility.

[0035] More preferably, the Q-learning algorithm is based on the greedy strategy, which constantly updates the Q value of the current state, so that the Q value is more and more close to the optimal value; Specifically:

[0036] In each time step, the agent selects an action according to the current state, and observes the next state of the environment and the corresponding reward signal;

[0037] The agent updates the Q value of the current state using the maximum Q value of the next state and stores the Q value in the Q table.

[0038] More preferably, the Q-learning algorithm generates a training process of the distributed execution plan, as follows:

[0039] Q(s,a) = Q(s,a) + a(r + g*max(Q(s',a')) - Q(s,a);

[0040] wherein: S represents the mapping between the plannode of the logical plan generation and the NodeID, as the current state; a represents the NodeID server NodeID, as the current action; s' represents the mapping between the plannode of the logical plan generation and the new NodeID, as the next state; a represents the learning rate; g represents the decay factor, which is used to adjust the discount factor of future rewards; Q(s,a) = Q(s,a) + a(r + g*max(Q(s',a')) - Q(s,a) represents the updating process of the Q value, through the continuous observation of the reward signal and the continuous updating of the Q value, the physical plan gradually learns the optimal distributed strategy.

[0041] A system for generating a distributed execution plan based on Q-learning, the system comprising a logical plan generation unit and a distributed physical plan generation unit;

[0042] The logical plan generation unit is configured to generate a planNode logical plan node, and each SQL has a corresponding planNode;

[0043] The distributed physical plan generation unit is configured to generate a distributed physical plan based on the logical plan; the distributed physical plan generation unit comprises a construction module, a conversion module, a generation module, an acquisition module, a selection module and an execution module;

[0044] The construction module is configured to query an optimizer to receive a SQL query and construct a logical query plan according to the query statement;

[0045] The conversion module is configured to convert the logical query plan into a distributed physical query plan, i.e., the optimizer generates a list of candidate physical execution plans, and each physical execution plan involves task allocation on one or more nodes;

[0046] The generation module is configured to generate an optimal agent selection and task allocation scheme for each candidate physical execution plan using a Q-learning algorithm;

[0047] The acquisition module is configured to acquire a utility function value of the physical execution plan according to the agent selection and the task allocation scheme. The definition of the utility function can be different in different scenarios, such as execution time, resource utilization, etc.

[0048] The selection module is configured to select the physical execution plan with the highest utility function value as the scheme for executing the query by comparing the utility function values of all candidate physical execution plans.

[0049] The execution module is configured to execute the optimal physical execution plan selected by the optimizer, coordinate the execution progress of each node in a distributed environment, and finally complete the query processing.

[0050] Preferably, the utility function value is related to the task completion time, resource utilization and load balancing, and specifically as follows:

[0051] The utility function value of completing the task is calculated according to the ratio of the task completion time and the deadline, and the utility function value of completing the task is normalized. Specifically:

[0052] completionUtility:=math.Max(0,(taskDeadline-taskExecutionTime) / taskDeadline);

[0053] Wherein, completionUtility represents a calculateUtility function; taskExecutionTime represents the actual completion time of the task; taskDeadline represents the deadline of the task;

[0054] The resource utilization utility function value is calculated according to the system resource utilization, and the resource utilization utility function value is normalized. Specifically:

[0055] resourceUtility:=math.Max(0,1-resourceUtilization);

[0056] Wherein, resourceUtilization represents the utilization rate of system resources;

[0057] The load balancing utility function value is calculated according to the agent load balancing, and the utility function value is smoothed using the function. Specifically:

[0058] loadBalanceUtility:=math.Exp(-agentLoadBalance)

[0059] Wherein, agentLoadBalance represents the load balancing of each agent.

[0060] The individual utility functions are weighted and averaged according to the weights to obtain the overall utility function value, specifically:

[0061] utility:=0.6*completionUtility+0.3*resourceUtility+0.1*loadBalanceUtility.

[0062] More preferably, the Q-learning algorithm is based on a greedy strategy, constantly updating the Q value of the current state, so that the Q value is more and more close to the optimal value; specifically:

[0063] In each time step, the agent selects an action according to the current state, and observes the next state of the environment and the corresponding reward signal;

[0064] The agent updates the Q value of the current state using the maximum Q value of the next state, and stores this Q value in the Q table;

[0065] The training process of the Q-learning algorithm for generating a distributed execution plan is as follows:

[0066] Q(s,a)=Q(s,a)+α(r+γ*max(Q(s',a'))-Q(s,a);

[0067] Where: S represents the mapping between the plannode generated by the logical plan and the NodeID, as the current state; a represents the NodeID server NodeID, as the current action; s' represents the mapping between the plannode generated by the logical plan and the new NodeID, as the next state; α represents the learning rate; γ represents the decay factor, which is used to adjust the discount factor of future rewards; Q(s,a)=Q(s,a)+α(r+γ*max(Q(s',a'))-Q(s,a) represents the updating process of the Q value, through the continuous observation of the reward signal and the continuous updating of the Q value, the physical plan gradually learns the optimal distributed strategy.

[0068] An electronic device, comprising: a memory and at least one processor;

[0069] Wherein the memory has stored thereon a computer program;

[0070] The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the method for generating a distributed execution plan based on Q-learning as described above.

[0071] A computer readable storage medium, the computer readable storage medium storing a computer program, the computer program being executable by a processor to implement the method for generating a distributed execution plan based on Q-learning as described above.

[0072] The method and system for generating a distributed execution plan based on Q-learning have the following advantages:

[0073] (1) The present application considers the distribution cost in the process of converting logical plan to distributed physical plan, and combines the powerful learning model of reinforcement learning to generate the distribution cost of all nodes, thereby ensuring that the entire execution plan is more reasonable and efficient, and improving the overall performance of the database operation;

[0074] (2) The present application uses Q-learning algorithm, which is a value iteration-based reinforcement learning algorithm that can be used to learn the optimal strategy. In the field of databases, Q-learning algorithm can be used to learn the optimal distribution strategy;

[0075] (3) The present application converts SQL into a "best" distributed physical execution plan, schedules the execution plan to the computing nodes, and the computing nodes execute the specific physical execution plan. Based on the Q-learning algorithm, the optimal distributed physical execution plan can be generated to improve query performance and response speed. The advantages are as follows:

[0076] ①Optimize query performance: Physical execution plan is the specific process of database execution query, and the query performance and response speed can be improved by optimizing the physical execution plan;

[0077] ②Execution plan generation: When generating the physical execution plan, the database system will consider various factors, such as data distribution, index usage, data access path, etc., to generate the best physical execution plan;

[0078] ③Scheduling execution plan: After generating the physical execution plan, the execution plan needs to be scheduled to the computing nodes for specific execution operation; at the same time, the execution process needs to be monitored and optimized to ensure the efficiency and stability of the entire query process;

[0079] ④Physical execution plan can be reused: Since the physical execution plan is generated for specific query statements, it can be cached and reused to reduce computation time and system overhead, and improve database query performance;

[0080] (4) The method based on Q-learning can be used to generate the optimal distributed physical execution plan, and the advantages are as follows:

[0081] ① No environment model required: Q-learning is a model-free reinforcement learning algorithm that does not require a pre-set environment model and learns the action value function directly through interaction with the environment;

[0082] ② Handling randomness issues: Q-learning can handle problems involving random transitions and rewards. For example, in database queries, due to factors such as data distribution and network latency, the query execution process has a certain degree of randomness, and Q-learning can effectively handle this situation.

[0083] ③ Learning optimal decision-making: Q-learning can find the optimal action sequence that maximizes cumulative reward by learning the action value function, thereby generating the optimal distributed physical execution plan;

[0084] ④ Adaptable to complex environments: Since Q-learning is a model-agnostic algorithm, it can adapt to complex and unknown environments, including scenarios such as distributed database queries. Attached Figure Description

[0085] The invention will be further described below with reference to the accompanying drawings.

[0086] Appendix Figure 1 A flowchart for generating a logical plan;

[0087] Appendix Figure 2 A flowchart for generating a distributed physical plan;

[0088] Appendix Figure 3 This is a flowchart for calculating the utility function value. Detailed Implementation

[0089] The method and system for generating distributed execution plans based on Q-learning of the present invention will be described in detail below with reference to the accompanying drawings and specific embodiments.

[0090] Example 1:

[0091] This embodiment provides a method for generating distributed execution plans based on Q-learning, as detailed below:

[0092] S1. Generate logical plan: Generate a planNode logical plan node. Each SQL statement has a corresponding planNode.

[0093] S2. Generate Distributed Physical Plan: Generate a distributed physical plan based on the logical plan.

[0094] Before the executor executes, the support of planning is needed. The planning is divided into logical planning and physical planning. The relationship between logical planning and physical planning is just like that we want to go on a trip, choosing a transportation tool is equivalent to logical planning, and after choosing a plane in this step, choosing an airline is equivalent to physical planning. Finally, when you really start to travel, it is equivalent to execution.

[0095] As shown in the accompanying Figure 2 The generation of the distributed physical plan in step S2 in this embodiment is specifically as follows:

[0096] S201, the query optimizer receives a SQL query, and constructs a logical query plan according to the query statement;

[0097] S202, the logical query machine is converted into a distributed physical query plan: the optimizer generates a list of candidate physical execution plans, and each physical execution plan involves task allocation on one or more nodes;

[0098] S203, for each candidate physical execution plan, an optimal agent selection and task allocation scheme is generated by using a Q-learning algorithm;

[0099] S204, the utility function value of the physical execution plan is calculated according to the agent selection and task allocation scheme, and the definition of the utility function may be different in different scenarios, such as execution time, resource utilization, etc.

[0100] S205, by comparing the utility function values of all candidate physical execution plans, the physical execution plan with the highest utility function is selected as the scheme for executing the query;

[0101] S206, execute the best physical execution plan selected by the optimizer, coordinate the execution progress of each node in the distributed environment, and finally complete the query processing.

[0102] As shown in the accompanying Figure 1 The generation of the logical plan in step S1 in this embodiment is specifically as follows:

[0103] S101, build Optimizer: the Optimizer converts the input expression tree into a logically equivalent expression tree and outputs an expression tree with the lowest execution cost;

[0104] S102, build Outscope: used for building process;

[0105] S103, build memo through Outscope; wherein, memo is a data structure for effectively storing query plan forest;

[0106] S104, optimize memo;

[0107] S105, constructing planNode: an interface for executing a query;

[0108] S106, constructing exeplan by planNode.

[0109] As shown in the accompanying drawings Figure 3 The utility function value in the embodiment is related to task completion time, resource utilization and load balancing, and is specifically as follows:

[0110] (1) The utility function value of completing a task is calculated according to the ratio of task completion time and deadline, and the utility function value of completing a task is normalized; specifically:

[0111] completionUtility:=math.Max(0,(taskDeadline-taskExecutionTime) / taskDeadline);

[0112] Wherein, completionUtility represents a calculateUtility function; taskExecutionTime represents the actual completion time of the task; taskDeadline represents the deadline of the task;

[0113] (2) The resource utilization utility function value is calculated according to the system resource utilization, and the resource utilization utility function value is normalized; specifically:

[0114] resourceUtility:=math.Max(0,1-resourceUtilization);

[0115] Wherein, resourceUtilization represents the utilization of system resources;

[0116] (3) The load balancing utility function value is calculated according to the agent load balancing, and the utility function value is smoothed using the function, specifically:

[0117] loadBalanceUtility:=math.Exp(-agentLoadBalance)

[0118] Wherein, agentLoadBalance represents the load balancing of each agent;

[0119] (4) The overall utility function value is calculated by weighted average of each utility function according to the weight, specifically:

[0120] utility := 0.6 * completionUtility + 0.3 * resourceUtility + 0.1 * loadBalanceUtility.

[0121] The Q-learning algorithm in this embodiment is based on a greedy strategy, which constantly updates the Q value of the current state, so that the Q value is more and more close to the optimal value. Specifically:

[0122] (1) In each time step, the agent selects an action according to the current state, and observes the next state of the environment and the corresponding reward signal;

[0123] (2) The agent updates the Q value of the current state using the maximum Q value of the next state, and stores this Q value in the Q table.

[0124] The training process of the Q-learning algorithm in this embodiment generates a distributed execution plan, and the formula is as follows:

[0125] Q(s,a) = Q(s,a) + a(r + g*max(Q(s',a')) - Q(s,a);

[0126] Where: S represents the mapping between the plannode generated by the logical plan and the NodeID, as the current state; a represents the NodeID server NodeID, as the current action; s' represents the mapping between the plannode generated by the logical plan and the new NodeID, as the next state; a represents the learning rate; g represents the decay factor, which is used to adjust the discount factor of future rewards; Q(s,a) = Q(s,a) + a(r + g*max(Q(s',a')) - Q(s,a) represents the updating process of Q value, through the continuous observation of reward signal and the continuous updating of Q value, the physical plan gradually learns the optimal distributed strategy.

[0127] In the reinforcement learning process, the agent is always interacting with the environment. The agent obtains the state in the environment, and the agent outputs an action, a decision, using the state. Then the decision is put into the environment, and the environment outputs the next state and the reward obtained by the current decision according to the decision taken by the agent. The purpose of the agent is to obtain as much reward as possible from the environment.

[0128] The basic elements of reinforcement learning are as follows:

[0129] The environment (environment) is an external system, in which the agent is located, can perceive the system and can make certain actions based on the perceived state.

[0130] An agent is a system embedded in an environment that can change the state of the environment by taking actions.

[0131] State / Observation: A state is a complete description of the world, which does not hide any information about the world. An observation is a partial description of the state, which may miss some information.

[0132] Action: Different environments allow different kinds of actions. In a given environment, the set of valid actions is often referred to as the action space, which includes discrete action spaces and continuous action spaces. For example, a robot walking a maze is a discrete action space if it only has four ways to move, east, south, west, and north. If the robot can move to any angle in 360 degrees, it is a continuous action space. Reward: A scalar feedback signal given by the environment, which shows how well the agent performs at a certain step by taking a certain policy.

[0133] Applying reinforcement learning techniques in databases can help solve some optimization problems in computer systems, such as generating distributed plans. Specifically, by developing an intelligent agent based on reinforcement learning, automatic optimization and adjustment of query execution plans can be achieved in database management systems, thereby improving query performance and throughput.

[0134] In the process of generating distributed plans, issues such as task scheduling, data distribution, and execution flow on multiple nodes need to be considered. Using reinforcement learning techniques can allow the agent to intelligently explore the search space and learn how to flexibly choose appropriate plans and adjust them according to actual conditions. At the same time, introducing reward functions or penalty mechanisms can make the agent better comply with the system's goals and constraints, thereby achieving better performance and results.

[0135] There are already some research works that have explored and practiced similar ideas, such as database automatic tuning methods based on deep reinforcement learning, deep Q networks applied to resource scheduling in cloud computing systems, etc. These works show that reinforcement learning techniques can provide new ideas and methods for solving problems such as generating distributed plans for databases.

[0136] Example 2:

[0137] The embodiment provides a system for generating a distributed execution plan based on Q-learning, which comprises a logical plan generation unit and a distributed physical plan generation unit;

[0138] The logical plan generation unit is used for generating a planNode logical plan node, and each SQL corresponds to a planNode;

[0139] The distributed physical plan generation unit is used for generating a distributed physical plan according to the logical plan; the distributed physical plan generation unit comprises a construction module, a conversion module, a generation module, an acquisition module, a selection module and an execution module;

[0140] The construction module is used for querying an optimizer to receive a SQL query, and constructing a logical query plan according to the query statement;

[0141] The conversion module is used for converting the logical query plan into a distributed physical query plan, that is, the optimizer generates a list of candidate physical execution plans, and each physical execution plan involves task allocation on one or more nodes;

[0142] The generation module is used for generating an optimal agent selection and task allocation scheme for each candidate physical execution plan by using a Q-learning algorithm;

[0143] The acquisition module is used for acquiring a utility function value of the physical execution plan according to the agent selection and task allocation scheme; the definition of the utility function may be different in different scenarios, for example, execution time, resource utilization and the like;

[0144] The selection module is used for selecting a physical execution plan with the highest utility function value as the scheme for executing the query by comparing the utility function values of all candidate physical execution plans;

[0145] The execution module is used for executing the optimal physical execution plan selected by the optimizer, coordinating the execution progress of each node in a distributed environment, and finally completing the query processing.

[0146] The utility function value in the embodiment is related to task completion time, resource utilization and load balancing, and is specifically as follows:

[0147] (1) The utility function value of completing a task is calculated according to the ratio of the task completion time and the deadline, and the utility function value of completing the task is normalized; specifically:

[0148] completionUtility:=math.Max(0,(taskDeadline-taskExecutionTime) / taskDeadline);

[0149] wherein, completionUtility represents a calculateUtility function; taskExecutionTime represents the actual completion time of the task; and taskDeadline represents the deadline of the task.

[0150] (2) The resource utilization utility function value is calculated according to the system resource utilization, and the resource utilization utility function value is normalized. Specifically, the resource utilization utility function value is calculated according to the following formula:

[0151] resourceUtility:=math.Max(0,1-resourceUtilization);

[0152] wherein, resourceUtilization represents the utilization of the system resource.

[0153] (3) The load balancing utility function value is calculated according to the agent load balancing, and the utility function value is smoothed using the function. Specifically, the load balancing utility function value is calculated according to the following formula:

[0154] loadBalanceUtility:=math.Exp(-agentLoadBalance)

[0155] wherein, agentLoadBalance represents the load balancing of each agent.

[0156] (4) The overall utility function value is calculated by weighting and averaging the utility function values according to the weights. Specifically, the overall utility function value is calculated according to the following formula:

[0157] utility:=0.6*completionUtility+0.3*resourceUtility+0.1*loadBalanceUtility.

[0158] The Q-learning algorithm in this embodiment is based on the greedy strategy, which constantly updates the Q value of the current state, so that the Q value is more and more close to the optimal value. Specifically, the Q-learning algorithm is as follows:

[0159] (1) In each time step, the agent selects an action according to the current state, and observes the next state of the environment and the corresponding reward signal.

[0160] (2) The agent updates the Q value of the current state using the maximum Q value of the next state, and stores this Q value in the Q table.

[0161] The training process of the Q-learning algorithm for generating distributed execution plans in this embodiment is as follows:

[0162] Q(s, a) = Q(s, a) + a(r + g*max(Q(s', a')) - Q(s, a));

[0163] Wherein: S represents the mapping between the plannode of the logical plan and the NodeID as the current state; a represents the NodeID server NodeID as the current action; s' represents the mapping between the plannode of the logical plan and the new NodeID as the next state; a represents the learning rate; g represents the decay factor, which is used to adjust the discount factor of future rewards; Q(s, a) = Q(s, a) + a(r + g*max(Q(s', a')) - Q(s, a) represents the updating process of the Q value, and through the continuous observation of the reward signal and the continuous updating of the Q value, the physical plan gradually learns the optimal distributed strategy.

[0164] Embodiment 3:

[0165] The embodiment also provides an electronic device, comprising: a memory and a processor;

[0166] The memory stores computer execution instructions;

[0167] The processor executes the computer execution instructions stored in the memory, so that the processor executes the method for generating a distributed execution plan based on Q-learning in any embodiment of the application.

[0168] The processor can be a central processing unit (CPU), and can also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), ready-to-program gate arrays (FPGAs) or other programmable logic devices, discrete gates or transistor logic devices, discrete hardware components, etc. The processor can be a microprocessor or any other conventional processor.

[0169] The memory can be used to store computer programs and / or modules, and the processor can realize various functions of the electronic device by running or executing the computer programs and / or modules stored in the memory, and calling the data stored in the memory. The memory can mainly include a program storage area and a data storage area, wherein the program storage area can store an operating system, at least one application required by a function, etc.; the data storage area can store data created according to the use of the terminal, etc. In addition, the memory can also include a high-speed random access memory, and can also include a non-volatile memory, such as a hard disk, a memory, a plug-in hard disk, a secure digital (SD) card, a flash memory card, at least one magnetic disk storage period, a flash memory device, or other volatile solid-state memory devices.

[0170] Embodiment 4:

[0171] The embodiment also provides a computer readable storage medium, in which a plurality of instructions are stored, the instructions are loaded by a processor to make the processor execute the method for generating a distributed execution plan based on Q-learning in any of the embodiments of the present application. Specifically, a system or device equipped with a storage medium can be provided, in which a software program code realizing the functions of any of the above embodiments is stored, and the computer (or CPU or MPU) of the system or device reads and executes the program code stored in the storage medium.

[0172] In this case, the program code read from the storage medium itself realizes the functions of any of the above embodiments, and thus the program code and the storage medium storing the program code constitute a part of the present application.

[0173] The storage medium for providing the program code includes a floppy disk, a hard disk, a magneto-optical disk, an optical disk (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RYM, DVD-RW, DVD+RW), a magnetic tape, a non-volatile memory card, and a ROM. Alternatively, the program code can be downloaded from a server computer via a communication network.

[0174] In addition, it should be clear that not only the program code read by the computer can be executed, but also part or all of the actual operations can be completed by the operating system or the like operating on the computer based on the instructions of the program code, so as to realize the functions of any of the above embodiments.

[0175] In addition, it should be understood that the program code read from the storage medium can be written into the memory provided in the expansion board inserted into the computer or the memory provided in the expansion unit connected to the computer, and then part or all of the actual operations can be executed by the CPU or the like installed on the expansion board or the expansion unit based on the instructions of the program code, so as to realize the functions of any of the above embodiments.

[0176] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present application, and not to limit the present application; although the present application has been described in detail with reference to the above embodiments, those skilled in the art should understand that the technical solutions recorded in the above embodiments can still be modified, or part or all of the technical features can be replaced by equivalents; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the scope of the technical solutions of the embodiments of the present application.

Claims

1. A method for generating a distributed execution plan based on Q-learning, the method comprising: The method is specifically as follows: Generate a logical plan: generate a planNode logical plan node, each SQL has a corresponding planNode; Generate a distributed physical plan: generate a distributed physical plan according to the logical plan; Specifically as follows: The query optimizer receives a SQL query, and constructs a logical query plan according to the query statement; Convert the logical query plan into a distributed physical query plan: the optimizer generates a list of candidate physical execution plans, and each physical execution plan involves task allocation on one or more nodes; For each candidate physical execution plan, generate the optimal agent selection and task allocation scheme using the Q-learning algorithm; Calculate the utility function value of the physical execution plan according to the agent selection and task allocation scheme; By comparing the utility function values of all candidate physical execution plans, select the physical execution plan with the highest utility function value as the scheme for executing the query; Execute the best physical execution plan selected by the optimizer, coordinate the execution progress of each node in the distributed environment, and finally complete the query processing; Wherein, the utility function value is related to the task completion time, resource utilization and load balancing, and the specific is as follows: According to the ratio of task completion time and deadline, calculate the utility function value of completing the task, and normalize the utility function value of completing the task; Specifically: completionUtility := math.Max(0, (taskDeadline - taskExecutionTime) / taskDeadline); Wherein, completionUtility represents a calculateUtility function; taskExecutionTime represents the actual completion time of the task; taskDeadline represents the deadline of the task; According to the system resource utilization, calculate the resource utilization utility function value, and normalize the resource utilization utility function value; Specifically: resourceUtility := math.Max(0, 1 - resourceUtilization); Wherein, resourceUtilization represents the utilization rate of system resources; According to the agent load balancing, calculate the load balancing utility function value, and use the function to smooth the utility function value, specifically: loadBalanceUtility := math.Exp(-agentLoadBalance) Wherein, agentLoadBalance represents the load balancing of each agent; Weighted average of each utility function according to the weight, comprehensive calculation of overall utility function value, specifically: utility := 0.6 completionUtility + 0.3 resourceUtility + 0.1 loadBalanceUtility; The Q-learning algorithm is based on the greedy strategy, which constantly updates the Q value of the current state, so that the Q value is more and more close to the optimal value; Specifically: At each time step, the agent selects an action according to the current state, and observes the next state of the environment and the corresponding reward signal; The agent updates the Q value of the current state using the maximum Q value of the next state, and stores the Q value in the Q table; The Q-learning algorithm generates a training process of the distributed execution plan, and the formula is as follows: Q(s, a) = Q(s, a) + a(r + g max(Q(s', a')) - Q(s, a)) Wherein: S represents the mapping between the plannode of the logical plan and the NodeID as the current state; a represents the NodeID server NodeID as the current action; s' represents the mapping between the plannode of the logical plan and the new NodeID as the next state; a represents the learning rate; g represents the decay factor, which is used to adjust the discount factor of the future reward; Q(s, a) = Q(s, a) + a(r + g max(Q(s', a')) - Q(s, a)) represents the updating process of the Q value, and through the continuous observation of the reward signal and the continuous updating of the Q value, the physical plan gradually learns the optimal distributed strategy. 2.The method for generating a distributed execution plan based on Q-learning according to claim 1, wherein, The generation of the logical plan is as follows: Optimizer: The optimizer converts the input expression tree into a logically equivalent expression tree, and outputs the expression tree with the lowest execution cost; Outscope: Used for construction process; Memo: A data structure used to effectively store the query plan forest is constructed through Outscope; Optimize memo; PlanNode: An interface for executing queries; Exeplan is constructed through planNode.

3. A system for generating a distributed execution plan based on Q-learning, the system comprising: The system comprises a logical plan generation unit and a distributed physical plan generation unit; The logical plan generation unit is used to generate a planNode logical plan node, and each SQL has a corresponding planNode; The distributed physical plan generation unit is used to generate a distributed physical plan according to the logical plan; the distributed physical plan generation unit comprises a construction module, a conversion module, a generation module, an acquisition module, a selection module and an execution module; The construction module is used to query the optimizer to receive a SQL query, and to construct a logical query plan according to the query statement; The conversion module is used to convert the logical query plan into a distributed physical query plan, i.e., the optimizer generates a list of candidate physical execution plans, and each physical execution plan involves task allocation on one or more nodes; The generation module is used to generate an optimal agent selection and task allocation scheme for each candidate physical execution plan using the Q-learning algorithm; The acquisition module is used to acquire the utility function value of the physical execution plan according to the agent selection and task allocation scheme; The selection module is used to select the physical execution plan with the highest utility function value as the scheme for executing the query by comparing the utility function values of all candidate physical execution plans; The execution module is used to execute the best physical execution plan selected by the optimizer, coordinate the execution progress of each node in the distributed environment, and finally complete the query processing; The utility function value is related to the task completion time, resource utilization and load balancing, and is calculated as follows: The utility function value of completing the task is calculated according to the ratio of the task completion time and the deadline, and the utility function value of completing the task is normalized; specifically: completionUtility := math.Max(0, (taskDeadline - taskExecutionTime) / taskDeadline). Wherein, completionUtility represents a calculateUtility function; taskExecutionTime represents the actual completion time of the task; taskDeadline represents the deadline of the task; According to the system resource utilization, a resource utilization utility function value is calculated, and the resource utilization utility function value is normalized; specifically: resourceUtility := math.Max(0, 1 - resourceUtilization); Wherein, resourceUtilization represents the utilization of system resources; According to the agent load balancing, a load balancing utility function value is calculated, and the utility function value is smoothed using a function, specifically: loadBalanceUtility := math.Exp(-agentLoadBalance) Wherein, agentLoadBalance represents the load balancing of each agent; The utility functions are weighted and averaged according to the weights, and the overall utility function value is calculated, specifically: utility := 0.6 completionUtility + 0.3 resourceUtility + 0.1 loadBalanceUtility; The Q-learning algorithm is based on the greedy strategy, which constantly updates the Q value of the current state, so that the Q value is more and more close to the optimal value; Specifically: In each time step, the agent selects an action according to the current state, and observes the next state of the environment and the corresponding reward signal; The agent updates the Q value of the current state using the maximum Q value of the next state, and stores this Q value in the Q table; The training process of the Q-learning algorithm to generate a distributed execution plan is as follows: Q(s, a) = Q(s, a) + a(r + g max(Q(s', a')) - Q(s, a)) Wherein: S represents the mapping between the plannode of the logical plan and the NodeID as the current state; a represents the NodeID server NodeID as the current action; s' represents the mapping between the plannode of the logical plan and the new NodeID as the next state; a represents the learning rate; g represents the decay factor, which is used to adjust the discount factor of the future reward; Q(s, a) = Q(s, a) + a(r + g max(Q(s', a')) - Q(s, a)) represents the updating process of the Q value, and through the continuous observation of the reward signal and the continuous updating of the Q value, the physical plan gradually learns the optimal distributed strategy.

4. An electronic device, comprising: It includes: Memory and at least one processor; Wherein, the memory has stored a computer program; The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the method for generating a distributed execution plan based on Q-learning as claimed in claim 1 or 2.

5. A computer readable storage medium, characterized in that, The computer readable storage medium has stored a computer program, which can be executed by the processor to realize the method for generating a distributed execution plan based on Q-learning as claimed in claim 1 or 2.

Images