Pro Yearly is on sale from $80 to $50! »

Moving Lanyrd: Changing database and datacentre

Moving Lanyrd: Changing database and datacentre

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

077e9a0cb34fa3eba2699240c9509717?s=128

Andrew Godwin

April 10, 2014
Tweet

Transcript

  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