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

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