Yesterday I attended CPOSC 2009. The conference was great. It was very well run, and I liked the sessions. I would definitely attend this conference again, and will recommend that Percona sponsor it next year. I attended the following talks:

• Stop Worrying and Start Monitoring with Nagios (Andrew Libby)
• DRBD, Network Raid, High Availability and General Awesomeness (Brian Gorka)
• MySQL Performance Tuning for non-DBAs (Tom Clark)
• Wonderful Desktop Tricks, and Aesthetics (Seth Jerome)
• Jump Start Django: The Web Framework for Perfectionists with Deadlines (Rob Yates)
• Watching and Manipulating Your Network Traffic (Josiah Ritchie)

And then of course I gave my own talk on Maatkit ( …

A client recently asked me to fix some Cacti graphs that had broken after upgrading the Cacti templates I wrote for MySQL. The symptoms were weird; I’m not sure I understand fully what happened, but some of the graphs were OK and some had only part of the data they were supposed to. Some graphs would have one data element as usual, and others would be nan (not a number).

After turning on the debug logs, I found that the script was returning the data correctly — it was not a script problem. But after Cacti got the data from the script, it wasn’t associating it correctly with the RRD archives. Here’s a log message:

10/14/2009 12:05:05 PM - CMDPHP: Poller[0] Host[11] DS[1270] CMD: /usr/bin/php -q
  /opt/cacti/scripts/ss_get_mysql_stats.php --host dbserver
  --items bj,bm --user --pass , output: bj:68 bm:64
10/14/2009 12:05:05 PM - CMDPHP: Poller[0] DEVEL: …

I wrote a while ago about how mext works — it runs “mysqladmin extended-status” and formats it nicely. But what if you want to use it to format saved output that you’ve put into a file? It’s actually very easy. You can tell it what command-line to run to generate its input. By default you are probably going to tell it to run “mysqladmin ext -ri10″ or something like that, but you can just as easily make it run “cat my-saved-output”.

Let’s see how this can be useful. Imagine I have a server that stalls every now and then, and I’ve set up mk-loadavg to watch for this and capture information about system activity with a script that contains

$ mysqladmin ext -c 30 -i1 > mysqladmin-output.txt

That’ll gather 30 samples one second apart. Now I’ll format it:

$ wget -q …

I’ll be attending and presenting at the 2009 Central Pennsylvania Open-Source Conference. My session is on Maatkit. I see Tom Clark has a session on MySQL performance! I hope to see you there — I’ve really become a fan of these regional conferences.

By the way, I’ve also created a speaker badge by adapting a wallpaper someone else made — you can find it on the sidebar of my blog if you’re also a speaker.

Related posts:

1. Speaking about Maatkit at CPOSC I’m
2. Speaking at EdUI Conference 2009 I’m

3. …

I am speaking at Enterprise LAMP Summit 2009 (and also should be speaking at the Camp the next day, but I see the schedule isn’t quite updated yet). My talk at the Summit will not be on the future landscape of MySQL — that’s a mistake. My talk’s title isn’t finalized yet.

This conference is the first weekend in November, in the Nashville area. I hope to see you there!

Related posts:

1. Speaking at CPOSC 2009

   I

2. …

The linux.conf.au organisers have given all miniconfs an additional few weeks to spruik for more proposal submissions, huzzah!

So if you didn’t submit a proposal because you weren’t sure whether you’d be able to attend LCA2010, you now have until October 23 to convince your boss to send you and get your proposal in.

I’ve been investigating a few different storage engines for MySQL lately, and something I’ve noticed is that they all list what they support, but they generally don’t say what they don’t support. For example, Infobright’s documentation shows a list of every data type supported. What’s missing? Hmm, I don’t see BLOB, BIT, ENUM, SET… it’s kind of hard to tell. The same thing is true of the list of functions that are optimized inside Infobright’s own code instead of at the server layer. I can see what’s optimized, but I can’t see whether FUNC_WHATEVER() is optimized without scanning the page — and there’s no list of un-optimized functions.

I don’t mean to pick on Infobright. I’ve recently looked at another third-party storage engine and they did exactly the same thing. It’s just that the docs I saw weren’t public as …

Key-value databases are catching fire these days. Memcached, Redis, Cassandra, Keyspace, Tokyo Tyrant, and a handful of others are surging in popularity, judging by the contents of my feed reader.

I find a number of things interesting about these tools.

• There are many more of them than open-source traditional relational databases. (edit: I mean that there are many options that all seem similar to each other, instead of 3 or 4 standing out as the giants.)
• It seems that a lot of people are simultaneously inventing solutions to their problems in private without being aware of each other, then open-sourcing the results. That points to a sudden sea change in architectures. Tipping points tend to be abrupt, which would explain isolated redundant development.
• Many of the products are feature-rich with things programmers need: diverse language bindings, APIs, embeddability, and the ability to speak familiar …

I used to develop with MySQL, and those were the golden days. These days I don’t write queries so much. But yesterday I needed to answer this question: are there any issues in our issue-tracking system that meet the following criteria?

• The last two or more emails are from the customer
• These emails were separated by at least two hours (i.e. it wasn’t a single train of thought)

I could do it with all kinds of correlated subqueries and so on — but maybe I could also just do it without them, no? Can this be done with plain old JOINS and GROUP BY? I’m sure you know the answer.

Here’s my approach: group emails by issue, and concatenate the dates they were sent in reverse order. If an email was sent from Percona to the customer, replace the date with a magical OUTBOUND constant. The result might look like this: “2009-09-11 13:17:34,OUTBOUND,…”. I’ll change this to create a good sample …

There’s a really important difference between a unique index (MySQL’s answer to a “unique constraint”) and a primary key in MySQL. Please take a look at this:

CREATE TABLE `t` (
  `a` int,
  `b` int,
  `c` int,
  UNIQUE KEY `a` (`a`,`b`)
)

The combination of columns a, b should uniquely identify any tuple in the table, right?

select * from t;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 | 
| NULL | NULL |    1 | 
| NULL | NULL |    1 | 
| NULL | NULL |    1 | 
+------+------+------+

Wrong. Our arch-enemy NULL messes things up again:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does …