If you’re like me, you’ve gotten tired of writing endless test cases for parsers that can understand the thousands of variations of text output by SHOW INNODB STATUS. I’ve decided to solve this issue once and for all by patching MySQL and InnoDB to output XML, the universal markup format, so tools can understand and manipulate it easily. Here’s a sample snippet:

<status><![CDATA[
=====================================
100320 15:46:24 INNODB MONITOR OUTPUT
=====================================
... text omitted, but you get the idea ...
]]>
</status>

PS: Yes, this is a late April Fool’s joke.

Related posts:

1. Don’t forget about SHOW PROFILES It seems t
2. …

I haven’t been blogging much about the changes in MySQL for a while. But the MySQL and InnoDB engineers have been doing a ton of work over the last couple years, and now it’s seeing the light of day, so it’s time to offer words of congratulations and appreciation about that.

I was holding my breath for a big-splash 5.5 GA announcement at last week’s conference, but I was wrong. Still, there’s a lot to talk about in 5.5, with a dozen or more substantial blog posts from the InnoDB and MySQL teams alone over the last week or so! Here are a few choice tidbits of the good, the bad, and the ugly.

InnoDB is the default storage engine

“No big deal,” I thought. “Serious users do this anyway.” But then Morgan Tocker pointed out that it really is a big deal. This is going to cause a sea change in the way MySQL is …

I got a couple of questions and comments about OpenSQL Camp in the past week, and I thought it would be worth noting down the history, because I think there is some difference in perception and memory about this series of events. The following is only my point of view.

What is OpenSQL Camp?

I can say what I had in mind when I created the original event, but this is bigger than me, so I don’t get to dictate anything. I wanted a free technical event created entirely by and for a community of open-source databases, in an inclusive sense. Not created or heavily influenced by someone employed by a corporation whose job title includes the word “Community,” but really by a community themselves. There’s nothing wrong with Community So-And-So employed at a corporation, but they are by nature a liaison with that company, and it’s not the same thing. My original …

I gave a presentation today at the MySQL Conference & Expo 2010, titled SQL Injection Myths and Fallacies. Thanks to everyone who came to my talk! I appreciate your interest in learning to develop more secure applications. SQL Injection is a serious threat to web applications, and it's only going to get worse. It's incumbent on you as software developers to learn how to write secure code!My

One of the things I preach about a lot is good monitoring of your database servers; having tools in place to tell you both what good looks like and when things go bad is critical for large scale success. But sometimes you just need to monitor a momentary process, where setting up a check in your normal monitoring software is overkill. In these cases one tool that can help out is the watch command.

Case in point, the other day I needed to back up a fairly large partitioned table (about 1.3TB on disk). The plan? A quick little script to pg_dump each of the partitions (about 325). Feed the script through xargs -P so I don't swamp the box, but I get some concurrency out …

Brian got interviewed by O’Relly recently, and part of it quoted him as saying this:

When everything doesn’t fit onto a computer, you have to be able to migrate data to multiple nodes. You need some sort of scaling solution there… MapReduce works as a solution when your queries are operating over a lot of data; Google sizes of data. Few companies have Google-sized datasets though. The average sites you see, they’re 10-20 gigs of data.

Users shouldn’t need to put that data onto multiple machines anyway. In fact, I don’t think we need a multi-machine solution for the common case at all. We need software that can scale up with today’s hardware. 37signals likes to run boxes with half a terabyte of RAM. Are we there yet with MySQL and InnoDB? No. Postgres? No. Anything …

Wow. Now’s not a good day to announce anything true on your blog. I see a bunch of stuff that probably is true — but who’s banking on that? Is there really a new version of HeidiSQL? Is Arjen really moving to California to do MySQL work? Is Tokutek really purchasing Oracle? Gee, I’m not sure.

If I had any thoughts left in my head, I’d make a funny myself, but my head’s pretty empty. Believe that if you want to.

Related posts:

1. One of the best blog headlines I have ever read Cruising t
2. The cache-oblivious algorithms inside Tokutek’s TokuDB Tokutek ha

In an application such as a database server, instrumentation is like sex: it’s not enough to know how often things happen. You also care about how long they took, and in many cases you want to know how big they were.

“Things” are the things you want to optimize. Want to optimize queries? Then you need to know what activities that query causes to happen. Most systems have at least some of this kind of instrumentation. If you look around at… let’s not pick on the usual targets… oh, say Sphinx, Redis, and memcached. What metrics do they provide? They provide counters that say how often various things happened. (Most of these systems provide very few and coarse-grained counters.) That’s not very helpful. So I read from disk N times, and I read from memory N times, and I compared rows N times… so what? I still don’t know anything relevant to execution time.

That’s why we need to measure how long things took. It’d be …

To program is human, to instrument is divine. Complex systems that will support a heavy workload will eventually have to be tuned for it. There are two prerequisites for tuning: tunability, and measurability.

Tunability generally means configuration settings. Adding configuration settings is a sign of a humble and wise programmer. It means that the programmer acknowledges “I don’t understand how this system will be used, what environment it will run in, or even what my code really does.” Sometimes things are hard-coded. InnoDB is notorious for this, although don’t take that to mean that I think Heikki Tuuri isn’t humble and wise — nobody’s perfect. Sometimes programmers set out to create systems that are self-tuning. I’m not aware of any success stories I can point to in this regard, but I can point to plenty of failures. Perhaps I can’t think of any successes because I don’t need to.

Measurability …

I got a comment from a reader about the Naive Trees section of my presentation SQL Antipatterns Strike Back. I've given this presentation at the MySQL Conference & Expo in the past.I'd also like to mention that I've developed these ideas into a new book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming. The book is now available in Beta and for pre-order from Pragmatic