Moving Lanyrd: Changing database and datacentre

A short talk I gave as part of the Eventbrite "Advanced Django" workshop at PyCon 2014

1. Andrew Godwin @andrewgodwin Moving : Changing database and datacenter
2. THE CHALLENGE Move from MySQL to PostgreSQL
3. THE CHALLENGE Move from MySQL to PostgreSQL ...and from AWS to Softlayer
4. Why?
5. MySQL PROBLEM ONE
6. MySQL PROBLEM ONE Adding columns is slow
7. MySQL PROBLEM ONE Adding columns is slow Data integrity is poor
8. MySQL PROBLEM ONE Adding columns is slow Data integrity is poor Limited set of data types
9. MySQL PROBLEM ONE Adding columns is slow Data integrity is poor Limited set of data types This is a whole talk by itself.
10. AWS PROBLEM TWO Demand Servers Theory Demand Servers Reality
11. AWS PROBLEM TWO Demand Servers Theory Demand Servers Reality Plus, disk I/O is very slow
12. THE SPECTRUM OF HOSTING Per-hour costs Least customisable Per-decade costs Most customisable "Cloud" providers Own Datacentre Colocation Dedicated servers VPS providers
13. You're doing it AT THE SAME TIME?
14. Both need time in read-only mode Both involve copying whole dataset Time usage is interleaved nicely
15. CONVERTING MySQL to PostgreSQL
16. MySQL
17. MySQL 1st SQL file "Postgres compatible" dump
18. MySQL 1st SQL file "Postgres compatible" dump 2nd SQL file Scripted file conversion
19. MySQL 1st SQL file "Postgres compatible" dump 2nd SQL file Scripted file conversion PostgreSQL 1st schema load over network
20. 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
21. Converter available at: github.com/lanyrd/mysql-postgresql-converter
22. The Move A timeline
23. THE DAY BEFORE Make sure new servers are ready Set DNS TTL to 300s
24. 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
25. 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
26. 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
27. 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
28. PRACTICE MAKES PERFECT 8 database dry runs, 2 whole dry runs
29. What did we learn?
30. Everything went well Quite pleased about this.
31. Server load dropped But not as much as we would have liked
32. Server costs were halved One of the main reasons for doing it
33. SSDs are not a panacea But they're pretty good if you tune PostgreSQL
34. Move before it's too late Eventbrite is probably too big, but we have other issues.
35. Thanks. @andrewgodwin andrewgodwin@eventbrite.com