Working with Huge Databases and Tables

Alternate Title: Zero-downtime Migration on Big Database

Presented at [TOKYO Rails #39](https://www.meetup.com/tokyo-rails/events/259500572/) on April 16, 2019.

Transcript

1. Working with Huge
   Databases and Tables
   Prem Sichanugrist (@sikachu)
   Tokyo Rails #39
   2019-04-16
   

   View Slide

2. Prem Sichanugrist
   @sikachu
   /sikachu
   

   View Slide

3. Working with Huge
   Databases and Tables
   

   View Slide

4. Migrations
   

   View Slide

5. Zero-Downtime Migrations
   

   View Slide

6. Why Zero-Downtime Migrations?
   

   View Slide

7. Why Zero-Downtime Migrations?
   ● Global service with users around the world
   ○ Users using the service 27/4 from different time zones
   ○ We want our service to always be available
   

   View Slide

8. Why Zero-Downtime Migrations?
   ● Global service with users around the world
   ○ Users using the service 27/4 from different time zones
   ○ We want our service to always be available
   ● Normal migration process locks table
   ○ We have a lot of user data (~300 GB on disk, biggest table has 500M rows)
   ○ Normal migration locks the whole table and could take service down
   

   View Slide

9. Why Zero-Downtime Migrations?
   ● Global service with users around the world
   ○ Users using the service 27/4 from different time zones
   ○ We want our service to always be available
   ● Normal migration process locks table
   ○ We have a lot of user data (~300 GB on disk, biggest table has 500M rows)
   ○ Normal migration locks the whole table and could take service down
   ● We want to be able to do continuous deployment
   ○ No “code freeze”, every merges to master goes to production right away
   ○ Normal migration will cause deployment disruption throughout the day
   

   View Slide

10. Zero-Downtime Migrations
    

    View Slide

11. Percona Online Schema Change
    

    View Slide

12. Percona Online Schema Change
    ● Also known as: pt-osc
    

    View Slide

13. Percona Online Schema Change
    ● Also known as: pt-osc
    ● Part of Percona Toolkit (http://www.percona.com/software/)
    ○ Collection of advanced command-line tools used by Percona support staff — README.md
    

    View Slide

14. Percona Online Schema Change
    ● Also known as: pt-osc
    ● Part of Percona Toolkit (http://www.percona.com/software/)
    ○ Collection of advanced command-line tools used by Percona support staff — README.md
    ● Provide command to perform online schema change
    ○ Create new temporary table with updated schema
    ○ Copy data over to the new table
    ○ Drop old table, and rename temporary table to old table’s name (atomic)
    

    View Slide

15. Percona Online Schema Change
    pt-online-schema-change \
    --alter "ADD COLUMN column_name VARCHAR(255)" \
    D=database_name,t=table_name
    https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html
    

    View Slide

16. Percona Online Schema Change
    

    View Slide

17. Percona Online Schema Change
    + Rails
    

    View Slide

18. Departure
    https://github.com/departurerb/departure
    

    View Slide

19. Departure
    ● Hooks into Active Record's connection adapter syntax
    ○ No need to write custom migration!
    

    View Slide

20. Departure
    ● Hooks into Active Record's connection adapter syntax
    ○ No need to write custom migration!
    ● Construct arguments for pt-online-schema-change
    ○ Then runs pt-online-schema-change to perform the migration
    

    View Slide

21. View Slide

22. View Slide

23. View Slide

24. One Morning in Slack
    ● A deploy to staging was going on for 3 hours.
    

    View Slide

25. One Morning in Slack
    ● A deploy to staging was going on for 3 hours
    ● Deployment was stuck at migration step
    ○ SRE team started investigate
    

    View Slide

26. One Morning in Slack
    ● A deploy to staging was going on for 3 hours
    ● Deployment was stuck at migration step
    ○ We tried to add a new column to a table that has about 100 million rows
    

    View Slide

27. Percona Online Schema Change
    ● Also known as: pt-osc
    ● Part of Percona Toolkit (http://www.percona.com/software/)
    ○ Collection of advanced command-line tools used by Percona support staff — README.md
    ● Provide command to perform online schema change
    ○ Create new temporary table with updated schema
    ○ Copy data over to the new table
    ○ Drop old table, and rename temporary table to old table’s name (atomic)
    

    View Slide

28. One Morning in Slack
    ● A deploy to staging was going on for 3 hours
    ● Deployment was stuck at migration step
    ○ We tried to add a new column to a table that has about 100M rows
    ○ It takes a long time to copy over data of 100M rows to the new table
    

    View Slide

29. One Morning in Slack
    ● A deploy to staging was going on for 3 hours
    ● Deployment was stuck at migration step
    ○ We tried to add a new column to a table that has about 100M rows
    ○ It takes a long time to copy over data of 100M rows to the new table
    ● Deployment canceled, SRE team took over the database migration
    ○ SRE team runs pt-online-schema-change directly on the server
    

    View Slide

30. View Slide

31. InnoDB Online DDL Operations
    https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
    

    View Slide

32. InnoDB Online DDL Operations
    ● Available from MySQL 5.6+
    ○ We are using Amazon RDS for MySQL 5.6+
    

    View Slide

33. InnoDB Online DDL Operations
    ● Available from MySQL 5.6+
    ○ We are using Amazon RDS for MySQL 5.6+
    ● Various operations can be performed online
    ○ CREATE INDEX, DROP INDEX
    ○ ADD COLUMN, DROP COLUMN*
    ○ Rename column, reorder column*
    ○ SET DEFAULT, DROP DEFAULT, change column nullability
    ○ Set AUTO_INCREMENT*
    ○ Add/remove foreign key
    ○ RENAME TABLE*
    

    View Slide

34. InnoDB Online DDL Operations
    ● Available from MySQL 5.6+
    ○ We are using Amazon RDS for MySQL 5.6+
    ● Various operations can be performed online
    ○ CREATE INDEX, DROP INDEX, ADD COLUMN, DROP COLUMN, …
    ● Some DDL queries need to be modified to run online
    

    View Slide

35. InnoDB Online DDL Operations
    ALTER TABLE tbl_name ADD COLUMN column_name
    column_definition;
    ALTER TABLE tbl_name ADD COLUMN column_name
    column_definition, ALGORITHM=INPLACE, LOCK=NONE;
    

    View Slide

36. InnoDB Online DDL Operations
    ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal;
    ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal,
    ALGORITHM=INPLACE, LOCK=NONE;
    

    View Slide

37. InnoDB Online DDL Operations
    CREATE INDEX name ON table (col_list);
    DROP INDEX name ON table; -- Already online operations
    

    View Slide

38. InnoDB Online DDL Operations
    + Rails
    

    View Slide

39. Index Operations
    ● CREATE INDEX, DROP INDEX
    

    View Slide

40. Index Operations
    ● CREATE INDEX, DROP INDEX
    ● Disable Departure, migration can be run during deploy if not too long
    

    View Slide

41. Column Operations
    ● ADD COLUMN, DROP COLUMN, rename column, change default value
    

    View Slide

42. Column Operations
    ● ADD COLUMN, DROP COLUMN, rename column, change default value
    ● Can’t run directly as Rails’ generated SQL locks table
    

    View Slide

43. Column Operations
    ● ADD COLUMN, DROP COLUMN, rename column, change default value
    ● Can’t run directly as Rails’ generated SQL locks table
    ● Open new issue with SRE team, asking to run migration DDL with
    ALGORITHM=INPLACE, LOCK=NONE;
    ○ Then, perform INSERT into schema_migrations with migration version directly
    

    View Slide

44. View Slide

45. View Slide

46. Online DDL Operations are faster, but ...
    ● We now require SRE’s help to perform migration tasks
    

    View Slide

47. Online DDL Operations are faster, but ...
    ● We now require SRE’s help to perform migration tasks
    ● Too many steps to perform by human
    ○ Open PR, get it approved
    ○ Open ticket with SRE with online SQL command to run
    ○ SRE has to run command in MySQL console, has to manually insert migration version
    ○ Merges PR
    

    View Slide

48. Online DDL Operations are faster, but ...
    ● We now require SRE’s help to perform migration tasks
    ● Too many steps required to perform by human
    ○ Open PR, get it approved
    ○ Open ticket with SRE with online SQL command to run
    ○ SRE has to run command in MySQL console, has to manually insert migration version
    ○ Merges PR
    ○ Mistake can happen if not done properly
    

    View Slide

49. View Slide

50. View Slide

51. Improvements
    ● Create Departure-like gem to hook into Active Record’s Migration
    ○ Generate SQL that tells MySQL to perform non-locking DDL operations
    ○ Migration runs automatically during deployment just like normal migrations
    ○ No need to ask for SRE help
    

    View Slide

52. Improvements
    ● Create Departure-like gem to hook into Active Record’s Migration
    ○ Generate SQL that tells MySQL to perform non-locking DDL operations
    ○ Migration runs automatically during deployment just like normal migrations
    ○ No need to ask for SRE help
    ● Separate migration deploy from code deployment
    ○ Prevent blocking code deployment to production
    ○ Deploy database migration first on a separate deploy process
    ■ Mark that migration as finished, so migration in code PR will be no-op
    ○ Needs to write code such that it still works before and after database migration
    ■ Multiple steps process for renaming column/table.
    

    View Slide

53. Alternatives
    ● Use external tool to manage database schema instead
    ○ Ridgepole (https://github.com/winebarrel/ridgepole)
    ○ Store database schema in a separate database
    ○ Database migrations can be apply independently from code changes
    

    View Slide

54. Prem Sichanugrist
    @sikachu
    /sikachu
    Thank you!
    All Illustrations are from いらすとや (https://www.irasutoya.com)
    

    View Slide