[PPT] - @andy_pavlo Part #1 Background Part # 2 Engineering Part # 3 PowerPoint Presentation

SLIDE 1

@andy_pavlo

SLIDE 2

Part #1 Part # 2 Part # 3 Background Engineering Oracle Rant

SLIDE 3 AUTONOMOUS DBMSs

3

SELF-ADAPTIVE DATABASES

1970-1990s Self-Adaptive Databases

SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' Admin

SLIDE 4 AUTONOMOUS DBMSs

3

SELF-ADAPTIVE DATABASES

1970-1990s Self-Adaptive Databases

SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' A.ID A.VAL B.ID B.NAME Tuning Algorithm Admin

SLIDE 5 AUTONOMOUS DBMSs

3

SELF-ADAPTIVE DATABASES

1970-1990s Self-Adaptive Databases

SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' A.ID A.VAL B.ID B.NAME

+100 +200 +50

Tuning Algorithm Admin

SLIDE 6 AUTONOMOUS DBMSs

3

SELF-ADAPTIVE DATABASES

1970-1990s Self-Adaptive Databases

SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' A.ID A.VAL B.ID B.NAME

+100 +200 +50

Tuning Algorithm Admin

SLIDE 7 AUTONOMOUS DBMSs

3

SELF-ADAPTIVE DATABASES

1970-1990s Self-Adaptive Databases

SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' A.ID A.VAL B.ID B.NAME

+100 +200 +50

Tuning Algorithm Admin

SLIDE 8 AUTONOMOUS DBMSs

3

SELF-ADAPTIVE DATABASES

1970-1990s Self-Adaptive Databases

SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' A.ID A.VAL B.ID B.NAME Tuning Algorithm Admin

→Index Selection →Partitioning / Sharding →Data Placement

SLIDE 9 AUTONOMOUS DBMSs

4

SELF-TUNING DATABASES

1990-2000s Self-Tuning Databases

SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' Tuning Algorithm Admin

→Index Selection →Partitioning / Sharding →Data Placement

A.ID A.VAL B.ID B.NAME

SLIDE 10 AUTONOMOUS DBMSs

4

SELF-TUNING DATABASES

1990-2000s Self-Tuning Databases

SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' Tuning Algorithm Admin

AutoAdmin

A.ID A.VAL B.ID B.NAME Optimizer Cost Model

SLIDE 11

541 291

200 400 600 2000 2004 2008 2012 2016 Number of Knobs

AUTONOMOUS DBMSs

4

SELF-TUNING DATABASES

1990-2000s Self-Tuning Databases

→Knob Configuration

SLIDE 12 AUTONOMOUS DBMSs

5

CLOUD MANAGED DATABASES

2010s Cloud Databases

SLIDE 13 AUTONOMOUS DBMSs

5

CLOUD MANAGED DATABASES

2010s Cloud Databases

→Initial Placement →Tenant Migration

SLIDE 14

W hy is this previous work insufficient?

SLIDE 15 AUTONOMOUS DBMSs

7

A BRIEF HISTORY

Problem #1 Human Judgements Problem #2 Reactionary Measures

SLIDE 16

W hat is different this time?

SLIDE 17 AUTONOMOUS DATABASES

Better hardware. Better machine learning tools. Better appreciation for data. We seek to complete the circle in autonomous databases.

WHY NOW?

SLIDE 18 CARNEGIE MELLON UNIVERSITY

10

RESEARCH PROJECTS

OtterTune Existing Systems Peloton New System

SLIDE 19

OtterTune

Database Tuning-as-a-Service → Automatically generate DBMS knob configurations. → Reuse data from previous tuning sessions.

ttertune.cs.cmu.edu

Supported Systems

SLIDE 20 OTTERTUNE

12

AUTOMATIC DBMS TUNING SERVICE

TARGET DATABASE

CONTROLLER

COLLECTOR INSTALL AGENT

SLIDE 21 OTTERTUNE

12

AUTOMATIC DBMS TUNING SERVICE

TARGET DATABASE

TUNING MANAGER CONTROLLER

COLLECTOR INSTALL AGENT Configur igurat ation

n

Recommend nder Knob Analyzer Metric Analyzer Interna ernal Reposit sitory ry

SLIDE 22 OTTERTUNE

12

AUTOMATIC DBMS TUNING SERVICE

TARGET DATABASE

TUNING MANAGER CONTROLLER

COLLECTOR INSTALL AGENT Configur igurat ation

n

Recommend nder Knob Analyzer Metric Analyzer Interna ernal Reposit sitory ry

SLIDE 23 OTTERTUNE

12

AUTOMATIC DBMS TUNING SERVICE

TARGET DATABASE

TUNING MANAGER CONTROLLER

COLLECTOR INSTALL AGENT Configur igurat ation

n

Recommend nder Knob Analyzer Metric Analyzer Interna ernal Reposit sitory ry

SLIDE 24 OTTERTUNE

12

AUTOMATIC DBMS TUNING SERVICE

TARGET DATABASE

TUNING MANAGER CONTROLLER

COLLECTOR INSTALL AGENT Configur igurat ation

n

Recommend nder Knob Analyzer Metric Analyzer Interna ernal Reposit sitory ry

SLIDE 25 OTTERTUNE

12

AUTOMATIC DBMS TUNING SERVICE

TARGET DATABASE

TUNING MANAGER CONTROLLER

COLLECTOR INSTALL AGENT Configur igurat ation

n

Recommend nder Knob Analyzer Metric Analyzer Interna ernal Reposit sitory ry

SLIDE 26 OTTERTUNE

12

AUTOMATIC DBMS TUNING SERVICE

TARGET DATABASE

TUNING MANAGER CONTROLLER

COLLECTOR INSTALL AGENT Configur igurat ation

n

Recommend nder Knob Analyzer Metric Analyzer Interna ernal Reposit sitory ry

SLIDE 27 OTTERTUNE

12

AUTOMATIC DBMS TUNING SERVICE

TARGET DATABASE

TUNING MANAGER CONTROLLER

COLLECTOR INSTALL AGENT Configur igurat ation

n

Recommend nder Knob Analyzer Metric Analyzer Interna ernal Reposit sitory ry

SLIDE 28 OTTERTUNE

12

AUTOMATIC DBMS TUNING SERVICE

TARGET DATABASE

TUNING MANAGER CONTROLLER

COLLECTOR INSTALL AGENT Configur igurat ation

n

Recommend nder Knob Analyzer Metric Analyzer Interna ernal Reposit sitory ry

SLIDE 29 OTTERTUNE

12

AUTOMATIC DBMS TUNING SERVICE

TARGET DATABASE

TUNING MANAGER CONTROLLER

COLLECTOR INSTALL AGENT Configur igurat ation

n

Recommend nder Knob Analyzer Metric Analyzer Interna ernal Reposit sitory ry

SLIDE 30 OTTERTUNE TPC-C TUNING AUTOMATIC DATABASE MANAGEMENT SYSTEM TUNING THROUGH LARGE-SCALE MACHINE LEARNING SIGMOD 2017 165 508 562 736 686

250 500 750 1000

426 845 714 843 946

250 500 750 1000

Throughput (txn/sec)

Default RDS DBA Scripts OtterTune

13

SLIDE 31

Peloton

Self-Driving Database System → In-memory DBMS with integrated ML/RL framework. → Designed for autonomous

perations.

pelotondb.io

SLIDE 32 PELOTON

15

THE SELF-DRIVING DBMS

TARGET DATABASE WORKLOAD HISTORY

SLIDE 33 PELOTON

15

THE SELF-DRIVING DBMS

FORECAST MODELS TARGET DATABASE WORKLOAD HISTORY

SLIDE 34

"THE BRAIN"

PELOTON

15

THE SELF-DRIVING DBMS

FORECAST MODELS

Search Tree ACTION CATALOG

TARGET DATABASE WORKLOAD HISTORY

SLIDE 35

"THE BRAIN"

PELOTON

15

THE SELF-DRIVING DBMS

FORECAST MODELS

Search Tree ACTION CATALOG ACTION SEQUENCE

TARGET DATABASE WORKLOAD HISTORY

SLIDE 36

"THE BRAIN"

PELOTON

15

THE SELF-DRIVING DBMS

FORECAST MODELS

Search Tree ACTION CATALOG ACTION SEQUENCE

TARGET DATABASE WORKLOAD HISTORY

SLIDE 37

"THE BRAIN"

PELOTON

15

THE SELF-DRIVING DBMS

FORECAST MODELS

Search Tree ACTION CATALOG ACTION SEQUENCE

TARGET DATABASE WORKLOAD HISTORY

? ? ?

SLIDE 38 PELOTON BUS TRACKING APP WITH ONE-HOUR HORIZON QUERY-BASED WORKLOAD FORECASTING FOR SELF-DRIVING DATABASE MANAGEMENT SYSTEM SIGMOD 2018 15000 30000 45000 60000 9-Jan 11-Jan 13-Jan 15-Jan 17-Jan

Ensemble (LR+RNN)

16

Actual Predicted Queries Per Hour

SLIDE 39 PELOTON ADMISSIONS APP WITH THREE-DAY HORIZON

5 10 15 26-Nov 30-Nov 4-Dec 8-Dec 12-Dec 16-Dec

Millions

Actual Predicted Ensemble (LR+RNN)

17

Queries Per Hour

SLIDE 40 PELOTON ADMISSIONS APP WITH THREE-DAY HORIZON

5 10 15 26-Nov 30-Nov 4-Dec 8-Dec 12-Dec 16-Dec

Millions

Actual Predicted

5 10 15 26-Nov 30-Nov 4-Dec 8-Dec 12-Dec 16-Dec

Millions

Ensemble (LR+RNN) Hybrid (LR+RNN+KR)

17

Queries Per Hour

SLIDE 41

Design Considerations for Autonomous Operation

SLIDE 42 AUTONMOUS DBMS

19

DESIGN CONSIDERATIONS

Configuration Knobs Internal Metrics Action Engineering

SLIDE 43

Anything that requires a human value judgement should be marked as off-limits to autonomous components.

– File Paths – Network Addresses – Durability / Isolation Levels 20

UNTUNABLE KNOBS CONFIGURATION KNOBS

SLIDE 44 CONFIGURATION KNOBS

The autonomous components need hints about how to change a knob

– Min/Max Ranges. – Non-Uniform Deltas. – Separate knobs to enable/disable a feature. 21

HOW TO CHANGE

SLIDE 45 CONFIGURATION KNOBS

The autonomous components need hints about how to change a knob

– Min/Max Ranges. – Non-Uniform Deltas. – Separate knobs to enable/disable a feature. 21

HOW TO CHANGE

SLIDE 46 CONFIGURATION KNOBS

Indicate which knobs are constrained by hardware resources.

– The sum of all buffers cannot exceed the total amount of available memory.

The problem is that sometimes it makes sense to overprovision.

22

HARDWARE RESOURCES

SLIDE 47 INTERNAL METRICS

Expose DBMS's hardware capabilities through a SQL interface.

– CPU, Memory, Disk, Network 23

HARDWARE INFORMATION Configu figura rati tion

n

Reco commender nder

SLIDE 48 INTERNAL METRICS

If the DBMS has sub-components that are tunable, then it must expose separate metrics for those components.

24

SUB-COMPONENTS

SLIDE 49 INTERNAL METRICS

If the DBMS has sub-components that are tunable, then it must expose separate metrics for those components.

24

SUB-COMPONENTS

SLIDE 50 INTERNAL METRICS

If the DBMS has sub-components that are tunable, then it must expose separate metrics for those components.

24

SUB-COMPONENTS

SLIDE 51 INTERNAL METRICS

If the DBMS has sub-components that are tunable, then it must expose separate metrics for those components.

24

SUB-COMPONENTS

SLIDE 52 ACTION ENGINEERING

No action should ever require the DBMS to restart in order for it to take affect. The commercial systems are much better than this than the open-source systems.

25

NO SHUTDOWN

SLIDE 53 ACTION ENGINEERING

Provide a notification callback to indicate when an action starts and when it completes. Harder for changes that can be used before the action completes.

26

NOTIFICATIONS

SLIDE 54 ACTION ENGINEERING

Support executing the same action with different resource usage levels.

27

RESOURCE USAGE

SLIDE 55 ACTION ENGINEERING

Support executing the same action with different resource usage levels.

27

RESOURCE USAGE

SLIDE 56 ACTION ENGINEERING

Support executing the same action with different resource usage levels.

27

RESOURCE USAGE

SLIDE 57 ACTION ENGINEERING

Rejected actions should not cause DBMS to halt or unexpected application behavior.

28

REJECTION HANDLING

Worker Threads Network Threads Background Threads

SLIDE 58 ACTION ENGINEERING

Rejected actions should not cause DBMS to halt or unexpected application behavior.

28

REJECTION HANDLING

Worker Threads Network Threads Background Threads

+2

SLIDE 59 ACTION ENGINEERING

Rejected actions should not cause DBMS to halt or unexpected application behavior.

28

REJECTION HANDLING

Worker Threads Network Threads Background Threads

+2

1

SLIDE 60 ACTION ENGINEERING

Rejected actions should not cause DBMS to halt or unexpected application behavior.

28

REJECTION HANDLING

Worker Threads Network Threads Background Threads

+2

1
1

SLIDE 61 ACTION ENGINEERING

Rejected actions should not cause DBMS to halt or unexpected application behavior.

28

REJECTION HANDLING

Worker Threads Network Threads Background Threads

+2

1
1

SLIDE 62 ACTION ENGINEERING

Allow replica configurations to diverge from each other.

29

REPLICA EXPLORATION

SLIDE 63 ACTION ENGINEERING

Allow replica configurations to diverge from each other.

29

REPLICA EXPLORATION

Master Replicas

SLIDE 64 ACTION ENGINEERING

Allow replica configurations to diverge from each other.

29

REPLICA EXPLORATION

Master Replicas

SLIDE 65 ACTION ENGINEERING

Allow replica configurations to diverge from each other.

29

REPLICA EXPLORATION

Master Replicas

SLIDE 66

W hat About Oracle's Self-Driving DBMS?

SLIDE 67 ORACLE SELF-DRIVING DBMS

31 September 2017 January 2017

SLIDE 68 ORACLE

Automatic Indexing Automatic Recovery Automatic Scaling Automatic Query Tuning

SELF-DRIVING DBMS

31 September 2017

SLIDE 69 ORACLE

Automatic Indexing Automatic Recovery Automatic Scaling Automatic Query Tuning

SELF-DRIVING DBMS

31

Problem #2 Reactionary Measures

September 2017

SLIDE 70 ORACLE

Automatic Indexing Automatic Recovery Automatic Scaling Automatic Query Tuning

SELF-DRIVING DBMS

31

Problem #2 Reactionary Measures

September 2017

SLIDE 71 CONCLUSION

True autonomous DBMSs are achievable in the next decade. You should think about how each new feature can be controlled by a machine.

MAIN TAKEAWAYS

32

SLIDE 72

END

@andy_pavlo