Monday, September 13, 2010

More on MySQL Cluster monitoring with MyQuery 3.3

A few days ago, I posted a blog on monitoring MySQL Cluster / NDB with the aid of MyQuery accessing the ndbinfo tables. Now, there are more ways than that to query the status of MySQL Cluster / NDB, you can use the MGMAPI, which is the low level API used to monitor and manage a NDB Cluster setup.So, if this API is available, and is accessible from C, and MyQuery has the ability to be extended with DLLs written in C or C++, then why not create a MyQuery plugin for managing a NDB Cluster? Tell you what, that is already part of MyQuery, which has by the way had a few bugfixes and is now up to version 3.3.4.

To get started, if you haven't done so already, download MyQuery and install it, and make sure to install the plugins / User defined tools. Once you have done that we are sort-of ready to go, but first a few words pf caution regarding the sourcecode.

If you haven't used any of the NDB low-level APIs, i.e. NDBAPI and MGMAPI, let me fill you in on a few details. NDBAPI is used for normal database DML and DDL, but it is much more low level than SQL, but also much MUCH faster. NDBAPI is strictly a C++ interface, although there are other bindings (Java) on top of it. MGMAPI on the other hand, which is used for monitoring and managing an NDB Cluster, is C on the surface, meaning the the interface used no classes, no overloading no nothing, and most MGMAPI code I have seen uses straght C. Bit this isn't the whole truth. Some NDBAPI includefiles that are also needed by MGMAPI still have no C++ specifics, but one thing it uses, which is valid in C++ but not in C, is to define structs with no members (don't ask me why), like this:

struct ndb_logevent_STTORRYRecieved {
};

So even though the MyQuery plugin that I will show later is written in C and is contained in the file NdbMonitor.c, it still must be compiled as C++ code. In Visual Studio 2008, this is done by right clicking on the file in Solution Explorer (yes, the file NdbMonitor.s, nit the project, that will not work!) select the Properties menu option and in the dialog go to C7C++ and then Advanced and then change Compile as to Compile as C++ Code. Also, when you look at the MyQuery Solution, note that the NdbMonitor project is compiled and linked against MySQL Cluster, and in fact, this is not the binary MySQL Cluster that you download from MySQL, rather this is built from source, see more in my previous blog postings on the subject.

OK, so that's it for the sourcecode, now let's look at what this little puppy can do when we run it (and it's part of the biinary MyQuery download by the way, so you do not have to use MGM API yourself or anything). To use this tool, after installing MyQuery, connect to an SQL node in a NDB Cluster and then select the Tools->NDB Monitor menu option. This will show a dialog like this:
If this is the first time you use NDB Monitor, the default Cluster connection string is filled in the connect string edit box, and adjust this to reflect your NDB Cluster setup. You may connect to NDB Cluster on any platform here, not just Windows. Then click Connect and the current Cluster status will be shown when you have connected to the Cluster:Most of the information here should be pretty obvious. The traffic lights are green if all datanodes are up, yellow if one is down and red if two or more are down. To refresh the status click Refresh and you may automatically refresh by turning on the Autorefresh checkbox and enter the number of seconds between refreshing.

There are two tabs, one for the nodes themselves and one for the node groups. When the node view is active, you can right click on a node to bring up a menu that allows you to do one of three things:
  • Stop a node - This will bring up a dialog that allows you to stop a node, gracefully or just kill it.
  • Restart node - This will bring down a node and then restart it. You have the option of leving it in "non-started" mode also.
  • Start node - If you have done a restart and left a node in non-started mode, then you can start it using this menu option.
And that's about it, NdbMonitor isn't more fancy than that just now, but I am planning a few more things:
  • Cluster backup - This is not that hard to implement, I just haven't gotten through to it yet.
  • Cluster log management - This is a bit more difficult, but it involves subscribing to the cluster log and showing log contents in a new tab.
If you have more ideas for future versions of NdbMonitor, let me know!

/Karlsson

Friday, September 10, 2010

MySQL Cluster users - Check out MyQuery 3.3.2

In MyQuery 3.3, where I just released version 3.3.2 which includes some MySQL Cluster NB specific fixes, there is some support for accessing MySQL Cluster / NDB beyond what is available as ndbinfo table and such things. MyQuery is a Windows based MySQL GUI, if you didn't know that already, and just because you run this tool on Windows (it is only supported on Windows), doesn't mean you cannot monitor a Cluster that runs on, say, Linux. Or Windows, should you so wish.

There are 2 predefined tools that support MySQL Cluster there, one that uses the ability to predefine a SQL statement that runs in a non-modal dialog and is optionally updated and one that uses the feature that allows a DLL, using a sepcfic MyQuery API, to run as a tool, and in this case, this tool is linked up with the NDBAPI to allow monitoring and a certain amount of control overthe Cluster.

Monitoring NDB Cluster memory usage

In MySQL Cluster / NDB 7 and up, there are tables in a schema specific to NDB, called ndbinfo that includes a bunch of tables with NDB specific information.
Defining a tool in MyQuery that monitors memory usage, like this tool. by selecting from a table is real easy. The Memory usage monitor is included with MyQuery 3.3 and up, so you can just download MyQuery and then connect to the Cluster and use it, but possibly you want to create tools of your own for other ndbinfo tables or something completely different.
Assuming we didn't have the NDB Menory tool already, and wated to create it, this is how you wold do that:
  • First, test the SQL statement you want to use in the MyQuery editor. Always use fully qualified table names (i.e. prefixing them with the database name), unless you have a specific reason not to do this. In this case, we end up with this statement:
    select * from ndbinfo.memoryusage
  • The next step is to create the tool. Select the Settings -> Configure user tools menu option, and you will get a dialog that shows all the tools that are currently defined. We are to create a new tool, so click the new button.
  • In the dialog that pops up, give the tool an appropriate name and select a type. In this case, the type is SQL SELECT. Then click OK.
  • A new tool is now created for you, but it will not be saved until we have added some required info, in this case teh SQL statement. In this case, if you have the SQL text in the editor, click the Copy button and select Copy from editor, else just tpe in the SELECT statement you want to run.
  • We probably want to tool to be shown in the Tool menu, and hence be accessible, only when it will work properly, in this case, the ndbinfo plugin is required. For this, select the Enabled for plugin radio button and then select the ndbinfo plugin in the listbox.
  • The last thing we want to do is select the connection to use. There are three options here: Use the connection used to execute normal SQL in MyQuery (Use execute connection), use the connection normally used by MyQuery tools (if MyQuery isn't connected with dual connections, then these two options are the same of course), which is the default (Use main connection) or use a completely separate connection. In our case, just use the default.
  • For tools like this, you would usually not enable the Modal dialog option. This is an option only used in certain special cases.
  • Finally, you might want to add some Help text.
  • That's it, now we have a new tool! Click OK and you are done!
Now, in the Tools menu, your newly created tool should appear. Select it, and a non-Modal dialog will appear, just like the one for the NDB Memory info tool. It should look something like this:
As you can see, there is the ability to refresh the contents automatically, to refresh every 2 seconds, just check the Autimatic refresh checkbox and set the time to 2 and you are all set. Let me know if you find out any really cools ways of using this feature for tools, and if they are generic enough, I will include them in a future release of MyQuery.

Happy Clustering!
/Karlsson

Thursday, September 9, 2010

MyQuery 3.3.1 releases - Fixes MyQuery 3.3.0 platform issues

I have released MyQuery 3.3.1 now. This is a minor release in many ways, but still significant in others. MyQuery 3.3.0 had issues on platforms where the runtime library I used wasn't installed. Also, there was a minor (but crashing) bug that caused Ndb Monitor to fail. All this, and a few other minor things, are fixed now.

I will eventually blog a bit more on the new features in MyQuery 3.3, such as the plugins, the InnoDB Lock monitor and how to easily define your own monitor dialogs with a simple SELECT statement.

Til then, download MyQuery 3.3 from Sourceforge.

Cheers
/Karlsson
And by the way, I'm sorry that I failed some QA with version 3.3.0, but it's just me coding away here, and I have a limited amount of machines and above all, time.

MyQuery 3.3.0 problems about to be fixed.

If you have tested MyQuery 3.3.0 and have problems with a dialog poping up saying that the "application configuration is incorrect", then I am working on this. The reason for this problem is that I now use Visual Stuio 2008 for building, and now Microsoft insists on things such as manifests and stuff. To be frank, insisting on manifests isn't necessarily a bad thing, actually it's pretty cool. The issue is that when there is a problem with them, the messages you get from Windows are, well, less helpful.
In the message you get when you try to run MyQuery probably says nothing about the manifest or anything. In my mind, it should. Also, if you link dynamcically with the Microsoft redistributable DLLs, then you want manifests for those also. Ah well. I am working on it anyway.
I will move to a new VS 2008 generated project, instead of migrating the old as I did before, and build a new distribution. As far as I can see, this will fix these problems and will also create a better, more clean, project. I'll release a 3.3.1 version later today with these fixes in it.

/Karlsson

Tuesday, September 7, 2010

Whoa! MyQuery 3.3.0 Beta Released at last!

After a long wait, today I release MyQuery 3.3.0. For you who haven't seen MyQuery before, this is a Windows based interactive query tool for MySQL (sorry no Mac or Linux support this time) . The emphasis is much on script editing and running, and on the needs of the DBA, more than on the end user. Also, for the database designer, there is some cool stuff in here.

MyQuery supports running scripts up to a specific point in the script, or starting from a specific point and supports a kind of "edit and continue", so you can develop a script, run it til it breaks, correct the error, and then continue where you left off.

That is not all, after all these releases MyQuery has gained quite a few useful features. Script editing is done with the Scintilla color coded editor for examples, and there are tools for managing database objects such as tables, views and events.

Every release of MyQuery has a theme, and MyQuery 3.3.0 has a very distinct theme: Extensibility. There is now support for adding your own favoutite SQL SELECT or Scripts to a user defined tools menu. And that is not all, there are many more types of tool that you can define, from opening a web link in a browser to running code in a specified DLL, using the new MyQuery Plugin API.

The main new features in summary are:
  • The User defined tools feature
  • The MyQuery Plugin API
  • A login dialog which now supports saved login settings for different servers
  • MySQL Cluster / NDB monitoring through User tool plugin
  • InnoDB Lock tree monitoring through a User tool plugin
  • Several bugfixes
This is still a beta version. It is hence feature complete, but I know there is a bug or two, in particular the plugin API (which is an advanced feature which required a fair amount of refactoring to work). Feedback is welcome!

I will write a few more blogposts here to describe some 3.3 details, such as the InnoDB Lock tree monitor, the NDB Monitor, the Plugin API and things like this, but for now, just download the thing and enjoy! As usual it's available on Sourceforge:
http://sourceforge.net/projects/myquery/files

/Karlsson