Method and device for estimating cross-system query cost of heterogeneous database

Abstract

The invention discloses a heterogeneous database cross-system query cost estimation method and device, and relates to the technical field of databases. Comprising the following steps: step 1, when a source database is in butt joint with a heterogeneous database system, selecting a query cost evaluation index: taking a calculation cost based on a calculation amount as the query cost evaluation index of the source database, and respectively taking rates in the processes of data introduction, calculation triggering and result transmission as the query cost evaluation index of the heterogeneous system; 2, establishing a cost estimation model according to the query cost evaluation indexes: establishing the cost estimation model by using the query cost evaluation indexes on the basis of database query and on the premise of executing aggregation and sorting calculation by the heterogeneous system; the heterogeneous query execution plan of the source database is sent to a heterogeneous system to be executed, a query cost evaluation index evaluation value and calculation timing are obtained to serve as feedback values, the feedback values are used for training the cost estimation model, and the cost estimation model is used for calculating the cost of the heterogeneous query execution plan.

Description

Technical Field

[0001] The invention discloses a method and a device, and relates to the technical field of databases, in particular to a method and a device for estimating the cost of cross-system queries on heterogeneous databases. Background Art

[0002] With the diversified development of computing technology, the emergence of heterogeneous computing, and the continuous penetration of cloud computing technology, the database field is also absorbing and adopting various technologies. Among them, heterogeneous databases may involve scenarios where database software calls heterogeneous hardware computing capabilities or calls basic components deployed on the cloud. When database technology involves heterogeneous computing scenarios, query costs need to be estimated. The query cost estimation of existing relational databases is often a single indicator, that is, computing time or computing volume. However, when heterogeneous modules are involved, the computing query costs of heterogeneous computing units, such as heterogeneous hardware or cloud resources, are often multi-dimensional. The query cost estimation of heterogeneous systems is often different from that of relational databases. There will be a situation where the query cost planned by the database is inconsistent with the actual query cost, and the actual calculated query cost is inconsistent with the estimated query cost, which affects the database usage experience. In severe cases, the query cost estimation of the database system may cause certain indicators of the heterogeneous computing system to exceed the tolerance value. Summary of the invention

[0003] In view of the problems of the prior art, the present invention provides a method and device for estimating the cross-system query cost of a heterogeneous database, which can bridge the differences in the query cost estimation systems between the database and heterogeneous resources, thereby achieving global efficiency in the database calling heterogeneous resources.

[0004] The specific scheme proposed by the present invention is:

[0005] The present invention provides a method for estimating the cost of cross-system queries of heterogeneous databases, comprising:

[0006] Step 1: When the source database is connected to a heterogeneous database system, select the query cost evaluation index: use the computational cost based on the computational amount as the query cost evaluation index of the source database, and use the rates of data input, triggering computation, and result output as the query cost evaluation index of the heterogeneous system;

[0007] Step 2: Establish a cost estimation model based on the query cost evaluation index: Based on the database query, the cost estimation model is established using the query cost evaluation index based on the premise that the heterogeneous system performs aggregation and sorting calculations;

[0008] Step 3: Training the cost estimation model: Send the heterogeneous query execution plan of the source database to the heterogeneous system for execution, obtain the query cost evaluation index evaluation value and calculation timing as feedback values, and use the feedback values ​​to train the cost estimation model.

[0009] Step 4: Apply the cost estimation model to estimate the cost of heterogeneous query execution plans.

[0010] Furthermore, in the heterogeneous database cross-system query cost estimation method, the heterogeneous library system to which the source database is connected in step 1 has a GPU device, and an evaluation indicator GOOM for judging memory overrun is added to the heterogeneous library system. Setting GOOM to 1 indicates that a memory overrun occurs, and setting GOOM to 0 indicates that there is no overrun.

[0011] Furthermore, in the heterogeneous database cross-system query cost estimation method, the cost estimation model established in step 2 according to the query cost evaluation index is:

[0012] C<==(push_down_flag*)((GPUI*agg_batch_size)+GPUC1+GPUO*agg_result_size)*agg_batch_num*a+(push_down_flag*)((GPUI+GPUO)*sort_batch_size)*b+GPUC2)*s ort_batch_num+GOOM*(k1+k2)+(1-push_down_flag)*CPU_AGG*agg_batch_size*agg_batch_num*c+(1-push_down_flag)*CPU_SORT*sort_batch_size*sort_batch_num*d,

[0013] a, b, c, d, k1, and k2 in the cost estimation model are all constant coefficients. C represents the computational cost. GPUC1 represents the computational rate of the GPU device for aggregate computation. GPUC2 represents the computational rate of the sorting computation. CPU_AGG and CPU_SORT represent the computational rates when the CPU is used for computation. agg_batch_size represents the relevant parameters set by the database for heterogeneous query execution plans based on data conditions. agg_result_size represents the size of the aggregate result set. agg_batch_num represents the amount of data per batch for aggregate computation. sort_batch_size represents the amount of data per batch for sorting computation. push_down_flag represents whether a computation is pushed down to a heterogeneous system.

[0014] After sorting out the cost estimation model, we get:

[0015] {push_down_flag,agg_batch_size,sort_batch_size,w1,w2,...wn}==>C

[0016] w1, w2... wn represent the weights of operators with different cash costs.

[0017] Furthermore, in step 3 of the heterogeneous database cross-system query cost estimation method described above, a supervised learning mechanism is used to train the cost estimation model. The optimization cost p*C0 of heterogeneous computing is used as a penalty function, indicating that 0 < p <= 1 times of C0 is used as the expected optimization cost. If the result of using heterogeneous computing is not better than that of using only the database computing, it means that heterogeneous computing is ineffective; when the cost obtained by heterogeneous computing is lower than C0, it means that the cost estimation model is effective.

[0018] Taking push_down_flag, agg_batch_size, sort_batch_size, w1, w2,... as the model input and C - p*C0 as the output for supervised training.

[0019] The training starts with p = 1 initially. If all test sets pass after training, the value of p is decreased and then tested again until a certain test set fails the test. If p < 1 at this time, the cost estimation model is mature and effective.

[0020] Furthermore, in step 4 of the heterogeneous database cross-system query cost estimation method described above, the actual execution cost of the heterogeneous query execution plan is obtained and fed back to the source database to adjust the parameters of the cost estimation model.

[0021] The present invention also provides a heterogeneous database cross-system query cost estimation device, including an index management module, a model management module, and an estimation module.

[0022] When the index management module manages the docking of the source database with the heterogeneous database system, it selects query cost evaluation indicators: taking the computational cost based on the amount of computation as the query cost evaluation indicator of the source database, and taking the rates in the processes of data input, triggering computation, and result output as the query cost evaluation indicators of the heterogeneous system respectively.

[0023] The model management module establishes a cost estimation model according to the query cost evaluation indicators: based on database queries, and according to the preconditions of the heterogeneous system for performing aggregation and sorting computations, uses the query cost evaluation indicators to establish a cost estimation model.

[0024] The model management module trains the cost estimation model: sends the heterogeneous query execution plan of the source database to the heterogeneous system for execution, obtains the evaluation values of the query cost evaluation indicators and the computation timing as feedback values, and uses the feedback values to train the cost estimation model.

[0025] The estimation module applies the cost estimation model to calculate the cost of the heterogeneous query execution plan.

[0026] Furthermore, the indicator management module of the heterogeneous database cross-system query cost estimation device manages the heterogeneous library system connected to the source database with a GPU device, and adds an evaluation indicator GOOM for judging memory overlimit to the heterogeneous library system. Setting GOOM to 1 indicates that a memory overlimit occurs, and setting GOOM to 0 indicates that there is no overlimit.

[0027] Furthermore, the model management module of the heterogeneous database cross-system query cost estimation device establishes a cost estimation model according to the query cost evaluation index as follows:

[0028] C<==(push_down_flag*)((GPUI*agg_batch_size)+GPUC1+GPUO*agg_result_size)*agg_batch_num*a+(push_down_flag*)((GPUI+GPUO)*sort_batch_size)*b+GPUC2)*s ort_batch_num+GOOM*(k1+k2)+(1-push_down_flag)*CPU_AGG*agg_batch_size*agg_batch_num*c+(1-push_down_flag)*CPU_SORT*sort_batch_size*sort_batch_num*d,

[0029] a, b, c, d, k1, and k2 in the cost estimation model are all constant coefficients. C represents the computational cost. GPUC1 represents the computational rate of the GPU device for aggregate computation. GPUC2 represents the computational rate of the sorting computation. CPU_AGG and CPU_SORT represent the computational rates when the CPU is used for computation. agg_batch_size represents the relevant parameters set by the database for heterogeneous query execution plans based on data conditions. agg_result_size represents the size of the aggregate result set. agg_batch_num represents the amount of data per batch for aggregate computation. sort_batch_size represents the amount of data per batch for sorting computation. push_down_flag represents whether a computation is pushed down to a heterogeneous system.

[0030] After sorting out the cost estimation model, we get:

[0031] {push_down_flag,agg_batch_size,sort_batch_size,w1,w2,...wn}==>C

[0032] w1,w2...wn represent the weights of different operators that generate cash costs.

[0033] Furthermore, the model management module of the heterogeneous database cross-system query cost estimation device uses a supervised learning mechanism to train the cost estimation model. The optimization cost p*C0 of heterogeneous computing is used as the penalty function, indicating that 0<p<=1 times of C0 is used as the expected optimization cost. If the result of using heterogeneous computing is not better than that of fully using the database computing, it means that heterogeneous computing is ineffective; when the cost obtained by heterogeneous computing is lower than C0, it means that the cost estimation model is effective.

[0034] Taking push_down_flag, agg_batch_size, sort_batch_size, w1, w2,... as the model input and C-p*C0 as the output for supervised training.

[0035] The training starts with p = 1 initially. If all test sets pass after training, the value of p is decreased and then tested again until a certain test set fails the test. If p<1 at this time, the cost estimation model is mature and effective.

[0036] Furthermore, the estimation module of the heterogeneous database cross-system query cost estimation device obtains the actual execution cost of the heterogeneous query execution plan and feeds the actual execution cost back to the source database to adjust the parameters of the cost estimation model.

[0037] The beneficial effects of the present invention are as follows:

[0038] The present invention provides a method for collaborative planning of query cost estimation between a database and heterogeneous resources, which can bridge the differences in the query cost estimation systems between the database and heterogeneous resources, thereby achieving global efficiency in the database's invocation of heterogeneous resources. It can also make the query cost planned by the database match the actual query cost, improve the database usage experience, and prevent situations where the query cost estimation of the database system exceeds the tolerance values of certain indicators of the heterogeneous computing system. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] Figure 1 It is a schematic diagram of the method flow of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0040] The following further describes the present invention in conjunction with the drawings and specific embodiments, so that those skilled in the art can better understand the present invention and implement it, but the embodiments given are not intended to limit the present invention.

[0041] Embodiment 1

[0042] The present invention provides a heterogeneous database cross-system query cost estimation method, including:

[0043] Step 1: When the source database is connected to a heterogeneous database system, select the query cost evaluation indicator: use the computational cost based on the computational amount as the query cost evaluation indicator of the source database, and use the rates of data input, triggering calculation, and result output as the query cost evaluation indicators of the heterogeneous system.

[0044] In step 1, the heterogeneous database system connected to the source database has a GPU device, and an evaluation indicator GOOM for judging memory overrun is added to the heterogeneous database system. Setting GOOM to 1 indicates that memory overrun occurs, and setting GOOM to 0 indicates that there is no overrun.

[0045] Step 2: Establish a cost estimation model based on the query cost evaluation index: Based on the database query, according to the premise that the heterogeneous system performs aggregation and sorting calculations, a cost estimation model is established using the query cost evaluation index.

[0046] The cost estimation model can be further established based on the query cost evaluation index:

[0047] C<==(push_down_flag*)((GPUI*agg_batch_size)+GPUC1+GPUO*agg_result_size)*agg_batch_num*a+(push_down_flag*)((GPUI+GPUO)*sort_batch_size)*b+GPUC2)*s ort_batch_num+GOOM*(k1+k2)+(1-push_down_flag)*CPU_AGG*agg_batch_size*agg_batch_num*c+(1-push_down_flag)*CPU_SORT*sort_batch_size*sort_batch_num*d,

[0048] a, b, c, d, k1, and k2 in the cost estimation model are all constant coefficients. C represents the computational cost. GPUC1 represents the computational rate of the GPU device for aggregate computation. GPUC2 represents the computational rate of the sorting computation. CPU_AGG and CPU_SORT represent the computational rates when the CPU is used for computation. agg_batch_size represents the relevant parameters set by the database for heterogeneous query execution plans based on data conditions. agg_result_size represents the size of the aggregate result set. agg_batch_num represents the amount of data per batch for aggregate computation. sort_batch_size represents the amount of data per batch for sorting computation. push_down_flag represents whether a computation is pushed down to a heterogeneous system. The <== symbol represents "depends on".

[0049] Organize the cost estimation model to obtain:

[0050] {push_down_flag, agg_batch_size, sort_batch_size, w1, w2,... wn} ==> C

[0051] w1, w2... wn represent the weights of different operators that generate cash costs.

[0052] Step 3: Train the cost estimation model: Send the heterogeneous query execution plan of the source database to the heterogeneous system for execution, obtain the evaluation value of the query cost evaluation index and the calculation timing as feedback values, and use the feedback values to train the cost estimation model.

[0053] Among them, a supervised learning mechanism can be further adopted to train the cost estimation model. The optimized cost p*C0 of heterogeneous computing is used as a penalty function, indicating that a certain 0 < p <= 1 times of C0 is used as the expected optimized cost. If the result of using heterogeneous computing is not better than that of using only the database for computing, it means that heterogeneous computing is ineffective; while when the heterogeneous computing obtains a cost lower than C0, it means that the cost estimation model is effective.

[0054] Use push_down_flag, agg_batch_size, sort_batch_size, w1, w2,... as the model input, and C - p*C0 as the output for supervised training.

[0055] The training starts with p = 1 initially. If all test sets pass after training, then reduce the p value and conduct tests again until a certain test set fails the test. If p < 1 at this time, the cost estimation model is mature and effective at this time.

[0056] Step 4: Apply the cost estimation model to calculate the cost of the heterogeneous query execution plan. The actual execution cost of the heterogeneous query execution plan can be further obtained, and the actual execution cost is fed back to the source database to adjust the parameters of the cost estimation model.

[0057] Embodiment 2

[0058] The present invention also provides a heterogeneous database cross-system query cost estimation device, including an index management module, a model management module, and an estimation module.

[0059] When the index management module manages the docking of the source database with the heterogeneous database system, it selects query cost evaluation indicators: uses the calculation cost based on the amount of calculation as the query cost evaluation indicator of the source database, and uses the rates in the processes of data input, triggering calculation, and result output as the query cost evaluation indicators of the heterogeneous system respectively.

[0060] The model management module establishes a cost estimation model based on the query cost evaluation index: Based on the database query, the cost estimation model is established using the query cost evaluation index based on the premise that the heterogeneous system performs aggregation and sorting calculations;

[0061] The model management module trains the cost estimation model: the heterogeneous query execution plan of the source database is sent to the heterogeneous system for execution, the query cost evaluation index evaluation value and calculation timing are obtained as feedback values, and the cost estimation model is trained using the feedback values.

[0062] The estimation module applies the cost estimation model to infer the cost of heterogeneous query execution plans.

[0063] As the information interaction and execution process between the modules in the above-mentioned device are based on the same concept as the embodiment of the method of the present invention, the specific contents can be found in the description of the embodiment of the method of the present invention and will not be repeated here.

[0064] Similarly, the device of the present invention can perform collaborative planning of query cost estimation between database and heterogeneous resources, and can bridge the difference between the query cost estimation system of database and heterogeneous resources, so as to achieve global efficiency of database calling heterogeneous resources. It can also make the query cost planned by the database consistent with the actual query cost, improve the database usage experience, and prevent the occurrence of situations such as the query cost estimation of the database system exceeding the tolerance value of certain indicators of the heterogeneous computing system.

[0065] It should be noted that not all steps and modules in the above-mentioned processes and device structures are necessary, and some steps or modules can be ignored according to actual needs. The execution order of each step is not fixed and can be adjusted as needed. The system structure described in the above-mentioned embodiments can be a physical structure or a logical structure, that is, some modules may be implemented by the same physical entity, or some modules may be implemented by multiple physical entities, or some components in multiple independent devices may be implemented together.

[0066] The above-described embodiments are only preferred embodiments for fully illustrating the present invention, and the protection scope of the present invention is not limited thereto. Equivalent substitutions or changes made by those skilled in the art based on the present invention are within the protection scope of the present invention. The protection scope of the present invention shall be subject to the claims.

Claims

1. A method for estimating the cost of cross-system queries in heterogeneous databases, characterized by: include: Step 1: When the source database is connected to a heterogeneous database system, select the query cost evaluation index: use the computational cost based on the computational amount as the query cost evaluation index of the source database, and use the rates of data input, triggering computation, and result output as the query cost evaluation index of the heterogeneous system; Step 2: Establish a cost estimation model based on the query cost evaluation index: Based on the database query, the cost estimation model is established using the query cost evaluation index based on the premise that the heterogeneous system performs aggregation and sorting calculations; Step 3: Training the cost estimation model: Send the heterogeneous query execution plan of the source database to the heterogeneous system for execution, obtain the query cost evaluation index evaluation value and calculation timing as feedback values, and use the feedback values ​​to train the cost estimation model. Step 4: Apply the cost estimation model to estimate the cost of heterogeneous query execution plans.

2. The method for estimating the cost of cross-system query of heterogeneous databases according to claim 1 is characterized by: In step 1, the heterogeneous database system connected to the source database has a GPU device, and an evaluation indicator GOOM for judging memory overrun is added to the heterogeneous database system. Setting GOOM to 1 indicates that memory overrun occurs, and setting GOOM to 0 indicates that there is no overrun.

3. The method for estimating the cost of cross-system query of heterogeneous databases according to claim 2 is characterized by: In step 2, the cost estimation model is established based on the query cost evaluation index: C<==(push_down_flag*)((GPUI*agg_batch_size)+GPUC1+GPUO*agg_result_size)*agg_batch_num*a+(push_down_flag*)((GPUI+GPUO)*sort_batch_size)*b+GPUC2)*s ort_batch_num+GOOM*(k1+k2)+(1-push_down_flag)*CPU_AGG*agg_batch_size*agg_batch_num*c+(1-push_down_flag)*CPU_SORT*sort_batch_size*sort_batch_num*d, a, b, c, d, k1, and k2 in the cost estimation model are all constant coefficients. C represents the computational cost. GPUC1 represents the computational rate of the GPU device for aggregate computation. GPUC2 represents the computational rate of the sorting computation. CPU_AGG and CPU_SORT represent the computational rates when the CPU is used for computation. agg_batch_size represents the relevant parameters set by the database for heterogeneous query execution plans based on data conditions. agg_result_size represents the size of the aggregate result set. agg_batch_num represents the amount of data per batch for aggregate computation. sort_batch_size represents the amount of data per batch for sorting computation. push_down_flag represents whether a computation is pushed down to a heterogeneous system. After sorting out the cost estimation model, we get: {push_down_flag, agg_batch_size, sort_batch_size, w1, w2,... wn} ==> C w1, w2... wn represent the weights of different operators that generate cash costs.

4. A heterogeneous database cross-system query cost estimation method according to claim 3, characterized in that in step 3, a supervised learning mechanism is used to train the cost estimation model, and the optimized cost p * C0 of heterogeneous computing is used as a penalty function, indicating that a certain 0 < p <= 1 times of C0 is used as the expected optimization cost. If the result of using heterogeneous computing is not better than that of using only the database computing, it means that heterogeneous computing is ineffective; when the cost lower than C0 is obtained by heterogeneous computing, it means that the cost estimation model is effective. Using push_down_flag, agg_batch_size, sort_batch_size, w1, w2,... as the model input and C - p * C0 as the output for supervised training. The training starts with p = 1 initially. If all test sets pass after training, the value of p is decreased and then tested again until a certain test set fails the test. If p < 1 at this time, the cost estimation model is mature and effective.

5. A heterogeneous database cross-system query cost estimation method according to claim 1, characterized in that in step 4, the actual execution cost of the heterogeneous query execution plan is obtained and fed back to the source database to adjust the parameters of the cost estimation model.

6. A heterogeneous database cross-system query cost estimation device, characterized by It includes an index management module, a model management module, and an estimation module. The index management module manages the source database when docking with the heterogeneous database system, selects query cost evaluation indicators: uses the computational cost based on the amount of computation as the query cost evaluation indicator for the source database, and respectively uses the rates during data input, trigger calculation, and result output as the query cost evaluation indicators for the heterogeneous system. The model management module establishes a cost estimation model according to the query cost evaluation indicators: based on database queries, according to the premise of the heterogeneous system performing aggregation and sorting calculations, uses the query cost evaluation indicators to establish a cost estimation model. The model management module trains the cost estimation model: sends the heterogeneous query execution plan of the source database to the heterogeneous system for execution, obtains the evaluation value of the query cost evaluation indicator and the calculation timing as feedback values, and uses the feedback values to train the cost estimation model. The estimation module applies the cost estimation model to calculate the cost of the heterogeneous query execution plan.

7. The device for estimating the cost of cross-system query of heterogeneous databases according to claim 6 is characterized in that The index management module manages the heterogeneous database system docked with the source database and has a GPU device, and adds an evaluation indicator GOOM for judging memory overrun to the heterogeneous database system. Setting GOOM to 1 indicates a memory overrun situation, and GOOM to 0 indicates no overrun.

8. The device for estimating the cost of cross-system query of heterogeneous databases according to claim 7, characterized in that The model management module establishes the cost estimation model according to the query cost evaluation indicators as: C <= (push_down_flag*)((GPUI * agg_batch_size)+GPUC1+GPUO * agg_result_size)*agg_batch_num*a+(push_down_flag*)((GPUI+GPUO)*sort_batch_size)*b+GPUC2)*sort_batch_num+GOOM*(k1+k2)+(1 - push_down_flag)*CPU_AGG*agg_batch_size*agg_batch_num*c+(1 - push_down_flag)*CPU_SORT*sort_batch_size*sort_batch_num*d, a, b, c, d, k1, k2 in the cost estimation model are all constant coefficients. C represents the computational cost. GPUC1 represents the computational rate of the GPU device for aggregation calculation. GPUC2 represents the computational rate of sorting calculation. CPU_AGG and CPU_SORT represent the rates when using the CPU for calculation. agg_batch_size represents the relevant parameter set by the database according to the data situation for the heterogeneous query execution plan. agg_result_size represents the size of the aggregation result set. agg_batch_num represents the amount of data per batch for aggregation calculation. sort_batch_size represents the amount of data per batch for sorting calculation. push_down_flag represents whether a certain calculation is pushed down to the heterogeneous system for execution. After reorganizing the cost estimation model, we get: {push_down_flag, agg_batch_size, sort_batch_size, w1, w2,...wn} => C w1, w2...wn represent the weights of different operators that generate cash costs.

9. The device for estimating the cost of cross-system query of heterogeneous databases according to claim 8, characterized in that the model The management module trains the cost estimation model using a supervised learning mechanism. The optimized cost p * C0 of heterogeneous computing is used as the penalty function, indicating that p * C0 times (where 0 < p <= 1) of C0 is used as the expected optimized cost. If the result of using heterogeneous computing is not better than that of using only the database for calculation, it means that heterogeneous computing is ineffective; while when heterogeneous computing obtains a lower cost than C0, it indicates that the cost estimation model is effective. Using push_down_flag, agg_batch_size, sort_batch_size, w1, w2,... as the model input and C - p * C0 as the output for supervised training. The training starts with p = 1 initially. If all test sets pass after training, the value of p is decreased and then tested again until a certain test set fails the test. If p < 1 at this time, the cost estimation model is mature and effective.

10. The device for estimating the cost of cross-system query of heterogeneous databases according to claim 6, characterized in that The estimation module obtains the actual execution cost of the heterogeneous query execution plan and feeds the actual execution cost back to the source database to adjust the parameters of the cost estimation model.

Images