MySQL in Amarok 2 - The Reality

There has been a lot of chatter lately regarding Amarok's switch to MySQL as its only SQL backend. A decent amount is FUD -- either by people simply pushing back against change, or by people that simply don't understand the decision. Some of it (particularly Adriaan's blog post) has been insightful and interesting, but miss the mark in terms of why this change was made. This post attempts to explain why this decision was made, what it really means for you the end-user, and why you should have a cup of tea and relax.

I want to point out first that I said that MySQL is going to be Amarok's only SQL backend. A2's collection system is very powerful. Just take a look at how varied music sources from Shoutcast, Jamendo, Magnatune, Ampache, MP3Tunes, as well as local sources like iPods and your local file system, are treated as equals in A2. A collection is a collection, and is limited only by what capabilities it advertises it can support (and of course, it can supply its own custom capabilities). It's not currently enabled, I don't think, but there's a Nepomuk-based collection option too. So take heart -- this change only affects Amarok's internal SQL collection, and not other sources (although those sources can store information in the SQL database if they wish to cache information).

Since I mentioned Nepomuk, it's time to discuss another common question/demand/complaint: KDE has this nice Strigi-Nepomuk thing going on...why aren't we using it for scanning music and storing information? There are a couple main reasons. The first is that Strigi and Nepomuk are optional, not required. (Update: Strigi is required, but Soprano isn't, so Nepomuk as a whole is still optional.) We can't rely on the user installing them, and even if they are installed, we can't rely on the user to configure them properly (remember that we're going cross-platform, making it even less likely). The second reason is speed: Amarok's custom collection scanner is extremely fast and pulls out specific pieces of information with TagLib. Strigi is, by comparison, very slow (it calculates hashes of all files, which means it needs to read the entire file) and pulls out less information. (Update: According to the Strigi developer, and despite what is said on kde-apps.org, Wikipedia, and even the author's own home page, it does not calculate hashes by default. So it's possible that Strigi, if properly configured, could be as fast as Amarok's internal scanner, although whether it would pull out all necessary information, I don't know. If it's configured to calculate SHA1 hashes of all files, then it will indeed be far slower.) On a local hard drive, it may not be a big issue, but it sure is a huge issue when you throw networked storage into the picture, which is a very common scenario. I've also heard, though don't remember specifics, that querying and such through Nepomuk is rather slow, compared to a normal SQL database.  Regardless, though, remember that when the Nepomuk-based collection is finished, tracks sourced through a Nepomuk-based collection will have their metadata changes saved back to Nepomuk. So, it's not that the SQL collection is in place of Nepomuk -- they are entirely independent. (Update: I forgot to mention that a Nepomuk collection already exists. It was developed by a GSoCer over the summer. I'm not sure what its status is as far as making the 2.0 release, but we Amarokers both like Strigi/Nepomuk and are excited about the idea of opening up the app and having all your music available right then and there with no pre-configuration. But there is a place for the SQL collection too. As I said: they are complimentary technologies.)

With those topics out of the way, on to the meat.

First, it is important to understand an important pair of facts. Number one: we are not database guys. Sure, we can store data in them, and more or less come up with a working schema, but none of us are gurus/wizards/jedis/etc. This leads in to number two: maintaining three databases was driving us crazy. Every time a minor schema change was needed, it had to be coded up for all three types of databases. Modifying a schema could be trivial for one database type, and super difficult (or impossible) for another. People would report bugs that we couldn't reproduce, only to find out that it was because we didn't quite understand how one database or another behaved (or in some cases, none of the active devs were using that type). And so on. So from the beginning of A2 development (and in our fantasies during A1 development) we knew we wanted just one database.

(We did actually look at abstraction layers like QtSQL and others. I'm not going to comment on them much, as I didn't do the evaluation, but in general they were found to not be flexible enough to handle all of our needs without doing some custom SQL coding (especially in the cases of things like schema changes), which kind of defeats the point. If you want to know more/want to insist that they are, try asking eean, as I think he did the evaluations.)

Now we had to choose the type. At first, SQLite seemed like a good choice. Using transactions, it's decently fast. It's pretty stable (those that complain about odd MySQL bugs should talk to markey, as he, being the SQLite maintainer in 1.4, can attest that SQLite's had its fair share). However, there were a few problems that in the end knocked it out of the running. The first problem is performance. Although for people with small collections it performs fairly well, people with large collections that switched to the MySQL or PostgreSQL backends in A1 would report enormous speed gains when operations performing complex or many queries were performed, such as adding many entries to the playlist, scanning files, or filtering/searching in the collection. Since we want to accommodate users with large collections just as well as those with smaller collections, and since digital music collections aren't getting smaller, the speed increase for our users with large collections was quite important. Many of our developers, after the switch to mysqle (as we call it, though that's not the official name), have noticed huge speed increases in their day-to-day use of A2, so that speed increase is carrying through to the embedded server as well as the normal server. That was the first knock against SQLite.

The other blow for SQLite came for a totally different reason. Many users (myself included) have multiple computers sharing a single Amarok database. Assuming all the computers have access to the music at the same mount point (and a few other things are configured right), this allows you to scan once, play everywhere, update the same ratings no matter where you play it, and more. Even if your aren't sharing the database among multiple computers, many users want their database stored on a particular server for speed, security, or backup reasons. If you think either of these isn't a common use-case, you'd be quite wrong. MySQL and PostrgreSQL were quite happy with this workload. It's a total no-go for SQLite, simply because it's designed for a different purpose. So SQLite had two big knocks against it. K.O.

However, just as we can't rely on the user to set up Strigi/Nepomuk correctly, we can't rely on them to get their tables set up in MySQL or PostgreSQL. So we needed the database to be embeddable, so that it could just work for the user without any setup necessary on their part. MySQL, with libmysqld, had the seeds of this in the 4.1 series, it works decently in 5.0, and it's becoming fully supported (AFAIK) in 5.1. PostgreSQL, on the other hand, does not have any such thing. (They have an interesting and cool concept of their own of embedded SQL though. Update: apparently that is part of the SQL standard. Still pretty cool. Still totally different from what we mean when we are talking about an embedded server.)

So this leaves us with -- as you guessed -- MySQL. It may not be any particular person's favorite database (although it is for plenty), and I don't know how much overhead it really has in embedded form, but it fit the bill. It's both embeddable and can run standalone on the local or a separate machine (yes, this is not supported yet in A2, but it will be). It is fast and robust for large collections. It is well understood by the development team. And most of all, it is a single-backend solution that fills all of our needs.

If you're still unhappy about our decision, I'm sorry. We try to please most and can't please everyone. But we're the ones that develop and support this thing, and so we made a decision based both upon our needs as developers and the real-world use-cases from the collective feedback of thousands of users that have contacted us over the last few years. Please remember that even if most of the comments on the Dot, or to this post, (i.e. much of the sudden visible feedback) are from people that are unhappy with our decision, it is a decision that will actually suit the vast, vast majority of our users better than the other options we currently have.

We're a project that is known for being good to our users -- we listen to them, we try to implement features they want, try to be responsive with support. It's one of the things that got us where we are today. So please, dear readers -- put some faith in us. This has not been an easy decision -- we've discussed, we've argued, we've thrown things, we've made up, we've had an after-the-make-up orgy or two -- but in the end it's what we collectively felt was the right way to go, and we feel that, in the long run, it will make Amarok even mores awesomer. Hopefully you'll feel that way too.