MySQL Cluster (NDB) on Win32 progress

Many things have been happenning in the land of NDB on Win32 as of late.

I’ve fixed about 700 compiler warnings (some of which were real bugs) leaving about 161 to go on Win32 (VS2003). We’re getting a few more warnings on Win64 (some of which look merely semantic, while others could be real bugs), but the main focus now is getting 32bit going really well.

I fixed a number of bugs that were around preventing lots of things from working properly:

Disk Data (i.e. CREATE TABLESPACE, CREATE LOGFILE GROUP, and CREATE TABLE… TABLESPACE ts1 STORAGE DISK) now works. The main problem here was that our filesystem abstraction layer for the NDB kernel (ndbd) once had a Win32 port… which has sorely bitrotted over the years. As new features were introduced to the file IO interface, they (of course) weren’t also added to the Win32 abstraction. In the disk data case, the OM_INIT feature, which on FSOPENREQ (open a file) allows data to be passed in for initialising the file. Previously, I fixed this to allocate the file on disk and create a file of the same size, but i didn’t add the feature that writes initial data to the file. This caused bugs as soon as you tried to use the disk data tables (the files weren’t initialised, so you hit asserts on corrupt disk data files).

Paths in the server: for whatever bizarre and stupid reason, the MySQL server can end up having paths to a table as ./database/table OR .\database\table. The latter *never* shows up on non-Win32 platforms but can *sometimes* show up on Win32. Ick ick ick ick. Anyway, we (in the NDB handler) weren’t dealing with this properly, causing problems around some metadata ops.

Our pushbuild system takes each push to a source tree, builds it on a variety of platfroms and runs the mysql-test-run.pl test suite. The Win32 hosts are actually running on vmware. In order to make tests run faster, on Linux we use /dev/shm for the data files. Microsoft Windows doesn’t have a good ram disk, so we create a file on /dev/shm on the host and map that as a drive inside Windows (and format it as NTFS). This drive is only 1GB. This is not enough disk space for running all the clusters (yes, plural) started by the test suite (and everything would die with ENOSPC). The workaround I’ve come up with is that for debug builds, we simply enable NTFS file compression on files ndbd creates.

Win64 is also working! Pushbuild builds and runs on 64bit, and the Win64 host is building with NDB and passing about the same amount of tests as the Win32 hosts!

The bad news is that the NDB with replication tests are pretty much all failing… so I’m fairly confident that cluster replication is very broken on Win32 (and 64) at the moment.

I’ve had to do a fair amount of fixing on a bunch of the test cases (mainly to do with finding where various NDB utilities are). They’ve also prompted fixes in NDB (automatically converting / to \ in ndbd on Win32 for CREATE DATAFILE/UNDOFILE).

If you want to give it a go – you can get the source from launchpad. Either in the mysql-5.1-telco-6.4 tree, or if you want a few more things fixed, always have a look at the mysql-5.1-telco-6.4-win tree. Hopefully both are synced with the latest internal trees (i.e. plain 6.4 is working on win32) by the time you read this.

Iggy and I discussed installers for NDB on Windows in Riga, and we should have something soon-ish for those of you who don’t build from source.

Building MySQL on Windows – MySQL Forge Wiki

Building MySQL on Windows – MySQL Forge Wiki

This one covers running mysqld in the VisualStudio debugger, which can be useful.

I have no special ndb_mgmd.exe or ndbd.exe in debugger instructions or wisdom (running them from mysql-test-run.pl at least). I’ve attached debugger to already running (started by mysql-test-run.pl) ndb processes, but haven’t made any changes to mtr to make it like the mysqld of “go and enter this”.

MySQL Conference & Expo 2009 – CFP open

Is it that time already? MySQL Conference & Expo 2009 has opened the CFP.

Submit (well) early and often. It’s always an exciting (and exhausting) conf. Good technical, relevant content is what makes it good. Getting to talk to people who do amazing things, people who use your software, people looking to use it, people who want to chat about how you can learn off each other.

Any suggestions for what you’d like to hear from me (Cluster, Drizzle et al) are welcome – either via private mail or comments here.

Building MySQL Cluster on Windows (for Windows)

You will need:

  • CMake (at least 2.4.7)
  • Bazaar (the newer the better – 1.6 was just released – at least use that)
  • Gnu Bison
  • Visual Studio (Express works, but I’m talking about 2005 here)
  • … and all this installed on a Microsoft Windows machine.
  • … and to hate yourself, you are going to be using Windows after all.

Then, get and build it:

  1. Get the source:
    bzr branch lp:~mysql/mysql-server/mysql-5.1-telco-6.4-win
  2. Run CMake. the CMake GUI can now be used to select compile options! You’ll have to set the path “where is the source code” to where you put the source code in step 1.
  3. Hit “Configure” in CMake
  4. Select the target (i.e. the version of Visual Studio you’re going to use)
  5. Select the build options. HINT: WITH_NDBCLUSTER_STORAGE_ENGINE may be a useful one to enable
  6. Hit Configure again
  7. Hit Ok.
  8. CMAKE now generates the Visual Studio project. Use this time to drink some good scotch.
  9. Open Mysql.sln (which should launch Visual Studio)
  10. Go Build -> Build Solution (or hit F7)

Now you can go and have much whisky as this will take a few minutes. You should now have a set of built binaries for MySQL Cluster on Windows. Scary.

ndb_mgm.exe builds (and works) in mysql-5.1-telco-6.4-win

“MySQL Cluster 6.4 Windows tree” branch in Launchpad

(which really should have the -fail suffix… but anyway)

In what will (soon) be mirrored to launchpad, all but 17 targets (yeah, working on that… but it’s out of 130 or something) build.

Not only that, I’ve used the management client (ndb_mgm.exe) to monitor the cluster running my Bugzilla instance (which is now a rather old 6.3 build).

Getting closer to NDB on Windows.

Be afraid. Be very, very afraid.

“MySQL Cluster 6.4 Windows tree” branch in Launchpad

“MySQL Cluster 6.4 Windows tree” branch in Launchpad

That’s right folks, I’m pushing up patches for MySQL Cluster on Windows. This tree is incomplete, and no promises on when enough will be pushed for it to even compile on Windows.

Tree is updated when launchpad pulls from our internal tree.

Adventures with OpenSolaris

So… some colleagues have been experimenting with DTrace a bit, and I’ve been (for a while now) wanting to experiment with it.

The challenge now, instead of in the past, is that I’m setting up a Solaris based system – not getting one premade.

I chose OpenSolaris as I’d previously tried Solaris 10 and just sunk too much time trying to get updates and a development environment installed (another colleague could get the opposite to me going: he got devtools but no updates. at least mine was up to date and secure… but without a compiler).

So… OpenSolaris. It isn’t 100% open, there’s binary only drivers and such… but compared to previous Solaris, a whole lot better. Now, if only it was GPL licensed so we could have cross-pollination with Linux.

I grabbed the 2008.05 ISO as soon (in fact, slightly before) it was released and installed it in VirtualBox.

The installation was shiny – one of the best OS installs I’ve seen in a while. It set up nice things (zfs, X) and (an improvement on the previous release) even managed to get all the hardware going (not sound though).

However, on first reboot, nasty surprise. DNS isn’t enabled by default.

I found out why DNS isn’t enabled by default – and (as usual) this comes down to hysterical raisins. Back in what we laughingly call the past, during install Solaris would ask you what services you wanted to use for name resolution (which I guess made sense when people used yp/NIS more often than DNS). The default didn’t include DNS.

In the graphical installer, it just chose the default without asking… which is no DNS. So my mother would be able to install OpenSolaris, but once done, she’d have to know to type in 150.101.98.214 instead of www.google.com.au into Firefox. However, I swallowed my pride, edited /etc/nsswitch.conf and went along my business (I wonder the percentage of users who would actually go from “hrrm, internet not working” to editing /etc/nsswitch.conf without intense googling).

The UI did look nice though. Nice looking GDM, GNOME desktop looked nice. You could tell that whoever did the theme had spent too much time near MacOS X, but I’ll forgive them for that. The default shell is remotely sane and even though the bash completions aren’t as funky as on Ubuntu, I managed (unlike sitting at cmd.exe, where somebody is likely to die each time my keystrokes end up there).

I even had a look at the graphical package management tool – which looked quite nice. I even tried to do an update via it… which ended in what seemed to be a locked package manager and general amounts of fail. To see if it had just stopped or was chewing up my CPU or memory, I opened a terminal and ran ‘top’.

I then found out that top isn’t installed by default. It’s 57kb on my Ubuntu 8.04 laptop so disk space couldn’t be the reason why it’s not installed. It’s certainly not a “it’s a minimal install” argument, there’s lots of other things there by default.

Next step, let’s get updates (some time had elapsed between first install and now).

Seeing as I hadn’t met too much success with the graphical utility (it was at version 0.0000001 or something, so I don’t lay blame there). I find out that ‘pkg image-update’ is what you want to run. So I do.

It chugs for a while and says there’s 1GB of updates. That’s okay, I (where I=Sun) pay for what here on the arse end of the Internet is considered a decent link to my home office. About 20-30minutes later, having downloaded about 600MB, it goes “url timedout error” and aborts. Oh well I think, that’s easy – i’ll run it again and it’ll just resume downloading (remember the revolution when that started working, you know, in 1997).

I then discovered that pkg doesn’t resume downloads. It creates a snapshot using ZFS and puts the updates in it. If anything goes wrong, it just deletes the snapshot. This is a huge benefit over (say) dpkg, which if you press the reset button at the right time will leave your system very, very fucked (magic incantations can revive it, but it’s not fun – and the dpkg developers don’t think it’s a problem – come to my “Eat My Data” talk at OSCON to find out the full story). So OpenSolaris pkg wins on the “don’t ruin my working OS install already” front, but fails on resuming downloads.

I try again. Same story.

It’s now wasted a bit over 1GB of downloads… which equates to a couple of dollars.

I wait a few days, a week, and try again. Same story. I even try with a few hints found online that should fix things (well.. they did let another 100MB on average download before dying with the same story).

I then decided to just try and do the minimal – I wanted a development environment so I could build a MySQL Server with NDB and then play with DTrace to help nut out a performance problem or two.

So i tell pkg to install SunStudio Express. I’m even using instructions off sun.com, so it has to work.

It’s only ~500MB now (IIRC). Fails with exactly the same error as before (url timedout). Gah!

So, this brings us to today. I head into the Sun office.

I figure “this just has to work from a Sun office… ” and I was right!

It got through the (now) 1500MB download of updates!

It even applied them!

Success!

Win!

Well, no, – FAIL.

It now refused to boot with the updates. Or rather, it just rebooted soon after having started booting. No panic, no error screen, no “will reboot in 120 seconds” or anything useful. Instead, you just saw a flicker of the error message before it rebooted.

So… with some very careful pause/unpause of the VM (thanks VirtualBox… I also have a feature request now – pause before reboot :) I got this:

Aparrently the successful update, not so much.

Hrrm… perhaps select the known good one from the GRUB menu? It did actually boot! But this wasn’t just the old kernel, it was the whole older system. I guess that’s a possible upside of ZFS snapshots…. but oh my, that could be sooooo subtle and lead to data loss that it’s really quite dangerous.

I was still no closer to getting an up to date opensolaris system with enough developer tools to build a MySQL Server and use dtrace.

And this was enough. It’s now gone and I get my 10GB of disk back.

Maybe I’ll try again later… but I’m finding the google-perftools to be rather exciting and they’re really satisfying shiny thing urges at the moment.

WL4271 Encrypted Online Backup: Preview 3

“WL4271 Encrypted Online Backup: Preview 3” branch in Launchpad

Now with Windows support. Many thanks to Chuck Bell for helping get the code going on Windows.

We can however, all sit around dumbfounded as to how Windows has so little of a POSIX like layer and yet doesn’t define ENOTSUP.

As a refresher, this tree implements:

  • Encryption for MySQL Online backup
  • Algorithms and keysizes supported:
    • 3DES
    • AES (128, 192 and 256bit)
  • World peace

(world peace not included)

UPDATE: If you’re wondering why the branch isn’t there, it’s still pushing to launchpad. Yes, that’s over 7 hours to push a branch. ick. Can’t be too much longer, surely. I cannot wait until lp uses shared repos.

Encrypted Online Backup Preview 2 (DES and AES)

New preview includes:

  • 3DES support
  • AES support with 128 (default), 192 or 256 bit keysize

bzr branch lp:~stewart-flamingspork/mysql-server/stew-encrypted-backup-preview2

(you can pull this directly into the previous preview1 branch, it’s just 2 extra patches).

Examples:

  • BACKUP DATABASE test to ‘test.ba’ ENCRYPTION_ALGORITHM=3des PASSWORD=’pants’;
  • RESTORE FROM ‘test.ba’ ENCRYPTION_ALGORITHM=3des PASSWORD=’pants’;
  • BACKUP DATABASE test to ‘test128.ba’ ENCRYPTION_ALGORITHM=aes  ENCRYPTION_KEYSIZE=128 PASSWORD=’pants’;
  • RESTORE FROM ‘test128.ba’ ENCRYPTION_ALGORITHM=aes ENCRYPTION_KEYSIZE=128 PASSWORD=’pants’;

Enjoy! Feedback is very muchly appreciated.

NDB$INFO scanning from ndb_mgm

In code just tested:

ndb_mgm> ndbinfo MEMUSAGE
RESOURCE_NAME    NODE_ID    PAGE_SIZE_KB    PAGES_USED    PAGES_TOTAL    BLOCK
IndexMemory,1,8192,16,160,DBACC
DataMemory,1,32768,20,640,DBTUP
IndexMemory,2,8192,16,160,DBACC
DataMemory,2,32768,20,640,DBTUP

Win!

This is the first time that we’ve been able to get this kind of info out of the cluster without using the magic “all dump 1000” (or “all report MemUsage”) which end up using events, which go to the log file, aren’t exactly reliable etc.

This performs a scan on the NDBINFO tables (in ndbd) from ndb_mgmd and returns the result to the management client. You can then use this in scripts from the command line. e.g. to find out how many pages of datamemory are used on each node:

$ ./storage/ndb/src/mgmclient/ndb_mgm -c localhost:9311 -e ‘ndbinfo MEMUSAGE’|tail -n +3|grep ‘DataMemory’|cut -d ‘,’ -f4
20
20

now, just to clean it up a bit, fix the one bug (yes, you guessed it: in metadata caching) and get a review….

but, a milestone!

NDB$INFO

There’s been talk over the years of better monitoring for NDB (MySQL Cluster). This has been dubiously named NDB$INFO, after some special magical naming convention for tables holding information on the insides of NDB. Otherwise known as Worklog 3363 (viewable on MySQL Forge).

The basic idea is to get a bunch of things that are already known inside NDB available through a rather standard interface (SQL is preferred).

My top examples are “How much DataMemory is used?” and “Do I need to increase MaxNoOf(Tables|Attributes|ConcurrentTransactions)?”. You can get some of this information now either through the management client (ndb_mgm -e “all report MemoryUsage”) or the MGM API using events and some other foo.

This is a rather limited interface though. It would be great if you could point all your monitoring stuff to a MySQL Server, throwing queries at it and finding out the state of your cluster.

So this year I’ve been working on implementing NDB$INFO. The big requirements (for me at least) are:

  1. Everything can be queried easly from SQL
  2. It’s easy to add a new NDB$INFO table (for a NDB developer)
  3. you can use NDB$INFO tables to diagnose problems (such as nodes not connecting)

Among the 492 things I’m currently doing, is fixing up a basic patchset for NDB$INFO and working on getting it into the tree. It’s all going to be basic scan interfaces in the current version, so things may be slow if there’s lots of rows, but they’ll get there.

What would you like to see exposed?

libeatmydata

Following my successful linux.conf.au talk “Eat My Data: How Everybody Gets POSIX File I/O Wrong“, I started to feel the need to easily be able to have my data eaten.

Okay, not quite. However, when you’ve written your software properly, so it uses fsync() correctly, opening files with O_SYNC or whatever – tests take longer as you’re having to wait for things to hit the rust.

So….. LD_PRELOAD=libeatmydata.so to the rescue! With a POSIX compliant fsync() (that does nothing) and filtering on open(2), it can take your test run times down dramatically.

The only time you shouldn’t use it for your tests is when you end up crashing the machine to test durability (i.e. when the OS doesn’t have the opportunity to cleanly write out the data to disk).

See the libeatmydata project page: http://www.flamingspork.com/projects/libeatmydata/

and the bazaar repository: http://www.flamingspork.com/src/libeatmydata

(it’s seemed to have saved somewhere between 20 and 30% of the time for innodb/ndb tests in mysql-test-run).

Compressed LCP and Compressed Backup (and switching them on/off online)

Quick experiment with online changing of enabling/disabling compressed backups and local checkpoints (LCPs).

Backup is incredibly trivial and correct (even have some nodes do compressed, some not).

LCPs are a bit trickier when it comes to restore… currently how the code sits is that a block using the compressed file interface in NDBFS must specify if it wants to use the compressed read/write interface or not. So when you have LCPs that differ in compressed/non-compressed than the current config file setting, you’re not going to be able to restore them (although setting CompressedLCP=1 should let you restore either compressed or non-compressed LCPs).

At some point, I’ll probably move AsyncFile (our async file IO class) to just use azio alway, and modify azio to be transparent for non-compressed files…. I just have to fix up azio for direct io.

ndb_mgmd on Win32 (an Alpha)

So, here is an Alpha quality port of the MySQL Cluster management server to Win32 based on the current MySQL 5.0 tree.

This isn’t going into 5.0, so don’t expect to ever have that.

This isn’t going into 5.1 either, so don’t expect it there.

It’ll go into some future release at some level of general “supported” status that has yet to be decided.

ONLY USE THIS FOR EXPERIMENTAL PURPOSES.
IT IS EARLY RELEASE – IT HARMS PUPPIES!

But, it would be great for those who may be interested in having a ndb_mgmd on Win32 at some point to grab the binary, have a play and find some bugs.

For any bugs filed, please submit to bugs.mysql.com and explicitly mention that it’s version “5.0.50-ndbwin32r1” and mention that it’s the specific build (i.e. it shouldn’t go through the normal bug verification procedure and instead end up with me looking at it directly).

So, here’s the files:

Hopefully this brings you joy.

Oh, and yes, you can go and run it under WINE so you don’t have to actually use MS Windows.

enjoy!