@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 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 600 541 Number of Knobs 400 291 200 1990-2000s 0 2000 2004 2008 2012 2016 Self-Tuning Databases → Knob Configuration

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 INSTALL AGENT 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

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

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

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

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

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

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

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

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 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

15 PELOTON THE SELF-DRIVING DBMS WORKLOAD HISTORY TARGET DATABASE

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

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

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

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

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

16 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

17 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

17 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

Design Considerations for Autonomous Operation

19 AUTONMOUS DBMS DESIGN CONSIDERATIONS Configuration Internal Action Knobs Metrics Engineering

20 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

21 CONFIGURATION KNOBS HOW TO CHANGE 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 CONFIGURATION KNOBS HOW TO CHANGE The autonomous components need hints about how to change a knob – Min/Max Ranges. – Non-Uniform Deltas. – Separate knobs to enable/disable a feature.

22 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.

23 INTERNAL METRICS HARDWARE INFORMATION Expose DBMS's hardware capabilities through a SQL interface. – CPU, Memory, Disk, Network Configu figura rati tion on Reco commender nder

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

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

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

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

25 ACTION ENGINEERING NO SHUTDOWN 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.