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

@andy_pavlo

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

3 AUTONOMOUS DBMSs SELF-ADAPTIVE DATABASES 1970-1990s → Index Selection Self-Adaptive → Partitioning / Sharding Databases → Data Placement

3 AUTONOMOUS DBMSs SELF-ADAPTIVE DATABASES SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' Admin 1970-1990s Self-Adaptive Databases

3 AUTONOMOUS DBMSs SELF-ADAPTIVE DATABASES SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' Admin A.ID A.VAL B.ID Tuning Algorithm B.NAME 1970-1990s Self-Adaptive Databases

3 AUTONOMOUS DBMSs SELF-ADAPTIVE DATABASES SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' Admin A.ID A.VAL B.ID Tuning Algorithm B.NAME 1970-1990s Self-Adaptive Databases +100 +200 +50

3 AUTONOMOUS DBMSs SELF-ADAPTIVE DATABASES SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' Admin A.ID A.VAL B.ID Tuning Algorithm B.NAME 1970-1990s Self-Adaptive Databases +100 +200 +50

3 AUTONOMOUS DBMSs SELF-ADAPTIVE DATABASES SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' Admin A.ID A.VAL B.ID Tuning Algorithm B.NAME 1970-1990s Self-Adaptive Databases +100 +200 +50

3 AUTONOMOUS DBMSs SELF-ADAPTIVE DATABASES SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' Admin A.ID A.VAL B.ID Tuning Algorithm B.NAME 1970-1990s → Index Selection Self-Adaptive → Partitioning / Sharding Databases → Data Placement

4 AUTONOMOUS DBMSs SELF-TUNING DATABASES SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' Admin A.ID A.VAL B.ID Tuning Algorithm B.NAME 1990-2000s → Index Selection Self-Tuning → Partitioning / Sharding Databases → Data Placement

4 AUTONOMOUS DBMSs SELF-TUNING DATABASES SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' Admin A.ID A.VAL B.ID Tuning Algorithm B.NAME Optimizer 1990-2000s Cost Model Self-Tuning AutoAdmin Databases

4 AUTONOMOUS DBMSs SELF-TUNING DATABASES SELECT * FROM A JOIN B ON A.ID = B.ID WHERE A.VAL > 123 AND B.NAME LIKE 'XY%' Admin A.ID A.VAL B.ID Tuning Algorithm B.NAME Optimizer 1990-2000s Cost Model Self-Tuning AutoAdmin Databases

4 AUTONOMOUS DBMSs SELF-TUNING DATABASES 600 541 Number of Knobs 400 291 200 1990-2000s 0 2000 2004 2008 2012 2016 Self-Tuning → Knob Configuration Databases

5 AUTONOMOUS DBMSs CLOUD MANAGED DATABASES 2010s Cloud Databases

5 AUTONOMOUS DBMSs CLOUD MANAGED DATABASES 2010s Cloud Databases

5 AUTONOMOUS DBMSs CLOUD MANAGED DATABASES → Initial Placement → Tenant Migration 2010s Cloud Databases

W hy is this previous work insufficient?

7 AUTONOMOUS DBMSs A BRIEF HISTORY Problem #2 Problem #1 Reactionary Human Measures Judgements

W hat is different this time?

AUTONOMOUS DATABASES WHY NOW? Better hardware. Better machine learning tools. Better appreciation for data. We seek to complete the circle in autonomous databases.

10 CARNEGIE MELLON UNIVERSITY RESEARCH PROJECTS Peloton OtterTune New Existing System Systems

Database Tuning-as-a-Service → Automatically generate DBMS knob configurations. → Reuse data from previous tuning sessions. OtterTune Supported ottertune.cs.cmu.edu Systems

12 OTTERTUNE AUTOMATIC DBMS TUNING SERVICE CONTROLLER COLLECTOR TARGET DATABASE

12 OTTERTUNE AUTOMATIC DBMS TUNING SERVICE CONTROLLER TUNING MANAGER COLLECTOR Interna ernal Reposit sitory ry Configur igurat ation on Metric Recommend nder Analyzer Knob Analyzer TARGET DATABASE

12 OTTERTUNE AUTOMATIC DBMS TUNING SERVICE CONTROLLER TUNING MANAGER COLLECTOR Interna ernal Reposit sitory ry Configur igurat ation on Metric Recommend nder Analyzer Knob Analyzer TARGET DATABASE

12 OTTERTUNE AUTOMATIC DBMS TUNING SERVICE CONTROLLER TUNING MANAGER COLLECTOR Interna ernal Reposit sitory ry Configur igurat ation on Metric Recommend nder Analyzer Knob Analyzer TARGET DATABASE

12 OTTERTUNE AUTOMATIC DBMS TUNING SERVICE CONTROLLER TUNING MANAGER COLLECTOR Interna ernal Reposit sitory ry Configur igurat ation on Metric Recommend nder Analyzer Knob Analyzer TARGET DATABASE

12 OTTERTUNE AUTOMATIC DBMS TUNING SERVICE CONTROLLER TUNING MANAGER COLLECTOR Interna ernal Reposit sitory ry Configur igurat ation on Metric Recommend nder Analyzer Knob Analyzer TARGET DATABASE

12 OTTERTUNE AUTOMATIC DBMS TUNING SERVICE CONTROLLER TUNING MANAGER COLLECTOR Interna ernal Reposit sitory ry Configur igurat ation on Metric Recommend nder Analyzer Knob Analyzer TARGET DATABASE

12 OTTERTUNE AUTOMATIC DBMS TUNING SERVICE CONTROLLER TUNING MANAGER COLLECTOR Interna ernal Reposit sitory ry Configur igurat ation on Metric Recommend nder Analyzer INSTALL AGENT Knob Analyzer TARGET DATABASE

13 OTTERTUNE DEMO Demonstration Postgres v9.3 TPC-C Benchmark

14 OTTERTUNE TPC-C TUNING Default Scripts RDS DBA OtterTune Throughput (txn/sec) 1000 1000 946 845 843 736 714 750 750 686 562 508 500 500 426 250 250 165 0 0 AUTOMATIC DATABASE MANAGEMENT SYSTEM TUNING THROUGH LARGE-SCALE MACHINE LEARNING SIGMOD 2017

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

16 PELOTON THE SELF-DRIVING DBMS WORKLOAD HISTORY TARGET DATABASE

16 PELOTON THE SELF-DRIVING DBMS WORKLOAD HISTORY FORECAST MODELS TARGET DATABASE

16 PELOTON "THE BRAIN" THE SELF-DRIVING DBMS WORKLOAD HISTORY Search Tree ACTION CATALOG FORECAST MODELS TARGET DATABASE

16 PELOTON "THE BRAIN" THE SELF-DRIVING DBMS WORKLOAD HISTORY Search Tree ACTION CATALOG FORECAST MODELS TARGET DATABASE ACTION SEQUENCE

16 PELOTON "THE BRAIN" THE SELF-DRIVING DBMS WORKLOAD HISTORY Search Tree ACTION CATALOG FORECAST MODELS TARGET DATABASE ACTION SEQUENCE

16 PELOTON "THE BRAIN" THE SELF-DRIVING DBMS WORKLOAD HISTORY Search Tree ACTION CATALOG FORECAST MODELS TARGET DATABASE ACTION SEQUENCE

16 PELOTON "THE BRAIN" THE SELF-DRIVING DBMS WORKLOAD ? HISTORY Search Tree ? ? ACTION CATALOG FORECAST MODELS TARGET DATABASE ACTION SEQUENCE

17 PELOTON BUS TRACKING APP WITH ONE-HOUR HORIZON Actual Predicted 60000 Ensemble (LR+RNN) Queries Per Hour 45000 30000 15000 0 9-Jan 11-Jan 13-Jan 15-Jan 17-Jan QUERY-BASED WORKLOAD FORECASTING FOR SELF-DRIVING DATABASE MANAGEMENT SYSTEM SIGMOD 2018

18 PELOTON ADMISSIONS APP WITH THREE-DAY HORIZON Actual Predicted 15 Ensemble (LR+RNN) Millions Queries Per Hour 10 5 0 26-Nov 30-Nov 4-Dec 8-Dec 12-Dec 16-Dec

18 PELOTON ADMISSIONS APP WITH THREE-DAY HORIZON Actual Predicted 15 Ensemble (LR+RNN) Millions Queries Per Hour 10 5 0 26-Nov 30-Nov 4-Dec 8-Dec 12-Dec 16-Dec

18 PELOTON ADMISSIONS APP WITH THREE-DAY HORIZON Actual Predicted 15 Ensemble (LR+RNN) Millions Queries Per Hour 10 5 0 26-Nov 30-Nov 4-Dec 8-Dec 12-Dec 16-Dec 15 Hybrid (LR+RNN+KR) Millions 10 5 0 26-Nov 30-Nov 4-Dec 8-Dec 12-Dec 16-Dec

19 OTTERTUNE DEMO Let's on check the demo…

Design Considerations for Autonomous Operation

21 AUTONMOUS DBMS DESIGN CONSIDERATIONS Configuration Internal Action Knobs Metrics Engineering

22 CONFIGURATION KNOBS UNTUNABLE KNOBS Anything that requires a human value judgement should be marked as off-limits to autonomous components. – File Paths – Network Addresses – Durability / Isolation Levels

23 CONFIGURATION KNOBS HOW TO CHANGE The autonomous components need hints about how to change a knob – Min/max ranges. – Separate knobs to enable/disable a feature. – Non-uniform deltas.

23 CONFIGURATION KNOBS HOW TO CHANGE The autonomous components need hints about how to change a knob – Min/max ranges. – Separate knobs to enable/disable a feature. – Non-uniform deltas. 1 KB 1 MB 1 GB 1 TB +10 KB +10 MB +10 GB

23 CONFIGURATION KNOBS HOW TO CHANGE The autonomous components need hints about how to change a knob – Min/max ranges. – Separate knobs to enable/disable a feature. – Non-uniform deltas.

24 CONFIGURATION KNOBS HARDWARE RESOURCES 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.

25 INTERNAL METRICS HARDWARE INFORMATION Expose DBMS's hardware capabilities: – CPU, Memory, Disk, Network Configu figura rati tion on Reco commender nder

25 INTERNAL METRICS HARDWARE INFORMATION Expose DBMS's hardware capabilities: – CPU, Memory, Disk, Network Otherwise you have to come up with clever ways to approximate this… Microbenchmark Threads