My So-Called Blog

If you’re interested in looking at what goes into the MySQL documentation, there’s a new and kind of cool gizmo we’ve just installed that makes browsing the docs sources a breeze. Fisheye lets you browse by project, directory, author, date, and other criteria. It also provides an easy way to get to the complete changelogs, and even provides a customisable changelog RSS feed — for example, this feed has commits for just the NDB API documentation, and this is a feed of (all) my commits to the mysqldoc repository.

The display also features a graph showing how many lines are in a given directory, from which you can see that the MySQL Manual as a whole recently passed the 2-million-line mark. The NDB API docs were up to about 170K, then suddenly dropped off to about 50,000 lines in mid-July. What happened there? Well, that’s when I replaced the Umbrello XMI files containing the sources for all of the class diagrams with tar.gz archives. That’s because I got tired of generating a 250KB commit seemingly every time I changed the signature of one class method. (Now that the NDB API Guide is fairly stable, perhaps I should put the XMI files back?)

Not only is this stuff fun to look at, but I can see where it might be good for my workflow as well, starting with one of my very favourite tasks in the whole world — weekly reports. Until now, I’ve been using Thunderbird and filtering the documentation commit emails… Hmmmm… I wonder if I could just tell my boss to subscribe to the RSS feed now…? In any case, this looks like it could speed up and simplify the process considerably.

Hopefully, it’ll also be helpful to users looking for updates in the docs as well.

Comments(2)

I recently discovered that I’d made a huge blunder in the Partitioning Limitations section of the MySQL 5.1 Manual. I’d had the idea that unique keys don’t effect your choice of partitioning columns unless there’s no primary key.

My latest revision of the documentation for this limitation isn’t much better.

Then, as I was sitting here just now trying to persuade my daughter that she really ought to have something for lunch besides marshmallows, it came to me.

It’s very simple, really.

For any user-partitioned table in MySQL 5.1:

1. If a table has any unique keys, then all columns used in the partitioning expression must also be part of any and all of these keys.
2. By definition, a primary key is a unique key.
3. If the table has no unique keys, then you don’t have to worry about this limitation.
4. By definition, a primary key is a unique key.

At the moment, I’ve a very important date for tea, but as soon as I get the chance, I’ll update the Manual with this much simpler version of something I’ve tried to make very complicated for a long time.

Comments(5)

I did a bunch of housekeeping in the MySQL Cluster documentation today.

Confession: I’m the bright light who came up with the term “SQL node” because I didn’t like “API node”. At the time, it seemed like a good idea, but as time has gone on, I’ve come to see the error of my ways. However, lots of people now use the term, so I guess it’s not a good idea to change it out from under them. So here’s what I’ve decided:

API node - Any application that accesses Cluster data. Basically this means any NDB API application.

SQL node - A subspecies of API node that provides an SQL interface to a Cluster. Basically, this means a MySQL Server that’s part of a Cluster. (mysqld itself isn’t an NDB API application, but the bits that let it talk to a Cluster use the NDB API.)

Also, ndbd processes were in the distant past referred referred to as “database nodes” or even “DB nodes”. Nobody at work liked these very much (AFAIK), as we were afraid that newcomers to MySQL and Cluster would find them confusing. I wanted to call them “storage nodes” while others preferred “data nodes”.

This was an argument that I lost - after I’d started using “storage nodes” in the Manual. Ooops.

So I’ve changed “storage nodes” to “data nodes” throughout the Cluster documentation. Since some people picked up on my term before it got axed, I’ve left a note in there explaining that it’s a deprecated term. For the record, however, the official term is now “data nodes”, okay? Okay.

There are also a few places in the software where “DB node” and “storage node” still appear, such as in the output of SHOW in the Cluster management client and some NDB-specific MySQL system variables. But these vestiges of Old NDB-lish should be Going Away soon, probably in 5.0.25 and 5.1.12, and maybe in the next 4.1 release as well.

Comments(0)

I’m headed to Thailand in September (along with my friend and MySQL colleague Morgan) for an extended working holiday. It’ll be my third time there, and each time I visit, I fall more in love with the place. The culture is fascinating, the food is marvelous, and the people are some of the nicest I’ve met anywhere.

Since we’re planning to be there for a while, we figure that we can take a little time out from work and play to do some Open Source evangelising and networking. We’ve already contacted Open Source Thailand about the possibility of participating in any events they’ve got planned for Software Freedom Day. (Actually, Morgan got the ball rolling on that.)

However, we’re not limited to that particular event. Either one of us (or even both of us) would be glad to meet up with and speak to your group about MySQL, Linux, or Open Source Software in general. We’ll be staying in Bangkok for the most part, but may be able to travel to other places in Thailand. (I’m supposed to be going to Krabi for a week at some point, but I don’t yet know exactly when that will be.) So if you’d like to get together, just get in touch with one of us through the mailing list, and we’ll see what we can arrange.

Comments(0)

The answer to the question posed in the title used to be, “Linux, Solaris, and Mac OS X - anywhere else, and you’re on your own, mate. And forget about running it on Windows.”

The answer is now more like, “If it’s one of the Unices, chances are that we support running Cluster on it. Windows still need not apply.”

In fact, it might be easier now just to list the platforms we definitely don’t support for using Cluster, which as far as I know are these:

• Minix
• Plan 9
• BeOS
• Windows

(To see what’s officially supported by MySQL for Cluster - and to what extent - check out this page that’s maintained by our illustrious Support Department: Supported Platforms for MySQL Cluster.)

However, if you can get a MySQL client running on one of these, then it can talk to a MySQL Server that’s running as an SQL node in a Cluster. For example, I can access one of my Linux machines from this one using the mysql client or from PHP running on this machine, as I did the other day, like so:

< ?php
  //  This is *not* especially good PHP code, it's just a quick &
  //  dirty test, and not intended for production, right?
 
  $db1 = new mysqli('gigan', 'pizza', 'anchovies', 'ctest1');
  $db2 = new mysqli('mothra', 'spagbol', 'tomatoes', 'ctest1');
 
  $q = "CREATE TABLE fish (
           id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           name VARCHAR(20)
           ) ENGINE=NDBCLUSTER";
 
  if($db1->query($q) === TRUE)
  {
    echo "<p>Cluster table created on Gigan, displaying on Mothra:</p>n";
 
    $q = "SHOW CREATE TABLE fish";
 
    if($res = $db2->query($q))
    {
      $row = $res->fetch_array();
 
      echo '<p>' . nl2br($row[1]) . "</p>n";
 
      $q = "INSERT INTO fish
                VALUES
                ('', 'trout'), ('', 'guppy'), ('', 'bass')";
      if($db1->query($q) === TRUE)
      {
        echo "<p>Data inserted into table on Gigan.</p>n";
 
        $q = "SELECT * FROM fish ORDER BY id";
 
        if($res = $db2->query($q))
        {
          echo "<p>Retrieving data from Mothra:</p>n<ul>n";
 
          while($row = $res->fetch_object())
          {
            echo "<li>Id: $row->id; Name: $row->name</li>n";
          }
 
          echo "</ul>n<p>Run completed.</p>";
 
          $db1->close();
          $db2->close();
        }
      }
    }
  }
?>

My ambition now is to be the first kid on my block to run his blog on a MySQL Cluster.

But I’ve got to do something about performance first - when two of your four data nodes are running on 433 MHz CPUs, it’s nothing to write home about.

In any case, you can run MySQL Cluster on lots of platforms now, and it is possible to run a client app on Windows that accesses a MySQL Cluster running on one of those.

Comments(0)

Some people might have lives, but I have a webserver.

I think I’ve now upgraded just about everything (software-wise) that’s upgradable on this machine:

• Apache 1.3.33 -> 1.3.37 (Thanks for hiding the win32 binaries under “Archives” when the *nix version is out in plain view, guys)
• PHP 5.0.3 -> 5.1.4 (This required ditching my old php.ini file and doing a new one from scratch)
• MySQL 5.1.8 -> 5.1.11 (Dead easy, even on Windows - yea, TEAM!)
• Perl 5.8.7 -> 5.8.8
• Python 2.3.2 -> 2.4.3
• Tcl 8.4.12 -> 8.5.0
• BlogCMS .3.4.6 -> WordPress 2.0.4 (The RSS feed was broken, I was getting tired of seeing my posts quoted elsewhere sans formatting, and every time I tried messing with the code, it just got worse)
• Singapore 0.9.11 -> 0.10.0 (The one part of BlogCMS that I still really liked after switching to WordPress, especially after finding that the upgrade fixed most of my outstanding annoyances with the old version of Singapore)
• Cygwin 1.4.16 -> 1.5.21 (Because trying to use DOS as a shell makes me want to bang my head against the monitor until one or the other breaks and puts me out of my misery)

I also invested a few hours yesterday learning some basic ins and outs of Apache’s mod_rewrite, which is a pain to deal with (as is anything when you’ve no idea WTF you’re doing), but the results are quite nice. Especially when you’re getting pings to files that no longer exist following a makeover of your server.

For now, I’ve *cough* integrated *cough* the new gallery by means of an IFRAME, which makes it easy to view in its own window if desired. There’s a Singapore mod for this that I might try out, but it appears to be a pain if you want to use the same gallery both within and independently of WordPress, so I might just leave it the way it is.

I also updated the OS. Why is it, Microsoft, that I can patch 5 Linux systems in less time than it takes for the Windows Update “Checking for the latest updates for your computer…” to finish flashing green at me? And that I invariably wind up rebooting the Windows machine 3 or 4 times, whereas patching the Linux machines almost never requires a reboot - just if and when I feel like upgrading the kernel? I think that’s what I find most annoying about Windows - other than the fact that I’m stuck with a single desktop, there’s no shell worth mentioning, it’s a haven for viruses (an issue that basically doesn’t exist on other operating systems), and that an install of the base OS alone takes 2-3 hours not counting drivers and Windows Update, and only then can you think about installing some apps so you can actually get any work done on the thing - everything requires a FREAKIN’ REBOOT. How on Earth can people consider this “normal”?

I suppose I’d better quit ranting and get some work done on my Linux laptop PC while I wait for the server to reboot. And ask myself why I even still have a Windows machine in the house.

Comments(11)

The article that Peter Gulutzan and I wrote a couple of weeks ago is now online:

MySQL 5.1 New Features: MySQL Partitions

Here’s a brief outline:

• A Grammar Description That’s Expressed Differently From The Manual
  • Partitioning Types
  • Creating Partitioned Tables
  • Partitioning Keys, Primary Keys, and Unique Keys
  • Altering Partitioning Tables
  • Performance Considerations
• Advantages of Partitioning
  • Partition Pruning
  • Maintenance of Big Tables
• Doing It With Dates And Times
• Some Simple Speed Tests
• Comparison With Other Database Systems
• Examples:
  • Creating a Table With 500 Partitions
  • Patching Maintenance Mistakes
  • Searching One Partition At A Time
• References

Since this is a rather long article (ca. 30 pages), I’ve decided to make it even more worthwhile reading by introducing an exciting Guess The Author Contest, which I’m sure will provide countless hours of enjoyment for the entire family: The first person to guess correctly which of us (Jon Stephens or Peter Gulutzan) wrote which sections of the article will receive a modest assortment of quality Australian-made Freddo chocolates from me. Send guesses to me in an email or post them in a comment to this article. Deadline: Midnight, Sunday 27 August 2006 GMT +10.00. Employees/Contractors of MySQL AB/Inc/GmbH/KK and their families are not eligible to participate in this contest. Neither Peter Gulutzan nor MySQL AB/Inc/GmbH/KK have any involvement whatsoever in this contest, so don’t bother asking them about it, please. Winner is responsible for taxes and customs duties. Not responsible for mail that is late, stolen, misdirected, or allowed to stay out in the sun for too long. Prohibited where void. Cash value of prize offering is approximately AUD10.00. I’ll post the name and URL (if desired) of the winner on this site on or before Monday 28 August 2006. Or - if there’s no winner by then - I’ll eat the chocolates myself, and tell you how nice they were.

Comments(0)

Someone at work pointed out to me this blog post about what to do when disk partitions housing MySQL databases fill up, with the caption, “An idea for [the MySQL 5.1 Partitioning] Docs?”

My reply was, “Nope”.

Well, I was wrong. In a way. This guy’s talking about disk partititions, not partitioned tables. And using symlinks to point to data or database directories is not particularly new or exciting. However, partitioning in MySQL 5.1 provides a kind of cool new way to move table data and index files pretty easily.

The DATA DIRECTORY and INDEX DIRECTORY options have been around for CREATE TABLE for quite some time, but they’re not supported for ALTER TABLE - if you try, this is what happens:

 
mysql> ALTER TABLE t1
    ->     DATA DIRECTORY = '/home/jon/data'
    ->     INDEX DIRECTORY = '/home/jon/data';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> SHOW WARNINGS;
+---------+------+------------------------------–+
| Level   | Code | Message                        |
+---------+------+------------------------------–+
| Warning |    0 | DATA DIRECTORY option ignored  |
| Warning |    0 | INDEX DIRECTORY option ignored |
+---------+------+------------------------------–+
2 rows in set (0.00 sec)
 

Well, that’s not very happy, is it? However, in MySQL 5.1, you can do this:

 
ALTER TABLE t1
    PARTITION BY HASH(col) (
        PARTITION p0
            DATA DIRECTORY = '/path/to/dir'
            INDEX DIRECTORY = '/path/to/dir'
);
 

In this case, MySQL dutifully creates copies of the table’s .MYD and .MYI files in the new location, then replaces the originals with symlinks. Hey, presto! You’ve just moved all the bulky bits of the table. And if the path points to a location on a different disk, then you’ve just freed up a chunk of space on the original drive.

And yes, it is perfectly legal to create a partitioned table with only one partition.

If you don’t want the table to remained partitioned, here’s all you have to do:

ALTER TABLE t1
    REMOVE PARTITIONING;

Now the partitioning is gone, but the symlinks remain. And the data and index files remain where you’ve moved them by means of the ALTER TABLE ... PARTITION BY. Too easy!

The fine print: The table schema doesn’t matter, except that if the table has a primary key (or a unique key in the event there’s no primary key), then any column used for partitioning that table must be part of that key.

Comments(2)

I was becoming decreasingly impressed with the blogware that I’d been using. Possibly inspired by the workmen who woke me up clattering about on my roof at 7.00 AM yesterday, I’ve decided to rip it out and replace it with WordPress. Hopefully I’ll get things sorted in a couple of days.

In the meantime, please pardon the dust and banging noises and such.

Comments(0)