47 ms·

I worked at a large startup that was trying to migrate from MySQL to Postgres because of how long table migrations take on MySQL. There's a tendency for adding

by jes5199 14y ago

I worked at a large startup that was trying to migrate from MySQL to Postgres because of how long table migrations take on MySQL. There's a tendency for adding a column in MySQL to a table to be stop-the-world for the whole server, and it can take hours. They had some pretty serious workarounds for that, and some of them were "try to never change the database structure". There was some thought that there were other problems that Postgres would solve, some advocacy happened here: http://corner.squareup.com/2011/06/postgresql-data-is-important.html http://corner.squareup.com/2011/06/postgresql-data-is-import...

reinhardt 14y ago
Percona's OSC [1] pretty much fixes the migration pain without locking. [1] http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html http://www.percona.com/doc/percona-toolkit/2.1/pt-online-sch...
- oasisbob 14y ago
  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.
- tene 14y ago
  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.
- wultsch 14y ago
  At Facebook we use our own OSC which may work better for circumstances and less well for others. http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/tools/revision/15 http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook... Check out the README for examples.
toast0 14y ago
try to never change the database structure works pretty well. For all other cases, the easiest solution requires that you have a good redundancy system in place, and some extra planning. But basically run alter table without replication on your out of rotation slaves and masters, and then swap. Depending on your backup situation, you could probably just run the alter table on your backup systems and then restore from then. Yes, alter table is inconvenient, but how often are you updating your schema and how often do you use it? In my experience, once major development was done (which is usually before there's a large amount of data in the tables), schema changes are rare, but queries are very common.
- simonw 14y ago
  This has been one of the biggest changes I've noticed since our team switched from MySQL to PostgreSQL: our database design is now much, much better and we make changes to it much more often. I has grown used to compromising on database quality to avoid having to make frequent changes to large tables: implementing a new feature as a join table for example rather than adding the columns it needs to a core, multi million row table. Being able to add a billable column to a large existing table without worrying about downtime frees you up to be much smarter about how you design your application, and much more agile about changes you make to exuding functionality. This is a far bigger benefit than I was expecting from the change.
  - simonw 14y ago
    That should be nullable, not billable.
tedchs 14y ago
> a large startup I think the word for that is "company".

by jes5199 14y ago

reinhardt 14y ago

Percona's OSC [1] pretty much fixes the migration pain without locking. [1] http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html http://www.percona.com/doc/percona-toolkit/2.1/pt-online-sch...

oasisbob 14y ago
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.
tene 14y ago
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.
wultsch 14y ago
At Facebook we use our own OSC which may work better for circumstances and less well for others. http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/tools/revision/15 http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook... Check out the README for examples.

toast0 14y ago

try to never change the database structure works pretty well. For all other cases, the easiest solution requires that you have a good redundancy system in place, and some extra planning. But basically run alter table without replication on your out of rotation slaves and masters, and then swap. Depending on your backup situation, you could probably just run the alter table on your backup systems and then restore from then. Yes, alter table is inconvenient, but how often are you updating your schema and how often do you use it? In my experience, once major development was done (which is usually before there's a large amount of data in the tables), schema changes are rare, but queries are very common.

simonw 14y ago
This has been one of the biggest changes I've noticed since our team switched from MySQL to PostgreSQL: our database design is now much, much better and we make changes to it much more often. I has grown used to compromising on database quality to avoid having to make frequent changes to large tables: implementing a new feature as a join table for example rather than adding the columns it needs to a core, multi million row table. Being able to add a billable column to a large existing table without worrying about downtime frees you up to be much smarter about how you design your application, and much more agile about changes you make to exuding functionality. This is a far bigger benefit than I was expecting from the change.
- simonw 14y ago
  That should be nullable, not billable.

tedchs 14y ago

> a large startup I think the word for that is "company".