Moving Lanyrd: Changing database and datacentre

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

Transcript

1. Andrew Godwin
   @andrewgodwin
   Moving :
   Changing database and datacenter
   

   View Slide

2. THE CHALLENGE
   Move from MySQL to PostgreSQL
   

   View Slide

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

   View Slide

4. Why?
   

   View Slide

5. MySQL
   PROBLEM ONE
   

   View Slide

6. MySQL
   PROBLEM ONE
   Adding columns is slow
   

   View Slide

7. MySQL
   PROBLEM ONE
   Adding columns is slow
   Data integrity is poor
   

   View Slide

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

   View Slide

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

   View Slide

10. AWS
    PROBLEM TWO
    Demand
    Servers
    Theory
    Demand
    Servers
    Reality
    

    View Slide

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

    View Slide

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

    View Slide

13. You're doing it
    AT THE SAME TIME?
    

    View Slide

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

    View Slide

15. CONVERTING
    MySQL to PostgreSQL
    

    View Slide

16. MySQL
    

    View Slide

17. MySQL
    1st SQL file
    "Postgres compatible" dump
    

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

22. The Move
    A timeline
    

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

29. What did we learn?
    

    View Slide

30. Everything went well
    Quite pleased about this.
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

35. Thanks.
    @andrewgodwin
    andrewgodwin@eventbrite.com
    

    View Slide