Workload predictor · tuner · organiser
Forecasting lab
The workload-driven half of our survey started from QueryBot 5000: a self-driving database should know what is coming before it tunes. Here QB5000's pipeline runs end to end on a synthetic three-week trace of the arena's queries — two weeks to learn from, one to test on — and its forecast then drives an index tuner, so you can see what predicting the workload is worth.
Turn statements into templates
QB5000's pre-processor strips the constants out of every statement, so queries that differ only in their parameters count as one template. Forecasting then works on twelve arrival-rate series instead of 60,639 individual statements.
| Template | Statement as executed → as templatised |
|---|---|
| Q3Part demand | SELECT COUNT(*) AS lines, SUM(l_quantity) AS units FROM lineitem WHERE l_partkey = 174SELECT COUNT(*) AS lines, SUM(l_quantity) AS units FROM lineitem WHERE l_partkey = ? |
| Q3Part demand | SELECT COUNT(*) AS lines, SUM(l_quantity) AS units FROM lineitem WHERE l_partkey = 697SELECT COUNT(*) AS lines, SUM(l_quantity) AS units FROM lineitem WHERE l_partkey = ? |
| Q2Shipping-window revenue | SELECT SUM(l_extendedprice * l_discount) AS revenue FROM lineitem WHERE l_shipdate >= '1994-01-03' AND l_shipdate < '1994-01-10' AND l_discount BETWEEN 0.05 AND 0.07 AND l_quantity < 24SELECT SUM(l_extendedprice * l_discount) AS revenue FROM lineitem WHERE l_shipdate >= ? AND l_shipdate < ? AND l_discount BETWEEN ? AND ? AND l_quantity < ? |
| Q2Shipping-window revenue | SELECT SUM(l_extendedprice * l_discount) AS revenue FROM lineitem WHERE l_shipdate >= '1996-04-24' AND l_shipdate < '1996-05-01' AND l_discount BETWEEN 0.07 AND 0.09 AND l_quantity < 24SELECT SUM(l_extendedprice * l_discount) AS revenue FROM lineitem WHERE l_shipdate >= ? AND l_shipdate < ? AND l_discount BETWEEN ? AND ? AND l_quantity < ? |
| Q5Customer order lines | SELECT o_orderkey, l_linenumber, l_quantity, l_extendedprice FROM orders JOIN lineitem ON l_orderkey = o_orderkey WHERE o_custkey = 739SELECT o_orderkey, l_linenumber, l_quantity, l_extendedprice FROM orders JOIN lineitem ON l_orderkey = o_orderkey WHERE o_custkey = ? |
| Q5Customer order lines | SELECT o_orderkey, l_linenumber, l_quantity, l_extendedprice FROM orders JOIN lineitem ON l_orderkey = o_orderkey WHERE o_custkey = 85SELECT o_orderkey, l_linenumber, l_quantity, l_extendedprice FROM orders JOIN lineitem ON l_orderkey = o_orderkey WHERE o_custkey = ? |
Cluster templates that rise and fall together
Forecasting every template separately does not scale, so QB5000's clusterer groups templates by the shape of their arrival-rate history: a template joins the cluster whose centre is most similar (cosine similarity above ρ; the paper uses 0.8), and clusters whose centres converge are merged. The trace below was generated from three hidden behaviours — office-hours ordering, a nightly shipping batch with a Monday spike, and evening browsing — so a good clustering finds three groups. Lower ρ and they collapse.
ρ = 0.80 → 3 clusters from 12 templates · matches the three hidden behaviours
Cluster 1
5 templates
Montotal hourly volume, training week 2Sun
- Q1Customer order historyOrder desk
- Q5Customer order linesOrder desk
- Q9Large open ordersOrder desk
- Q10Daily order totalsOrder desk
- Q11Priority backlogOrder desk
Cluster 2
3 templates
Montotal hourly volume, training week 2Sun
- Q2Shipping-window revenueShipping analytics
- Q4Supplier by ship modeShipping analytics
- Q8Late returnsShipping analytics
Cluster 3
4 templates
Montotal hourly volume, training week 2Sun
- Q3Part demandParts & customers
- Q6Segment directoryParts & customers
- Q7Brand catalogueParts & customers
- Q12Suppliers of a partParts & customers
Forecast each cluster's volume
Each cluster's hourly volume is forecast 3 hours ahead on the log scale. Linear regression looks at the last day; kernel regression compares the last week with every week it has seen, so it remembers periodic spikes. QB5000's HYBRID rule trusts kernel regression only when it predicts a spike more than 150% above the regular forecast. Accuracy is the mean squared error of log volumes on the held-out third week, as in the paper.
Hourly volume, last training days and the test week
Forecasts start where the test week begins; each point was predicted 3 h earlier.
- Actual
- LR
- KR
- HYBRID
Members: Q1, Q10, Q11, Q5, Q9 · HYBRID switched to the kernel-regression spike forecast in 8 of 168 test hours.
Accuracy on the test week
Mean squared error of log(1 + volume); lower is better.
| Cluster | LR | KR | HYBRID |
|---|---|---|---|
| Cluster 1(5) | 0.159 | 0.568 | 0.160 |
| Cluster 2(3) | 0.774 | 0.973 | 0.637 |
| Cluster 3(4) | 0.159 | 0.400 | 0.201 |
QB5000's ENSEMBLE averages linear regression with an LSTM. Training an LSTM in a browser tab is out of scope, so linear regression stands in for the ensemble here; the HYBRID rule is unchanged. LR uses the last 24 hours as input, KR the last 168.
Tune before the demand arrives
This is the loop Kossmann and Schlosser describe and our report built on: the predictor hands a forecast to a tuner, and the organiser applies the tuner's configuration before each 3-hour window starts. The tuner is AutoAdmin's Greedy search under a 30% storage budget; costs are the what-if model's estimates in milliseconds for the test week's real statements, plus the time to build each new index. The forecast-driven tuner only sees data from before each window: it uses 3-hour-ahead forecasts.
Total cost over the test week
Budget 781 kB · 56 windows of 3 h. Lower is better.
| Row | Estimated query time | Index builds | Total |
|---|---|---|---|
| No index | 21.1 s | 0 ms | 21.1 s |
| Tune once | 11.9 s | 49.3 ms | 11.9 s |
| Reactive | 10.4 s | 899 ms | 11.3 s |
| Forecast-driven | 9.46 s | 741 ms | 10.2 s |
| Oracle | 8.73 s | 849 ms | 9.58 s |
- No index
- Never tunes.
- Tune once
- One configuration from the two training weeks.
- Reactive
- Tunes for the window that just finished.
- Forecast-driven
- Tunes for QB5000's forecast of the next window.
- Oracle
- Tunes for the true next window. Perfect foresight, but blind to build cost.
Cumulative cost through the week
The gap opens at each predictable spike.
- No index
- Tune once
- Reactive
- Forecast-driven
- Oracle
Which indexes were in place, window by window
Forecast-driven tuning builds for the coming window; reactive tuning builds for the one that just ended, so it is a window late at every switch.
| index | Mon | Window 2 | Window 3 | Window 4 | Window 5 | Window 6 | Window 7 | Window 8 | Tue | Window 10 | Window 11 | Window 12 | Window 13 | Window 14 | Window 15 | Window 16 | Wed | Window 18 | Window 19 | Window 20 | Window 21 | Window 22 | Window 23 | Window 24 | Thu | Window 26 | Window 27 | Window 28 | Window 29 | Window 30 | Window 31 | Window 32 | Fri | Window 34 | Window 35 | Window 36 | Window 37 | Window 38 | Window 39 | Window 40 | Sat | Window 42 | Window 43 | Window 44 | Window 45 | Window 46 | Window 47 | Window 48 | Sun | Window 50 | Window 51 | Window 52 | Window 53 | Window 54 | Window 55 | Window 56 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| lineitem(l_shipdate) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| orders(o_orderdate, o_orderpriority) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| lineitem(l_orderkey) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| lineitem(l_partkey, l_suppkey) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| part(p_size, p_brand) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| lineitem(l_partkey) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| orders(o_custkey) |
Across 10 traces, not one
The default settings (horizon 3 h, cluster threshold ρ = 0.8, 3-hour tuning windows, budget 30% of the data), not the ones chosen in the lab above, on traces generated from seeds 2023 to 2032. Means with 95% percentile-bootstrap intervals over traces (B = 2000, seed 90050). Comparisons are paired by trace.
| Model | Mean [95% CI] | vs LR [95% CI] | Better / worse |
|---|---|---|---|
| Linear regression (LR) | 0.343 [0.334, 0.352] | reference | – |
| Kernel regression (KR) | 0.635 [0.628, 0.641] | +85% [+81%, +89%] | 0 / 10 (p 0.0020) |
| HYBRID | 0.333 [0.323, 0.343] | −3% [−7%, +1%] | 7 / 3 (p 0.34) |
| Strategy | Mean [95% CI] | vs reactive [95% CI] | Cheaper / dearer |
|---|---|---|---|
| No index | 23,782 [22,521, 25,074] | +106% [+94%, +117%] | 0 / 10 (p 0.0020) |
| Tune once | 11,844 [11,374, 12,300] | +2% [−1%, +5%] | 2 / 8 (p 0.11) |
| Reactive | 11,568 [11,242, 11,923] | reference | – |
| Forecast-driven | 10,567 [10,257, 10,920] | −9% [−10%, −7%] | 10 / 0 (p 0.0020) |
| Oracle | 9,823 [9,471, 10,205] | −15% [−16%, −14%] | 10 / 0 (p 0.0020) |
An interval that crosses 0% means the traces do not settle the comparison. The oracle tunes for the true next window and is a ceiling, not a contender. All costs are what-if estimates on one generated database, so they rank strategies rather than predict milliseconds.
What is real here, and what is simplified
The pre-processor, the on-line clusterer, linear regression, Nadaraya–Watson kernel regression and the HYBRID switching rule follow QB5000 as Ma describes it. The trace is synthetic — three behaviours with daily and weekly cycles plus noise — because the admissions, bus-tracking and online-course traces QB5000 was evaluated on are not public.
The tuner is the same AutoAdmin implementation the arena uses, scored with the lab's what-if cost model rather than live SQLite runs, so a week of 3-hour windows evaluates in milliseconds. PilotBot0's Monte Carlo tree search over action sequences is not implemented: the organiser here simply builds each window's configuration as it starts.