Time for an UPDATE using the Maria Storage Engine then...

Again, another simple test. Same basic tuning as yesterday, and the table schema is the same:
CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` char(100) DEFAULT NULL,
PRIMARY KEY (`c1`)
);
The table is again filled with 1.3 million rows, but this time I'm doing an UPDATE. The update is again a simple primary key update:
UPDATE t1 SET c2 = CONCAT('xxx', RAND(), 'yyy') WHERE c1 = <random value 1 - 1000000>;
I run this on the Maria, InnoDB and MyISAM engines. The issues with the MyISAM and Maria engines here is that they lack row level locking. In MariaDB 5.1.47, the InnoDB version is 1.0.6, so it is more scalable than what it used to be. The testbench is not an incredibly hot machiine, just a 4 core AMD box.

I run the test in some different configurations, using a single thread, using 10 threads and using 100 threads. Here we can see that InnoDB Row-level locking really hlps. With InnoDB, performance was on par with Maria with 1 thread, MyISAM lagging behind (yes, here Maria was faster than MyISAM, it might be some MyISAM tuning that I missed, beyond basic key_buffer_size and all that).

Moving to 10 threads, InnoDB could now perform about twice as many operations per second, whereas Maris throughput increased by aout 15%, MyISAM stayed the same old slow. Going to 100 threads, again InnoDB nearly doubled the number of ops, Maria abot 15% up, and MyISAM still about the same, actually slightly slower both at 10 and 100 threads.

Is this a scientific test? Nope, hardly. Does it reflect something that might be happening in a system in the real world? Yes, I'd say so, given that there would be other things going on also.

All in all, I think the Maria Engine has improved a bit, and may be an alternative to MyiSAM, if it wasn't for the fact that InnoDB really kicks them both in their rearward facing bodily regions.

/Karlsson

Maria Engine Performance once again

Note: Article has been edited as I was confusing MarisDB and the Maria Stoare Engine!

Just for the heck of it, I decided to try a very simple benchmark on the Maria Storage Engine again. This time, I'm using a simple SELECT. The table I use looks like this:
CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` char(100) DEFAULT NULL,
PRIMARY KEY (`c1`)
);
Which should be simple enough. I fill this with 1.3 millions rows, with c1 having consequitive numbers from 1 and up. My benchmark consists of random SELECTs of the c2 column from this table, using a primary key lookup on c1. I run this on 400 concurrent threads with each thread doing 1000 SELECTs. Ignoring the actual values, MyISAM and InnoDB come out pretty close in performance, with InnoDB slightly behind. Which is reasonable. Maria is at less than half the performance of MyISAM though. This is worrying. And I know what you say, Maria is new, performance comes later. I say performance comes mainly from design. As Maria does not have proper transactions yet, only row level transactions (not statement level. Try to stop a INSERT INTO ... SELECT FROM and you see what I mean).

I was hoping for more from Maria. As I tested MariaDB 5.1.47, I could also test PBXT. This particular test is hardly targeted at what PBXT is good at though, and I didn't even have the energy to wait for it to finish a single round of this test.

In this case, in difference to before, I did some basic tuning. Maria proved difficult here, as there is no documentation on the parameters, at least none that I could find.

Note: All tests run using MariaDB 5.1.47, including the ones involving InnoDB, MyISAM and PBXT.

/Karlsson

Running MySQL Cluster as a Service on Windows

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 bit and download these two puppies and open a DOS Window and change directory to where you installed these guys. Now we are realy to install three services (1 for the mgm and two datanodes), so they will run the srvany program:
F:>ResKit> instsrv mgm1 F:\ResKit\srvany.exe
F:>ResKit> instsrv ndbd1 F:\ResKit\srvany.exe
F:>ResKit> instsrv ndbd2 F:\ResKit\srvany.exe

These three services are rather boring now, they do not do anything, really. To fix this, open the registry editor (regedit) and open:
HKEY_LOCAL_MACHINE\System\CurrentControlset\services\mgm1
Now, we need to define what srvany.exe should run. In the key above, create a new key called Parameters. In this key then, create two values of the type REG_SZ:

• Application - The full path to the program srvany should run.
• AppParameters - The parameters to pass to the program srvany will run.

In my case, I set these to, for the mgm service:
Application: F:\MySQL714\bin\ndb_mgmd.exe
AppParams: --config-file=F:\MySQL714\mgm1\config.ini --configdir=F:\MySQL714\mgm1

I think you see where I'm going with this, but just to be sure, this is what I set for the ndbd1 service:
Application: F:\MySQL714\bin\ndbd.exe
AppParams: --ndb_connectstring=nodeid=2;host=localhost:1186

And that's it, folks! Well, one more thing I guess, we want to install the MySQL Server as a serive also, but this knows how to install itself as a service and can also run nicely as a service, so this is easy:
F:\ResKit>F:\MySQL714\bin\mysqld --install MySQL714 --defaults-file=F:\MySQL714\my.ini

Make sure that the ini-file for the MySQL server is configured to access the cluster we just set up, and to have the ndbcluster engine enabled (for some reason, the latter is not enabled by default). Do this by editing the my.ini file used by the server (and referenced above) and add the following two lines to the [mysqld] section:
ndb-connectstring=host=localhost:1186
ndbcluster

Now we have created and configured a bunch of services for a MySQL Cluster setup, and to try it out, lets start them using the NET command line program (the SC command may also be used):
F:\ResKit> net start mgm1
F:\ResKit> net start ndbd1
F:\ResKit> net start ndbd2
F:\ResKit> net start MySQL714

Have fun! And just a word of caution: Although this works just fine for testing, note that shutdown of the ndbd and mgm services isnt really graceful here, so this is just for fun and testing. Also, ndbd and ndb_mgmd will sure be possible to run as a Windows service in their own right, in some future version of MySQL Cluster.

/Karlsson

MySQL Cluster NDB MGM API on Windows

As MySQL Cluster is now available, and GA, on Windows, maybe it's time for some NDB API coding on that platform, right? The reason for this might, as it is in my case, be that Windows is a pretty good GUI Desktop platform, and MySQL Cluster / NDB really needs something like this. Those of you who have followed and used Cluster for a while, might remember my ndbtop tool that I created way back, and which is a MySQL Cluster monitor for Linux using ncurses. This is still useful I guess, but as far as a nice GUI presentation goes, ncurses leaves a lot to be desiered, to say the least.

So where do we start on Windows then? Well, to be honest, MySQL Cluster on Windows doesn't currently come with an installer, it's just a .zip file to unpack. But we are only using NDBAPI and the NDBMGMAPI, so that it no big deal, right? Just unpack the zip-file, write your C or C++ and compile with the supplied includefiles and libraries? Right?

Nope, sorry to disappoint you, but it is more complicated than that. MySQL Cluster on Windows doesn't include ndbapi in the binary release. You might think it does, considering there is a ndbapi.lib file and stuff. But this is not enough, you want the includefiles and also a few more libraries. Also, these are static libraries, which will confuse things a bit. Let me explain this first:

Microsoft has a habit of introducing new things in the C and C++ compilers that rely on things in the C runtime library. This is not a good habit, as it break compatability when building static libraries in particular. What happens is that the static library contains references to the embedded C RTL, and if I link with that library, I will have conflicts between the C RTL that my program wants, and the one that is in the library. And if I link my application without including the standard C RTL, and rely on the one in the library I am linking with, then things may break as my compiled code may contain references that are part of the C RTL I am using, but isn't part of the C RTL of the library I am linking with.

Anyone who has, on Windows, linked with the static MySQL Client library (mysqlclient.lib) knows what I am talking about here, and you also know the solution out of this mess: Compile the library yourself with the Microsoft compiler you use for your application.

So we need to revert to the source anyway. I use Visual Studio 2010 these days, but this is not really supported by the MySQL Build process, or to be honest, I haven't even tried it. I have VS 2008 installed on the same machine, build the project for that, and then let VS 2010 convert it.

So go along and do what is usually done when building MySQL from Source on Windows:

• Get cmake from http://www.cmake.org.
• Use a DOS prompt and go to the main directory where you unpacked the MySQL Cluster sources.
• In the DOS Window run the command:
  win\configure.js WITH_NDBCLUSTER_STORAGE_ENGINE
• In the DOS Window run the command:
  win\build-vs9
• All this will create a MySql.sln file in the main directory of the MySQL Cluster sources.
• Open the MySql.sln file with the Visual Stuio versioon of your choise.

Having done all this, select the Release build and build the following projects:

• mysys (mysys\Release\mysys.lib)
  
• strings (strings\Release\strings.lib)
  
• ndbgeneral (storage\ndb\src\common\util\Release\ndbgeneral.lib)
  
• ndbportlib (storage\ndb\src\common\portlib\Release\ndbportlib.lib)
  
• ndblogger (storage\ndb\src\common\logger\Release\ndblogger.lib)
  
• ndbtrace (storage\ndb\src\common\debugger\Release\ndbtrace.lib)
  
• ndbmgmapi (storage\ndb\src\mgmapi\Release\ndbmgmapi.lib)

You can of course build the whole shebang also, if you so wish. Now, after all this fun, what's the next step? First copy the above libraries (the paths are in prenteses after the library names) to some easy to remember place. I have downloaded the MySQL Cluster Binary also, and have placed these files in a new ndbapi directory under the lib directory. In addition, you need to copy a few includefiles, in my case to the MySQL Cluster binary include directory:

• ndb_init.h (storage\ndb\include\ndb_init.h)
  
• ndb_types.h (storage\ndb\include\ndb_types.h)
• ndb_constants.h (storage\ndb\include\ndb_constants.h)
• the whole mgmapi directory (storage\ndb\include\mgmapi)

The files are copied in my case to the location where I have the MySQL Cluster Binary includefiles, and the mgmapi directory is copied as sibdirectory to this).

Whoa, that was a lot of fun and copying! Right? Or?
Anyway, you should now be able to build a MySQL Cluster NDB MGM API application. Note that we did a Release build of the libaries, so to avoid warnings, build your application in Release build also. Include the path to the MySQL Cluster Binary include directory for the compiler and point the linked to the lib directory created above. And then one more this:
The static libraries already have a reference to the C Run Time library, so tell Visual Studio to disreard the LIBCMT default library. You do this Linker->Input settings in the "Ignore Specific Default Libraries", which you set to LIBCMT. If you build from the commandline, this is the same as /NODEFAULTLIB:"LIBCMT"

Having done all this, your application should compile, link and run just fine. For your reference, here is a Windows command-line program that will show the nodes in the Cluster and the status of them. And yes, the whole idea was to build a GUI application, but it's just too much code for a sample like this. Here you go anyway:

#include
#include
#include
#include
#include
#include
#include

typedef struct tagTEXTMAP
{
int nValue;
char *pText;
} TEXTMAP, *PTEXTMAP;

TEXTMAP g_NodeType[] = {
{ NDB_MGM_NODE_TYPE_API, "API" },
{ NDB_MGM_NODE_TYPE_NDB, "Data" },
{ NDB_MGM_NODE_TYPE_MGM, "MGM" },
{ NDB_MGM_NODE_TYPE_UNKNOWN, "Unknown" },
{ 0, NULL }};

TEXTMAP g_NodeStatus[] = {
{ NDB_MGM_NODE_STATUS_UNKNOWN, "Unknown status" },
{ NDB_MGM_NODE_STATUS_NO_CONTACT, "No contact" },
{ NDB_MGM_NODE_STATUS_NOT_STARTED, "Not started" },
{ NDB_MGM_NODE_STATUS_STARTING, "Starting" },
{ NDB_MGM_NODE_STATUS_STARTED, "Started" },
{ NDB_MGM_NODE_STATUS_SHUTTING_DOWN, "Shutting down" },
{ NDB_MGM_NODE_STATUS_RESTARTING, "Restarting" },
{ NDB_MGM_NODE_STATUS_SINGLEUSER, "Single user" },
{ NDB_MGM_NODE_STATUS_RESUME, "Resuming" },
{ 0, NULL }};

int main(int argc, char *argv[])
{
NdbMgmHandle hMgm;
ndb_mgm_cluster_state *pState;
ndb_mgm_node_state *pNodeState;
PTEXTMAP pMap;
char *pNodeType;
char *pNodeStatus;
int i;

// Check arguments.
if(argc < 2)
{
fprintf(stderr, "Usage: %s \n", argv[0]);
return 1;
}
ndb_init();
hMgm = ndb_mgm_create_handle();

// Connect to mgm server.
ndb_mgm_set_connectstring(hMgm, argv[1]);
if(ndb_mgm_connect(hMgm, 0, 0, 0) == -1)
{
fprintf(stderr, "Error connecting to cluster\n");
return 1;
}

pState = ndb_mgm_get_status(hMgm);
for(i = 0; i < no_of_nodes; i++)
{
pNodeState = &pState->node_states[i];
for(pMap = g_NodeType; pMap->pText != NULL && pMap->nValue != pNodeState->node_type; pMap++)
;
if(pMap->pText == NULL)
pNodeStatus = "Unknown type";
else
pNodeType = pMap->pText;
for(pMap = g_NodeStatus; pMap->pText != NULL && pMap->nValue != pNodeState->node_status; pMap++)
;
if(pMap->pText == NULL)
pNodeStatus = "Unknown status";
else
pNodeStatus = pMap->pText;

printf("Node id: %d at %s. Type: %s. Status: %s\n", pNodeState->node_id, pNodeState->connect_address,
pNodeType, pNodeStatus);
}
}

/Karlsson