My So-Called Blog

As I mentioned a few days ago, I’ve been working recently on documenting the Cluster utilities; I’m still working on ndb_config, but should get that into the Manual Real Soon Now™. In the meantime, here’s a tip I received from one of the developers. This generates a connectstring for use with data, SQL, or API nodes, using ndb_config, which you should be able to find in your mysql/bin directory:

ndb_config −−config-file=path/to/config.ini −−query=hostname,portnumber −−fields=: −−rows=, −−type=ndb_mgmd

For this to work, you need to have the Cluster management server running, with a viable config.ini at the location specified by path/to/config.ini.

Hope that someone finds this useful.

Comments(1)

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)

The first batch of photos from the current visit can now be viewed in the Gallery (direct link). These are from a boat ride down the Chao Phraya River, stopping at Wat Pho (home of the Reclining Buddha), Wat Phra Kaeo, Wat Arun, and the Grand Royal Palace, that we made on 15th September.

I’ve some more — from last week’s visit to Krabi Province and Koh Lanta Island, as well as from just around the neighbourhood here on Sukhumvit 77 on Bangkok’s Lower East Side — that I’ll try to finish editing and to post sometime in the next couple of days or so.

Practically everything and everybody that I’ve seen in this city has been worth photographing. It’s probably a good thing that I’ve only two memory cards and two sets of batteries for the camera, or I’d get nothing else done.

Oh, if I only I hadn’t had to go back to work this week… Mike, where’s that tripod?

Comments(0)

If you’ve been using the Event Scheduler that was introduced in MySQL 5.1.6, there are some changes in the workings of the event_scheduler server variable. These changes are fairly simple — but not trivial — and were just committed to the 5.1 tree a few days ago. They will definitely be part of the official 5.1.12 release — which should be out just about any day now — so you’ll want to be aware of them, and how they’ll affect the behaviour of the Event Scheduler, before you upgrade.

This variable determines whether the Event Scheduler is available, and whether it’s actually running. In MySQL 5.1.12, it can be used in either of two ways:

1. a server option that can be used from the command line (--event-scheduler), or in the my.cnf file (event-scheduler)
2. a global variable event_scheduler that you can get and set within mysql or any other MySQL client.

As a server option, it can take one of three values:

• OFF: The Event Scheduler is available, but the event scheduler thread does not run, and no scheduled events are executed. This is the default state of the Event Scheduler.
• ON: The Event Scheduler is active, and events are executed according to their schedules.
• DISABLED: The Event Scheduler is not available, and cannot be made available without restarting the MySQL Server.

As a global variable, event_scheduler can only be toggled between ON and OFF.

You can read the Event Scheduler’s state from a client app using either of these two queries:

• SELECT @@GLOBAL.event_scheduler; (you can actually just use @@event_scheduler instead of @@GLOBAL.event_scheduler here, but the value is always the same as the global value).
• SHOW VARIABLES LIKE 'event%' ;

You can set it from a MySQL client with the following restrictions:

• The current value must be one of ON or OFF. If the current value is DISABLED, then any attempt to set it from the client fails with an error. In other words — if MySQL was started with the --event-scheduler=DISABLED command-line option, or if event-scheduler=DISABLED is present in my.cnf — then you can’t change event_scheduler at all from mysql (or any other MySQL client).
• You cannot set event_scheduler to DISABLED from a MySQL client, either. You must restart the server, making the change from the command line or in my.cnf before doing so.
• event_scheduler must be set as a global variable. If you attempt to set it as a session variable (either implicitly or explicitly, using SESSION), then the SET statement will fail with an error.
• The previous item implies that you must have the SUPER privilege to change the value of event_scheduler at all, just as with any other global variable in MySQL.

This variable/option is no longer numeric (as in 5.1.11), but rather enumerated. It is possible to use 0 instead of OFF, and 1 instead of ON, but I don’t recommend it. This is because, there’s no numeric alias for DISABLED, and trying to set event_scheduler to any value other than 0 or 1 now causes an error, as an esteemed colleague of mine discovered while I was away on holiday, before I had a chance to update the Manual. The output from the SHOW or SELECT statements shown above will always yield one of the values ON, OFF, or DISABLED…

…As soon as the fix for Bug #22662 — now pending — is merged, that is. Sorry about that, Andrey. ;)

Comments(1)

I’ve had an introductory article to MySQL Cluster 5.1 Disk Data published in the September 2006 issue of International PHP Magazine.

If you’re using Cluster or you’re interested in doing so, and you’ve not yet tried out MySQL 5.1, you’ll find that disk data storage makes MySQL Cluster more flexible, scalable, and cheaper to run than MySQL 4.1 and 5.0 Cluster. In the article, I’ve outlined some reasons why this is so. The article covers the basics of creating disk-based Cluster tables, and discusses some Disk Data do’s and dont’s. There’s also some info about some other improvements to MySQL Cluster that are being made in 5.1, as well as some diagrams and sample PHP5 code for accessing a MySQL Cluster. Just in case you’re not that familiar with setting up a MySQL Cluster, I’ve also included a quick setup guide to help you get started.

You can read more about the article or download the PDF version from the International PHP September TOC page. A single issue is €4.00 or you can get a year’s subscription for €42.00.

Comments(0)

I was on holiday most of this week in Krabi Province, where I awoke Wednesday morning to find nothing but films about the King running on the all Thai channels and reports on BBCWorld that there’d been a coup d’état in Bangkok. Needless to say, I was a bit taken aback at news video showing tanks around Government House, but all in all it seems to be about as much a non-event as such things can be.

I don’t really know the ins and outs of Thai politics, and as a guest here, it’s not really my place to speculate very much, but so far the new bosses haven’t been very heavy-handed, and most people that I’ve spoken with thought something had to be done about the previous régime, which they’ve said was increasingly corrupt, divisive, and ineffectual. On my return to the capital yesterday, I took a taxi all the way across the city from the airport (dropping off Nui in Ladprao before going on to On Nut), and saw exactly two soldiers the whole time — they were standing guard at an expresway tollbooth and looking extremely bored.

So far, at least, there doesn’t seem to be much to get excited about. Life in Bangkok appears to be going on pretty much as normal, and I’ve decided to carry on as usual until I see a reason to do differently.

(Which for me means catching up on about 800 emails and posting some holiday photos to my gallery, which I’ll try to get done over the weekend.)

Comments(2)

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)

I arrived Friday night in Bangkok, where I’ll be spending the next 10 weeks or so. It’s still much as I remember it — hot, humid, and alive in a way I’ve seen nowhere else.

One thing that was quite different about my landing this time — as opposed to my two previous visits — was the reception I received at the airport, which was quite personal and enthusiastic, thanks very much.

Following his side trip to Singapore, my co-worker Morgan arrived Saturday. We originally had reserved rooms at the Gold Orchid Hotel on Vibhaphavadi Rangsit Road, not far from the Chatuchak Weekend Market, but we soon discovered that the WiFi was not only not free, but was also hideously expensive (about 10 times more than what would be considered reasonable back in Oz) and wasn’t very reliable. After a couple of calls to our travel agent and a fortuitous chance meeting with a Thai limo driver who got us in to see the right people at the right apartment-rental office, we decided to bug out, and today moved to The Bedrooms on Sukhumvit 77, not far from On Nut Station. (Don’t let their website fool you, it looks like a good place if you’re looking for somewhere to stay in Bangkok for longer than just a couple of days. Most important for us, the WiFi here is gratis and appears to work quite well.)

Between dealing with the accomodation situation and trying to get a bit of work done despite being stuck offline, I’ve not had a lot of time for getting out and adventuring just yet, but Morgan and I did have some fun whilst wandering about in search of a better place to stay. In fact, if this photo’s any indicator, I think that he might even be considering a career change… ;)

I’ll get out and take in some major sites soon, but for now I’d best turn my thoughts back to more mundane matters — such as slogging through the 500+ emails I’ve just downloaded, and figuring out where I’m going to do laundry, which needs to happen Real Soon Now®.

Comments(1)