Tweet
Tweet

Slide 1

Slide 1 text

Andrew Godwin @andrewgodwin Moving : Changing database and datacenter

Slide 2

Slide 2 text

THE CHALLENGE Move from MySQL to PostgreSQL

Slide 3

Slide 3 text

THE CHALLENGE Move from MySQL to PostgreSQL ...and from AWS to Softlayer

Slide 4

Slide 4 text

Why?

Slide 5

Slide 5 text

MySQL PROBLEM ONE

Slide 6

Slide 6 text

MySQL PROBLEM ONE Adding columns is slow

Slide 7

Slide 7 text

MySQL PROBLEM ONE Adding columns is slow Data integrity is poor

Slide 8

Slide 8 text

MySQL PROBLEM ONE Adding columns is slow Data integrity is poor Limited set of data types

Slide 9

Slide 9 text

MySQL PROBLEM ONE Adding columns is slow Data integrity is poor Limited set of data types This is a whole talk by itself.

Slide 10

Slide 10 text

AWS PROBLEM TWO Demand Servers Theory Demand Servers Reality

Slide 11

Slide 11 text

AWS PROBLEM TWO Demand Servers Theory Demand Servers Reality Plus, disk I/O is very slow

Slide 12

Slide 12 text

THE SPECTRUM OF HOSTING Per-hour costs Least customisable Per-decade costs Most customisable "Cloud" providers Own Datacentre Colocation Dedicated servers VPS providers

Slide 13

Slide 13 text

You're doing it AT THE SAME TIME?

Slide 14

Slide 14 text

Both need time in read-only mode Both involve copying whole dataset Time usage is interleaved nicely

Slide 15

Slide 15 text

CONVERTING MySQL to PostgreSQL

Slide 16

Slide 16 text

MySQL

Slide 17

Slide 17 text

MySQL 1st SQL file "Postgres compatible" dump

Slide 18

Slide 18 text

MySQL 1st SQL file "Postgres compatible" dump 2nd SQL file Scripted file conversion

Slide 19

Slide 19 text

MySQL 1st SQL file "Postgres compatible" dump 2nd SQL file Scripted file conversion PostgreSQL 1st schema load over network

Slide 20

Slide 20 text

MySQL 1st SQL file "Postgres compatible" dump 2nd SQL file Scripted file conversion PostgreSQL 1st schema PostgreSQL 2nd schema Schema alteration / type casts load over network

Slide 21

Slide 21 text

Converter available at: github.com/lanyrd/mysql-postgresql-converter

Slide 22

Slide 22 text

The Move A timeline

Slide 23

Slide 23 text

THE DAY BEFORE Make sure new servers are ready Set DNS TTL to 300s

Slide 24

Slide 24 text

THE DAY BEFORE Make sure new servers are ready Set DNS TTL to 300s GO! (9am GMT) Put site in read-only mode Start database dump & conversion

Slide 25

Slide 25 text

THE DAY BEFORE Make sure new servers are ready Set DNS TTL to 300s GO! (9am GMT) Put site in read-only mode Start database dump & conversion CONVERSION FINISHED (9:30am GMT) Start load and cast of data into new DB

Slide 26

Slide 26 text

THE DAY BEFORE Make sure new servers are ready Set DNS TTL to 300s GO! (9am GMT) Put site in read-only mode Start database dump & conversion CONVERSION FINISHED (9:30am GMT) Start load and cast of data into new DB LOAD FINISHED (10:15am GMT) Verify site is working correctly

Slide 27

Slide 27 text

THE DAY BEFORE Make sure new servers are ready Set DNS TTL to 300s GO! (9am GMT) Put site in read-only mode Start database dump & conversion CONVERSION FINISHED (9:30am GMT) Start load and cast of data into new DB LOAD FINISHED (10:15am GMT) Verify site is working correctly VERIFIED (10:30am GMT) Switch DNS Point old loadbalancers to new servers Exit read-only mode

Slide 28

Slide 28 text

PRACTICE MAKES PERFECT 8 database dry runs, 2 whole dry runs

Slide 29

Slide 29 text

What did we learn?

Slide 30

Slide 30 text

Everything went well Quite pleased about this.

Slide 31

Slide 31 text

Server load dropped But not as much as we would have liked

Slide 32

Slide 32 text

Server costs were halved One of the main reasons for doing it

Slide 33

Slide 33 text

SSDs are not a panacea But they're pretty good if you tune PostgreSQL

Slide 34

Slide 34 text

Move before it's too late Eventbrite is probably too big, but we have other issues.

Slide 35

Slide 35 text

Thanks. @andrewgodwin [email protected]