6 ms·

I appreciate this well-argued piece of persuasive writing for not choosing MySQL, but the premise is surprising -- I don't recall seeing, on Hacker News or else

I appreciate this well-argued piece of persuasive writing for not choosing MySQL, but the premise is surprising -- I don't recall seeing, on Hacker News or elsewhere, any writeups from companies that chose MySQL then ran into significant problems they had to architect around, nor writeups from companies that chose MySQL then had to rip everything out to switch to something in the same family of solutions (Postgres, Oracle, etc).

simonw 14y ago
We migrated Lanyrd from MySQL to PostgreSQL, primarily to reduce the pain involved in running schemes alterations against large tables but we've since been enjoying some if the more advanced PostgreSQL features (pg_trgm indexes for example). You can read about our migration here: http://lanyrd.com/blog/2012/lanyrds-big-move/ http://lanyrd.com/blog/2012/lanyrds-big-move/
- taligent 14y ago
  Just wanted to confirm something. You say you have no single point of failure but you are running everything in one data center ?
  - simonw 14y ago
    That was slightly loose wording in the article: yes, the single data center is currently a single point of failure (though we can spin up a replacement stack elsewhere very quickly). Andrew was referring to the way we had rearranged our software stack to reduce the SPOFs (our previous architecture had a single load balancer for example).
  - twerquie 14y ago
    There is always a single point of failure if you zoom out far enough.
- mscarborough 14y ago
  Thanks for being clear and detailed about your DB migration. I've been involved in a few and they've generally been brought on by DBAs convincing the bosses that "everything is wrong"...once it was done the DBAs wanted to blame the developers again.
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.
lmm 14y ago
You don't tend to hit the kind of problems you have to rip out or architect around; it's more a constant set of nagging exceptions to remember, bugs that take longer than they should to fix, application improvements that you'd like to make but can't because you can't change a table definition - but never any single critical showstopper. The PHP comparison is apt (and I've never heard anywhere choose to rip everything out of PHP and start again - although when I worked at last.fm a lot of our effort was replacing PHP code with better alternatives, one piece at a time. Rather like my job before that, where we spent about four years gradually getting our application into a state where certain mysql tables became small enough that we could afford to alter them).
- meaty 14y ago
  Yes this is called "we're stuck with it syndrome"
pnathan 14y ago
One of my colleagues has been involved in rearchictecting a reasonably large database on MySQL; his complaints ran along the same lines as the article. Many bad defaults, many subtle edge cases of wrong. E.g., the encoding based on connection string problem is very real. :-/
armored_mammal 14y ago
Perhaps most companies prefer to keep the press positive? Mine certainly does. (And it'll probably stick with MySQL until its dying day despite it causing problems repeatedly.)

simonw 14y ago

We migrated Lanyrd from MySQL to PostgreSQL, primarily to reduce the pain involved in running schemes alterations against large tables but we've since been enjoying some if the more advanced PostgreSQL features (pg_trgm indexes for example). You can read about our migration here: http://lanyrd.com/blog/2012/lanyrds-big-move/ http://lanyrd.com/blog/2012/lanyrds-big-move/

taligent 14y ago
Just wanted to confirm something. You say you have no single point of failure but you are running everything in one data center ?
- simonw 14y ago
  That was slightly loose wording in the article: yes, the single data center is currently a single point of failure (though we can spin up a replacement stack elsewhere very quickly). Andrew was referring to the way we had rearranged our software stack to reduce the SPOFs (our previous architecture had a single load balancer for example).
- twerquie 14y ago
  There is always a single point of failure if you zoom out far enough.
mscarborough 14y ago
Thanks for being clear and detailed about your DB migration. I've been involved in a few and they've generally been brought on by DBAs convincing the bosses that "everything is wrong"...once it was done the DBAs wanted to blame the developers again.

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.

lmm 14y ago

You don't tend to hit the kind of problems you have to rip out or architect around; it's more a constant set of nagging exceptions to remember, bugs that take longer than they should to fix, application improvements that you'd like to make but can't because you can't change a table definition - but never any single critical showstopper. The PHP comparison is apt (and I've never heard anywhere choose to rip everything out of PHP and start again - although when I worked at last.fm a lot of our effort was replacing PHP code with better alternatives, one piece at a time. Rather like my job before that, where we spent about four years gradually getting our application into a state where certain mysql tables became small enough that we could afford to alter them).

meaty 14y ago
Yes this is called "we're stuck with it syndrome"

pnathan 14y ago

One of my colleagues has been involved in rearchictecting a reasonably large database on MySQL; his complaints ran along the same lines as the article. Many bad defaults, many subtle edge cases of wrong. E.g., the encoding based on connection string problem is very real. :-/

armored_mammal 14y ago

Perhaps most companies prefer to keep the press positive? Mine certainly does. (And it'll probably stick with MySQL until its dying day despite it causing problems repeatedly.)