Taking the cue from Jay Pipes, as so many other bloggers have done, I present the five things I would most like to see in a future release of MySQL.
1. Metrics, Metrics, Metrics!
The more the better. I want to be able to debug issues on the database, but MySQL lacks good metrics on what exactly the database is doing at any given time, or what it did in the last little while.
2. Lock Trees
Again, I’d like to identify lock holders and blocking sessions on the database. Right now this is almost impossible, specifically with InnoDB.
3. Resource Usage Optimization
A query or a few queries should not be allowed to hog the whole database as they do currently. MySQL should take a long running query and push it down the priority list, giving higher priority to the smaller/faster queries. This will enable the system to still be responsive while a few bad queries are running slowly in the background.
4. Query Tracing
I’d like to be able to get an accurate idea of what resources a query actually takes — under a special debug/trace mode for instance — as opposed to just the time taken.
5. Read-Only Slaves
I would like to be able to set up slaves in a “read-only” mode in which the only thread with the ability to write would be the slave thread. This will save us a lot of headaches where developers “accidentally” run stuff on the slave instead of the master.
The list isn’t that big. MySQL is pretty good at what it does, its just a little frustrating from a DBA point of view to debug and tune since all of the feedback is indirect. Query times, system load, and I/O throughput are all very indirect measurements, and they can be quite meaningless when you’re trying to indicate quantitatively how good or bad a system design is.
The read-only slave is perhaps the only one of the five that doesn’t fall somehow in the classification of metrics. It comes from my own laziness — I have had to rebuild far too many slaves because the developers pointed to it instead of the master db, and I really don’t want to have to do another one because of someone’s carelessness.
I have been informally giving MySQL AB feedback on what I’d like, and I believe a few of these features are in the works. Feel free to add to this list any you’d like to see, and I will pass them on to MySQL.
Cheers!
–Raj.
5 Comments. Leave new
#5 is already there? https://dev.mysql.com/doc/refman/5.0/en/replication-options.html#option_mysqld_read-only
Or do you want this to be even more restricted?
As for your #5, there is already a read_only setting which will only allow two people to change data, the SQL thread in replication, and anyone with the SUPER privilege. As long as you don’t give your developers SUPER, then it will work exactly how you want.
SET global read_only = 1;
Your list has a couple items I’ve worked to address with innotop and mysqltoolkit. Still, there is no substitute for something the server implements itself; that will be much better than anything I can do with scripts.
Could you give more specific examples of #1 and #3, perhaps from other RDBMS’s? It would probably be 2 posts…but I think it would be really helpful for folks who don’t even realize what they need….
Looks like this blog post has received a lot of attention / comments.
While I never intended it to be more than a quick-list, I guess my part-oracle background does show through, and there is a lot of stuff here that requires clarification
Rather than replying to each post, I think I’m going to write up a separate post about each of the 5 items in my wishlist in more detail, with real-world examples based on issues I’ve faced and how I would have liked to resolve them.
In short though, for #5 yes, I’m looking for something more restrictive than what MySQL currently provides. for #1 and #3, I’m looking at how I can quickly get to the root of problems in Oracle by looking at the metrics (wait events, locks etc.) that Oracle provides which I find sorely missing in MySQL..
Details to come.
–Raj.