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

[CHISEL] Introduction to zero-downtime schema e...

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

Michael de Jong

October 16, 2014
Tweet

More Decks by Michael de Jong

Other Decks in Programming

Transcript

  1. Relevance • Web applications • Healthcare systems • Banking /

    financial systems • Air travel / booking systems • Anything which is too expensive to go offline • Continuous Deployment
  2. • OpenArk kit • Percona toolkit • Large Hadron Migrator

    • Table Migrator • Online Schema Change Solutions Tools from industry All work more or less 
 the same way
  3. • PRISM++ • IMAGO • Column-oriented databases (noSQL) • Google’s

    Spanner & F1 database (newSQL) Solutions Academic research
  4. 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?
  5. 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
  6. 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.
  7. Time (100ms periods) Duration of queries (1px = 1ms) Period

    where DDL statement is executing Colors represent different 
 types of DML queries
  8. 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
  9. 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
  10. 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.
  11. 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.