Tuesday, August 12, 2008

Cluster Monitoring - CMON

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!

Sunday, August 10, 2008

Evaluation and Development Packages (UPDATE)

I have changed the scripts a bit.

Now everything is "self-contained" within the package so no directories outside mysqlcluster-63 is created.

You can now install this as any user! Before you needed to be root, but this is no longer needed! However, you should avoid installing it on an NFS mounted volume.

There is also a README that describes the directory and file structure and how to get going.

Wednesday, August 06, 2008

Evaluation and Development Packages

Hello Cluster users,

I have put together a set of evaluation and development packages that allows you to install a MySQL Cluster on "localhost" in a matter of seconds (ok, a minute is more realistic). If you want a fully distributed setup, please use the configuration tool.

The packages comes in four sizes (tiny, small, medium, large) and deploys a cluster consisting of:
  • 1 management server
  • 2 data nodes
  • 2 mysql servers
All installed on localhost. Of course, you also get a set of scripts to manage your cluster!

Check it out on http://www.severalnines.com/eval/
Download a package and follow the instructions below (tested on Linux):

To install and start MySQL Cluster 6.2:

tar xvfz mysqlcluster-62.tgz
cd mysqlcluster-62
cd scripts
## Download the latest MySQL Cluster 6.2 or use wget..
## Distribute the binary to "localhost" in this case
bash dist.sh mysql-cluster-gpl-6.2.15-linux-i686-glibc23.tar.gz
## Copy config files, install mysql servers (no processes are started yet)
bash bootstrap.sh
(bash stop-cluster.sh #i recommend this to get rid of any running mysql or cluster nodes)
## Start the cluster
bash start-cluster-initial.sh
## Start a mysql client to mysql server 1
bash mysqlclient1.sh
## In another terminal, start a mysql client to mysql server 2
bash mysqlclient2.sh


To install and start MySQL Cluster 6.3:

tar xvfz mysqlcluster-63.tgz
cd mysqlcluster-63
cd scripts
## Download the latest 6.3 src and compile it.
bash download-and-compile.sh
## Distribute the binary to "localhost" in this case
bash dist.sh
## Copy config files, install mysql servers (no processes are started yet)
bash bootstrap.sh
(bash stop-cluster.sh #i recommend this to get rid of any running mysql or cluster nodes)
## Start the cluster
bash start-cluster-initial.sh
## Start a mysql client to mysql server 1
bash mysqlclient1.sh
## In another terminal, start a mysql client to mysql server 2
bash mysqlclient2.sh

Testing the setup
In one terminal, we call it term1:

bash mysqlclient1.sh


In another terminal, we call it term2:

bash mysqlclient2.sh


In term1:

mysql> use test;
mysql> create table t1 ( a integer primary key, b integer) engine=ndb;
mysql> insert into t1 values(1,1);


In term2:

mysql> use test;
mysql> select * from t1;



Another script to launch the management client:

bash ndb_mgm.sh
ndb_mgm> all report mem

There are also other scripts to take backups, conduct rolling restarts etc.

Good luck. Future examples will be based on the infrastructure provided by severalnines.com.