Share
Embed
[ICSE '17] Zero-Downtime SQL Database Schema Evolution for Continuous Deployment
Presentation given at ICSE 2017 in Buenos Aires in the "Software Engineering In Practice" track.
Michael de Jong
May 25, 2017
Transcript
62 slides
-
Michael de Jong Magnet.me Arie van Deursen Delft University of
Technology Anthony Cleve University of Namur -
Mixed-State How to deal with combinations of different versions of
the database schema and your application -
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 -
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 -
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 -
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) -
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) -
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) -
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) -
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) -
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) -
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) -
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 -
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? -
OpenArk Kit TableMigrator pt-online-schema-change (Percona) Large Hadron Migrator (SoundCloud) Online
Schema Change (Facebook) Gh-ost (GitHub) Existing tools -
Limitations Only support MySQL None support defining changesets All use
an atomic switchover None support Referential Integrity -
R1: Non-Blocking Schema Changes R2: Schema Changesets R3: Concurrently Active
Schemas R4: Referential Integrity R5: Schema Isolation R6: Non-Invasive R7: Resilience -
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 -
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 -
movies rentals* customers* rentals customers Evolving the schema A structurally
forked database schema -
movies rentals* customers* rentals customers Evolving the schema Creating forward
database triggers (1-way sync) -
movies rentals* customers* rentals customers Evolving the schema Copy data
from original tables to their ghost tables -
movies customers* rentals customers rentals* Evolving the schema Creating backward
database triggers (2-way sync) -
New Schema Original Schema movies rentals* customers* rentals customers Accessing
the database Two co-existing database schemas -
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 -
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 -
movies rentals customers Dropping a version When it’s no longer
needed rentals* customers* -
movies rentals customers Dropping a version Drop the database triggers
rentals* customers* -
movies rentals* customers* Dropping a version Drop tables only used
in the obsolete version -
movies rentals* customers* Dropping a version Leaving you with the
resulting schema New Schema -
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 -
Due to missing support for Executing DML queries during migration
User-defined functions User-defined database triggers User-defined views -
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) -
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) -
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 -
Future research direction? Using QuantumDB to perform A/B testing of
database schemas -
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 -
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