SLA-driven resource re-allocation for SQL-like queries in the cloud

Abstract

Cloud computing has become a widely used environment for database querying. In this context, the goal of a query optimizer is to satisfy the needs of tenants and maximize the provider’s benefit. Resource allocation is an important step toward achieving this goal. Allocation methods are based on analytical formulas and statistics collected from a catalog to estimate the cost of various possible allocations and then choose the best one. However, the allocation initially chosen is not necessarily the optimal one because of the approximate nature of the analytical formulas and the fact that the catalog may not be up to date. To solve this problem, existing work was proposed to collect statistics during the execution of the query and then trigger a re-allocation if suboptimality is detected. However, these proposals consider that queries have the same level of priority. Unlike the existing work, we propose in this paper a method of statistics collector placement and resource re-allocation by taking into account that the cloud is a multi-tenant environment and queries have different services-level agreements. In the experimental section, we show that our method provides a better benefit for the provider compared to state-of-the-art methods.

Appendix A: Computation of \(T_{op}\)

In this appendix, we present the computation detail of $T_{op}(s)$ for the stages of the query of Figure 1 . For stages that contain joins or aggregations, we consider the case where a one-pass or two-pass algorithm is applied. The M1$\rightarrow$M2 and M2$\rightarrow$M3 communications are broadcast type, while the M3$\rightarrow$R1 and R1$\rightarrow$R2 communications are partitioning type.

The stage M1: (1) read $R_{M1}$ from DFS, (2) apply the filter , (3)apply the projection.

$$\begin{aligned}&T_{op}(M1)=(\nonumber \\&(||R_{M1}||/pd_{M1})/db_{dfs}+{ \textit{// (1)}}\nonumber \\&(|R_{M1}|/pd_{M1})*t_{filter}+{ \textit{// (2)}}\nonumber \\&\sigma (|R_{M1}|/pd_{M1})*t_{project}){ \textit{// (3)}} \end{aligned}$$

(14)

The stage M2 (one-pass join): (1) read $R_{M2}$ from local disk, (2) execute debuild phase, (3) read $S_{M2}$ from DFS, (4) apply the filter, (5) execute deprobe phase.

$$\begin{aligned}&T_{op}(M2)=(\nonumber \\&||R_{M2}||/db_l+{ \textit{// (1)}}\nonumber \\&|R_{M2}|*t_{hash}+{ \textit{// (2)}}\nonumber \\&(||S_{M2}||/pd_{M2})/db_{dfs}+{ \textit{// (3)}}\nonumber \\&(|S_{M2}|/pd_{M2})*t_{filter}+{ \textit{// (4)}}\nonumber \\&(\sigma (|S_{M2}|)/pd_{M2})*(t_{hash}+t_{search})+\nonumber \\&(\psi (|R_{M2}|*\sigma (|S_{M2}|))/pd_{M2})*t_{join}) { \textit{// (5)}} \end{aligned}$$

(15)

The stage M2 (two-pass join): (1) read $R_{M2}$ from local disk, (2) partition $R_{M2}$ for the two-pass join, (3) write $R_{M2}$ on local disk, (4) read $S_{M2}$ from DFS, (5) apply the filter, (6) partition $S_{M2}$ for the two-pass join, (7) write $S_{M2}$ on local disk, (8) read $R_{M2}$ from local disk, (9) execute debuild phase, (10) read $S_{M2}$ from local disk, (11) execute deprobe phase.

$$\begin{aligned}&T_{op}(M2)=(\nonumber \\&||R_{M2}||/db_{l}+{ \textit{// (1)}}\nonumber \\&|R_{M2}|*t_{hash}+{ \textit{// (2)}}\nonumber \\&((1-f)*||R_{M2}||)/db_l+{ \textit{// (3)}}\nonumber \\&(||S_{M2}||/pd_{M2})/db_{dfs}+{ \textit{// (4)}}\nonumber \\&(|S_{M2}|/pd_{M2})*t_{filter}+{ \textit{// (5)}}\nonumber \\&(\sigma (|S_{M2}|)/pd_{M2})*t_{hash}+{ \textit{// (6)}}\nonumber \\&(((1-f)*\sigma (||S_{M2}||))/pd_{M2})/db_l+{ \textit{// (7)}}\nonumber \\&((1-f)*||R_{M2}||)/db_l+{ \textit{// (8)}}\nonumber \\&|R_{M2}|*t_{hash}+{ \textit{// (9)}}\nonumber \\&(((1-f)*\sigma (||S_{M2}||))/pd_{M2})/db_{l}+{ \textit{// (10)}}\nonumber \\&(\sigma (|S_{M2}|)/pd_{M2})*(t_{hash}+t_{search})+\nonumber \\&(\psi (|R_{M2}|*\sigma (|S_{M2}|))/pd_{M2})*t_{join}) { \textit{// (11)}} \end{aligned}$$

(16)

The stage M3 (one-pass join): (1) read $R_{M3}$ from local disk, (2) execute debuild phase, (3) read $S_{M3}$ from DFS, (4) apply the filter, (5) apply the projection, (6) execute deprobe phase, (7) partition the data for the aggregation, (8) write the data on local disk, (9) read the data from local disk, (10) execute the aggregation operator.

$$\begin{aligned}&T_{op}(M3)=(\nonumber \\&||R_{M3}||/db_l+{ \textit{// (1)}}\nonumber \\&|R_{M3}|*t_{hash}+{ \textit{// (2)}}\nonumber \\&(||S_{M3}||/pd_{M3})/db_{dfs}+{ \textit{// (3)}}\nonumber \\&(|S_{M3}|/pd_{M3})*t_{filter}+{ \textit{// (4)}}\nonumber \\&(\sigma (|S_{M3}|)/pd_{M3})*t_{project}+{ \textit{// (5)}}\nonumber \\&(\sigma (|S_{M3}|)/pd_{M3})*(t_{hash}+t_{search})+\nonumber \\&(\psi (|R_{M3}|*\sigma (|S_{M3}|))/pd_{M3})*t_{join}+ { \textit{// (6)}}\nonumber \\&(\psi (|R_{M3}|*\sigma (|S_{M3}|))/pd_{M3})*t_{hash}+{ \textit{// (7)}}\nonumber \\&\psi (||R_{M3}||*\pi (\sigma (||S_{M3}||))/pd_{M3})/db_l+{ \textit{// (8)}}\nonumber \\&\psi (||R_{M3}||*\pi (\sigma (||S_{M3}||))/pd_{M3})/db_l+{ \textit{// (9)}}\nonumber \\&(\psi (|R_{M3}|*\sigma (|S_{M3}|))/pd_{M3})*(t_{hash}+t_{search}+t_{agg}))\nonumber \\&{ \textit{// (10)}} \end{aligned}$$

(17)

The stage M3 (two-pass join): (1) read $R_{M3}$ from local disk, (2) partition $R_{M3}$ for the two-pass join, (3) write $R_{M3}$ on local disk, (4) read $S_{M3}$ from DFS, (5) apply the filter, (6) apply the projection, (7) partition $S_{M3}$ for the two-pass join, (8) write $S_{M3}$ on local disk, (9) read $R_{M3}$ from local disk, (10) execute the build phase, (11) read $S_{M3}$ from DFS, (12) execute de probe phase, (13) partition the data for the aggregation, (14) write the data on local disk, (15) read the data from local disk, (16) execute de aggregation operator.

$$\begin{aligned}&T_{op}(M3)=(\nonumber \\&||R_{M3}||/db_{l}+{ \textit{// (1)}}\nonumber \\&|R_{M3}|*t_{hash}+{ \textit{// (2)}}\nonumber \\&((1-f)*||R_{M3}||)/db_l+{ \textit{// (3)}}\nonumber \\&(||S_{M3}||/pd_{M3})/db_{dfs}+{ \textit{// (4)}}\nonumber \\&(|S_{M3}|/pd_{M3})*t_{filter}+{ \textit{// (5)}}\nonumber \\&(\sigma (|S_{M3}|)*t_{project}+{ \textit{// (6)}}\nonumber \\&(\sigma (|S_{M3}|)/pd_{M3})*t_{hash}+{ \textit{// (7)}}\nonumber \\&(((1-f)*\pi (\sigma (||S_{M3}||)))/pd_{M3})/db_l+{ \textit{// (8)}}\nonumber \\ \end{aligned}$$

(18)

$$\begin{aligned}&((1-f)*||R_{M3}||)/db_l+{ \textit{// (9)}}\nonumber \\&|R_{M3}|*t_{hash}+{ \textit{// (10)}}\nonumber \\&(((1-f)*\pi (\sigma (||S_{M3}||)))/pd_{M3})/db_l+{ \textit{// (11)}}\nonumber \\&(\sigma (|S_{M3}|)/pd_{M3})*(t_{hash}+t_{search})+\nonumber \\&((\psi (|R_{M3}|*\sigma (|S_{M3}|))/pd_{M3})*t_{join})+ { \textit{// (12)}}\nonumber \\&((\psi (|R_{M3}|*\sigma (|S_{M3}|))/pd_{M3})*t_{hash}+{ \textit{// (13)}}\nonumber \\&((\psi (||R_{M3}||*\pi (\sigma (||S_{M3}||)))/pd_{M3})/db_l+{ \textit{// (14)}}\nonumber \\&(\psi (||R_{M3}||*\pi (\sigma (||S_{M3}||)))/pd_{M3})/db_l+{ \textit{// (15)}}\nonumber \\&(|R_{M3}|/pd_{M3})*(t_{hash}+t_{search}+t_{agg})){ \textit{// (16)}} \end{aligned}$$

(19)

The stage R1 (one-pass aggregation): (1) read $R_{R1}$ from local disk, (2) execute deaggregation operator.

$$\begin{aligned}&T_{op}(R1)=(\nonumber \\&(||R_{R1}||/pd_{R1})/db_{l}+{ \textit{// (1)}}\nonumber \\&(|R_{R1}|/pd_{R1})*(t_{hash}+t_{search}+t_{agg})){ \textit{// (2)}} \end{aligned}$$

(20)

The stage R1 (two-pass aggregation): (1) read $R_{R1}$ from local disk, (2) partition the data for the aggregation, (3) write the data on local disk, (4) read the data from local disk, (5) execute de aggregation operator.

$$\begin{aligned}&T_{op}(R1)=(\nonumber \\&(||R_{R1}||/pd_{R1})/db_{l}+{ \textit{// (1)}}\nonumber \\&(||R_{R1}||/pd_{R1})*t_{hash}+{ \textit{// (2)}}\nonumber \\&(((1-f)*||R_{R1}||)/pd_{R1})/db_l+{ \textit{// (3)}}\nonumber \\&(((1-f)*||R_{R1}||)/pd_{R1})/db_l+{ \textit{// (4)}}\nonumber \\&(|R_{R1}|/pd_{R1})*(t_{hash}+t_{search}+t_{agg})\nonumber \\&{ \textit{// (5)}} \end{aligned}$$

(21)

The stage R2: (1) read the data from local disk, (2) write the data on DFS.

$$\begin{aligned}&T_{op}(R2)=(\nonumber \\&limit(R_{R2})/db_l+{ \textit{// (1)}}\nonumber \\&limit(R_{R2})/db_{dfs}){ \textit{// (2)}} \end{aligned}$$

(22)