Upgrade to Pro — share decks privately, control downloads, hide ads and more …

[ICSE '17] Zero-Downtime SQL Database Schema Ev...

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

More Decks by Michael de Jong

Other Decks in Research

Transcript

  1. Michael de Jong Magnet.me Arie van Deursen Delft University of

    Technology Anthony Cleve University of Namur
  2. 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
  3. 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
  4. 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
  5. 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)
  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)
  7. 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)
  8. 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)
  9. 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)
  10. 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)
  11. 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)
  12. 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
  13. 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?
  14. Limitations Only support MySQL None support defining changesets All use

    an atomic switchover None support Referential Integrity
  15. R1: Non-Blocking Schema Changes R2: Schema Changesets R3: Concurrently Active

    Schemas R4: Referential Integrity R5: Schema Isolation R6: Non-Invasive R7: Resilience
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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
  21. Due to missing support for Executing DML queries during migration

    User-defined functions User-defined database triggers User-defined views
  22. 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)
  23. 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)
  24. 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
  25. 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
  26. 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