I (belatedly) noticed a meme running on Planet MySQL regarding wishlist items for the company. I think it started with Jay Pipes and Mårten Mickos, but has since moved on to users. In particular, I'd endorse most of Jeremy Cole's and Ronald Bradford's wishes myself as well.

But let me jump on the bandwagon and offer my view of the things that would most help us run and develop our services.

1. Online table changes. Ronald mentioned this as well, but I have to emphasize this more: for all the good that InnoDB did in terms of eliminating table-level locks for INSERTs and UPDATEs, it has caused almost more pain for us in terms of locks during schema updates. Until you've tried it yourself, you can't imagine the pain of running an ALTER TABLE ADD INDEX on a 150-million row table during a routine application upgrade that would otherwise be over in 10 minutes...

2. Reliable baseline functionality, including replication. This is the big one: MySQL 5.0 improved many things, but one thing it didn't improve is dependability – version 4.1 was far more stable. Maybe we're pushing the boundaries, but when you're managing 20+ database servers and several terabytes of data, what you don't want to start your days with is a check of which slaves have stopped updating and which processes you need to restart and scheduled maintenance jobs to run again.

3. A smarter query planner. In addition to our normal application OLTP-style development, we're currently busy working on a multi-terabyte DWH project. MySQL happily proceeds to execute three-table JOIN queries sequentially scanning 200 million row facts when it can't quite figure out whether a query selects 20 or 200 rows from a dimension table. Oops. Please come back after lunch...

4. Index assistance. While most of Microsoft's wizards are not very useful, SQL Server has for years had one that is really nice; one that captures all queries against a database and evaluates whether new indexes would improve performance, and whether existing indexes are helping. It's really cumbersome to do this with the slow_query_log and analysing EXPLAIN output.. Especially since that output isn't all that detailed (see previous)...

5. Runtime-changeable InnoDB and logging parameters. Sometimes finding the root of a performance issue is a hit-and-miss job of looking for slow queries, parallel updates, buffer pool settings, etc parameters, but many of the settings you need to try require a restart to take effect. Not only does that make it impossible to even contemplate fixing something in a live installation, running benchmarks in a test deployment is also a slow and cumbersome undertaking.

Some earlier time I might have mentioned on this list also integrated hot backups – today it will only make it as a runner up. Not that the standard tools are any better, but because we've developed a mechanism that works on top of storage system snapshots, we can deal with it anyway. No, mysqldump really doesn't do the trick, thanks. Not when you're talking of databases in the range of hundreds of gigs. Another one that I might have wanted to mention was partitioned tablespaces, but I guess when 5.1 eventually becomes GA, I get to offer an opinion on that...