MySQL Hate
We've been using MySQL for MochiBot for a while. It worked OK, until this week.
MySQL replication is unreliable. We had two MySQL servers running, one as a master, and one as a slave; the simplest of all replication scenearios. The slave was used only for redundancy and load balancing. It receives only SELECT queries and never mutates data (none of the connections connecting to it even have permission to mutate!). What happened? Replication stopped -- but only on one table, with no errors. The binlogs are all synchronized, it just decided not to update that table anymore. The workaround for this was easy, we stopped using replication. Granted, I consulted a few people who are running really high profile and high traffic sites on MySQL and they've never seen this before, but I can no longer trust MySQL to replicate our data.
The final blow was MySQL's MyISAM tables are fast, but dangerous. What happens when you try and put a lot of data in them? They explode. There's an arcane MAX_TABLE_ROWS limit to a MyISAM table. If you hit this limit, it won't let you insert any more rows. It doesn't grow for you. Why not? Beats me. I should've known this, but frankly, I've never trusted MySQL to store an 8GB table before, so I never ran up against it. Never again!
We're through, and are in the process of migrating to PostgreSQL ASAP. The bonus is that we can use all the nice features of SQL that you lose when making the MySQL choice: subselects, views, triggers, stored procedures, etc. I really should've listened to myself and avoided MySQL in the first place.
What version of MySQL have you been using? Why are you using MyISAM tables instead of InnoDB tables, which give you transactions, and have a maximum table size of 64 TB? And 5.0 (in late beta) gives you subselects, views (updatable), triggers, and stored procedures.
Comment by Andy Dustman — 2005-08-13 @ 1:21 am
This was MySQL 4.1.10a-log from FreeBSD ports. We were using MyISAM because the performance was quite a bit better for what we were doing.
Using a beta database was not even considered. I can get all of that functionality from PostgreSQL, which is extremely stable and mature. I should’ve just used it in the first place, but it was marginally easier at the time to choose MySQL. I doubt I’ll be giving MySQL another chance any time soon.
Comment by Bob Ippolito — 2005-08-13 @ 1:25 am
> I really should’ve listened to myself and avoided MySQL in the first place.
With some luck, you could have stumbled into http://sql-info.de/ , for corroboration. :-)
First-hand experience is always best, however, even if expensively hard-earned.
> I doubt I’ll be giving MySQL another chance any time soon.
Nor should you. It’s hard to trust a tool with such an history. It’s not accidental that it’s often used with PHP. ;-)
Comment by Nicola Larosa — 2005-08-13 @ 4:50 am
As you probably now know, MyISAM isn’t appropriate for 8GB of data. InnoDB tables could well have handled it but I’d say that PostgreSQL is still the way to go.
Hopefully you didn’t lose too much data.
Comment by duncan — 2005-08-13 @ 5:28 am
I’m sorry to hear that you ran into problems with MySQL. I started working fulltime on MySQL development in May, and we are working very hard to fix all the known bugs (my team focuses on replication). I sincerely hope you have better luck with Postgres, and won’t put any pressure on you to switch back. However, I do have a personal commitment to make sure every problem with replication is fixed, so I’d like to get some more info from you privately about this bug so that we can track down and fix the problem. Any other suggestions you might have for improvement are welcome also.
Kind regards, and good luck with MochiBot!
Comment by Elliot Murphy — 2005-08-14 @ 8:14 pm
When you did “show slave status;”, did it show nothing under “Last_Error”?
We use MySQL databases for redundancy, and they do need a degree of babysitting. (No database is over 0.5 gigs, but everything is replicated.)
Let us know how the MySQL to PostgreSQL migration goes.
Comment by Joe Grossberg — 2005-08-14 @ 9:13 pm
show slave status did not report any errors whatsoever, and the binlog positions were sync’ed up. I wouldn’t have minded so much if it reported errors, because we’d have noticed sooner. Instead, we were serving stale data off the slave for hours until we noticed that everyone’s statistics were flatlining.
In any case, the damage is done, I don’t trust MySQL anymore. It doesn’t really matter to me whether they fix it or not because I don’t plan on using it for anything more importantant than dumb php forums from here on out.
Comment by Bob Ippolito — 2005-08-14 @ 9:27 pm
That’s an all too common story. We saw the same exact kind of thing where I currently work, and in other places. MySQL inexplicably dies, crashes, loses data, etc.
As for MySQL 5.0, when it finally comes out, it’ll be at the same place PostgreSQL was 6 years ago, except probably with more bugs, given MySQL’s track record and the diminished reliability that comes with time and experience.
What people using MySQL should ask themselves — but usually fail to do so — is:
* Why does MySQL have so many deviations from the standard? (|| for “or” instead of SQL’s concatenation? date_add()?)
* Why did the MySQL developers shun transactions, and saw them as “unnecessary” and “slow” (check writings by Monty Widenius), and it took a third-party to develop a transactional backend for MySQL?
* Why does MySQL take a “best guess” approach to your data? (http://sql-info.de/mysql/gotchas.html#1_13)
* Why does MySQL has a huge number of documented “gotchas” and unexpected and unstandard behavior, some in critical areas? (http://sql-info.de/mysql/gotchas.html)
Could this all be coincidence? Or could it be that MySQL was written to only manage very simple applications needing to store and retrieve data fast, where reliability and scalability are not really necessary?
Comment by Roberto Mello — 2005-08-15 @ 12:47 pm
Hear hear!! Down with MySQL - long live Postgresql!
Comment by A Non-Mouse Cowherd — 2005-08-18 @ 1:50 pm