My So-Called Blog

Giuseppe Maxia’s new article at the MySQL DevZone rightly points out that using partitions doesn’t work by magic; as with any other enhancement, it requires a bit of know-how to realise its value. MySQL 5.1 Partitions in Practice reviews some of the basics and points out a few gotchas (if you want to partition on a DATE or DATETIME column, pay close attention here). He then provides some realistic examples showing how you actually get partition pruning working for your queries and backs it up with some interesting performance figures.

On a related note, I recently reorganised the MySQL Manual’s discussion of functions in partitioning expressions to get rid of the multiple listings (at Giuseppe’s behest). Now there’s a single Supported Functions list, which we hope you’ll find less confusing and more useful.

Comments(1)

A little over a year since the project to write this book began, the MySQL 5.1 Cluster Certification Study Guide is now at long last available. It covers everything you’ll need to know to pass the Certified MySQL Cluster 5.1 Database Administrator exam, including MySQL Cluster Concepts, Architecture, Configuration, Deployment, NDB Internals basics, High Availability techniques, Security Issues, and more.

(It does not cover MySQL Cluster 5.1 Carrier Grade Edition, for the simple reason that MCCGE features are not part of the official mainline MySQL 5.1 release. However, those features will be part of MySQL 6.0 [yes, MySQL 6.0 is already starting to happen, and there are already some cool new Cluster, Replication, and XML features in the 6.0 tree, but I’m starting to get off-topic here…], and there’s a good chance that there will be a 6.0 version of the certification course and book in a year or so.)

In addition to yours truly, the authors include NDB developer Stewart Smith, Pro MySQL author Mike Kruckenberg, and MySQL training course developer (and former MySQL certification exam developer) Roland Bouman. Roland also helped me edit and index. (Did I mention that this is my first official Editor credit? Yay.)

You can order your copy here, and still have time to read it and then get certified on MySQL 5.1 Cluster before MySQL 5.1 is GA.

Comments(0)

We’ve been getting lots of questions about how MySQL 5.1 optimises queries on partitioned tables. Recently, we added some new information about this to our Internals Manual provided by one of our developers, Sergey Petrunia. (I hope that my edits and formatting haven’t messed up anything — if there are any problems with what’s been published in the Internals Manual, then the fault’s probably mine and not his.)

The new section can be accessed here, and includes coverage of the following topics:

• An overview of how MySQL performs partition pruning
• What partitioning and subpartitioning intervals are, and why they’re important
• Converting WHERE and ON clauses into intervals
• Partition selection
• Where to find partition pruning and selection code in the 5.1 sources

Sergey’s included several helpful examples and diagrams as well. Even if you’re not interested in hacking the source, you might find this material useful for understanding better how MySQL Partitioning works “behind the scenes”, so be sure to check it out.

Comments(0)

Recently, MySQL Cluster developer and team leader Martin Sköld committed some major revisions and additions in the storage/ndb/ndbapi-examples directory of the MySQL 5.1 source. Although I don’t think these have yet been pushed out to the public 5.1 tree, I went ahead late last week and added them to the Practical Examples section of the NDB API Guide so that interested parties can take advantage of a number of corrections and other improvelements that Martin’s made.

There’s two new additions, also — here and here — these demonstrate for the first time in the Guide the use of the API with multiple clusters. You might find these particularly useful if you’re interested in rolling your own application-level partitioning and/or replication for Cluster.

Martin’s also updated the makefiles for all of the examples. These aren’t currently included in the Guide, but if you’re interested in having them added, let me know in a comment and I’ll see what I can do about it.

Comments(0)

Ever wonder what all those binaries with names starting in “ndb” were doing in mysql/bin? Not surprisingly, they’re MySQL Cluster monitoring, utility, and demonstration programs, and after a couple of fun-filled weeks of experimenting with them (and asking a few of the usual sorts of silly questions I’m know for asking in the right quarters) I’ve been able to document most of them in a new Cluster Utility Programs section of the MySQL Manual (I’ve linked to the 5.1 version, but they’re available in 4.1 and 5.0, too).

If you’ve not looked at these before, check them out. A couple of them — like ndb_waiter and ndb_desc — are handy for monitoring a MySQL Cluster or getting information about NDB tables and other Cluster data objects; others — such as ndb_select_all and ndb_delete_all — can accomplish some basic maintenance tasks on large tables, sometimes faster than can be done using mysqld. And if you’re interested in writing Cluster apps against the NDB API, you’ll find that these might provide some nice examples to help get you started.

I’ve not yet added a description of ndb_config to the Manual, but I’ll try my best to do so in the next day or two. Otherwise, the list of utilities that have been documetned in the MySQL Manual is fairly complete.

Comments(3)

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)