MySQL Cluster performance revisited - tcmalloc kicks some ***

My now long-running series of posts on getting max performance from a very simple MySQL Cluster setup (see details here) is continuing here. As a short intro to what I am trying out here, is to see if I can validate the claim that MySQL Cluster / NDB would be a good replacement for a Key Value Store (KVS) such as MongoDB. I test this in a rather simple single-server environment, but this is for a reason, just not a coincidence: The reason is that RAM is getting inexpensive and servers that can take a lot of RAM are also getting less expensive, which in turns means that the saying that many small servers are more cost-effective that few big ones, might not be as valid as it used to be. Also, I wanted to test what MySQL Cluster can do for me, from a KVS centric view. In short, I run on one server (16 Gb RAM, 8 cores) with all data in memory, what do I get.

I can tell you I have been trying real hard to get MySQL Cluster to work better here. Yes, SQL parsing in MySQL is to blame for a lot of why MySQL Cluster is so much slower than MongoDB. But why is MySQL Cluster slower than a fully cached (hot) InnoDB then? Admittedly, I was running the InnoDB tests with MySQL 5.6, so there you have one more thing for me to try (i.e. running InnoDB tests with the mysqld packaged with MySQL Cluster).

A lot of different MySQL Configurations was tried, as was a few other things. Lookiing at what was going on in the box revealed that most of the CPU was consumed by mysqld, so I decided to try to speed that up a bit, as the CPUs were maxed out by mysqld using up some 85 % or so. Using tcmalloc seemed like one obvious fix here, and I got some 10% better performance using that, MySQL Cluster using the SQL interface now performance about 35 k requests per second, which still is below InnoDB (with MySQL 5.6) and not even close to MongoDB, but still an improvement.

But as I said, there is still more stuff to test and I am not giving up yet. But I have given up on distributing some mysqld load to some other server. I got that working, but as the load on the test is distributed evenly across the mysqld servers, the slowest one will hold things up (that is how thing work right now, but I can and will revisit that one). Tarantool, some more NDBAPI tests and then some remains to be tested!

Take care
/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