Transcript Slide 1

Jiexing Li#, Rimma Nehme*, Jeff Naughton#
#University of Wisconsin-Madison
*Microsoft Jim Gray Systems Lab
1
Progress indicator (PI)
 A PI provides feedback to users on how much of the
task has been completed or when the task will finish.
 It is useful for workload management, admission
control, skew handling, etc.
2
Main results
 Existing PIs generate inaccurate results, if a query has
phases with different performance characteristics.
 A new PI that provides more accurate predictions by
using
 optimizer’s cost estimates, and
 deeper analysis of query pipelines.
3
Execution plan
 Blocking operator: no outputs until the operator has consumed
at least one of its inputs (e.g., sort and hash match)
 Pipeline: a subtree of concurrently executing operators delimited
by blocking operators
Hash
Match
Filter
Pipeline: P2
Table
Scan [B]
Pipeline: P1
Table
Scan [A]
An execution plan
4
Basic ideas of previous PIs
 Pipelines are processed one after another.
 Each of them processes a certain number of tuples.
 Speed: how fast tuples can be processed.
Time
P1
P2
Cardinality N1
Cardinality N2
Estimated remaining time =
…
Pn
Cardinality Nn
𝑇𝑜𝑡𝑎𝑙 𝑛𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝑢𝑛𝑓𝑖𝑛𝑖𝑠ℎ𝑒𝑑 𝑡𝑢𝑝𝑙𝑒𝑠
𝑆𝑝𝑒𝑒𝑑
 Estimated cardinalities
 Current speed (future speed is unknown)
5
Limitations of previous PIs
 select count(distinct orderkey) from lineitem
Processing speed
1.80E+07
Compute
Scalar
1.60E+07
1.40E+07
P2
Hash Match
(orderkey)
Speed
1.20E+07
1.00E+07
8.00E+06
6.00E+06
P1
Table Scan
[lineitem]
Execution plan
4.00E+06
P1
2.00E+06
P2
0.00E+00
0
50
100
Time (sec)
Test results for TPC-H 10GB data
6
Limitations of Previous PIs (cont.)
 select count(distinct orderkey) from lineitem
1.40E+07
Speed
1.20E+07
1.00E+07
8.00E+06
6.00E+06
4.00E+06
P1
P2
0.00E+00
0
50
Time (sec)
100
Estimated remaining time (sec)
1.60E+07
2.00E+06
Progress estimation
120
Processing speed
1.80E+07
PreviousPI
100
PerfectPI
80
60
40
20
0
0
50
100
Time (sec)
 Before P2 starts, only the processing speed for P1 is known.
 Over estimates the execution time for P2 by using the current speed
of P1 for future pipeline P2.
7
GSLPI improvement
Estimated remaining time (sec)
 Different phases have
different processing speeds.
Progress estimation
120
PreviousPI
P1
GSLPI
100
PerfectPI
Cardinality N1
Speed S1
80
60
P2
Cardinality N2
Speed S2
40
 How does GSLPI estimate the
20
0
0
20
40
60
80
100
processing speeds for future
pipelines?
Time (sec)
8
GSLPI: a cost-based progress indicator
P1
P2
Cardinality N1
Cardinality N2
…
Pn
Cardinality Nn
Further division (uniform speed)
P1
P2
P’2
N1
N2
N’2
…
Pn
Nn
Cost: work to process a tuple
Cost C1
Cost C2
Cost C’2
Cost Cn
Speed: adjusts based on the cost
Speed S1
Speed S2
Speed S’2
Speed Sn
Estimates the remaining time
Estimated T1 Estimated T2 Estimated T’2 Estimated Tn
9
GSLPI: Cost
select count(distinct orderkey) from lineitem
Estimated
[CPU, IO]
Compute
Scalar
[0, 0]
Hash Match
(orderkey)
[275, 0]
Table Scan
[lineitem]
[66, 767]
P2
P1
Execution plan
 Redistributes the cost for
operators on the boundaries.
 P1 scans a tuple and inserts it
into the hash table.
 P2 fetches a tuple from the
hash table and counts.
 Splits the cost for Hash
Match operator.
 Identifies bottleneck cost.
 Due to concurrent execution.
10
GSLPI: Speed
 select count(distinct orderkey) from lineitem
Avg. cost per tuple
Compute
Scalar
1.40E-07
1.20E-07
Hash Match
(orderkey)
P1
Table Scan
[lineitem]
Cost unit
P2
1.00E-07
8.00E-08
6.00E-08
4.00E-08
2.00E-08
0.00E+00
P1
Execution plan
P2
Pipeline
 P2 should be able to process its tuples much faster than P1.
 Adjusts the speeds for future pipelines based on how difficult it is to
process a tuple.
11
Experimental evaluation
 Database: 10GB TPC-H.
 Setup: Intel Core 2 Duo CPU and 4GB RAM.
 Tested on 22 TPC-H queries.
 They usually contain 6 to 16 operators, which may be
divided into 3 to 9 different pipelines.
 Estimated the remaining time in every 10 seconds.
12
Example of progress estimation
Processing speed
Progress estimation
7.00E+06
Speed
6.00E+06
5.00E+06
P4
4.00E+06
P5
3.00E+06
2.00E+06
1.00E+06
0.00E+00
0
50
100
Time (sec)
P1, P2, P3
150
Estimated remaining time (sec)
8.00E+06
1400
MSRPI
1200
WiscPI
GSLPI
1000
PerfectPI
800
600
400
200
0
0
P6, P7
50
100
150
Time (sec)
Experiment results for TPC-H Q5
13
Error metric
 Similar to Q5, 20 out of 22 TPC-H queries process
tuples at different speeds during their executions.
 Estimated percentage of completion:
f i=
𝑇𝑖𝑚𝑒 𝑠𝑝𝑒𝑛𝑡
𝐸𝑠𝑡𝑖𝑚𝑎𝑡𝑒𝑑 𝑒𝑥𝑒𝑐𝑢𝑡𝑖𝑜𝑛 𝑡𝑖𝑚𝑒
 Error: the distance between f i and the actual
percentage of completion.
 For each query, we calculated the average error and the
maximum error.
14
GSLPI accuracy
Query
Avg. Err.
Max. Err.
Query
Avg. Err.
Max. Err.
Q1
Q2
Q3
Q4
Q5
Q6
Q7
Q8
Q9
Q10
Q11
0.50%
0.90%
4.00%
1.50%
2.40%
0.30%
3.60%
3.20%
1.30%
1.70%
1.10%
0.60%
1.90%
10.40%
8.50%
10.30%
0.70%
9.40%
8.20%
6.30%
7.70%
3.20%
Q12
Q13
Q14
Q15
Q16
Q17
Q18
Q19
Q20
Q21
Q22
10.50%
1.20%
1.00%
0.10%
1.60%
0.50%
0.30%
0.20%
16.80%
5.70%
1.10%
24.70%
3.50%
2.20%
0.50%
4.00%
0.90%
1.60%
0.50%
41.10%
16.00%
3.60%
Due to cardinality estimation error!
15
Conclusions
 Investigated the limitations with previous PIs.
 Developed a new progress indicator: GSLPI.
 Evaluated their performance.
 More accurate than previous PIs.
 Remaining progress estimation error for TPC-H
queries is mostly due to cardinality estimation error!
16
Thank you!
17