Hacker News new | past | comments | ask | show | jobs | submit login

Percona's OSC [1] pretty much fixes the migration pain without locking.

[1] http://www.percona.com/doc/percona-toolkit/2.1/pt-online-sch...




We recently spent a few weeks trying to make it work in production, and couldn't. We always ran into deadlocks due to gap locking and auto_increment columns.

After a few weeks of failure, we finally gave up and did it the slow way, unweigh a slave, do the alter offline, catch up in replication, and fail over. Repeat as needed.

Percona's OSC is great for the cases where it works, but there are still many cases where it doesn't.


pt-online-schema change is a great tool, but not a panacea.

I've seen a serious issue where under moderately heavy load, the OSC tool would trigger a deadlock on the final atomic table swap, blocking all transactions against the table, including the final `RENAME TABLE`.

All operations (including reads) would block, waiting on a table metadata lock. Killing the offending RENAME thread was the only way to restore access.

IIRC, the ultimate cause was due to a bug in our code that would occasionally leave stale transactions open in edge cases. Happened a few times, and caused a fairly severe outage the first time we hit it.

Not sure where the blame here lies -- and what can fundamentally be improved -- but OSC techniques and MySQL still leave plenty of room for a moderately savvy user to get into trouble.


At Facebook we use our own OSC which may work better for circumstances and less well for others.

http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook...

Check out the README for examples.




Join us for AI Startup School this June 16-17 in San Francisco!

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: