On the 9th of September at 10am CET I will present in Italian a Webinar titled: "Scaling Web Services with MySQL Cluster". You can register here.

This is a summary of the english webinar series in two parts that are now available on-demand:

• Scaling Web Services with MySQL Cluster, Part 1: An Alternative to MySQL Server & memcached
• Scaling Web Services with MySQL Cluster, Part 2: An Alternative to the Memory Storage Engine

Content

There are two common choices to power web …

One problem with MySQL Cluster we are starting to see quite often is to do with the current generation of Xeon processors.  This post outlines the problem and how to avoid it.

With the Nehalem based Intel Xeons (and also in some older AMD CPUs) they add a technology called NUMA (Non-Uniform Memory Access).  This basically gives each CPU its own bank of memory instead of all CPUs accessing all the memory.  For many applications this means much faster memory access. You should be able to see if NUMA is on by looking for it in dmesg.

So why is this a bad thing?

MySQL Cluster data nodes typically require a large portion of the memory, this means very often that one CPU will need to access the memory from another other CPU.  This in general is quite slow, on a busy cluster we have seen this access take 100ms - 500ms!  MySQL Cluster is real-time and is not a happy bunny when there are things stopping it …

I tried the Linux instructions on my Mac, and it worked without need for any changes. Things may be different when you deploy a real set of servers on separate hosts, but it's a good …

The MySQL Cluster daemon for MySQL Cluster (ndbd and ndb_mgmd) doesn't by themselves yet let them run as a service (apparently ndb_mgmd does, but I haven't seen it documented anywhere on how to do that). But there are ways to fix this, using some simple Windows tools and some registry hacking.

What you need to find is the Windows Resource Kit from some version of Windows that includes instsrv.exe and srvany.exe. It is not too picky with the actual version of Windows you run it seems, I used the Windows NT 32-bit versions of these on a 64-bit Windows 7 box, and it works just fine.

These two programs are simple and are easy to use:

• instsrv allows you to install a service, it's real simple, just run the program and it will show the options (and these are few).
• srvany allows you to run any odd program, that is not intended run as a service, do do this anyway.

Now, Google a …

This post explains how to disable Arbitration when using MySQL Cluster. It gives a case where this could be useful.

First, a piece of advice: you do not want to run MySQL Cluster with arbitration disabled. But if you must, e.g. because of an oversight in your implementation, you can.
Arbitration is very important in MySQL Cluster. It makes sure you don't end up with a Split Brain situation: 2 halves working independently, continuing changing data, making it impossible for them to work together later on.

However, Arbitration comes with a price: you need an extra machine. "Sure, what's …

Last week I was struggling to find an easy way to simulate a troubled Data Node (ndbd process) using MySQL Cluster. It's as simple as pancackes: using the kill command!

To freeze a process you just need to kill the process using the SIGSTOP signal. To let the processes continue, use SIGCONT. Here's an example shell script showing how you would use these two signals on a data node:

# 2010-05-03 08:11:46 [ndbd] INFO     -- Angel pid: 542 ndb pid: 543
 NDBDPID=`grep 'Angel pid' ndb_3_out.log | tail -n1 | awk '{ print $11 }'`
 kill -STOP $NDBDPID
 sleep 10
 kill -CONT $NDBDPID

I'm using the out-log because the file ndb_3.pid contains only the PID of the Angel process. The sleep command is …

For those who've seen my presentation on MySQL HA, you already know that I often use a multimaster setup with a meta OCF resource that groups my favoured MySQL instance with the service ip , using a meta resource means that pacemaker monitors mysql, but it doesn't actually manage it. It's an approach that works for us.

One of the other approaches I will be looking at soon is the freshly released OCF resource that Florian announced last week.

Back in the days our approach meant we didn't have to use clone resources, which you might remember being pretty buggy in the v2 era, not wanting to use clons resources isn't really a valid reason anymore these days . I've also frequently mentioned the combination of using DRBD and MultiMaster replication, using this set of OCF resource makes that a lot more easy ..

…

You are using MySQL Cluster and crazy enough to digest NDB API? Sick of SQL? Here's a treat: a function to make C/C++ strings ready for inserting into a VARCHAR field. The special thing about them is that the length is prefixed in the first 2 bytes.

void make_ndb_varchar(char *buffer, char *str)
{
  int len = strlen(str);
  int hlen = (len > 255) ? 2 : 1;
  buffer[0] = len & 0xff;
  if( len > 255 )
    buffer[1] = (len / 256);
  strcpy(buffer+hlen, str);
}

Yes, you can use memcpy. Whatever floats your boat.

Lets use this function for a table t1, defined as follows (note: latin1!):

CREATE TABLE t1 (
  id INT UNSIGNED NOT NULL,
  vc VARCHAR(128),
  vclong VARCHAR(1280),
  PRIMARY KEY (id)
  ) ENGINE=NDB DEFAULT CHARSET=latin1

Here is part of the code, simplified for this post:

char vc[128+1]; // Size of 'vc', +1 for length …

Most software people are aware of the ACID acronym coined by Jim Gray. With the growth of the web and open source, the scaling and complexity constraints imposed on DBMS implementations supporting ACID are more visible, and new (or at least new terms for known) compromises and tradeoffs are being discussed widely. The better known NoSQL systems are giving insight by example into particular choices of tradeoffs.

Working at MySQL, I have often been surprised at the variety of potential alternatives when implementing a DBMS, and the number of applications which don't need the full set of ACID letters in the strictest form. The original MySQL storage engine, MyISAM is one of the first and most successful examples of an 'ACID remix'. The people …