MySQL Online Schema Change 

Why? ● New features require schema changes ● Availability - offline migration locks up table 

Altering Table ALTER TABLE `raw_data_clicks` ADD `json` VARCHAR(256); CREATE INDEX `raw_data_conversions_app_id_f5b7e601_idx` ON `raw_data_conversions` (`app_id`, `publisher_user_id`); 

BuzzAd: Load on MySQL ● raw_data_conversions ○ ~150 million rows (~80GB) ○ ~3 write tps ● raw_data_clicks ○ ~1.8 billion rows (~800GB) ○ ~60 write tps No replicas 

Available Tools ● Built-in online schema migration on InnoDB ● Online Schema Change by Percona ● gh-ost by GitHub 

“Online” Schema Change for InnoDB “Improved responsiveness and availability in busy production environments, where making a table unavailable for minutes or hours is not practical.” 1. Keeps a temporary log to store inserted/updated/deleted data 2. Alters the table in-place 3. The log gets replayed on altered table 

“Online” Schema Change for InnoDB ● Reasons not to use ○ Limit on temporary log size ○ Not “online” enough ○ Table gets rebuilt ○ Poor documentation 

MySQL Trigger >CREATE TRIGGER pt_osc_lockjoyUS_raw_data_conversions_ins AFTER INSERT ON raw_data_conversions FOR EACH ROW REPLACE INTO `lockjoyUS`.`_raw_data_conversions_new` (`id`, `viewer_id`, `order_id`, `lineitem_id`, ...) >SHOW TRIGGERS IN lockjoyUS LIKE ‘%raw_data_conversions%’ Trigger: pt_osc_lockjoyUS_raw_data_conversions_ins Event: INSERT Table: raw_data_conversions Statement: REPLACE INTO `lockjoyUS`.`_raw_data_conversions_new` (`id`, `viewer_id`, `order_id`, `lineitem_id`, ...) VALUES (NEW.`id`, NEW.`viewer_id`, NEW.`order_id`, NEW.`lineitem_id`, ...) Timing: AFTER 

Online Schema Change(OSC) by Percona Trigger-based tool 1. Create ‘ghost table’ with altered schema 2. Repeat until original and ghost table converge a. Create INSERT/UPDATE/DELETE triggers on original table b. Copy existing data on original table to the ghost table 3. Cutover(swap the ghost table with original) 

Online Schema Change(OSC) by Percona $ pt-online-schema-change --alter "ADD COLUMN app_id bigint NULL, ADD COLUMN publisher_user_id varchar(500) NULL" D=lockjoyUS,t=raw_data_conversions --host= --port= --user= --recursion-method=none --no-drop-old-table --no-drop-new-table --ask-pass --chunk-size=3000 --chunk-size-limit=3000 --max-load="Threads_running=100" --critical-load="Threads_running=1000" --chunk-index=PRIMARY --execute 

Online Schema Change(OSC) by Percona Copying `lockjoyUS`.`raw_data_conversions`: 43% 08:40:08 remain Copying `lockjoyUS`.`raw_data_conversions`: 43% 08:40:40 remain Copying `lockjoyUS`.`raw_data_conversions`: 43% 08:42:10 remain 2018-05-03T15:04:12 Dropping triggers... 2018-05-03T15:04:14 Dropped triggers OK. Not dropping the new table `lockjoyUS`.`_raw_data_conversions_new` because --no-drop-new-table was specified. To drop the new table, execute: DROP TABLE IF EXISTS `lockjoyUS`.`_raw_data_conversions_new`; `lockjoyUS`.`raw_data_conversions` was not altered. 2018-05-03T15:04:12 Error copying rows from `lockjoyUS`.`raw_data_conversions` to `lockjoyUS`.`_raw_data_conversions_new`: Threads_running=1845 exceeds its critical threshold 1000 

Online Schema Change(OSC) by Percona ● Pros ○ Intuitive implementation ○ Works if DB load is uniform ● Cons ○ Synchronous ○ No batch operations ○ Can’t pause 

gh-ost by GitHub Triggerless, binary log-based 1. “Ghost table” created with altered schema 2. Repeat until original and ghost table converge a. Stream binlog from original table and apply on the ghost table b. Copy rows from original table onto ghost table 3. Cutover(swap the ghost table with original) 

MySQL Binary Log ● Contains events that describes DB changes ● Used for replication and data recovery ● Types ○ Statement-based ○ Row-based ○ Mixed 

gh-ost ● Pros ○ Asynchronous ○ Able to pause ● Cons ○ Requires row-based binary log 

What now? ● Enable row-based binlog on MySQL ● Gain confidence with gh-ost on staging DB ● Use it on production