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

[CHISEL] Introduction to zero-downtime schema evolution in SQL databases.

[CHISEL] Introduction to zero-downtime schema evolution in SQL databases.

Presentation on zero-downtime schema evolution in SQL databases. A short introduction, current tools, research, and approach to deal with this problem.

4c65fc25a7ba3ba7689ce3a040f200c5?s=128

Michael de Jong

October 16, 2014
Tweet

More Decks by Michael de Jong

Other Decks in Programming

Transcript

  1. Zero-downtime schema evolution in SQL databases

  2. Michael de Jong @java_devver

  3. Zero-downtime schema evolution in SQL databases You remember these, right?

  4. Zero-downtime schema evolution in SQL databases Modifying tables, columns, indices,

    constraints, yada, yada, yada…
  5. Zero-downtime schema evolution in SQL databases Without disruption, or performance

    degradation!
  6. Relevance • Web applications • Healthcare systems • Banking /

    financial systems • Air travel / booking systems • Anything which is too expensive to go offline • Continuous Deployment
  7. Current state It’s not that pretty…

  8. • Blue-Green 
 deployments • Expand-Contract 
 deployments Approaches

  9. • OpenArk kit • Percona toolkit • Large Hadron Migrator

    • Table Migrator • Online Schema Change Solutions Tools from industry All work more or less 
 the same way
  10. 1. Current situation

  11. 1. Current situation

  12. 2. Create clone of table

  13. 3. Copy data to new table

  14. 3. Copy data to new table

  15. 4. Atomically rename table

  16. • PRISM++ • IMAGO • Column-oriented databases (noSQL) • Google’s

    Spanner & F1 database (newSQL) Solutions Academic research
  17. Challenges • Referential integrity support. • Versioning schema changes. •

    Rolling back failures. • Complex and mostly manual task. • Mostly tools only support MySQL. • Switching to alternative database can be costly. What could go wrong?
  18. What do we do now?

  19. Approach 1. Create tool to test databases for blocking behaviour.

    2. Profile commonly used SQL databases. 3. Prototype solution to deal with schema changes. 4. Verify prototype shows no blocking behaviour with said profiling tool. 5. Test in production environments / case studies. Run away if that fails
  20. Nemesis 1. Create a table and insert 100m records. 2.

    “Simulate load” by barraging the database with SELECT, UPDATE, INSERT, and DELETE queries. 3. Execute a DDL query to modify the table’s structure. 4. Record all start and end times for every query. 5. Plot the results.
  21. Time (100ms periods) Duration of queries (1px = 1ms) Period

    where DDL statement is executing Colors represent different 
 types of DML queries
  22. The results I have no idea what I’m doing…

  23. Add nullable column Add non-nullable column Rename nullable column Rename

    non-nullable column Drop nullable column Drop non-nullable column Create index Rename index Drop index ! Make column nullable Make column non-nullable Set default on nullable column Set default on non-nullable column Modify type on nullable column Modify type on non-nullable column Modify type from int to text Add non-nullable foreign key Add nullable foreign key ! Non-blocking Semi-blocking Blocking Too fast to profile
  24. Add nullable column Add non-nullable column Rename nullable column Rename

    non-nullable column Drop nullable column Drop non-nullable column Create index Rename index Drop index ! Make column nullable Make column non-nullable Set default on nullable column Set default on non-nullable column Modify type on nullable column Modify type on non-nullable column Modify type from int to text Add non-nullable foreign key Add nullable foreign key ! Non-blocking Semi-blocking Blocking Too fast to profile
  25. Take away • Don’t ever trust DDL statements 
 that

    operate on a “live” table. • Unless you have a very intimate 
 understanding of your own SQL database. • And then still don’t trust DDL statements.
  26. What’s next? Been there, done that…

  27. Next up • Discuss results on mailing lists 
 of

    profiled SQL databases. • I really should start blogging about this… • Lock myself up in a room, 
 and start working on a prototype.