Massive Schema Changes in Facebook Jesse Salomon, Junyi Lu - - PowerPoint PPT Presentation

▶
massive schema changes in facebook
SMART_READER_LITE
LIVE PREVIEW

Massive Schema Changes in Facebook Jesse Salomon, Junyi Lu - - PowerPoint PPT Presentation

Oct 05, 2022 361 likes •1.77k views

Massive Schema Changes in Facebook Jesse Salomon, Junyi Lu Software Engineer, Production Engineer Agenda How O Online Schema Change Online Schema Change Why Why not MySQL's native online DDL? - cannot cover all our use cases - no resource

slide-1
SLIDE 1
slide-2
SLIDE 2

Massive Schema Changes in Facebook

Jesse Salomon, Junyi Lu

Software Engineer, Production Engineer

slide-3
SLIDE 3

Agenda

How O

slide-4
SLIDE 4

Online Schema Change

slide-5
SLIDE 5

Online Schema Change

Why Why not MySQL's native online DDL?

  • cannot cover all our use cases
  • no resource control
slide-6
SLIDE 6

Existing approach

  • pt-online-schema-change
  • Large Hadron Migrator(LHM)
  • Github's Online Schema Change (gh-ost)?

2011 2011 2016

Online Schema Change

slide-7
SLIDE 7

OSC.php (2009 - 2016) Core logic is written in PHP First open sourced in Sep 2010

https://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932/

A wrapper is required Hard to test

Online Schema Change

slide-8
SLIDE 8

Online Schema Change

▪ Well tested (unittest/integration test) & bug fix ▪ Checksum ▪ Hook system ▪ Unicode

OSC.py (2016 - ?)

CREATE TABLE `(ﾉ≧∇≦)ﾉ ﾐ ┸━┸` ...

slide-9
SLIDE 9

▪ Out of replication ▪ Async dump & load ▪ Trigger based

Online Schema Change

What is OSC?

S S M S S S S M

slide-10
SLIDE 10

Online Schema Change

How to use

Cats

ID int PRIMARY Name varchar(10) LastUpdated timestamp

Cats

ID int PRIMARY Name varchar(10) PictureURL varchar(30) LastUpdated timestamp

slide-11
SLIDE 11

Online Schema Change

How to use >> osc --database cats_db --ddl-file-list /path/to/a.sql

CREATE TABLE Cats ( ID INT NOT NULL, Name VARCHAR(10), PictureURL VARCHAR(30), LastUpdated TIMESTAMP, PRIMARY (ID) ) ENGINE=INNODB ALTER TABLE Cats ADD COLUMN PictureURL VARCHAR(30)

slide-12
SLIDE 12

INIT DUMP LOAD CHECKS UM SWAP CLEANU P Init Dump Load Checksum Swap Cleanup

OnlineSchemaChange

slide-13
SLIDE 13

Sanity check:

  • Primary key/Unique key (required)
  • Foreign key (not supported)
  • Table existence
  • Accidentally drop column

OnlineSchemaChange

Init Dump Load Checksum Swap Cleanup

slide-14
SLIDE 14

OnlineSchemaChange OnlineSchemaChange

Cats

CatID int PRIMARY Name varchar(10) LastUpdated timestamp

__osc_chg_Cats

ID int PRIMARY DML_TYPE ENUM CatID int Name varchar(10) LastUpdated timestamp

__osc_new_Cats

CatID int PRIMARY Name varchar(10) PictureURL varchar(20) LastUpdated timestamp

Init Dump Load Checksum Swap Cleanup

slide-15
SLIDE 15

Cats

MySQL

__osc_new_Cats __osc_chg_Cats

INSERT/UPDATE

OnlineSchemaChange

Init Dump Load Checksum Swap Cleanup

after_insert after_update after_delete

slide-16
SLIDE 16

OnlineSchemaChange

ID DML_TYPE CatID Name LastUpdated

1 DML_TYPE_INSERT 1 foo 2016-10-02 00:00:00

INSERT INTO Cats VALUES (1, 'foo')

Init Dump Load Checksum Swap Cleanup

slide-17
SLIDE 17

OnlineSchemaChange

DELETE FROM Cats WHERE CatID = 1

Init Dump Load Checksum Swap Cleanup

ID DML_TYPE CatID Name LastUpdated

2 DML_TYPE_DELETE 1

slide-18
SLIDE 18

OnlineSchemaChange

UPDATE Cats set Name = 'bar' WHERE CatID = 2

ID DML_TYPE CatID Name LastUpdated

3 DML_TYPE_UPDATE 2 bar 2016-10-02 00:00:00

Init Dump Load Checksum Swap Cleanup

slide-19
SLIDE 19

OnlineSchemaChange

UPDATE Cats set CatID = 4, Name = 'foobar' WHERE CatID = 3

ID DML_TYPE CatID Name LastUpdated

4 DML_TYPE_DELETE 3 5 DML_TYPE_INSERT 4 foobar 2016-10-02 00:00:00

Init Dump Load Checksum Swap Cleanup

slide-20
SLIDE 20

OnlineSchemaChange

ID CHANGE_TYPE CatID Name LastUpdated

1 DML_TYPE_INSERT 1 foo 2016-10-02 00:00:00 2 DML_TYPE_DELETE 1 3 DML_TYPE_UPDATE 3 bar 2016-10-02 00:00:00 4 DML_TYPE_DELETE 3 5 DML_TYPE_INSERT 4 foobar 2016-10-02 00:00:00

Init Dump Load Checksum Swap Cleanup

INSERT INTO Cats VALUES (1, 'foo') DELETE FROM Cats WHERE CatID = 1 UPDATE Cats set Name = 'new_foo' WHERE CatID = 3 UPDATE Cats set CatID = 4, Name = 'foobar' WHERE CatID = 3

slide-21
SLIDE 21

OnlineSchemaChange

Cats UUID: 1-1000

MySQL

_osc_new_Cats _osc_chg_Cats Init Dump Load Checksum Swap Cleanup

Disk START TRANSACTION

slide-22
SLIDE 22

OnlineSchemaChange

Cats UUID: 1-1000

Disk MySQL

_osc_new_Cats _osc_chg_Cats

SELECT INTO OUTFILE

Cats.1 Cats.N ... Init Dump Load Checksum Swap Cleanup

UUID: 1-1000

slide-23
SLIDE 23

OnlineSchemaChange

Cats UUID: 1-1000 _osc_chg_Cats

INSERT/UPDATE

Init Dump Load Checksum Swap Cleanup Cats.1 Cats.N ...

Disk MySQL

_osc_new_Cats

UUID: 1-1000

slide-24
SLIDE 24

_osc_new_Cats _osc_new_Cats _osc_new_Cats

OnlineSchemaChange

_osc_chg_Cats

LOAD DATA INFILE

Cats.1 Cats.N ... _osc_new_Cats UUID: 1-1000

Disk MySQL UUID: 1-1000

Cats UUID: 1-1000 Cats UUID: 1-2000

slide-25
SLIDE 25

Replay

slide-26
SLIDE 26

OnlineSchemaChange

Cats UUID: 1-2000

MySQL

_osc_chg_Cats _osc_new_tbl UUID: 1-1000

Disk

_osc_new_tbl UUID: 1-1000 _osc_new_Cats UUID: 1-2000 Checksum

slide-27
SLIDE 27

OnlineSchemaChange

ID DML_TYPE CatID(PRIMARY) Name LastUpdated

1 DML_TYPE_INSERT 1 foo 2016-10-02 00:00:00

INSERT INTO __osc_new_Cats (CatID, Name, LastUpdated) SELECT CatID, Name, LastUpdated FROM __osc_chg_Cats WHERE ID = 1;

Checksum

slide-28
SLIDE 28

OnlineSchemaChange

DELETE __osc_new_Cats FROM __osc_chg_Cats, __osc_new_Cats WHERE __osc_chg_Cats.CatID = __osc_new_Cats.CatID AND ID = 2

ID DML_TYPE CatID(PRIMARY) Name LastUpdated

2 DML_TYPE_DELETE 2

Checksum

slide-29
SLIDE 29

OnlineSchemaChange

UPDATE __osc_new_Cats, __osc_chg_Cats SET __osc_new_Cats.Name = __osc_chg_Cats.Name WHERE __osc_new_Cats.CatID = __osc_chg_Cats.CatID AND ID=3 AND

ID DML_TYPE CatID(PRIMARY) Name LastUpdated

3 DML_TYPE_UPDATE 3 bar 2016-10-02 00:00:00

Checksum

slide-30
SLIDE 30

_osc_new_Cats UUID: 1-2000 _osc_new_Cats UUID: 1-2200 Cats UUID: 1-2500

START TRANSACTION

__osc_chg_Cat s

OnlineSchemaChange

_osc_new_Cats UUID: 1-2500

slide-31
SLIDE 31

OnlineSchemaChange

Cats __osc_new_Cat s Cats Cats Cats __osc_new_Cat s __osc_new_Cat s __osc_new_Cat s

PRIMARY KEY BIT_XOR(CRC32(c

  • l))

E1397DA2 8E

__osc_new_Cat s

BIT_XOR(CRC32(col)) E1397DA28E

Cats

slide-32
SLIDE 32

OnlineSchemaChange

Stage Changes Stacked Time Spent in Replay

Dump 10000 Load 20000 Replay 5000 500 Seconds Checksum 10000 Replay Round 1 2000 200 Seconds Replay Round 2 1000 50 Seconds Replay Round 3 100 5 Seconds Replay Round 4 1 0.1 Seconds

slide-33
SLIDE 33

OnlineSchemaChange

Cats UUID: 1-4000

MySQL

_osc_new_Cats UUID: 1-3990 _osc_chg_Cats _osc_new_Cats UUID: 1-4000

slide-34
SLIDE 34

OnlineSchemaChange

Cats UUID: 1-4000

MySQL

_osc_new_Cats UUID: 1-4000 Cats UUID: 1-4000 _osc_old_Cats UUID: 1-4000 _osc_chg_Cats

slide-35
SLIDE 35

_osc_chg_Cats

OnlineSchemaChange

_osc_old_Cats Cats

MySQL Cats

ID int PRIMARY Name varchar(10) PictureURL varchar(30) LastUpdated timestamp

slide-36
SLIDE 36

Hooks

slide-37
SLIDE 37

OnlineSchemaChange

Hook

Internal System OSC post_init_stage hook

INIT DUMP LOAD REPLAY SWAP Audit System Alarm System Stats Collecto r

slide-38
SLIDE 38

OnlineSchemaChange

Hook

Internal System OSC post_load_chunk hook

INIT DUMP LOAD REPLAY SWAP Audit System Alarm System Stats Collecto r

slide-39
SLIDE 39

OnlineSchemaChange

Hook

INIT DUMP

Internal System OSC post_swap hook

LOAD REPLAY SWAP Audit System Alarm System Stats Collecto r

slide-40
SLIDE 40

OnlineSchemaChange

What's coming

▪ No more triggers by using RBR ▪ Real time resource control ▪ Parallel mode ▪ Heuristic decision ▪ To be open sourced

https://github.com/github/gh-ost/blob/master/doc/why-triggerless.md

slide-41
SLIDE 41

Demo

slide-42
SLIDE 42

Automatic Online Schema Change

slide-43
SLIDE 43

Schema Changes at Scale

Problem

OSC

slide-44
SLIDE 44

Schema Changes at Scale

Problem

OSC

slide-45
SLIDE 45

Schema Changes at Scale

Problem

OSC

slide-46
SLIDE 46

Schema Changes at Scale

Problem

slide-47
SLIDE 47

Schema Changes at Scale

Problem

slide-48
SLIDE 48

Schema Changes at Scale

Problem

slide-49
SLIDE 49

Schema Changes at Scale

Problem

slide-50
SLIDE 50

Schema Changes at Scale

Problem

slide-51
SLIDE 51

Schema Changes at Scale

Problem

slide-52
SLIDE 52

Schema Changes at Scale

Problem

slide-53
SLIDE 53

Schema Changes at Scale

Problem

slide-54
SLIDE 54

Schema Changes at Scale

Problem

slide-55
SLIDE 55

Schema Changes at Scale

Problem

megadb

slide-56
SLIDE 56

Schema Changes at Scale

Problem

megadb xdb.cats xdb.dogs xdb.food

slide-57
SLIDE 57

Schema Changes at Scale

Goal

▪ A given table on a given tier should have an eventually

consistent schema across all relevant databases

▪ Self-service ▪ Fast ▪ Safe

slide-58
SLIDE 58

Automatic OSC

Solution

AOSC Server

slide-59
SLIDE 59

Automatic OSC

Solution

AOSC Server

slide-60
SLIDE 60

Automatic OSC

Git Repository

AOSC Server

slide-61
SLIDE 61

Automatic OSC

Git Repository

AOSC Server

~/mysql-schemas $ ls megadb xdb.cats xdb.dogs xdb.food ~/mysql-schemas $ ls xdb.food apple banana pear ~/mysql-schemas $ cat xdb.food/apple CREATE TABLE `apple` ( `id` int(11) AUTO_INCREMENT, `name` varchar(32), `quantity` int(11), PRIMARY KEY (`id`) ) ENGINE=RocksDB

slide-62
SLIDE 62

Automatic OSC

Git Repository

AOSC Server

~/mysql-schemas $ cat xdb.food/apple CREATE TABLE `apple` ( `id` int(11) AUTO_INCREMENT, `name` varchar(32), `quantity` int(11), PRIMARY KEY (`id`) ) ENGINE=RocksDB ~/mysql-schemas $ ... ~/mysql-schemas $ git diff CREATE TABLE `apple` ( `id` int(11) AUTO_INCREMENT, `name` varchar(32), `quantity` int(11), + `color` enum('red','green'), PRIMARY KEY (`id`) ) ENGINE=RocksDB

slide-63
SLIDE 63

Automatic OSC

~/mysql-schemas $ ... ~/mysql-schemas $ git diff CREATE TABLE `apple` ( `id` int(11) AUTO_INCREMENT, `name` varchar(32), `quantity` int(11), + `color` enum('red','green'), PRIMARY KEY (`id`) ) ENGINE=RocksDB

▪ Versioning ▪ Code review ▪ Commit hook

Git Repository

AOSC Server

slide-64
SLIDE 64

Automatic OSC

User Interface

AOSC Server

~/mysql-schemas $ ... ~/mysql-schemas $ git diff CREATE TABLE `apple` ( `id` int(11) AUTO_INCREMENT, `name` varchar(32), `quantity` int(11), + `color` enum('red','green'), PRIMARY KEY (`id`) ) ENGINE=RocksDB ~ $ aosc allow xdb.food apple ... ~ $ aosc cancel xdb.food apple ... ~ $ aosc hold xdb.food apple ... ~ $ aosc continue xdb.food apple ...

slide-65
SLIDE 65

Finished Current Pending

Automatic OSC

Global Coordination

AOSC Server

slide-66
SLIDE 66

Automatic OSC

Alter-Level Coordination

Current Pending Finished

slide-67
SLIDE 67

Automatic OSC

Alter-Level Coordination

Current Pending Finished

slide-68
SLIDE 68

Automatic OSC

Alter-Level Coordination

Current Pending Finished

slide-69
SLIDE 69

Automatic OSC

Alter-Level Coordination

Current Pending Finished

slide-70
SLIDE 70

Automatic OSC

Alter-Level Coordination

Current Pending Finished

slide-71
SLIDE 71

Automatic OSC

Alter-Level Coordination: Pending

Pending

slide-72
SLIDE 72

db1 > SHOW CREATE TABLE `apple`; CREATE TABLE `apple` ( `id` int(11) AUTO_INCREMENT, `name` varchar(32), `quantity` int(11), `color` enum('red','green'), PRIMARY KEY (`id`) ) ENGINE=RocksDB db2 > SHOW CREATE TABLE `apple`; CREATE TABLE `apple` ( `id` int(11) AUTO_INCREMENT, `name` varchar(32), `quantity` int(11), `color` enum('red','green'), PRIMARY KEY (`id`) ) ENGINE=RocksDB db3 > SHOW CREATE TABLE `apple`; CREATE TABLE `apple` ( `id` int(11) AUTO_INCREMENT, `name` varchar(32), `quantity` int(11), PRIMARY KEY (`id`) ) ENGINE=RocksDB

Automatic OSC

Alter-Level Coordination: Pending

~/mysql-schemas $ git diff CREATE TABLE `apple` ( `id` int(11) AUTO_INCREMENT, `name` varchar(32), `quantity` int(11), + `color` enum('red','green'), PRIMARY KEY (`id`) ) ENGINE=RocksDB

Pending

slide-73
SLIDE 73

Automatic OSC

Alter-Level Coordination: Pending

Pending

slide-74
SLIDE 74

Automatic OSC

Alter-Level Coordination: Current

Current

slide-75
SLIDE 75

Automatic OSC

Alter-Level Coordination: Finished

Finished

slide-76
SLIDE 76

Automatic OSC

Alter-Level Coordination: Finished

Current Pending Finished

slide-77
SLIDE 77

Automatic OSC

Alter-Level Coordination: Finished

Current Pending Finished

slide-78
SLIDE 78

Automatic OSC

Alter-Level Coordination: Finished

Current Pending Finished

slide-79
SLIDE 79

Automatic OSC

Alter-Level Coordination: Finished

Current Pending Finished

slide-80
SLIDE 80

Automatic OSC

Alter-Level Coordination: Finished

Current Pending Finished

slide-81
SLIDE 81

Automatic OSC

Alter-Level Coordination: Finished

Current Pending Finished

slide-82
SLIDE 82

Automatic OSC

Alter-Level Coordination: Finished

Finished

slide-83
SLIDE 83

Automatic OSC

Alter-Level Coordination: Current

Current

slide-84
SLIDE 84

Automatic OSC

Alter-Level Coordination: Finished

Finished

slide-85
SLIDE 85

Host-Level Coordination

slide-86
SLIDE 86

Automatic OSC

Host-Level Coordination

Current

slide-87
SLIDE 87

Automatic OSC

Host-Level Coordination

Current

slide-88
SLIDE 88

Automatic OSC

Host-Level Coordination

Current

slide-89
SLIDE 89

R

Automatic OSC

Host-Level Coordination

M R R R

slide-90
SLIDE 90

Automatic OSC

Host-Level Coordination

R R R R M R R R

slide-91
SLIDE 91

Automatic OSC

Host-Level Coordination

R R R M R M R R R

slide-92
SLIDE 92

Automatic OSC

Host-Level Coordination

R R R M M R R R R

slide-93
SLIDE 93

Automatic OSC

Host-Level Coordination

Current

slide-94
SLIDE 94

Current

Automatic OSC

Host-Level Coordination

slide-95
SLIDE 95

Current

Automatic OSC

Host-Level Coordination

slide-96
SLIDE 96

Current

Automatic OSC

Host-Level Coordination

slide-97
SLIDE 97

Current

Automatic OSC

Host-Level Coordination

slide-98
SLIDE 98

Current

Automatic OSC

Host-Level Coordination

slide-99
SLIDE 99

Current

Automatic OSC

Host-Level Coordination

slide-100
SLIDE 100

Current

Automatic OSC

Host-Level Coordination

slide-101
SLIDE 101

Current

Automatic OSC

Host-Level Coordination

slide-102
SLIDE 102

Automatic OSC

Host-Level Coordination

R M R R R

slide-103
SLIDE 103

Automatic OSC

Host-Level Coordination

R M R R R

slide-104
SLIDE 104

Automatic OSC

Host-Level Coordination

R M R R R

slide-105
SLIDE 105

Automatic OSC

Host-Level Coordination

R M R R R

slide-106
SLIDE 106

Automatic OSC

Host-Level Coordination

R M R R R

slide-107
SLIDE 107

Automatic OSC

Host-Level Coordination

R M R R R

slide-108
SLIDE 108

Automatic OSC

Host-Level Coordination

R M R R R

slide-109
SLIDE 109

Automatic OSC

Host-Level Coordination

R M R R R

slide-110
SLIDE 110

Automatic OSC

Host-Level Coordination

R M R R R

slide-111
SLIDE 111

R

Automatic OSC

Host-Level Coordination

R M R R

slide-112
SLIDE 112

Automatic OSC

Host-Level Coordination

R M R R

slide-113
SLIDE 113

Automatic OSC

Host-Level Coordination

R M R R

slide-114
SLIDE 114

Automatic OSC

Host-Level Coordination

R M R R

slide-115
SLIDE 115

Automatic OSC

Host-Level Coordination

R M R R

slide-116
SLIDE 116

Automatic OSC

Host-Level Coordination

R M R R

slide-117
SLIDE 117

Automatic OSC

Host-Level Coordination

R M R R

slide-118
SLIDE 118

Automatic OSC

Host-Level Coordination

R M R R

slide-119
SLIDE 119

Automatic OSC

Host-Level Coordination

M R R R

slide-120
SLIDE 120

Automatic OSC

Host-Level Coordination

M R R R

slide-121
SLIDE 121

Automatic OSC

Host-Level Coordination

M R R

slide-122
SLIDE 122

Automatic OSC

Host-Level Coordination

M R R

slide-123
SLIDE 123

Automatic OSC

Host-Level Coordination

M R R

slide-124
SLIDE 124

Automatic OSC

Host-Level Coordination

R M R

slide-125
SLIDE 125

Automatic OSC

Host-Level Coordination

R M R

slide-126
SLIDE 126

Automatic OSC

Host-Level Coordination

R M R

slide-127
SLIDE 127

Automatic OSC

Host-Level Coordination

R M R

slide-128
SLIDE 128

Automatic OSC

Host-Level Coordination

M R R

slide-129
SLIDE 129

R R

Automatic OSC

Host-Level Coordination

M

slide-130
SLIDE 130

Automatic OSC

Host-Level Coordination

M

slide-131
SLIDE 131

Automatic OSC

Host-Level Coordination

M

slide-132
SLIDE 132

Automatic OSC

Host-Level Coordination

M

slide-133
SLIDE 133

Automatic OSC

Host-Level Coordination

M

slide-134
SLIDE 134

Automatic OSC

Host-Level Coordination

M

slide-135
SLIDE 135

Automatic OSC

Host-Level Coordination

slide-136
SLIDE 136

Automatic OSC

Host-Level Coordination

R M R R R

Current

slide-137
SLIDE 137

Automatic OSC

Host-Level Coordination

R M R R R

Current

slide-138
SLIDE 138

Future Work

slide-139
SLIDE 139

Automatic OSC

Future Work

▪ Edge cases ▪ Ease of use ▪ Cross-automation interaction

slide-140
SLIDE 140

Questions?

slide-141
SLIDE 141