No more MySQL on my machine

I've said it before: I think MySQL is a toy, and I want as little to do with it as possible.

Unfortunately, since the KDE PIM suite depends on akonadi, which depends on a database, which was initially implemented against MySQL, not having MySQL installed on a Debian machine if you want to use the KDE PIM suite, for the longest time, wasn't even possible. Today it is, but it requires some manual steps:

sudo apt-get --purge install akonadi-backend-postgresql akonadi-backend-mysql-
akonadictl stop
rm -rf .config/akonadi
rm -rf .local/share/akonadi
cat > .config/akonadi/akonadiserverrc <<EOF
[%General]
Driver=QPSQL

[QPSQL]
Name=akonadi-wouter
Host=
Options=
StartServer=false
EOF
createdb akonadi-wouter

Change "akonadi-wouter" for a suitable database name.

And now it should work. Note that this requires you run PostgreSQL system-wide and create a system-wide database in that server. It should, in theory, be possible to have akonadi run its own private PostgreSQL instance, much like how the MySQL backend does things, but a) I couldn't get that to work, and b) I don't think having each user run their own database server instance is a good idea to start with, so honestly I didn't try very hard.

And there, pronto! I now no longer have toy databases on my laptop. Word.

This question begs to be asked
Why do you think mysql is a toy? And why do you think a toy is bad?
Comment by flaggy Wed Sep 7 19:19:31 2011
Re: No more MySQL on my machine

"b) I don't think having each user run their own database server instance is a good idea to start with"

Here are some downsides of making Akonadi use a system-wide database (be it MySQL or PostgreSQL):

  • needs a lot of manual configuration
  • your data is not located in your home directory anymore (circumvents encryption of home dir)
  • messes with the schedulers (postgres user "pays" for the IO and CPU time, not your user)

The only upside I can think of: - may save a few bytes compared to the per-user database scheme

Comment by Anonymous Wed Sep 7 23:44:25 2011
Re: No more MySQL on my machine

You are so wrong.

  • "manual configuration", yes, well, that's a choice you make. If you don't care, it doesn't matter. If you do care, you'll have to do manual configuration anyhow. So why's that a problem?
  • Not located in your home directory, and encryption: I'm not a fan of encrypted storage, but at any rate, if you do that, full disk encryption is always the better choice, since if you 'only' encrypt your home directory, you'll find that you'll be writing trace amounts of data to other places, such as /tmp or /var. This will result in data leaks. If you do full disk encryption, then it doesn't matter where the data is, since everything is encrypted.
  • It does not "mess" with the scheduler, it allows you to make the scheduler do what it's supposed to do.
  • Only running one instance saves you a lot more than just "a few bytes". Database servers allocate memory so that they can store temporary results in RAM. While the allocated memory is usually not static over the life time of the server, it is not always immediately deallocated after use. If every user runs their own server instance, then every server instance may allocate memory, resulting in much higher memory requirements than if you run only one system-wide instance.

Upsides you missed: - Database tablespace is shared among all users with the system-wide databases, so data usage will be more economic. Of course this does not happen with MySQL, since MySQL is a toy and (unless you use InnoDB, which is not the default) does not do tablespaces. - Sysadmin can more easily tune runaway databases, which is much harder to do if you have 100 server instances each with their own configuration - Depending on implementation, a database server may detect that two databases have a similar layout and share cached optimizer plans among them. - Running a single database server instance means the database server has information on all reads and writes among all databases, so it can optimize better. This will improve performance.

Note also that I didn't say manually configuring akonadi is the right choice for everyone. Most of the above is moot if you're running a single-user system, anyway. But hey, I prefer doing things this way.

Comment by wouter Thu Sep 8 00:58:44 2011
Re: No more MySQL on my machine
  • "It does not "mess" with the scheduler, it allows you to make the scheduler do what it's supposed to do." Really? Users A and B are pushing the same system to its limits. A and B are watching a video each, while B is also searching through his 500k emails. Consequence: A gets 33% of cpu and io time, B gets 67%.

  • "Sysadmin can more easily tune runaway databases, which is much harder to do if you have 100 server instances each with their own configuration" Tune for whom? For user A with his 100 emails a year, or for user B with his 100k emails a year?

  • "Depending on implementation, a database server may detect that two databases have a similar layout and share cached optimizer plans among them." I doubt there's any db server that does that.

Comment by Anonymous Thu Sep 8 04:10:57 2011
Layout
Completely unrelated: your navigation hiding parts of the content (e.g. on http://grep.be/blog/en/) is really annoying.
Comment by Anonymous Thu Sep 8 04:15:13 2011