I recently had to alter(edit) a very large MySQL table on a production system to drop an unnecessary index. This particular table has over 15 million records in it and occupies over 6GB of space. Since this database runs the We’re Related facebook app, I couldn’t just bring it down for hours and make the change. This particular table stores data for a main part of the application.

Enter EC2. All of our production systems that power We’re Related are running on Amazon’s EC2 so this was an easy problem to work around. Using MySQL replication, I setup a system that would mirror this production database. The nice thing about replication is that it automatically keeps any mirrors up to date with the latest data.

Once I had the mirror up and running, I was able to run the command to alter the table on the mirror. This took over 6 hours to complete. Once done, it quickly caught up with those 6 hours worth of updates from the main system.

Tonight, I will be reconfiguring the webservers to connect to this new mirrored system instead of the original. This will result in less than five minutes of actual downtime.

End result:
downtime - < 5 minutes compared to 6+ hours
additional cost - $20 (for running an additional “beefy” server for 1 day)

That’s one thing I just love about EC2, you pay for what you use. If you need a temporary server for hours or days, you just fire one up and pay minimally for it.