Querying for InnoDB Lock contention

In the previous post, I showed how the "virtual tables" that the InnoDB Plugin use for finding lock contention works, and what they look like. I also showed what an InnoDB lock graph looks will look like when monitored from MyQuery 3.3, which is not yet, but soon, released.

So, we are looking at three tables, the transaction table, where each transaction has no, one or more locks. For our intents and purposes, we will disregard transactions without locks, so we can join transactions table to the locks table. Then we need to link up the locks table to the lock waiters, to see if the lock in question is waiting on another lock, Each lock is either not waiting on one, or, as we have seen, more, locks, or it is not waiting at all. As a lock might not be waiting at all, we need an OUTER join here.

So the query I am using, the result of which is massaged a fair bit before I shown the lock tree, but this is the query I use to get the data:

SELECT t.trx_id, l.lock_id, w.blocking_lock_id, l.lock_table, l.lock_index,
t.trx_query, l.lock_data
  FROM information_schema.innodb_trx AS t
  JOIN information_schema.innodb_locks AS l ON t.trx_id = l.lock_trx_id
  LEFT OUTER JOIN information_schema.innodb_lock_waits AS w
  ON l.lock_id = w.requested_lock_id

/Karlsson

Monitoring InnoDB Lock contention

When you use InnoDB, or any other RDBMS or storage engine that supports row level locking and transactions, you get to realize a few things:

• The chance of lock contention is less, as the lock is much more granual that page or table level locking.
• On the other hand, when you get into lock contention, maybe because you have structured your data in such a way so there may well be as many locks as before (for example if one small table with very few rows is updated frequently by many threads), then the effect is worse.
• With transactions, which are a good thing by the way, I'm not promoting not transactions datastores here and assuming auto commit isn't used, then locks are held longer, and as each transaction may well hold several locks, because of multiple rows being updated or multiple statements are part of one transaction for example.
• With InnoDB then, fining the root cause when you end up with lock contention, with several transactions locking each other out in a complex manner, is HARD!

Luckily, finding locks and waiters for them, and the relationship between these, is A LOT easier when you use the InnoDB Plugin instead of the builtin InnoDB (are you still using the built-in? Dont. Just don't! The plugin has better performance, is easier to monitor, has online-operations and is GA, even in 5.1, you just enable it with a few settings and you are ready to go).

With the InnoDB Plugin and the InnoDB Plugin Information schema tables installed, the latter by installing the plugins using the plugin_load my.ini setting, for example:

plugin_load="innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;innodb_locks=ha_innodb_plugin.dll;innodb_lock_waits=ha_innodb_plugin.dll"

we are ready to go.

There are 3 new tables in the INFORMATION_SCHEMA schema now, one for each object we need for this exercise:

• INNODB_TRX - This keeps track of the InnoDB transactions. Each transaction may have no, one or more locks and may also be waiting on locks (locks I say? What? More about that later)
• INNODB_LOCKS - This keeps track of all locks for all transactions. Locks are unique for transactions, so if two transactions are waiting for one lock, we will see a total of three locks, one holding the lock, and two other locks, for the same row, waiting for it.
• INNODB_LOCK_WAITS - This table has one row for each lock that is waiting for another lock.

In InnoDB, only locks that are actually interesting are show in these tables, so if a transactions holds a lock that noone is waiting for, this lock will not show up.

The are some intersting things to note about these tables. If we have 3 transactions:

• A holds a lock on a row
• B is waiting for the lock A holds.
• C is waiting for the same lock that A holds and that B is waiting for.

We will see 3 locks! Why? A holds a lock, right, so that is fine. B holds a lock and is waiting for A, so that is fine too. But does C hold a lock? Yes, it holds a lock and s waiting for the one that B holds. I told you that locks, as seen in this table, are unqiue to each transaction.
If we now assume that A just holds the lock, and isnät waiting on anything, we have how many waiters? Well, we have 2 waiting transactions, but 3 waiters! Transaction C, to complete, needs both the lock that A holds AND the lock that B holds (as we saw above, just because B is waiting for a lock on a row, doesn't maen it can also HOLD a row on that row. A different loc, but the same row). B waits for A and C waits for A and B, whch menas we have a total of three waiters! But if you look at the individual transactions, as represented by the requesting_trx_id column in the INNODB_LOCK_WAITS table, we see only two unique transactions, which is right, as although we have 3 lock waiting, only 2 transactions are actually blocked (B and C). And if we look at the column blocking_trx_id column, then we again see only 2 unique transactions blocking something, A and B this time, as C isn't really blocking something!
The actual data may look something like this, in this example:
SELECT * FROM innodb_locks;

lock_id    lock_trx_id lock_mode lock_type lock_table  lock_index lock_space lock_page lock_rec lock_data
B87:0:52:6 B87         X         RECORD    `test`.`t1` `PRIMARY`  0          52        6        5
B86:0:52:6 B86         X         RECORD    `test`.`t1` `PRIMARY`  0          52        6        5
B85:0:52:6 B85         X         RECORD    `test`.`t1` `PRIMARY`  0          52        6        5

And the the waiters:SELECT * FROM innodb_lock_waits;

requesting_trx_id requested_lock_id blocking_trx_id blocking_lock_id
B87               B87:0:52:6        B86             B86:0:52:6
B87               B87:0:52:6        B85             B85:0:52:6
B86               B86:0:52:6        B85             B85:0:52:6

One interesting column that has not been discussed so far is the column lock_data in the INNODB_LOCKS table. This shows the value that is being locked, in this case I have an integer column with the value 5 (and this is the PRIMARY KEY also, as you can see from the lock_index column).
Does this help? Yes, it is much better than using the built-in InnoDB, but there is still some way to go. A simple SELECT from these tables will be a good help, but as this data is actually in a hierarchy, it would be real nice to see this as a lock graph? Right? Well that is something I am working on for MyQuery 3.3, which is due soon. It will look something like this:
This is the same lock tree as in the example above, and how it will look, but there is something that is not right here, right? The C lock is just shown as waiting on A not B? Well, for the sake of clarity, I have cleaned that up in the GUI itself, so only relevant locks and waits are really shown.

/Karlsson

MySQL Cluster on Windows - NDB API part 4 - Finishing it up

So we have come to the forth and last part of this small series on how to get started with NDB MGM API on Windows. I am planning some more code using NDB API specifically, but that will be a separate series.
If you haven't followed this series before, the parts before this one were:

• MySQL Cluster on Windows - NDB API part 1 - Where I go through the basics.
• MySQL Cluster on Windows - Not so NDB oriented: Set up a dev environment - Where I show how to set up a reasonable development environment for NDB MGM API on Windows
• MySQL Cluster on Windows - NDB API part 3 - The code - Where I show the code for a small, but complete and somewhat useful, application

In this part, I will show you how to build the finished application, but before that I will make a correction in the code that I showed last time. The code shown then will usually work, but I have a slightly weird development environment, as I use so many different tools. because of this, I have modified the code to compile nicely in Visual Studio 2008.

So, the file NDBMonitor.rc really should look like this:

#include "resource.h"
#include "WinUser.h"
#define IDC_STATIC (-1)

IDD_DIALOG_MAIN DIALOGEX 0, 0, 310, 194
STYLE DS_SETFONT | DS_MODALFRAME | DS_FIXEDSYS | WS_POPUP | WS_CAPTION | 
    WS_SYSMENU
CAPTION "NDB Monitor"
FONT 8, "MS Shell Dlg", 400, 0, 0x1
BEGIN    DEFPUSHBUTTON   "OK",IDOK,253,173,50,14
    LISTBOX         IDC_LIST_NODES,7,23,186,164,LBS_NOINTEGRALHEIGHT | 
                    WS_VSCROLL | WS_TABSTOP
    LTEXT           "Connect string:",IDC_STATIC,7,7,55,14,SS_CENTERIMAGE
    EDITTEXT        IDC_EDIT_CONNECTSTRING,63,7,182,14,ES_AUTOHSCROLL
    PUSHBUTTON      "Connect",IDC_BUTTON_CONNECT,253,7,50,14
    PUSHBUTTON      "Refresh",IDC_BUTTON_REFRESH,199,173,50,14,WS_DISABLED
END

Not much, but somewhat, different than before. OK, so we are OK so far, now we will build the thing. We have prepared the MySQL Cluster libraries and includefiles and stiff like that before, but I will assume a few things going forward:

• MySQL Cluster is installed in C:\MYSQL714b
• Microsoft Visual Studio, and the Express version is fine for this exercise, is located in C:\Program, Files\Microsoft Visual Studio 9.0
• Microsoft Windows SDK is installed at C:\Program Files\Microsoft SDK2\Windows\v7.0a (This is part of Visual Studio 2008)

If you have different locations for your stuff, then some modifications are necessary, or course. I will not use the Visual Studio IDE for building this, showing and explaining how to set thet up would take up too much time, rather, I use a Makefile. So, in the directory where you placed the other source files we products, create a file called Makefile (with no extension, not even .txt. Watch out if you use Notepad!) with this contents:

MYSQL_HOME=C:\MySQL714b

VSHOME=C:\Program Files\Microsoft Visual Studio 9.0
SDKHOME=C:\Program Files\Microsoft SDKs\Windows\v7.0A
CC="$(VSHOME)\VC\bin\cl"
RC="$(SDKHOME)\bin\rc"

RCINCLUDE=/i "$(SDKHOME)\include" /i "$(VSHOME)\VC\include"
CFLAGS=/TP /MT /D "WIN32" /D "_WINDOWS" /I "$(SDKHOME)\Include" \
/I "$(VSHOME)\VC\Include" /I "$(MYSQL_HOME)\include" \
/I "$(MYSQL_HOME)\include\storage\ndb"


LDFLAGS=/LIBPATH:"$(SDKHOME)\lib" /LIBPATH:"$(VSHOME)\VC\lib" \
/LIBPATH:"$(MYSQL_HOME)\lib" \
/LIBPATH:"$(MYSQL_HOME)\lib\opt" /MACHINE:X86 ndbapi.lib \
ndbmgmapi.lib mysys.lib ndbclient.lib ndblogger.lib \
wsock32.lib strings.lib kernel32.lib user32.lib gdi32.lib \
winspool.lib comdlg32.lib advapi32.lib shell32.lib ole32.lib \
oleaut32.lib uuid.lib odbc32.lib odbccp32.lib
NDBMonitor.exe: NDBMonitor.c NDBMonitor.res
$(CC) $(CFLAGS) NDBMonitor.c /link $(LDFLAGS) NDBMonitor.res
NBDMonitor.res: NDBMonitor.rc
$(RC) $(RCINCLUDE) /fo NDBMonitor.res NDBMonitor.rc

Now we are nearly ready to begin building our application, if you installed Visual Studio 2008 the usual way, then you can start a command prompt with the appropriate environment by using the Start->Programs->Microsoft Visual C++ 2008 Express Edition->Visual Studio Tools->Visual Studio 2008 Command Prompt. Alternatively, make sure that you have the following directories in your path in a DOS Window:

• C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE
• C:\Program Files\Microsoft Visual Studio 9.0\VC\BIN
• C:\Program Files\Microsoft SDKs\Windows\v7.0A\bin
  

Now, from this DOS Windows, enter the command nmake and assuming you have no errors, you have an NDBMonitor.exe executable in th at directory. Run if from the commandline and you will get a Window that looks like this:

Not very exciting, I know, but if you type in the connect string to a MySQL Cluster management node, like localhost:1186 in the Connect string edit box and click Connect, then you will see the status of that Cluster in a simple way, like this:

Click the Refresh button to refresh the status shown in the listbox. So that's it for now. Happy Clustering folks!

/Karlsson

MySQL Cluster on Windows - NDB API part 3 - The code

If you followed this series on Windows programing with NDB API on Windows, you should now have a reasonable MySQL Cluster with NDB API setup. And if you are not following it, and think that the binary MySQL Cluster build and some basic MySQL knowledge is all you need to start writing code: Think again! That sure isn't the case, so have a look at the previous blogs in this series:
MySQL Cluster on Windows - NDB API part 1
and:
MySQL Cluster on Windows - Not so NDB oriented: Set up a dev environment
In this part, the third, I'm going to show some code. Some of it may be very basic if you're a seasoned Windows Win32 developer, but at least you don't have to write the code yourself. What we about to embark on is to write a simple MySQL Clusre NDB MGM API application, the MGM API is the API that is used for managing MySQL Cluster, and doing this from a nice Windows based GUI seems like a good idea, at least to me. I want to show some code that is not only an example, but is also something useful and which serves as an example of NDB API in the Windows environment, but on the other hand is simple enough to be posted in it's entirety and is easy to read and extend.

So what the application presented here will do is to show the status of a running MySQL Cluster in a dialog window. It will be a dialog based application, and if you don't know what that means, let me elaborate a bit:
Usually, a full scale Windows GUI application creates a main Windows that contains the object objects in the application, and which pops up dialogs for configuration, attributes etc. Windows itself knows how to deal with a dialog, where a dialog is a binary resource, built from a textbased definition by a special Resource Compiler and which is managed by a Dialog procedure, written in C or C++.
Now, whereas there are tools to paint and manage the dialog box controls, and Windows know how to deal with these when it comes to repainting them, handle the keyboard, Tab'ing between object in a dialog etc., none of this is automated by Windows when using a normal Winow-based application.
So for a simple application, like the one we look at here, that will just pop up a Windows with some object that shows some status or something, you can create a dialog-based application, where the main Windows is really a dialog. And that is what we are going to do here.
So the main application Window, which is actually a Windows dialog, looks like this:


In the following code examples, just look at the the code, then copy it and paste it into a file using the suggested name. We will look at three files here:

• NDBMonitor.c - This is the C file that contains the main code of the application. Here is the code that shows and manages the dialog on the application, and other supporting code. Although this is a C file, it must be compiled as C++, as some of the included files that the MGM API depends on (the MGM API is also in itself C-based) in the NDB API, assumes that C++ is used.
• NDBMonitor.rc - A Resource file that contains the definition of the dialog that is shown by the application. This file is compiled by the resource compiler and is then attached to the finished executable as a binary resource.
  
• resource.h - An include-file that defines the macros that names the objects in NDBMonitor.rc, in short, this is the link of names between NDBMonitor.c and NDBMonitor.rc.

So, lets look at NDBMonitor.c first. To use this example, copy this code and paste it into file called NDBMonitor.c in some directory of your choice:

#define _CRT_SECURE_NO_WARNINGS
#include 
#include 
#include 
#include 
#include 
#include 
#include "resource.h"

HINSTANCE g_hInstance;

BOOL CALLBACK NDBMonitorDlgProc(HWND hDlg, UINT nMsg, WPARAM wParam, LPARAM lParam);
void FillNodeList(HWND hWnd, NdbMgmHandle hMgm);

int APIENTRY WinMain(HINSTANCE hInstance, HINSTANCE hPrevInstance,
  LPSTR lpCmdLine, int nCmdShow)
   {
    g_hInstance = hInstance;

    DialogBox(hInstance, MAKEINTRESOURCE(IDD_DIALOG_MAIN), NULL,
      (DLGPROC) NDBMonitorDlgProc);

    return 0;
    } /* End of WinMain(). */


BOOL CALLBACK NDBMonitorDlgProc(HWND hDlg, UINT nMsg, WPARAM wParam, LPARAM lParam)
    {
    char szBuf[256];
    static NdbMgmHandle hMgm = NULL;

    switch(nMsg)
        {
        case WM_INITDIALOG:
            ndb_init();
            break;

        case WM_COMMAND:
            switch LOWORD(wParam)
                {
                case IDOK:
                    ndb_end(0);
                    EndDialog(hDlg, IDOK);
                    break;

                case IDC_BUTTON_CONNECT:
                    if(hMgm != NULL)
                        {
                       ndb_mgm_disconnect(hMgm);
                       hMgm = NULL;
                        SetDlgItemText(hDlg, IDC_BUTTON_CONNECT, "Connect");
                        EnableWindow(GetDlgItem(hDlg, IDC_BUTTON_REFRESH), FALSE);

                       break;
                        }
                    GetDlgItemText(hDlg, IDC_EDIT_CONNECTSTRING, szBuf, sizeof(szBuf));
                    hMgm = ndb_mgm_create_handle();
                    ndb_mgm_set_connectstring(hMgm, szBuf);
                    if(ndb_mgm_connect(hMgm, 1, 10, 1) == -1)
                        {
                        MessageBox(hDlg, "Error connecting to Cluster", "Cluster error", MB_OK | MB_ICONSTOP);
                        ndb_mgm_destroy_handle(&hMgm);
                        hMgm = NULL;
                        }
                    else
                        {
                        SetDlgItemText(hDlg, IDC_BUTTON_CONNECT, "Disconnect");
                        EnableWindow(GetDlgItem(hDlg, IDC_BUTTON_REFRESH), TRUE);
                        FillNodeList(hDlg, hMgm);
                        }
                    break;

                case IDC_BUTTON_REFRESH:
                    if(hMgm == NULL)
                        break;
                    FillNodeList(hDlg, hMgm);
                    break;
                }
            break;

        case WM_SYSCOMMAND:
            if(wParam == SC_CLOSE)
                {
                ndb_end(0);
                if(hMgm != NULL)
                   ndb_mgm_disconnect(hMgm);
                EndDialog(hDlg, IDOK);
                }

        default:
            break;
        }

    return 0;
    } /* End of NDBMonitorDlgProc(). */

void FillNodeList(HWND hWnd, NdbMgmHandle hMgm)
    {
    ndb_mgm_cluster_state *pState;
    int i;
    char *pType;
    char *pAddr;
    char szBuf[256];

    if((pState = ndb_mgm_get_status(hMgm)) == NULL)
        {
        MessageBox(hWnd, "Error getting Cluster status", "NDB API Error", MB_OK | MB_ICONSTOP);
        return;
        }

    SendDlgItemMessage(hWnd, IDC_LIST_NODES, LB_RESETCONTENT, 0, 0);
    for(i = 0; i <>no_of_nodes; i++)
        {
        if(pState->node_states[i].node_type == NDB_MGM_NODE_TYPE_NDB)
            pType = "NDB";
        else if(pState->node_states[i].node_type == NDB_MGM_NODE_TYPE_MGM)
            pType = "MGM";
        else
            pType = "API";

        pAddr = pState->node_states[i].connect_address;

        wsprintf(szBuf, "%s Node: %d %s%s Status %d", pType,
          pState->node_states[i].node_id, pAddr == NULL ? "" : "on ",
          pAddr == NULL ? "" : pAddr, pState->node_states[i].node_status);

        SendDlgItemMessage(hWnd, IDC_LIST_NODES, LB_ADDSTRING, 0, (LPARAM) szBuf);
        }

    return;
    } /* End of FillNodeList(). */

What is contained in this code are three main functions:

• WinMain() - This is the main function for all Windows programs.
• NDBMonitorDlgProc() - This is the function that manages the dialog itself.
• FillNodeList() - This is a helper function that fills in the list in the dialog with the status of the nodes in the Cluster.

The next thing is a definition of the dialog box to be displayed itself. This code, which goes into the file NDBMonitor.rc, is simple and looks like this:

#include "resource.h"
#include "afxres.h"

IDD_DIALOG_MAIN DIALOGEX 0, 0, 310, 194
STYLE DS_SETFONT | DS_MODALFRAME | DS_FIXEDSYS | WS_POPUP | WS_CAPTION | 
    WS_SYSMENU
CAPTION "NDB Monitor"
FONT 8, "MS Shell Dlg", 400, 0, 0x1
BEGIN
    DEFPUSHBUTTON   "OK",IDOK,253,173,50,14
    LISTBOX         IDC_LIST_NODES,7,23,186,164,LBS_NOINTEGRALHEIGHT | 
                    WS_VSCROLL | WS_TABSTOP
    LTEXT           "Connect string:",IDC_STATIC,7,7,55,14,SS_CENTERIMAGE
    EDITTEXT        IDC_EDIT_CONNECTSTRING,63,7,182,14,ES_AUTOHSCROLL
    PUSHBUTTON      "Connect",IDC_BUTTON_CONNECT,253,7,50,14
    PUSHBUTTON      "Refresh",IDC_BUTTON_REFRESH,199,173,50,14,WS_DISABLED
END

And finally, we need resource.h, which looks like this:

#define IDC_BUTTON_CONNECT              2
#define IDC_BUTTN_REFRESH               3
#define IDC_BUTTON_REFRESH              3
#define IDD_DIALOG_MAIN                 101
#define IDC_LIST_NODES                  1001
#define IDC_EDIT_CONNECTSTRING          1002

That's it for now. Next time we'll build this code into a finished application, by linking with the libraries we built ourselves previously.

/Karlsson

Eventual consistency - Bah!

The notion "eventual consistency" is bogus to me, in my mind, either it's consistent or it is not. Full stop. Which is not to say that consistency is always a requirement, there are many cases where you can do without it. But do not think that "eventual consistency" is a kind of "relaxed consistency", rather it is a lack of consistency.

Look, any kind of distributed system could claim to have "eventual consistency", in the meaning that at some point, things will be consistent. Eventual consistency does not mean that we know when things will be consistent at some known point in time. Frankly, not even if all the involved servers in a distributed system claiming "eventual consistency" would stop at the same time, would the data be consistent. But there is a way o achieve consistency: If all the application transactions running were allowed to stop in a controlled manner, and all operations are then stopped, then data should, I guess, be consistent. So is it consistent then?

Well, the system as a whole may well be, but this consistency isn't an aspect of the systems that claims to provide eventual consistency, rather this is the attribute of things as a whole. Or in other words, these servers don't provide consistency, rather they put that burden on someone else. And even that consistency is "eventual", whatever that means. But that is fine too.

Look. if I put $500 into my bank account, and I realize that only $300 was added, the explanation thar "it will get the eventually" wil not work with me at least. On the other hand, if I post a message on Facebook and I see the message as being sent, but the receiver hasn't seen it in the exact same instant, well than I don't care, even if I knew it, which I usually don't.

But in the latter case, if the Facebook message operation really was consistent, would that hurt me? The answer is no, of course. But if the overhead of making that operations in terms of performance was enormous, would that hurt me? And the answer is yes.

So, consistency is something we actually want, and eventual consistency is a comprimise that is good enough in some cases, where the performance impact of real consistency is too high. And will performance in system stay the way they are right now, and will the cost per performed consisten transaction stay the same? No, it will be lower. So there will, in time, be less reasons for not using a fully consistent system. But there will always be the case where performance requirements are really high, or developers has been sloppy or something, where a comprimise is what is needed. Which is fine with me.

If a driver on the road has his car swaying back and forth across the lanes, he will probably get caught by the police eventually. And the explanation that he was in the middle lane "eventually" will probably not work for him or her.

On the other hand, use the right tool for the job. If you need something done with the absolutely minimal overheadm and can do with the odd compromize, then fine. But don't fool yourself into thinking that you are not compromising by using 1984'ish newspeak.

/Karlsson

MySQL Cluster on Windows - Not so NDB oriented: Set up a dev environment

Getting closer to some serious MySQL Cluster NDB API development on Windows, we still need a decent development environment, and as for myself, I like the ability to have a self-contained environment for this, at least contained within one computer, so that I can develop when on the road. So this means I have to use Windows as a Server anyway, right, if I am developing Windows applications? Well, not necessarily, there is always the option of using a VM, such as VirtualBox or something. But I really do prefer to use the native Windows environment for this, which has a few issues.

As I have said before, I do prefer Linux / Unix as a Server platform. But in this case, Windows will only be a development server, which is different. And if you want to use Windows as a production server, well as I have said before, that is fine too, although not my cup of tea,

But running MySQL Cluster on Windows as of version 7.1.4b of MySQL Cluster is less than exciting. There is no installer, just a ZIP file and the MySQL Cluster servers will not run as Windows Services (except the SQL node of course).

Well, this can be solved, so let me show you what my MySQL Cluster development setup looks like. I have downloaded MySQL Cluster 7.1.4b binary and I unpacked it into C:\MySQL714b. The install directory isn't important, except that of you choose something else, then of course you need to adjust the config files accordingly.

So, the my.ini file for the MySQL Server, which is placed in C:\MySQL714b, looks like this:

[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=7104

#Path to installation directory.
basedir="C:/MySQL714b/"

#Path to the database root
datadir="C:/MySQL714b/Data/"
character-set-server=utf8

default-storage-engine=NDB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=700
table_cache=100
tmp_table_size=16M
thread_cache_size=64

# InnoDB settings
innodb_buffer_pool_size=32M
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_log_file_size=10M
innodb_thread_concurrency=18

# NDB Options
ndb-connectstring=host=localhost:1186
ndbcluster

[ndb_mgmd]
config-file=C:\MySQL714b\mgm1\config.ini
config-dir=C:\MySQL714b\mgm1

Among the things to note is that you MUST set the ndbcluster variables, as well as the connect string. The MySQL Server that is part of the MySQL Cluster distribution does NOT have ndb enabled by default, don't ask me why, but this is the case (and this is true even for MySQL Cluster on Linux etc by the way).

Also, note that I have set up the config files and directories for the MySQL Cluster management daemon (the ndb_mgmd section), this is not necessary, but it makes using the MySQL Cluster batch-files that manages the Cluster, which I will show later, easier to use.

Now we need a NDB configuration file, a config.ini, but before this we need to create a few directories for the NDB management and data nodes. From a DOS window, run:

mkdir C:\MySQL714b\mgm1
mkdir C:\MySQL714b\node1
mkdir C:\MySQL714b\node1\data
mkdir C:\MySQL714b\node2
mkdir C:\MySQL714b\node2\data

Now we are ready to create a NDB configuration file, called config.ini and placed in C:\MySQL714b\mgm1. It looks like this in my case, and as you can see, this is a minimal configuration:

[ndbd default]
NoOfReplicas=2

[mysqld default]
[ndb_mgmd default]
[tcp default]

[ndb_mgmd]
PortNumber=1186
HostName=127.0.0.1

[ndbd]
HostName=127.0.0.1
DataDir=C:/MySQL714b/node1/data

[ndbd]
HostName=127.0.0.1
DataDir=C:/MySQL714b/node2/data

[mysqld]
[mysqld]
[mysqld]

I will not get into details on the individual configuration parameters here, but I think it is fairly obvious, and that this is just a starting point.

OK, now we have the configuration files we need, there is just one more thing we want to do before we start creating some good old DOS batch-file to start and stop MySQL Cluster, we need to install the MySQL daemon as a service, as it runs best that way. And this is pretty easy. Again, open a DOS window and execute:

C:\MySQL714b\bin\mysqld --install MySQL714b --defaults-file=C:\MySQL714b\my.ini

This will install a service called MySQL714b that will start a MySQL daemon using the config file C:\MySQL714b\my.ini. There is one issue with this service though, and that is that it will be started automatically when Windows starts, which we don't want, as the NDB daemons will not be running at that time. So in the same DOS Window, use the sc command to set the start type of a service. In this case, to change the service to manual, run:

sc config MySQL714b start= demand

Now we are ready to create a DOS batch-file that will start the Cluster. I will use the START DOS command, which starts a Windows process in a DOS Window. Usually, START will run the process in a separate DOS Windows, but there is a /B flag that will run it in a separate process in the same Window as it was started from, this is similar to use the & sign in *ix environment. This script will also start the MySQL Server:

REM Start MySQL Cluster processes
REM
SET MYSQL_HOME=C:\MySQL714b
SET MYSQL_SERVICE=MySQL714b

START /B %MYSQL_HOME%\bin\ndb_mgmd
START /B %MYSQL_HOME%\bin\ndbd --ndb-connectstring=nodeid=2;host=localhost:1186
START /B %MYSQL_HOME%\bin\ndbd --ndb-connectstring=nodeid=3;host=localhost:1186
NET START %MYSQL_SERVICE%

And then a script to stop it:

REM Stop MySQL Cluster processes
REM

SET MYSQL_HOME=C:\MySQL714b
SET MYSQL_SERVICE=MySQL714b

NET STOP %MYSQL_SERVICE%
%MYSQL_HOME%\bin\ndb_mgm --execute=shutdown

Not so bad after all, right? OK, we need a DOS Windows to start and stop the server, but only one. I think this setup makes working with MySQL Cluster on Windows A LOT easier. Once you have done this, just change to the C:\MySQL714b directory in a DOS Window and run ndbstart to start MySQL Cluster on your local machine, and run ndbstop to stop it.

In the next installment on this series on MySQL Cluster NDB Development on Windows, I'll get down to some more details. And I know that this part wasn't NDB API specific at all, but hey, it was necessary!

See you in a while...

/Karlsson

MySQL Cluster on Windows - NDB API part 1

So you thought that just because MySQL Cluster 7.1 is GA on Windows that NDB API was available and you could just download the MySQL Cluster 7.1 binary for Windows and start hacking the NDB API. Nope. But fear not, there is help!

And before I go on with that: For all you *ix users here, who claim that no one in their right mind would use MySQL Cluster on Windows, let me tell you that you may well be right! But, and this may be a not so PC statement: Although I like and use *ix (I've actually been using *ix for close to 30 years) as a server platform, when it comes to the desktop, I think Windows still has the edge. There is just too many options on, say Linux, too little coherence and too little commonality between applications. And it sure is getting better, very much so, but there is still some way to go.

So then, when we have my reasons for me doing what I am currently doing behind our backs, what I am trying to achive is a nice GUI application, using the currently best (in my mind, but maybe not by a long stretch), to manage a MySQL Cluster setup running on the best, most cost effective (again in my mind), server platform: Linux.

And to do that, you really need to use the NDB API and most important, the NDB MGM API. And to use those, you have to complile MySQL Cluster on Windows yourself. And it's not that difficult. What you need is:

• The MySQL Cluster for Windows source.
• Download this and unpack it and look in the file INSTALL-WIN-SOURCE and follow the instructions.
• CMAKE (look in INSTALL-WIN-SOURCE in the root on the unzipped source).
  
• Some version of Microsoft Visual Studio. VS 2008 and 2010 is fine, whatever INSTALL-WIN-SOURCE says. What is NOT fine is VS 2003, again contradicting what INSTALL-WIN-SOURCE says.
• And speaking of contradictions in INSTALL-WIN-SOURCE, MySQL Cluster on Windows is NOT "Experimental" or "Beta" or something, it is fully supported GA, indepentent of what INSTALL-WIN-SOURCE says.

OK, so follow the instructions in INSTALL-WIN-SOURCE and from Visual Studio open the project file as indicated by INSTALL-WIN-SOURCE and do a build.

Now, when the build is finished, you may well see a few errors. If the errors are related to something indication JAVAC missing, you are also OK. The requirement for a java compiler was, again, not mentioned in INSTALL-WIN-SOURCE, but what the heck, let's go on with our lives and have some fun instead of complaining about an outrageously ourdated text-file, most of the instructions are OK anyway. And yes, even though the JAVAC missing stuff, the build of NDB API and NDB MGM API probably went OK.

The next step is to install the appropriate includiles and libraries, but I'll hold that til the next part. In part 3, I will show some NDB API code for Windows, and by that time, I'll be ready to release MyQuery 3.3 that includes some NDB management functions, accessible from the nice MyQuery SQL GUI. Looking something like the image to the left!

See you soon!

/Karlsson