Peregrine: workload optimization for cloud query engines* Alekh - - PowerPoint PPT Presentation

▶
peregrine workload optimization for cloud query engines
SMART_READER_LITE
LIVE PREVIEW

Peregrine: workload optimization for cloud query engines* Alekh - - PowerPoint PPT Presentation

Dec 10, 2022 531 likes •842 views

Peregrine: workload optimization for cloud query engines* Alekh Jindal Gray Systems Lab * Peregrine: Workload Optimization for Cloud Query Engines . Alekh Jindal, Hiren Patel, Abhishek Roy, Shi Qiao, Jarod Yin, Rathijit Sen, Subru Krishnan. SOCC

slide-1
SLIDE 1

Peregrine: workload optimization for cloud query engines*

Alekh Jindal Gray Systems Lab

* Peregrine: Workload Optimization for Cloud Query Engines. Alekh Jindal, Hiren Patel, Abhishek Roy, Shi Qiao, Jarod Yin, Rathijit Sen, Subru Krishnan. SOCC 2019.

slide-2
SLIDE 2

Engine DBA

Workload

slide-3
SLIDE 3

On-Premise

DBA

slide-4
SLIDE 4

DBA

On-Premise

slide-5
SLIDE 5

DBA

On-Premise

Need to reach by 10, can we drive faster? Sure!

slide-6
SLIDE 6

Cloud Query Engines

  • Setup, installation, maintenance taken care of
  • On-demand provisioning, pay as you go
slide-7
SLIDE 7

Cloud Query Engines

Need to reach by 10, can we drive faster? Sorry, we don’t have a DBA

Reality Check for customers:

  • Lots of services to choose from (even within Azure, GCP, AWS)
  • Lot of knobs to tune for good perf and low cost
  • Lack of control; and lack of expertise
  • And, the DBA is gone!

Reality Check for providers:

  • System developers == virtual DBAs!
  • Too many cloud users, compared to system developers
  • Too many support requests; often redundant
  • Less time for feature development

.. ahhh!

slide-8
SLIDE 8

Cosmos: big data infra at Microsoft

  • 100s of thousands of machines
  • Exabytes of data at rest; Petabytes ingress/egress daily
  • 500k+ batch jobs / day
  • 3B+ tasks executed / day
  • 10s of millions interactive queries / day
  • 10s of thousands of SCOPE developers
  • 1000s of teams
slide-9
SLIDE 9

The missing DBA and the growing pain in Cosmos

  • Large number of knobs/hints at script, data, plan level
  • Only few expert users
  • Rest need guidance
  • Survey: better tooling for improving SCOPE queries
  • Support challenge
  • 10s of thousands incidents / years
  • 10 incidents per system developer on call
  • 100x users compared to system developers
  • ~10% growth in SCOPE workload in 2019
slide-10
SLIDE 10

Cloud pain

..….. Database Vendor Developers DB DBA Users

Customer 1

DB DBA Users

Customer 2

DB DBA Users

Customer n

Workload Workload Workload DS1 Users DS2 DS3 DSn ..… Developers Data Services Workload

Pain Pain Pain

On-premise pain

  • >
slide-11
SLIDE 11

The cloud opportunity

Workload Workload Workload Workload Fragmented on-premise workloads

Massive cloud workloads

slide-12
SLIDE 12

The Cosmos opportunity

Workload

Massive cloud workloads

Job metadata name, user, account, submit/start/end times Query plans logical, physical, stage graph, estimates Runtime statistics Operator-wise observables Task level logs start/end events Machine counters CPU, IO, etc. Several TBs of metadata / day

slide-13
SLIDE 13

The case for a workload optimization platform

  • DBA-as-a-Service
  • Another service in the cloud (easier integration)
  • Based on cloud workloads at hand (instance optimization)
  • Engine agnostic
  • Not specific to different query engines, e.g., SCOPE, Spark, SQL DW, or etc.
  • E.g., view selection is still the same problem
  • Global optimizations
  • Cloud workloads are organized into data pipelines
  • People often care about end-to-end aggregate costs in the cloud
slide-14
SLIDE 14

St Step 1: w 1: work

  • rkloa
  • ad r

representation

  • n

Instrument, log, and collect workload characteristics

slide-15
SLIDE 15

Engine-agnostic workload representation

Logical plan Physical plan Stage graph Tasks Signatures Denormalized view Anonymized (Workload IR) Log + metrics Log + metrics Log + metrics Log + metrics

slide-16
SLIDE 16

Step 2: optimize for patterns

slide-17
SLIDE 17

Typical workload patterns

  • Consider a simplified 2D space of data and queries

Queries Data Queries Data Data Queries Data

Recurring Similarity Dependency

Query templates appear

  • ver newer datasets

Queries over same datasets have similarities Queries depend on datasets produced by previous queries

slide-18
SLIDE 18

Recurring pattern

  • Majority of production workloads
  • There is a regular ETL needed before other

things can happen

  • Opportunity to learn from the past
  • Examples
  • Learned cardinality
  • Learned cost models
  • Learned resources
  • Learned etc.

Queries Data

Recurring

Query templates appear

  • ver newer datasets
slide-19
SLIDE 19

SCOPE Cardinality Estimation

Under- estimation Over- estimation

Ideal

Recap from NWDS’19

CARDINALITY ESTIMATION

𝐹𝑊𝐽𝑀

slide-20
SLIDE 20

0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1 10-6 10-4 10-2 100 102 104 106 108 Fraction Subgraph Instances Estimated/Actual Cardinality Ratio Neural Network Linear Regression Poisson Regression

SCOPE Cardinality Estimation

Under- estimation Over- estimation

Ideal

Recap from NWDS’19

Towards a Learning Optimizer for Shared Clouds. Chenggang Wu, Alekh Jindal, Saeed Amizadeh, Hiren Patel, Wangchao Le, Shi Qiao, Sriram Rao. VLDB 2019.

slide-21
SLIDE 21

SCOPE Cost Estimation

  • Costs models are orders of magnitude off!

c) Manually Improved Cost Model d) Cost Models with Cardinality Feedback

ideal Under Estimation Over Estimation ideal Under Estimation Over Estimation ideal Under Estimation Over Estimation

CARDINALITY ESTIMATION 𝐹𝑊𝐽𝑀

COST ESTIMATION

BAD!

slide-22
SLIDE 22

SCOPE Cost Estimation

  • Costs models are orders of magnitude off!

c) Manually Improved Cost Model d) Cost Models with Cardinality Feedback

ideal Under Estimation Over Estimation ideal Under Estimation Over Estimation ideal Under Estimation Over Estimation

  • Pervasive use of user defined functions
  • Complexity of big data systems
  • Variance in the cloud environments

Manually tuned cost model Feeding perfect cardinalities

slide-23
SLIDE 23

Why cardinality is not enough?

  • Incrementally add features
  • Error drops from 110% to 40%
  • Additional transformations needed
  • Hard to come up with such heuristics
  • Two sets of hash join instances
  • Different feature weights
  • Hard to instance optimize manually
slide-24
SLIDE 24

Ensemble of Models over Recurring Patterns

Operator-subgraph Operator-subgraphApprox Operator-inputs Operator Coverage Accuracy fixed approximately fixed featurized

Op-Subgraph

a b c d e f

Op-SubgraphApprox Op-Input Operator Combined

error

no coverage no coverage no coverage

slide-25
SLIDE 25

SCOPE Cost Estimation

  • Can learn pretty accurate cost models!

c) Manually Improved Cost Model d) Cost Models with Cardinality Feedback

ideal Under Estimation Over Estimation ideal Under Estimation Over Estimation ideal Under Estimation Over Estimation

Cost Models for Big Data Query Processing: Learning, Retrofitting, and Our Findings. Tarique Siddiqui, Alekh Jindal, Shi Qiao, Hiren Patel, Wangchao Le. SIGMOD 2020 (to appear).

slide-26
SLIDE 26

Similarity pattern

  • Very typical in multi-user shared cloud

environments

  • Cosmos, HDI, Ant Financial, ML workflows, etc.
  • Opportunity for multi-query optimization
  • SCOPE compute reuse

Queries Data Data

Similarity

Queries over same datasets have similarities

emerging as a

  • nment or

manage they pay ever, the and teams ., parts of generating computation reuse

20 40 60 80 100 clus er1 clus er2 clus er3 clus er4 clus er5

Percentage

Overlapping jobs Users with overlapping jobs Overlapping subgraphs

Computation Reuse in Analytics Job Service at Microsoft. Alekh Jindal, Shi Qiao, Hiren Patel, Jarod Yin, Jieming Di, Malay Bag, Marc Friedman, Yifung Lin, Konstantinos Karanasos, Sriram Rao. SIGMOD 2018.

slide-27
SLIDE 27

Spark Compute Reuse

  • Instrument application log
  • Analyze common subexpressions
  • ver Spark SQL plans
  • Optimizer rules to automatically

materialize/reuse in future queries

  • Almost 30% improvement in total

time on TPC-DS

SparkCruise: Handsfree Computation Reuse in Spark. Abhishek Roy, Alekh Jindal, Hiren Patel, Ashit Gosalia, Subru Krishnan, Carlo Curino. VLDB 2019 (Demo).

slide-28
SLIDE 28

Step 3: feeding it back

  • Actions
  • Insights
  • Recommendations
  • Self-tuning
slide-29
SLIDE 29

Extensions Jar

Optimizer Rule1: Online materialize Optimizer Rule2: Computation Reuse

SCOPE Modifications to compiler/optimizer

Pluggable extensions from outside

SCOPE

Compiler flags

Illustration: Scope and Spark query engines

Compiler Optimizer Scheduler Runtime

Query Result

Query Engine

Feedback Service View Selection

Selected Views

Learn Cardinality

Cardinality Models

Common Subexpressions Query Subexpressions IR Workload Repository

SCOPE

Connectors Parsers Enumerators Recurring Signature Strict Signature

Peregrine

slide-30
SLIDE 30

Peregrine Summary

  • Easier to add newer features
  • Easier to add newer engines
  • Easier for people to participate
  • Researchers, developers, interns
  • Abstracts the painful steps
  • Build on top of each other
  • Focus on workload optimizations
  • Gray Systems Lab: aka.ms/gsl

SCOPE

Spark Hive

..… Workload-aware Query Engines Sharing Recurring Coordinating

Multi-query Optimization, e.g., CloudViews Learned optimizations, e.g., Learned Cardinality

..…

Mathematical Solvers Machine Learning Graph Analytics

Workload Optimization

Patterns

Dependency-driven optimizations, e.g., physical design for pipeline Metadata Plans Statistics

Feature Store

Ingest Parse Enumerate

Workload Intermediate Representation (IR)

Signatures

Query Plan Instrumentation ..… Workload Representation Insights Recommendations Self-tuning Users

Dashboard Alerts

Feedback Service

Query Annotations

Workload Feedback

Feedback

We are hiring!