Cluster Monitoring - CMON

Tweet Jan 14th 2010 - See this post instead: http://johanandersson.blogspot.com/2010/01/cmon-install-instructions.html ! For installation instrictions.

A system is not a system unless it can be monitored and controlled...I recall something like that from a university course in Control Theory.

However, in a couple of blogs from now on I am going to give some ideas on how to monitor and control MySQL Cluster. To control, we need to have process management software that automatically restarts processes.

To do monitoring we need to have a tool to collect information... and here is a new monitoring tool, called CMON (instructions further down). CMON aggregates information from MySQL Cluster that earlier was only accessible from the cluster log or the management client, such as:

• cluster state
• node state
• backup statistics
• statistics
• cluster events (cluster log basically)

.. and let's you access the information using SQL, because CMON logs the information into ordinary MYISAM tables! So, it is really easy to use!
Here are some examples:

-- get memory usage
select nodeid,100*(pg_used_im/pg_tot_im) as im_used_percent from memory_usage order by nodeid;
select nodeid,100*(pg_used_dm/pg_tot_dm) as dm_used_percent from memory_usage order by nodeid;


-- get status of all data nodes
select * from node_state where node_type='NDBD';

-- backup status
select * from backup;

-- query the cluster log e.g,
select * from cluster_log where event="DISCONNECT";

Note: You must have MemReportFrequency=30 (or some other value) set in config.ini to get the Memory usage events!!

There is a README in the tarball that describes how to install it. If you want the long version, please read on.

Deployment (example HA environment)
Here is a recommended setup that we will base the setup and installation CMON on. In an HA environment we typically have a setup like shown in the picture below:


We have a number of processes that are deployed on its own hardware (hostA, hostB etc). Now, let's add CMON to this layer. I would add CMON to the Management Layer, and I would also add a mysqld on that layer so that CMON can log locally. The picture would then look like:


The MySQL server on hostA and hostB does not have to be connected to the Cluster but can be standalone. In later blogs we are going to add process management to this picture as well. As you can see we have a redundant pair of CMONs, and thanks to the SQL interface to cluster state information it is easy to write web applications to hook into this. So we are all good.

Moreover, a solution like the above picture can easily be generated using the config tool.

Installation
In order to install CMON on a computer corresponding to hostA or hostB in the picture above.

1. Download the code
2. tar xvfz cmon.tgz
3. make sure you have mysql_config on the path!
1. if you have used the config tool (and the default install path):
2. export PATH=/usr/local/mysql/mysql/bin:$PATH
   
4. cd cmon
5. sh autogen.sh
6. ./configure
   
7. make
8. make install
9. export LD_LIBRARY_PATH=/usr/local/mysql/mysql/lib:/usr/local/mysql/mysql/lib/mysql
10. cmon --help
11. if you have a mysqld started on localhost
1. mysql> source sql/cmon.sql
12. cmon &
1. this is equivalent to:
   cmon --mysqluser='root' --mysqlpasswd='' \
   --mysqlhost='localhost' --mysqlport=3306 \
   --mysqlsocket='/tmp/mysql.sock' \
   --ndb-connectstring='localhost' &
   
   
2. if you don't have the mysqld on localhost you might have to GRANT and use the options that you get from cmon --help to connect correctly to the mysql server.
13. You can find more example queries in sql/queries.sql
    

Ps, if I get more that 100 unique downloads I will maintain this :)

And please let me know what is wrong with.. johan at severalnines dot com!
On the road map:

• make it as a real daemon
• add more data to the tables
• notifications
  

And I would be very grateful if someone could write a php script that could present the average load (like ops_per_sec etc) in a graph on a web page.
Because I have no idea how to do that.

Known issue: sometimes the cmon crashes on startup. The crash actually happens in libmysqlclient_r. However, it is just to start it again if this happens. I am looking into this.


Aug 13. 2008 - fixed bug in cmon.sql script.

Please note that all this is of MySQL Cluster 6.2 and MySQL Cluster 6.3.

Good luck!