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

[ICSE '17] Zero-Downtime SQL Database Schema Evolution for Continuous Deployment

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

4c65fc25a7ba3ba7689ce3a040f200c5?s=128

Michael de Jong

May 25, 2017
Tweet

More Decks by Michael de Jong

Other Decks in Research

Transcript

  1. Zero-Downtime SQL Database Schema Evolution for Continuous Deployment

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

    Technology Anthony Cleve University of Namur
  3. None
  4. 30+ 1.200+ 90.000+ 2 Employees Companies Students Countries

  5. Full-time developers (Micro)services Master is green? deploy! No manual testing!

    6 20+
  6. We deploy frequently and we’re not the only ones [3][13]

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

  8. version deploys/month schema changes/month ~1.400 ~16 Roughly doubled since writing

    the paper
  9. Blocking schema operations Why are my schema changes crashing my

    service?
  10. Mixed-State How to deal with combinations of different versions of

    the database schema and your application
  11. 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
  12. 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
  13. Understanding behaviour blocking

  14. 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
  15. 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)
  16. 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)
  17. 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)
  18. 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)
  19. 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)
  20. 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)
  21. 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)
  22. 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
  23. 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?
  24. OpenArk Kit TableMigrator pt-online-schema-change (Percona) Large Hadron Migrator (SoundCloud) Online

    Schema Change (Facebook) Gh-ost (GitHub) Existing tools
  25. Limitations Only support MySQL None support defining changesets All use

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

    Schemas R4: Referential Integrity R5: Schema Isolation R6: Non-Invasive R7: Resilience
  27. QuantumDB

  28. movies rentals customers Netflix 2.0

  29. 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
  30. movies rentals customers Evolving the schema

  31. movies rentals customers customers* Evolving the schema Forking the table

    under change
  32. 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
  33. movies rentals customers customers* Evolving the schema Forking the table

    under change
  34. movies rentals rentals* customers customers* Evolving the schema Forking dependent

    tables
  35. movies rentals* customers* rentals customers Evolving the schema A structurally

    forked database schema
  36. movies rentals* customers* rentals customers Evolving the schema Creating forward

    database triggers (1-way sync)
  37. movies rentals* customers* rentals customers Evolving the schema Copy data

    from original tables to their ghost tables
  38. movies customers* rentals customers rentals* Evolving the schema Creating backward

    database triggers (2-way sync)
  39. movies rentals* customers* rentals customers Accessing the database Original Schema

  40. New Schema Original Schema movies rentals* customers* rentals customers Accessing

    the database Two co-existing database schemas
  41. Application Driver Accessing the database

  42. Application Wrapper Driver Accessing the database Intercepting database interactions

  43. 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
  44. 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
  45. movies rentals customers Dropping a version When it’s no longer

    needed rentals* customers*
  46. movies rentals customers Dropping a version Drop the database triggers

    rentals* customers*
  47. movies rentals* customers* Dropping a version Drop tables only used

    in the obsolete version
  48. movies rentals* customers* Dropping a version Leaving you with the

    resulting schema New Schema
  49. Evaluation

  50. Experiments

  51. 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
  52. Industry case

  53. Changesets (11 month period ’15/‘16) of these contain blocking operations

    95 37%
  54. supported by QuantumDB partially supported not yet supported 64% 25%

    11%
  55. Due to missing support for Executing DML queries during migration

    User-defined functions User-defined database triggers User-defined views
  56. 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)
  57. 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)
  58. Where to next?

  59. 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
  60. Future research direction? Using QuantumDB to perform A/B testing of

    database schemas
  61. 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
  62. 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