My Love Hate Relationship with MySQL
Posted by Sam
The Hate
It seems everybody is using MySQL these days. It's getting plenty of press, especially lately with them getting bought by Sun and all. I've been using MySQL for at least 10 years. Only recently did I throw out an old O'Reilly book that cover MySQL version 3. But lately I've been pretty dissatisfied with MySQL and it's annoying limitations. For instance, why do we need so many database engines that all do different things? MyISAM, HEAP, InnoDB, NDB, Falcon and NitroEDB to name but six and I've probably left out a couple. But with all these options there isn't a single option if you want something as simple as say, transactions AND Full Text Indexing. You can get Full Text Indexing with MyISAM and transactions with InnoDB but if you want/need both then you are out of luck or relying on a third party hack. No offense meant to the Sphinx folks with that 3rd party hack comment, it's just that I want to tightly integrated into my database and I'd prefer not to have compile, install and configure another application for FTI. Then there's also the well documented performance limitations concerning the use of lots of cores.
A while back I was working on a project that I needed a modern database engine and Full Text Indexing. The searching was going to be a critical part of the site so that's what I focused on. I spent hours researching FTI on MySQL only to walk away realizing that it just wasn't going to work without going the MyISAM route or some third party hack. And the more research I did on MyISAM the scarier it got because it apparently has some serious scaling issues regarding FTI. All this research led me to one thing. Postgres. Postgres has one database engine that does everything. Oh and these days it happens to be really fast. Often faster than MySQL. Now that it's fast and they have the vacuuming stuff more automatic Postgres is a great database and people really should be looking into it for replacing MySQL IMHO. But Postgres aside, the more I research MySQL the more convinced I was that the company just doesn't get it. They seem to be more interested in creating more engines than in making a single engine that does 90 - 95% of what most people want.
The Love
So what do I love about MySQL? Well I'm glad you asked. The other day I was talking to a co-worker and I was telling him how excited I was about our new MySQL cluster. We have a master/slave configuration set up so recovering from a database disaster should be just a quick reconfigure away. And if we need the capacity we can point sites at the slaves. And we can do backups on the slaves without taking down the site because the database is locked. Needless to say I was excited. So many possibilities. I like everything to be redundant. Everything. So the database redundancy was a big deal. And MySQL makes it easy to set up. It really is nice. So after I finished extolling the virtues of our set up he asked me if this means that I no longer hate MySQL. This is where I realized that I had a love/hate relationship with it. From the admin side I love it. MySQL is easy to work with, runs anywhere and makes some cool stuff (like clustering) uber easy. From the development side I hate it for the reasons I stated above. If I'm programming I'm going to lean towards Postgres, but if I'm wearing my sys admin hat I'm going to lean towards MySQL. It gets really confusing when I'm doing both roles as if often the case. Hrm, maybe I need to look into Postgres' clustering and then it will be an easy choice.
Where to now?
I'm really hoping that Sun can do something to turn around MySQL because for a lot of projects it's a really good database but for just as many it's an awful choice. They really need to fix the scaling issues and stop with the database engine madness. A couple of database engines are OK, but come on. They are just out of control. Pick a couple of engines and just pour all your time into supporting them. And the scaling issue is a no brainer. Lots of cores are the norm and MySQL needs to get with the times or risk being left behind.
Tags: mysql