jcole’s weblog

In the past few days, MySQL Community 5.0.41 was released. While reading through the changelog, I noticed the following entry:

The patches for Bug #19370 and Bug #21789 were reverted.

Upon looking at Bug #21789, I noted that it was originally committed in MySQL Enterprise 5.0.32, released December 20th, 2006. The next community release which would have contained the patch is MySQL Community 5.0.33, released January 9th, 2007. This means that not only was the patch not vetted by the community, but there was a full 20 days between the enterprise release with the patch, and the next community release which contained it. According to MySQL’s release process, it could have been a full 5 months, given the right timing…

The patches were rolled back in MySQL Enterprise 5.0.40, released April 17th, 2007. Yes, the patch was committed without much vetting, and then had to be rolled back, 118 days later, in the “enterprise” version of MySQL. Why?

Back when MySQL first polled me about the community/enterprise split, I told them that this would happen. The reason it happened, of course, is that MySQL willingly shut down its only avenue for vetting these sorts of patches. They made a similar split to RedHat Enterprise Linux (RHEL) vs. Fedora Core Linux, but for some reason broke the process at the same time: they produce releases of community much less often than enterprise. That means that nobody in the community is testing the features that they stick in enterprise. They just get pushed out with no public vetting.

The way that RHEL and Fedora work is that all the shiny new stuff is pushed into Fedora first. After it has been deemed that the Fedora process, plus plenty of internal vetting, has been successful, those patches or new versions are merged into RHEL either for the next patchset, or the next full release. This, of course, means that Fedora is always ahead of RHEL. That’s exactly the idea. RedHat is betting that enterprise users (whatever that really means, these days) want a stable slowly-moving release that is “guaranteed” to work, and easy to keep up with.

On the flip side, Fedora is great for users who want the latest and greatest all the time—primarily desktop users and developers—people who are willing to work through the quirks and contribute a bit back in the way of feedback. People that like to run yum update a couple times a week. What do they get in return? A (usually) good product that is completely free.

Why did MySQL reverse the process and make it (in my opinion) useless? I suspect their sales team thinks it would look bad if the community users “get more” than the enterprise ones. But, take a look at the MySQL releases themselves, discounting any other “features”—which are debatable—that you receive with MySQL Enterprise. Why would I pay to get a release with the same unvetted, broken, may-be-rolled-back patches as everyone else gets? Why would I suggest that our customers pay?

Blogging used to be fun.

Then it started to suck. Spam sucked. Life sucked.

Now life is good. Spam is no more. Matt told me to use Akismet; I was skeptical. I am no longer skeptical. I love you, Akismet.

Akismet has caught 501,725 spam for you since you first installed it.

Yup. Since January 15.

Just a little heads-up and a bit of MySQL-related technical content for all of you still out there following along…

At Proven Scaling, we take on MySQL performance problems pretty regularly, I’m often in need of good tools to characterize current performance and find any issues. In the database world, you’re really looking for a few things of interest related to I/O: throughput in bytes, requests, and latency. The typical tool to get this information on Linux is iostat. You would normally run it like iostat -dx 1 sda and its output would be something like this, repeating every 1 second:


Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 8.00 0.00 4.00 0.00 96.00 0.00 48.00 24.00 0.06 15.75 15.75 6.30


Most of the output of iostat is interesting and reasonable for its intended purpose, which is as a general purpose way to monitor I/O. The really interesting things for most database servers (especially those in trouble) are:

• avgrq-sz — Average request size, in kilobytes.
• avgqu-sz — Average I/O queue length, in requests.
• await — Average waiting time (in queue and scheduler) before servicing a request, in milliseconds.
• svctm — Average total service time for I/O requests, in milliseconds. This includes await, so should always be higher than await. This is the most interesting number for any write-heavy transactional database server, as it translates directly to transaction commit time.
• %util — Approximate percent utilization for the device.

There are one major problem with using iostat to monitor MySQL/InnoDB servers: svctm and await combine reads and writes. With a reasonably configured InnoDB, on a server with RAID with a battery-backed write cache (BBWC), reads and writes will have very different behaviour. In general, with a non-filled cache, writes should complete (to the BBWC) in just about zero milliseconds. Reads should take approximately the theoretical average time possible on the underlying disk subsystem.

I’ve often times found myself scratching my head looking at a non-sensical svctm due to reads and writes being combined together. One day I was perplexed enough to do something about it: I opened up the code for iostat to see how it worked. It turns out that the core of what it does is quite simple (so much so, I wonder why it’s C instead of Perl) — it opens /proc/diskstats, and /proc/stat and does some magic to the contents.

What I really wanted is a histogram of the reads and writes (separately, please!) for the given device. I hacked up a quick script to do that, and noticed how incredibly useful it is. I recently had to extend it to address other customer needs, so I worked on it a bit more and now it looks pretty good. Here’s an example from a test machine (so not that realistic for a MySQL server):

util:   1.27% r_ios:     0  w_ios:     1  aveq:     0,
ms : r_svctm                     : w_svctm
 0 :                             :
 1 :                             :
 2 :                             :
 3 : x                           :
 4 : x                           :
 5 : xxx                         :
 6 : xxxx                        :
 7 :                             :
 8 : x                           : x
 9 : x                           : xx
10 : x                           : xxxxx
11 :                             : xxxxxxxxxxxxxxx
12 :                             : xxxxxxxxxxxxxxxxxxxxxxxxx
13 : xx                          : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
14 :                             : xxxxxxxxxxxxxxxxxxxxx
15 : xx                          : xxxxxxxxxxx
16 : x                           : xxxxx
17 : x                           : xxxxxx
18 :                             : xxxx
19 : x                           : xx
20 :                             : x
21 :                             : x
22 :                             : x
23 :                             : x
24 :                             : x
25 :                             :
26 :                             :
27 :                             :
28 : x                           :
29 :                             :
30 :                             :
++ : 0                           : 250

It uses Curses now to avoid redrawing the entire screen, and I’ve got a ton of ideas on how to improve it. I have a few more must-haves before I release it formally to the world, but I wonder what more features people would want from it. It is Linux-only for the foreseeable future.

What do you think?