Slide 1

Slide 1 text

Zero-Downtime SQL Database Schema Evolution for Continuous Deployment

Slide 2

Slide 2 text

Michael de Jong Magnet.me Arie van Deursen Delft University of Technology Anthony Cleve University of Namur

Slide 3

Slide 3 text

No content

Slide 4

Slide 4 text

30+ 1.200+ 90.000+ 2 Employees Companies Students Countries

Slide 5

Slide 5 text

Full-time developers (Micro)services Master is green? deploy! No manual testing! 6 20+

Slide 6

Slide 6 text

We deploy frequently and we’re not the only ones [3][13]

Slide 7

Slide 7 text

version deploys/month schema changes/month ~1.400 ~16

Slide 8

Slide 8 text

version deploys/month schema changes/month ~1.400 ~16 Roughly doubled since writing the paper

Slide 9

Slide 9 text

Blocking schema operations Why are my schema changes crashing my service?

Slide 10

Slide 10 text

Mixed-State How to deal with combinations of different versions of the database schema and your application

Slide 11

Slide 11 text

Result: manual deploys Can’t deploy new schema during the day because it’s not safe, so you have to deploy it manually at night

Slide 12

Slide 12 text

Result: manual deploys Can’t deploy new schema during the day because it’s not safe, so you have to deploy it manually at night Not Continuous Deployment

Slide 13

Slide 13 text

Understanding behaviour blocking

Slide 14

Slide 14 text

Experiments Created test database Simulated application running on top Tested 19 different schema operations Using different version of MySQL / PG Plotted when queries were executed

Slide 15

Slide 15 text

Activity plots Adding a non-nullable column (PG 9.4) Making a column non-nullable (MySQL 5.6) Adding a non-nullable column (MySQL 5.6)

Slide 16

Slide 16 text

Activity plots Before applying schema operation Adding a non-nullable column (PG 9.4) Making a column non-nullable (MySQL 5.6) Adding a non-nullable column (MySQL 5.6)

Slide 17

Slide 17 text

Activity plots While applying schema operation Adding a non-nullable column (PG 9.4) Making a column non-nullable (MySQL 5.6) Adding a non-nullable column (MySQL 5.6)

Slide 18

Slide 18 text

Activity plots Adding a non-nullable column (PG 9.4) Making a column non-nullable (MySQL 5.6) Adding a non-nullable column (MySQL 5.6)

Slide 19

Slide 19 text

Activity plots Blocking Adding a non-nullable column (PG 9.4) Making a column non-nullable (MySQL 5.6) Adding a non-nullable column (MySQL 5.6)

Slide 20

Slide 20 text

Activity plots Read-only Blocking Adding a non-nullable column (PG 9.4) Making a column non-nullable (MySQL 5.6) Adding a non-nullable column (MySQL 5.6)

Slide 21

Slide 21 text

Activity plots Blocking Read-only Non-blocking Adding a non-nullable column (PG 9.4) Making a column non-nullable (MySQL 5.6) Adding a non-nullable column (MySQL 5.6)

Slide 22

Slide 22 text

MySQL 5.5 MySQL 5.6 PostgreSQL 9.3 PostgreSQL 9.4 S1 Read-only Non-Blocking Blocking Blocking S2 Read-only Non-Blocking Non-Blocking Non-Blocking S3 Read-only Non-Blocking Non-Blocking Non-Blocking S4 Read-only Non-Blocking Non-Blocking Non-Blocking S5 Read-only Non-Blocking Non-Blocking Non-Blocking S6 Read-only Non-Blocking Non-Blocking Non-Blocking S7 N/A N/A Non-Blocking Non-Blocking S8 N/A N/A Non-Blocking Non-Blocking S9 N/A N/A Blocking Blocking S10 Read-only Read-only Non-Blocking Non-Blocking S11 Read-only Read-only Blocking Blocking S12 Non-Blocking Non-Blocking Non-Blocking Non-Blocking S13 Non-Blocking Non-Blocking Non-Blocking Non-Blocking S14 Read-only Read-only Blocking Blocking S15 Read-only Read-only Blocking Blocking S16 Read-only Non-Blocking Non-Blocking Non-Blocking S17 N/A N/A Non-Blocking Blocking S18 Blocking Non-Blocking Non-Blocking Non-Blocking S19 Non-Blocking Non-Blocking Non-Blocking Non-Blocking

Slide 23

Slide 23 text

Observations Schema changes are high-risk CD leads to frequent schema changes We need to automate this process Are there any tools already out there?

Slide 24

Slide 24 text

OpenArk Kit TableMigrator pt-online-schema-change (Percona) Large Hadron Migrator (SoundCloud) Online Schema Change (Facebook) Gh-ost (GitHub) Existing tools

Slide 25

Slide 25 text

Limitations Only support MySQL None support defining changesets All use an atomic switchover None support Referential Integrity

Slide 26

Slide 26 text

R1: Non-Blocking Schema Changes R2: Schema Changesets R3: Concurrently Active Schemas R4: Referential Integrity R5: Schema Isolation R6: Non-Invasive R7: Resilience

Slide 27

Slide 27 text

QuantumDB

Slide 28

Slide 28 text

movies rentals customers Netflix 2.0

Slide 29

Slide 29 text

changelog.addChangeSet(“Add referral column to customers table”, addColumn(“customers”, “referred_by”, integer()), addForeignKey(“customers”, “referred_by”) .named(“customer_referred_by_fk”) .onDelete(NO_ACTION) .referencing(“customers”, “id”)); Defining changes

Slide 30

Slide 30 text

movies rentals customers Evolving the schema

Slide 31

Slide 31 text

movies rentals customers customers* Evolving the schema Forking the table under change

Slide 32

Slide 32 text

Ghost table: Structural copy of an existing table with schema changes already applied to it movies rentals customers customers* Evolving the schema Forking the table under change

Slide 33

Slide 33 text

movies rentals customers customers* Evolving the schema Forking the table under change

Slide 34

Slide 34 text

movies rentals rentals* customers customers* Evolving the schema Forking dependent tables

Slide 35

Slide 35 text

movies rentals* customers* rentals customers Evolving the schema A structurally forked database schema

Slide 36

Slide 36 text

movies rentals* customers* rentals customers Evolving the schema Creating forward database triggers (1-way sync)

Slide 37

Slide 37 text

movies rentals* customers* rentals customers Evolving the schema Copy data from original tables to their ghost tables

Slide 38

Slide 38 text

movies customers* rentals customers rentals* Evolving the schema Creating backward database triggers (2-way sync)

Slide 39

Slide 39 text

movies rentals* customers* rentals customers Accessing the database Original Schema

Slide 40

Slide 40 text

New Schema Original Schema movies rentals* customers* rentals customers Accessing the database Two co-existing database schemas

Slide 41

Slide 41 text

Application Driver Accessing the database

Slide 42

Slide 42 text

Application Wrapper Driver Accessing the database Intercepting database interactions

Slide 43

Slide 43 text

Connection connection = DriverManager.getConnection( “jdbc:postgresql://localhost:5432/database”, “username”, “password”); Connection connection = DriverManager.getConnection( “jdbc:quantumdb:postgresql://localhost:5432/database? version=80bfa11”,“username”, “password”); Accessing the database Intercepting database interactions

Slide 44

Slide 44 text

SELECT * FROM rentals WHERE customer_id = 2372 AND return_date < NOW() AND returned = false; SELECT * FROM rentals* WHERE customer_id = 2372 AND return_date < NOW() AND returned = false; Accessing the database Rewriting intercepted queries

Slide 45

Slide 45 text

movies rentals customers Dropping a version When it’s no longer needed rentals* customers*

Slide 46

Slide 46 text

movies rentals customers Dropping a version Drop the database triggers rentals* customers*

Slide 47

Slide 47 text

movies rentals* customers* Dropping a version Drop tables only used in the obsolete version

Slide 48

Slide 48 text

movies rentals* customers* Dropping a version Leaving you with the resulting schema New Schema

Slide 49

Slide 49 text

Evaluation

Slide 50

Slide 50 text

Experiments

Slide 51

Slide 51 text

MySQL 5.5 MySQL 5.6 PostgreSQL 9.3 PostgreSQL 9.4 PG 9.4 + QuantumDB S1 Read-only Non-Blocking Blocking Blocking Non-Blocking S2 Read-only Non-Blocking Non-Blocking Non-Blocking Non-Blocking S3 Read-only Non-Blocking Non-Blocking Non-Blocking Non-Blocking S4 Read-only Non-Blocking Non-Blocking Non-Blocking Non-Blocking S5 Read-only Non-Blocking Non-Blocking Non-Blocking Non-Blocking S6 Read-only Non-Blocking Non-Blocking Non-Blocking Non-Blocking S7 N/A N/A Non-Blocking Non-Blocking Non-Blocking S8 N/A N/A Non-Blocking Non-Blocking Non-Blocking S9 N/A N/A Blocking Blocking Non-Blocking S10 Read-only Read-only Non-Blocking Non-Blocking Non-Blocking S11 Read-only Read-only Blocking Blocking Non-Blocking S12 Non-Blocking Non-Blocking Non-Blocking Non-Blocking Non-Blocking S13 Non-Blocking Non-Blocking Non-Blocking Non-Blocking Non-Blocking S14 Read-only Read-only Blocking Blocking Non-Blocking S15 Read-only Read-only Blocking Blocking Non-Blocking S16 Read-only Non-Blocking Non-Blocking Non-Blocking Non-Blocking S17 N/A N/A Non-Blocking Blocking Non-Blocking S18 Blocking Non-Blocking Non-Blocking Non-Blocking Non-Blocking S19 Non-Blocking Non-Blocking Non-Blocking Non-Blocking Non-Blocking

Slide 52

Slide 52 text

Industry case

Slide 53

Slide 53 text

Changesets (11 month period ’15/‘16) of these contain blocking operations 95 37%

Slide 54

Slide 54 text

supported by QuantumDB partially supported not yet supported 64% 25% 11%

Slide 55

Slide 55 text

Due to missing support for Executing DML queries during migration User-defined functions User-defined database triggers User-defined views

Slide 56

Slide 56 text

Benchmarking “Normal” method Done using Liquibase Requires downtime in production (ie. serving error pages or limiting functionality) ~8 minutes in total for all 95 changesets (on test server without any load on the database)

Slide 57

Slide 57 text

Benchmarking QuantumDB method Done using QuantumDB No downtime required at all (ie. service can operate normally) ~2h 25m in total for all 95 changesets (on test server without any load on the database)

Slide 58

Slide 58 text

Where to next?

Slide 59

Slide 59 text

Add more support for views, functions, and triggers Start testing in a live production environment Which allows us to get real-world measurements, and developer input

Slide 60

Slide 60 text

Future research direction? Using QuantumDB to perform A/B testing of database schemas

Slide 61

Slide 61 text

Summary CD leads to frequent code/schema deploys Not all schema ops are safe in production We need a tool to do evolution safely QuantumDB is that tool

Slide 62

Slide 62 text

QuantumDB Zero-Downtime SQL Database Schema Evolution for Continuous Deployment Open-Source github.com/quantumdb quantumdb.io We need your help! Contribute code, insights, or more research