CS 839: Design the Next-Generation Database Lecture 22: Snowflake - - PowerPoint PPT Presentation

▶
cs 839 design the next generation database lecture 22
SMART_READER_LITE
LIVE PREVIEW

CS 839: Design the Next-Generation Database Lecture 22: Snowflake - - PowerPoint PPT Presentation

Mar 03, 2024 123 likes •455 views

CS 839: Design the Next-Generation Database Lecture 22: Snowflake Xiangyao Yu 4/9/2020 1 Announcements Course project Submission deadline: Apr. 23 Peer review: Apr. 23 Apr. 30 Presentation: Apr. 28 & 30 Submission

slide-1
SLIDE 1

Xiangyao Yu 4/9/2020

CS 839: Design the Next-Generation Database Lecture 22: Snowflake

1

slide-2
SLIDE 2

Announcements

2

Course project

  • Submission deadline: Apr. 23
  • Peer review: Apr. 23 – Apr. 30
  • Presentation: Apr. 28 & 30
  • Submission deadline: May 4

Will create google sheet for presentation signup

slide-3
SLIDE 3

Discussion Highlights

3 Optimal design that combines the advantages?

  • Athena with instances pre-running
  • Hybrid instance store and S3; decide caching based on the workload
  • High-quality code compilers
  • Heterogeneous system that combines all the existing systems together

Optimization opportunities for serverless databases?

  • Optimize resource sharing among users (e.g., cache, computation)
  • SW/HW codesign
  • Heterogeneous hardware and storage (e.g., different function on different hardware)
  • Scale computation and storage on demand
  • Keep instances pre-warmed to reduce cold starts

Cloud databases benefit from new hardware?

  • Using GPU
  • SmartSSD
  • RDMA and SmartNIC (e.g., shared cache in SSD, computation offloading)
  • Persistent memory to improve bandwidth and aid fast restarts
slide-4
SLIDE 4

Today’s Paper

4

SIGMOD 2016

slide-5
SLIDE 5

On-Premises vs. Cloud

5

On-premises

  • Fixed and limited hardware

resources Cloud

  • Virtually infinite computation & storage
  • Pay-as-you-go

CPU Mem

HDD

CPU Mem

HDD

CPU Mem

HDD

CPU Mem

HDD

CPU Mem

HDD

CPU Mem

HDD

… … … …

slide-6
SLIDE 6

Shared Nothing – Advantages

6 CPU Mem

VM

HDD

CPU Mem

VM

HDD

CPU Mem

VM

HDD

CPU Mem

VM

HDD

Scalability: horizontal scaling

  • Scales well for star-schema

queries Dimension Table Fact Table

slide-7
SLIDE 7

Shared Nothing – Disadvantages

7

Workload A Workload B More CPU intensive Less CPU intensive

CPU Mem

VM

HDD

CPU Mem

VM

HDD

CPU Mem

VM

HDD

CPU Mem

VM

HDD

Heterogeneous workload

slide-8
SLIDE 8

Shared Nothing – Disadvantages

8

Heterogeneous workload Membership changes

  • Add a node: data redistribution

CPU Mem

VM

HDD

CPU Mem

VM

HDD

CPU Mem

VM

HDD

CPU Mem

VM

HDD

CPU Mem

VM

HDD

slide-9
SLIDE 9

Shared Nothing – Disadvantages

9

Heterogeneous workload Membership changes

  • Add a node: data redistribution
  • Delete a node: fault tolerance

CPU Mem

VM

HDD

CPU Mem

VM

HDD

CPU Mem

VM

HDD

CPU Mem

VM

HDD

CPU Mem

VM

HDD

slide-10
SLIDE 10

Shared Nothing – Disadvantages

10

Heterogeneous workload Membership changes Online upgrade

  • Similar to membership change

CPU Mem

VM

HDD

CPU Mem

VM

HDD

CPU Mem

VM

HDD

CPU Mem

VM

HDD

slide-11
SLIDE 11

Web User Interface

Serverless (similar to Athena)

11

slide-12
SLIDE 12

Multi-Cluster Shared-Data Architecture

12

Control layer Compute layer Storage layer

slide-13
SLIDE 13

Architecture – Storage

13

Data format: PAX Data horizontally partitioned into immutable files (~16MB)

  • An update = remove and add an entire file
  • Queries download file headers and columns they are interested in

Intermediate data spilling to S3

slide-14
SLIDE 14

Architecture – Virtual Warehouse

14

T-Shirt sizes: XS to 4XL Elasticity and Isolation

  • Created, destroyed, or resized at any point (may shutdown all VWs)
  • User may create multiple VWs for multiple queries

Workload A Workload B More CPU intensive Less CPU intensive Large VW Small VW

slide-15
SLIDE 15

Architecture – Virtual Warehouse

15

Local caching

  • S3 data can be cached in local memory or disk

CPU CPU CPU HDD HDD HDD HDD HDD

slide-16
SLIDE 16

Architecture – Virtual Warehouse

16

Local caching

  • S3 data can be cached in local memory or disk

CPU CPU CPU HDD HDD HDD HDD HDD

Consistent hashing

  • When the hash table (n keys and

m slots) is resized, only n/m keys need to be remapped

slide-17
SLIDE 17

Architecture – Virtual Warehouse

17

Local caching

  • S3 data can be cached in local memory or disk

CPU CPU CPU HDD HDD HDD HDD HDD CPU

Consistent hashing

  • When the hash table (n keys and

m slots) is resized, only n/m keys need to be remapped

slide-18
SLIDE 18

Architecture – Virtual Warehouse

18

Local caching

  • S3 data can be cached in local memory or disk

CPU CPU CPU HDD HDD HDD HDD HDD CPU

Consistent hashing

  • When the hash table (n keys and

m slots) is resized, only n/m keys need to be remapped

  • When a VW is resized, no data

shuffle required; rely on LRU to replace cache content

slide-19
SLIDE 19

Architecture – Virtual Warehouse

19

Local caching

  • S3 data can be cached in local memory or disk

CPU CPU CPU HDD HDD HDD HDD HDD CPU

Consistent hashing

  • When the hash table (n keys and

m slots) is resized, only n/m keys need to be remapped

  • When a VW is resized, no data

shuffle required; rely on LRU to replace cache content File stealing to tolerate skew

slide-20
SLIDE 20

Architecture – Virtual Warehouse

20

Execution engine

  • Columnar: SIMD, compression
  • Vectorized: process a group of elements at a time
  • Push-based
slide-21
SLIDE 21

Architecture – Cloud Services

21

Multi-tenant layer shared across multiple users Query optimization Concurrency control

  • Isolation: snapshot isolation (SI)
  • S3 data is immutable, update entire files with MVCC
  • Versioned snapshots used for time traveling

Pruning

  • Snowflake has no index (same in Athena, Presto, Hive, etc)
  • Min-max based pruning: store min and max values for a data block
slide-22
SLIDE 22

High Availability and Fault Tolerance

22

Stateless services

slide-23
SLIDE 23

High Availability and Fault Tolerance

23

Replicated metadata

slide-24
SLIDE 24

High Availability and Fault Tolerance

24

One node failure in VW

  • Re-execute with failed node

immediately replaced

  • Re-execute with reduced

number of nodes

Whole AZ failure

  • Re-execute by re-

provisioning a new VW

Hot-standby nodes

slide-25
SLIDE 25

High Availability and Fault Tolerance

25

S3 is highly available and durable

slide-26
SLIDE 26

Online Upgrade

26

Deploy new versions of services and VWs

slide-27
SLIDE 27

Semi-Structured Data

27

Extensible Markup Language (XML) JavaScript Object Notation(JSON)

slide-28
SLIDE 28

Extract-Transform-Load (ETL)

Transform (e.g., converting to column format) adds latency to the system

28

slide-29
SLIDE 29

ETL vs. ELT

29

Picture from https://aws.amazon.com/blogs/big-data/etl-and-elt-design-patterns-for-lake- house-architecture-using-amazon-redshift-part-1/

slide-30
SLIDE 30

Optimization for Semi-Structured Data

Automatic type inference Hybrid columnar format

  • Frequently paths are detected, projected out, and stored in separate

columns in table file (typed and compressed)

  • Collect metadata on these columns for optimization (e.g., pruning)

30

slide-31
SLIDE 31

Summary

Snowflake vs shared nothing

  • Heterogeneous workload
  • Membership changes

Snowflake vs. Redshift (Spectrum) Snowflake vs. Athena Snowflake vs. Presto/Hive/Vertica

31

slide-32
SLIDE 32

Snowflake – Q/A

Storage system better than S3 (e.g., allow updates) Row store for transaction processing? Server-side cursor? Min-max based pruning replacing indices? Other systems similar to Snowflake? Pay-as-you-go? Push vs. pull? Pruning requires sorting? Snowflake autoscaling compute based on demand?

32

slide-33
SLIDE 33

Group Discussion

How far away is Snowflake from the “optimal design” that you discussed last time?

  • High-quality code compilers
  • Athena with instances pre-running
  • Hybrid instance store and S3; decide caching based on the workload
  • Heterogeneous system that combines all the existing systems together

Can you come up with a nice way of combining cloud data warehousing (e.g., Snowflake) with cloud transaction processing (e.g., Aurora)?

33