Streaming SQL execution plan optimization method based on optimizer

Abstract

The present invention discloses a method for optimizing streaming SQL execution plans based on an optimizer, comprising: collecting required statistical information for a data source stream with historical input, and implementing cost-based rules to enable the optimizer to generate multiple execution plans; constructing a cost model that conforms to streaming computing, and using the collected statistical information to calculate the costs of multiple execution plans generated by the optimizer based on the cost rules, and selecting the final execution plan with the lowest cost based on the calculated cost comparison; obtaining the final execution plan, and executing the final streaming SQL. The cost model proposed by the present invention takes into account CPU, I / O, memory, and network transmission overhead, and is more consistent with the actual execution process of streaming computing. It comprehensively considers the cost overhead of state management unique to streaming computing, making cost calculation more accurate and enabling the selection of execution plans with higher execution performance and fewer computing resources for execution.

Description

Technical Field

[0001] The present invention relates to the technical field of stream computing, and in particular to an optimizer-based stream SQL execution plan optimization method. Background Art

[0002] With the advent of the big data era, data volumes are exploding, and the demand for real-time processing of massive data is also increasing. Many systems need to respond promptly to real-time data changes, leading to the emergence and development of numerous distributed stream processing engines, such as Storm, Spark Streaming, and Flink. However, these engines, like traditional big data engines, face the same challenges: they significantly increase the programming threshold for developers and data analysts. SQL is a relatively easy and commonly used data processing language, leading to the emergence of SQL-like statements, or streaming SQL, that enable developers to easily leverage streaming computing to process big data in real time.

[0003] Because streaming SQL can generate multiple execution plans for streaming computing jobs based on query statements, the performance differences between these plans can be significant. Therefore, optimizing streaming SQL queries to generate optimal execution plans and improve query performance is particularly important. Based on prior knowledge, rule-based optimizations for streaming SQL, such as predicate pushdown, can generally save computing resources and improve performance. However, some optimizations are cost-based. Directly changing the execution plan without considering statistical information and cost models can lead to increased computation and waste of resources. However, traditional batch processing statistical information collection methods and cost models are not suitable for streaming computing execution, so cost-based optimization of streaming SQL is necessary. However, streaming computing is characterized by long runtimes and large data volume fluctuations. Collecting statistics during runtime consumes significant computing resources. Moreover, since switching to other plans at runtime will face the problem of compatibility of the state that stores historical calculation information and the high cost of building the state required for the new plan, if the historical input is not available, the new state cannot be calculated, and there will be data consistency issues. Therefore, it is difficult and risky to dynamically adjust the execution plan of the streaming calculation at runtime. However, in reality, most streams are stable in the long term and the data distribution is relatively stable. Therefore, the present invention can evaluate based on historical input and calculate the cost in combination with the cost model, and select the plan with the lowest execution cost from the execution plans that the multiple streaming SQL may be converted into, so that a better execution plan can be obtained based on historical data before startup. Summary of the Invention

[0004] The purpose of the present invention is to overcome the shortcomings and deficiencies of the existing technology and propose an optimizer-based streaming SQL execution plan optimization method. For data source streams with historical input, cost-based optimization is performed before the actual execution of the streaming SQL to generate a better execution plan, improve the execution performance of the streaming SQL, and reduce the use of computing resources.

[0005] To achieve the above objectives, the present invention provides a technical solution: a streaming SQL execution plan optimization method based on an optimizer, comprising the following steps:

[0006] S1. For data source streams with historical input, collect the required statistical information and implement cost-based rules to enable the optimizer to generate multiple execution plans.

[0007] S2. Build a cost model that conforms to streaming computing. Using the statistical information collected in step S1, calculate the costs of various execution plans generated by the optimizer based on cost rules. Then, based on the calculated costs, select the final execution plan with the lowest cost.

[0008] S3. Get the final execution plan and execute the final streaming SQL.

[0009] Furthermore, in step S1, the collected statistical information includes the number of tuples, the cardinality of each column, the maximum value, the minimum value, and the character size occupied.

[0010] Furthermore, in step S1, in streaming computing, for the aggregation operator Aggregate, it is implemented that based on the batch operation of the aggregation operator, a two-stage execution plan can be generated for opening or closing the Aggregate; opening the two-stage Aggregate can divide the Aggregate into local aggregation Local Aggregate and global aggregation Global Aggregate; Local Aggregate pre-aggregates the data to be sent downstream locally before sending it to Global Aggregate; for the connection operator Join, when joining multiple streams, it is implemented to infer different Join orders based on the Join conditions to generate rules for multiple different execution plans.

[0011] Furthermore, in step S2, in order to consider the resource consumption required for the actual execution of streaming computing, and the performance indicators of streaming computing mainly include throughput and latency, the cost model comprehensively considers the four resources of CPU, I / O, memory and network transmission to calculate the cost overhead. The final calculated cost is:

[0012] cost=Normalize(CPU,I / O,Memory,Net)

[0013] In the formula, cost represents the final computing cost, Normalize represents the normalization of the four resource costs, CPU represents the required CPU resources, I / O represents the required I / O resources, Memory represents the required memory resources, and Net represents the required network transmission resources.

[0014] Further, in step S2, the cost model includes cost calculations for Aggregate, Local Aggregate, GlobalAggregate, and Join operators;

[0015] The cost of an Aggregate is defined as:

[0016] CPU=T(I)×(R function ×N f +R state ×N k )+T(O)×R serialize_deserialize

[0017]

[0018] Memory=0

[0019] Net=T sz (O)×T(O)

[0020] In the formula, T(I) represents the number of tuples of the input source, T(O) represents the number of tuples of the output, and R function Represents the ratio of CPU required to execute a calculation function, R state Represents the ratio of CPU required to execute the detection state, R serialize_deserialize Represents the ratio of CPU required to perform serialization and deserialization, N f Represents the number of aggregate functions in the query statement, N k Represents the number of aggregated key values, T sz (O) represents the average size of the output tuple;

[0021] The CPU overhead required for Aggregate includes N operations on the input data. k Calculation of aggregate functions, and N calculations of data in the state k The overhead of searching for each key, performing the serialization and deserialization required to complete the aggregation process, and storing the state;

[0022] The I / O overhead required by Aggregate can be divided into two cases: First, in N kWhen the aggregation key is 0, that is, the aggregation key is empty, it means that there is no need to group by key, and the input data is finally aggregated into one piece of data; the second type is in N k If it is not 0, the final result of the input data will be aggregated into key-grouped data;

[0023] The cost model considers storing the state to disk, so the memory overhead is negligible;

[0024] The network transmission overhead required by Aggregate includes the network overhead of transmitting the operator calculation results to the downstream through the network;

[0025] The cost of Local Aggregate is defined as:

[0026] CPU=T(I)×(R function ×N f +R hash ×N k )

[0027] I / O=0

[0028]

[0029] Net=T sz (O)×T(O)

[0030] Where R function Represents the ratio of CPU required to perform hash probing;

[0031] Since Local Aggregate does not involve state management, it does not involve serialization and deserialization overhead. Therefore, the CPU overhead required by Local Aggregate includes N-order processing of input data. f Calculation of aggregate functions, the stored data is N k The cost of searching for each key;

[0032] Since Local Aggregate does not involve state management, there is no need to consider the overhead of reading and writing state to disk, so the I / O overhead is negligible;

[0033] The memory overhead required by Local Aggregate can be divided into two cases: First, in N k When the aggregation key is 0, that is, the aggregation key is empty, it means that there is no need to group by key, and the input data is finally aggregated into one piece of data; the second type is in N k If it is not 0, the final result of the input data will be aggregated into key-grouped data;

[0034] The network transmission overhead required by Local Aggregate includes the network overhead of transmitting the operator calculation results to the downstream through the network;

[0035] The cost of Global Aggregate is defined as:

[0036] CPU=T(I)×(R function ×N f +R state ×N k )+T(O)×R serialize_deserialize

[0037]

[0038] Memory=0

[0039] Net=T sz (O)×T(O)

[0040] The formulas for group aggregation and global aggregation are the same. However, when the degree of aggregation is high or there is data skew, global aggregation needs to process less data after local aggregation than aggregation without the two-stage aggregation.

[0041] The cost of Join is defined as:

[0042] CPU=(T(left)+T(right))×(R state +R serialize_deserialize )

[0043] I / O=T(left)×T sz (left)+T(right)×T sz (right)

[0044] Memory=0

[0045] Net=T sz (O)×T(O)

[0046] Where T(left) represents the number of tuples in the left stream, and T(right) represents the number of tuples in the right stream;

[0047] The CPU overhead required for Join includes the serialization and deserialization overhead required to store the input data state of the left and right streams, as well as the search overhead of the left stream to the right stream and the right stream to the left stream state;

[0048] The I / O overhead required for Join includes the I / O overhead required to store the input data of the left and right streams into the state;

[0049] The cost model considers storing the state to disk, so the memory overhead is negligible;

[0050] The network transmission overhead required for Join includes the network overhead of transmitting the operator calculation results to the downstream through the network.

[0051] Compared with the prior art, the present invention has the following advantages and beneficial effects:

[0052] 1. The present invention collects statistical information on data streams in advance and collects statistical information before executing streaming SQL to obtain statistical information and select an execution plan in conjunction with a cost model.

[0053] 2. The present invention proposes a cost model that adapts to the actual execution process of streaming operators. This cost model takes into account four resources: CPU, I / O, memory, and network transmission, and considers the state management issues involved in streaming computing.

[0054] 3. The present invention can generate an execution plan that is more in line with the data characteristics of the data source stream than rule-based optimization before streaming SQL is executed, and can provide an execution plan with lower cost based on different data characteristics. BRIEF DESCRIPTION OF THE DRAWINGS

[0055] Figure 1 A flowchart for generating an execution plan for streaming SQL, wherein the rectangular box represents the part involved in the method of the present invention. DETAILED DESCRIPTION

[0056] The present invention will be described in further detail below with reference to the embodiments and drawings, but the embodiments of the present invention are not limited thereto.

[0057] This embodiment provides a streaming SQL execution plan optimization method based on an optimizer, including the following steps:

[0058] S1. For data source streams with historical input, collect the required statistical information and implement cost-based rules to enable the optimizer to generate multiple execution plans. The specific situation is as follows:

[0059] The statistics collected include the number of tuples, the cardinality of each column, the maximum value, the minimum value, and the size of the characters occupied;

[0060] In stream computing, for the Aggregate operator, based on the batch operation of the Aggregate operator, it is possible to generate a two-stage execution plan for the Aggregate, which can be turned on or off. Enabling two-stage Aggregation can divide the Aggregate into Local Aggregate and Global Aggregate. Local Aggregate pre-aggregates the data to be sent downstream locally before sending it to Global Aggregate. For the Join operator, when joining multiple streams, it is possible to infer different Join orders based on the Join conditions, thereby generating various execution plan rules.

[0061] S2. Build a cost model that conforms to streaming computing. Use the statistical information collected in step S1 to calculate the costs of various execution plans generated by the optimizer based on cost rules. Then, based on the calculated costs, select the final execution plan with the lowest cost. The details are as follows:

[0062] To account for the resource consumption required for the actual execution of streaming computing, and because streaming computing's performance metrics primarily include throughput and latency, the cost model comprehensively considers four resources: CPU, I / O, memory, and network transmission. The final calculated cost is:

[0063] cost=Normalize(CPU,I / O,Memory,Net)

[0064] In the formula, cost represents the final computing cost, Normalize represents the normalization of the four resource costs, CPU represents the required CPU resources, I / O represents the required I / O resources, Memory represents the required memory resources, and Net represents the required network transmission resources.

[0065] The cost model includes cost calculations for Aggregate, Local Aggregate, Global Aggregate, and Join operators;

[0066] The cost of an Aggregate is defined as:

[0067] CPU=T(I)×(R function ×N f +R state ×N k )+T(O)×R serialize_deserialize

[0068]

[0069] Memory=0

[0070] Net=T sz (O)×T(O)

[0071] In the formula, T(I) represents the number of tuples of the input source, T(O) represents the number of tuples of the output, and R function Represents the ratio of CPU required to execute a calculation function, R state Represents the ratio of CPU required to execute the detection state, R serialize_deserialize Represents the ratio of CPU required to perform serialization and deserialization, N f Represents the number of aggregate functions in the query statement, N k Represents the number of aggregated key values, T sz (O) represents the average size of the output tuple;

[0072] The CPU overhead required for Aggregate includes N operations on the input data. k Calculation of aggregate functions, and N calculations of data in the state k The overhead of searching for each key, performing the serialization and deserialization required to complete the aggregation process, and storing the state;

[0073] The I / O overhead required by Aggregate can be divided into two cases: First, in N k When the aggregation key is 0, that is, the aggregation key is empty, it means that there is no need to group by key, and the input data is finally aggregated into one piece of data; the second type is in N k If it is not 0, the final result of the input data will be aggregated into key-grouped data;

[0074] The cost model considers storing the state to disk, so the memory overhead is negligible;

[0075] The network transmission overhead required by Aggregate includes the network overhead of transmitting the operator calculation results to the downstream through the network;

[0076] The cost of Local Aggregate is defined as:

[0077] CPU=T(I)×(R function ×N f +R hash ×N k )

[0078] I / O=0

[0079]

[0080] Net=T sz (O)×T(O)

[0081] Where R function Represents the ratio of CPU required to perform hash probing;

[0082] Since Local Aggregate does not involve state management, it does not involve serialization and deserialization overhead. Therefore, the CPU overhead required by Local Aggregate includes N-order processing of input data. f Calculation of aggregate functions, the stored data is N k The cost of searching for each key;

[0083] Since Local Aggregate does not involve state management, there is no need to consider the overhead of reading and writing state to disk, so the I / O overhead is negligible;

[0084] The memory overhead required by Local Aggregate can be divided into two cases: First, in N k When the aggregation key is 0, that is, the aggregation key is empty, it means that there is no need to group by key, and the input data is finally aggregated into one piece of data; the second type is in N k If it is not 0, the final result of the input data will be aggregated into key-grouped data;

[0085] The network transmission overhead required by Local Aggregate includes the network overhead of transmitting the operator calculation results to the downstream through the network;

[0086] The cost of Global Aggregate is defined as:

[0087] CPU=T(I)×(R function ×N f +R state ×N k )+T(O)×R serialize_deserialize

[0088]

[0089] Memory=0

[0090] Net=T sz (O)×T(O)

[0091] The formulas for group aggregation and global aggregation are the same. However, when the degree of aggregation is high or there is data skew, global aggregation needs to process less data after local aggregation than aggregation without the two-stage aggregation.

[0092] The cost of Join is defined as:

[0093] CPU=(T(left)+T(right))×(R state +R serialize_deserialize )

[0094] I / O=T(left)×T sz (left)+T(right)×T sz (right)

[0095] Memory=0

[0096] Net=T sz (O)×T(O)

[0097] Where T(left) represents the number of tuples in the left stream, and T(right) represents the number of tuples in the right stream;

[0098] The CPU overhead required for Join includes the serialization and deserialization overhead required to store the input data state of the left and right streams, as well as the search overhead of the left stream to the right stream and the right stream to the left stream state;

[0099] The I / O overhead required for Join includes the I / O overhead required to store the input data of the left and right streams into the state;

[0100] The cost model considers storing the state to disk, so the memory overhead is negligible;

[0101] The network transmission overhead required for Join includes the network overhead of transmitting the operator calculation results to the downstream through the network.

[0102] S3. Get the final execution plan and execute the final streaming SQL.

[0103] The following example uses the cost model implementation on the stream processing engine Apache Flink 1.17. The implementation includes the following steps:

[0104] S1. Apache Flink 1.17 is one of the most commonly used stream processing engines. Flink currently does not utilize statistical information of the data source for the optimization of streaming SQL, so it can be considered as a rule-based optimization. Since Flink lacks some cost-based optimization rules, the method of the present invention requires adding corresponding optimization rules to the Flink optimizer. For the aggregation operator Aggregate, it is implemented to generate rules for opening or closing two-stage execution plans for Aggregate based on the batch operation of the aggregation operator. For the connection operator Join, in the case of Joining multiple streams, it is implemented to adjust the order of the streams according to the Join conditions to generate rules for a variety of different execution plans. Such as Figure 1 ,Before the actual execution process of streaming SQL, statistical ,information of the data source stream will be collected, including the element array, ,maximum value, minimum value, cardinality and average size.

[0105] S2. Set the formula for Flink to calculate the final cost:

[0106] cost=Normalize(CPU,I / O,Memory,Net)

[0107] The cost of the aggregation operator Aggregate in Flink is defined as:

[0108] CPU=T(I)×(R function ×N f +R state ×N k )+T(O)×R serialize_deserialize

[0109]

[0110] Memory=0

[0111] Net=T sz (O)×T(O)

[0112] The cost of Local Aggregate in Flink is defined as:

[0113] CPU=T(I)×(R function ×N f +R hash ×N k )

[0114] I / O=0

[0115]

[0116] Net=T sz(O)×T(O)

[0117] The cost of Global Aggregate in Flink is defined as:

[0118] CPU=T(I)×(R function ×N f +R state ×N k )+T(O)×R serialize_deserialize

[0119]

[0120] Memory=0

[0121] Net=T sz (O)×T(O)

[0122] The cost of Join in Flink is defined as:

[0123] CPU=(T(left)+T(right))×(R state +R serialize_deserialize )

[0124] I / O=T(left)×T sz (left)+T(right)×T sz (right)

[0125] Memory=0

[0126] Net=T sz (O)×T(O)

[0127] S3, such as Figure 1 As shown in the figure, the optimizer combines statistical information and cost models to compare the costs of multiple generated execution plans and selects the execution plan with the lowest computational cost as the final execution plan for the streaming SQL.

[0128] After deployment, performance testing was conducted using the TPC-H dataset and query statements conforming to the cost model of the present invention. The evaluation metric was the execution time for the same amount of data, with the same computing resources. The evaluation showed that the present invention was able to execute the same amount of data in a shorter time than a method not using the present invention, with the same resources, and required fewer CPU cores, making it worthy of promotion.

[0129] The above embodiments are preferred implementation modes of the present invention, but the implementation modes of the present invention are not limited to the above embodiments. Any other changes, modifications, substitutions, combinations, and simplifications that do not deviate from the spirit and principles of the present invention should be considered as equivalent replacement methods and are included in the scope of protection of the present invention.

Claims

1. The optimizer-based streaming SQL execution plan optimization method is characterized by: The following steps are involved: S1. For data source streams with historical input, collect the required statistical information and implement cost-based rules to enable the optimizer to generate multiple execution plans. The statistics collected include the number of tuples, the cardinality of each column, the maximum value, the minimum value, and the size of the characters occupied; In stream computing, the Aggregate operator can generate a two-stage execution plan based on batch operations. Enabling two-stage aggregation divides the Aggregate into local aggregation and global aggregation. Local Aggregate pre-aggregates data to be sent downstream before sending it to the Global Aggregate. For the Join operator, when joining multiple streams, different join orders are inferred based on the join conditions, thereby generating various execution plan rules. S2. Build a cost model that conforms to streaming computing. Using the statistical information collected in step S1, calculate the costs of various execution plans generated by the optimizer based on cost rules. Then, based on the calculated costs, select the final execution plan with the lowest cost. The cost model includes cost calculations for Aggregate, Local Aggregate, Global Aggregate, and Join operators; S3. Get the final execution plan and execute the final streaming SQL.

2. The optimizer-based streaming SQL execution plan optimization method according to claim 1, characterized in that: In step S2, the cost model comprehensively considers the four resources of CPU, I / O, memory and network transmission to calculate the cost overhead, and the final calculated cost is: cost=Normalize(CPU,I / O,Memory,Net) In the formula, cost represents the final computing cost, Normalize represents the normalization of the four resource costs, CPU represents the required CPU resources, I / O represents the required I / O resources, Memory represents the required memory resources, and Net represents the required network transmission resources.

3. The optimizer-based streaming SQL execution plan optimization method according to claim 2, characterized in that: In step S2, the cost of the Aggregate is defined as: CPU=T(I)×(R function ×N f +R state ×N k )+T(O)×R serialize_deserialize Memory=0 Net=T sz (O)×T(O) In the formula, T(I) represents the number of tuples of the input source, T(O) represents the number of tuples of the output, and R function Represents the ratio of CPU required to execute a calculation function, R state Represents the ratio of CPU required to execute the detection state, R serialize_deserialize Represents the ratio of CPU required to perform serialization and deserialization, N f Represents the number of aggregate functions in the query statement, N k Represents the number of aggregated key values, T sz (O) represents the average size of the output tuple; The CPU overhead required for Aggregate includes N operations on the input data. k Calculation of aggregate functions, and N calculations of data in the state k The overhead of searching for each key, performing the serialization and deserialization required to complete the aggregation process, and storing the state; The I / O overhead required by Aggregate can be divided into two cases: First, in N k When the aggregation key is 0, that is, the aggregation key is empty, it means that there is no need to group by key, and the input data is finally aggregated into one piece of data; the second type is in N k If it is not 0, the final result of the input data will be aggregated into key-grouped data; The cost model considers storing the state to disk, so the memory overhead is negligible; The network transmission overhead required by Aggregate includes the network overhead of transmitting the operator calculation results to the downstream through the network; Define the cost of LocalAggregate as: CPU=T(I)×(R function ×N f +R hash ×N k ) I / O=0 Net=T sz (O)×T(O) Where R hash Represents the ratio of CPU required to perform hash probing; Since Local Aggregate does not involve state management, it does not involve serialization and deserialization overhead. Therefore, the CPU overhead required by Local Aggregate includes N-order processing of input data. f Calculation of aggregate functions, the stored data is N k The cost of searching for each key; Since Local Aggregate does not involve state management, there is no need to consider the overhead of reading and writing state to disk, so the I / O overhead is negligible; The memory overhead required by Local Aggregate can be divided into two cases: First, in N k When the aggregation key is 0, that is, the aggregation key is empty, it means that there is no need to group by key, and the input data is finally aggregated into one piece of data; the second type is in N k If it is not 0, the final result of the input data will be aggregated into key-grouped data; The network transmission overhead required by Local Aggregate includes the network overhead of transmitting the operator calculation results to the downstream through the network; The cost of Global Aggregate is defined as: CPU=T(I)×(R function ×N f +R state ×N k )+T(O)×R serialize_deserialize Memory=0 Net=T sz (O)×T(O) The formulas for Aggregate and Global Aggregate are the same. However, when the degree of aggregation is high or there is data skew, Global Aggregate will need to process less data after Local Aggregate than Aggregate without the two-stage process. The cost of Join is defined as: CPU=(T(left)+T(right))×(R state +R serialize_deserialize ) I / O=T(left)×T sz (left)+T(right)×T sz (right) Memory=0 Net=T sz (O)×T(O) Where T(left) represents the number of tuples in the left stream, and T(right) represents the number of tuples in the right stream; The CPU overhead required for Join includes the serialization and deserialization overhead required to store the input data state of the left and right streams, as well as the search overhead of the left stream to the right stream and the right stream to the left stream state; The I / O overhead required for Join includes the I / O overhead required to store the input data of the left and right streams into the state; The cost model considers storing the state to disk, so the memory overhead is negligible; The network transmission overhead required for Join includes the network overhead of transmitting the operator calculation results to the downstream through the network.

Images