MySQL Cluster 6.3.17 - binaries released

Tweet Check out http://dev.mysql.com/downloads/cluster/! Finally, there are binaries for MySQL Cluster 6.3!

Moreover, read my previous post :)

Indestructible Cluster part 2

Tweet In part 1 we got the cluster monitoring up and running. Indeed a good step forward into taming Cluster. This time we are going to add process management to our solution - if a node fails (ndbd, ndb_mgmd, mysqld etc) fails, then it should be automatically restarted.

To do this we need:

• initd scripts for each process so there is a way to start and stop the processes.
  
• process management software (i have chosen monit for this).

The initid scripts presented below are templates and you may have to change things such as paths.
For monit we do not need init.d scripts as we will install monit in inittab so that the OS (I live in a Linux world so if you use Solaris or something this may not necessarily apply in that environment).

initd scripts - walkthrough
We need initd scripts for the following processes:

• ndb_mgmd - (scripts open in new tab)
  In this script you should change:
  
host=<hostname where the ndb_mgmd resides>
configini=<path to config.ini>
bindir=<path to ndb_mgmd executable>

  If you have two ndb_mgmd, you need to copy this file to the two computers where you have the management server and change the hostname to match each computer's hostname.
  

• ndbd
  If you run >1 ndbd on each computer use the ndbdmulti script.
  In this script you should change:
  
host=<hostname where the ndbd runs>
connectstring=<hostname_of_ndb_mgmd_1;hostname_of_ndb_mgmd_2>
bindir=<path to ndbd executable>
##remove --nostart if you are not using cmon!!
option=<--nostart>
##if you use ndbdmulti you need to set the ndbd node id here (must correspond to Id in [ndbd] section of your config.ini.
nodeid=<nodeid of data node on host X as you have set in [ndbd] in config.ini>
  
  If you use ndbdmulti, then you need to have one ndbdmulti script for each data node you have on a host, because each ndbdmulti script must have its unique nodeid set. Thus you have e.g /etc/initd/ndbdmulti_1 and /etc/initd/ndbmulti_2.


• mysqld
  In this script you may want to change the following things to reflect your configuration.
  
basedir=<e.g /usr/local/mysql/>
mycnf=<path to my.cnf>
bindir=<e.g /usr/local/mysql/bin/>

  
• cmon
  In the initd script for cmon you can change the following things.
  However, it is recommended to have a mysql server and a management server on the same host/computer as where cmon is running.
  
bindir=<by default cmon is installed in /usr/local/bin>
## the following libdir covers the most common places for the mysql libraries, otherwise you must change so that you have libndbclient on the libdir path.
libdir=/usr/local/mysql//mysql/lib/mysql:/usr/local/mysql//mysql/lib/:/usr/local/mysql/lib/:/usr/local/mysql/lib/mysql
OPTIONS - there are a bunch of cmon options you might have to change as well. By default cmon will try to connect to a mysql server on localhost, port=3306, socket=/tmp/mysql.sock, user=root, no password, and to a managment server (nbd_mgmd) on localhost


install init.d scripts
You should copy the scripts to /etc/init.d/ on the relevant computers and try them before moving on!

If all scripts are tested and ok, Then you need to add the scripts to the runlevels so that when the computers reboot they are started automatically.

# on the relevant host(s):
sudo cp cmon /etc/init.d/
# on the relevant host(s):
sudo cp ndb_mgmd /etc/init.d/
# on the relevant host(s):
sudo cp ndbd /etc/init.d/
# on the relevant host(s):
sudo cp mysqld /etc/init.d/

On Redhat (and I presume Fedora, OpenSuse, Centos etc):

# on the relevant host(s):
sudo chkconfig --add cmon
# on the relevant host(s):
sudo chkconfig --add ndb_mgmd
etc..

On Ubuntu/Debian and relatives:

# on the relevant host(s):
sudo update-rc.d cmon start 99 2 3 4 5 . stop 20 2 3 4 5 .
# on the relevant host(s):
sudo update-rc.d ndb_mgmd start 80 2 3 4 5 . stop 20 2 3 4 5 .
# on the relevant host(s):
sudo update-rc.d ndbd start 80 2 3 4 5 . stop 20 2 3 4 5 .
etc..

Reboot a computer and make sure the processes start up ok.
If you are going to use cmon then cmon will control the start of the data node. If the data nodes are started with --nostart the data nodes will only connect to the management server and then wait for cmon to restart the data node.

monit - build
The first thing we need to do is to build monit on every computer:
download monit
unpack it
tar xvfz monit-4.10.1.tar.gzcompile
cd monit-4.10.1
./configure
make
sudo make install
I think you have to do this on every computer (I have not tried to copy the binary from one computer to every other computer).

Now when monit is installed on every computer we want to monit it is time to define what monit should monitor. Basically monit checks a pid file. If the process is alive, then great else monit will restart it. There are many more options and I really suggest that you read the monit documentation!

monit - scripts
monit uses a configuration file called monitrc. This file defines what should be monitored. Below are some examples that I am using:

• monitrc for ndb_mgmd, mysqld, and cmon - (scripts open in new tab)
  You should copy this script to /etc/monitrc:
  sudo cp monitrc_ndb_mgmd /etc/monitrc
  and change the permission:
  sudo chmod 700 /etc/monitrc
  Then you should edit monitrc and change where the pid files are (it is likely you have different data directories than me):
  check process ndb_mgmd with pidfile /data1/mysqlcluster/ndb_1.pid
  etc..


• monitrc for ndbd
  If you have >1 ndbd on a machine, use monitrc for multiple ndbd on same host
  You should copy this script to /etc/monitrc:
  sudo cp monitrc_ndbd /etc/monitrc
  and change the permission:
  sudo chmod 700 /etc/monitrc
  Then you should edit monitrc and change where the pid files are (it is likely you have different data directories than me):
  check process ndbd with pidfile /data1/mysqlcluster/ndb_3.pid
  etc..


• monitrc for mysqld - (scripts open in new tab)
  You should copy this script to /etc/monitrc:
  sudo cp monitrc_mysqld /etc/monitrc
  and change the permission:
  sudo chmod 700 /etc/monitrc
  Then you should edit monitrc and change where the pid files are (it is likely you have different data directories than me and hostnames):
  check process mysqld with pidfile /data1/mysqlcluster/ps-ndb01.pid
  etc..

monit - add to inittab
The final thing is to add monit to inittab so that if monit dies, then the OS will restart it!

On every computer you have to do:
echo '#Run monit in standard run-levels' >> /etc/inittab
echo 'mo:2345:respawn:/usr/local/bin/monit -Ic /etc/monitrc' >> /etc/inittab
monit will log to /var/log/messages so tail it check if all is ok:
tail -100f /var/log/messages
If not then you have to change something :)
If it seems ok, then kill a process that is managed:
killall -9 ndbd
and monit will automatically restart it:
Sep 30 13:15:44 ps-ndb05 monit[4771]: 'ndbd_3' process is not running
Sep 30 13:15:44 ps-ndb05 monit[4771]: 'ndbd_3' trying to restart
Sep 30 13:15:44 ps-ndb05 monit[4771]: 'ndbd_3' start: /etc/init.d/ndbd_3
Sep 30 13:15:44 ps-ndb05 monit[4771]: 'ndbd_7' process is not running
Sep 30 13:15:44 ps-ndb05 monit[4771]: 'ndbd_7' trying to restart
Sep 30 13:15:44 ps-ndb05 monit[4771]: 'ndbd_7' start: /etc/init.d/ndbd_7
Sep 30 13:15:49 ps-ndb05 monit[4771]: 'ndbd_3' process is running with pid 5680
Sep 30 13:15:49 ps-ndb05 monit[4771]: 'ndbd_7' process is running with pid 5682

So that should be it. If you have done the things correctly you should now have a MySQL Cluster complete with monitoring and process management. As easy way to accomplish this is the use the configuration tool, which generates the various scripts (initd, build scripts etc)!

Please let me know if you find better ways or know about better ways to do this!

cmon 1.1 - released

Tweet New features in cmon 1.1:

• used-defined log destination (earlier only possible to log to syslog or console)
  cmon --logfile=/tmp/cmon.log
  The logs are rotating and rotate when 1MB size has been reached.
• init.d script - in initd/cmon is a script that can be used to start and stop cmon. You may have to edit the content of that script (e.g paths).
  

Bugs fixed in cmon 1.1:

• various issues in the php scripts
• fixed queries managing the backup and backuplog tables (the queries periodically removed all entries). Now the backup/backuplog/restore/restorelog tables can max contain 100 records, based on age.
• Improvements in documentation around how to get graphs working (you need php-gd).
  

Get cmon at http://www.severalnines.com/cmon/

cmon 1.0.1 released

Tweet Thank you all for you feedback and especially to Matt and Oli for spotting some serious issues. Hence a new version cmon 1.0.1 is out.


Bugs fixed in 1.0.1 is:

• option handling is fixed (--mysqluser and --mysqlpasswd was not handled properly).
• initd/cmon (the documentation talks about this initd file) is now included
  Please not that you may have to edit and change it a bit (PATHs etc).

Version 1.1 will include:

• possibility to log to other place than syslog.

Indestructible Cluster part 1

Tweet Version 1.0 of CMON (gpl license) has now been released.
It is dead easy to use and gives you a lot of helpful information about the state of the cluster. Memory utilization, statistics etc.

You can also get graphical representation using a web server and the php scripts that comes with cmon.

If you use it with the new and improved config tool then you can setup a system with process management (monit) and init.d scripts etc in minutes, and cmon will then automatically coordinate the start procedures of the data nodes in the cluster!

First you have to download cmon and then you can check out the documentation for it.

Then you have to compile it:

tar xvfz cmon-1.0.tar.gz
cd cmon-1.0
sh ./autogen.sh
./configure
make
make install ## -- optional step - will install into /usr/local/bin as default and you need to be root


The easiest and recommended way is to have a mysql server and a management server on the computer where you are installing cmon. This makes things simpler.

Now you can start cmon which will daemonize itself (start with --nodaemon if you don't want this to happen):

/usr/local/bin/cmon ##if you did 'make install' above
or
src/cmon ##if you didn't 'make install' above

This corresponds to starting cmon with the following arguments:

cmon --ndb-connectstring=localhost \
--mysqlhost=localhost \
--mysqluser=root \
--mysqlpasswd="" \
--mysqlsocket="/tmp/mysql.sock"\
--savetime-clusterlog=24 \
--savetime-statistics=24

cmon will:

• automatically create a database called 'cmon' on the localhost the necessary tables.
• log to syslog (tail -f /var/log/message )

If you start up cmon and tail /var/log/message you will get:

Sep 16 23:14:09 ps-ndb01 cmon: Starting cmon with the following parameters: --mysqldatabase = cmon --mysqluser = root --mysqlpasswd = --mysqlhost = localhost --mysqlport = 3306 --mysqlsocket = /tmp/mysql.sock --ndb-connectstring = localhost --savetime-clusterlog = 24 hours --savetime-statistics = 24 hours
Sep 16 23:14:09 ps-ndb01 cmon: If that doesn't look correct, kill cmon and restart with -? for help on the parameters, or change the params in /etc/init.d/cmon
Sep 16 23:14:09 ps-ndb01 cmon: Creating pid file /tmp/cmon.pid
Sep 16 23:14:09 ps-ndb01 cmon: Creating database 'cmon' if it does not exist
Sep 16 23:14:09 ps-ndb01 cmon: Table 'backup' did not exist. Schema incomplete. Going to recreate missing tables.
Sep 16 23:14:09 ps-ndb01 cmon: Recreated missing tables
Sep 16 23:14:09 ps-ndb01 cmon: Created table 'backup'
Sep 16 23:14:09 ps-ndb01 cmon: Created table 'backup_log'
Sep 16 23:14:09 ps-ndb01 cmon: Created table 'restore'
Sep 16 23:14:09 ps-ndb01 cmon: Created table 'restore_log'
Sep 16 23:14:09 ps-ndb01 cmon: Created table 'cluster_log'
Sep 16 23:14:09 ps-ndb01 cmon: Created table 'cluster_state'
Sep 16 23:14:09 ps-ndb01 cmon: Created table 'memory_usage'
Sep 16 23:14:09 ps-ndb01 cmon: Created table 'node_state'
Sep 16 23:14:09 ps-ndb01 cmon: Created table 'cluster_statistics'
Sep 16 23:14:09 ps-ndb01 cmon: Created table 'node_statistics'
Sep 16 23:14:09 ps-ndb01 cmon: Done
Sep 16 23:14:09 ps-ndb01 cmon: Setting up threads to handle monitoring
Sep 16 23:14:09 ps-ndb01 cmon: Starting MonitorThread
Sep 16 23:14:09 ps-ndb01 cmon: Starting LogEventThread
Sep 16 23:14:09 ps-ndb01 cmon: connected to mysqld
Sep 16 23:14:09 ps-ndb01 cmon: connected to ndb_mgmd on host localhost, port 1186


Using the PHP scripts that are located in the 'www' directory and a webserver you can get nice stats about Cluster. Such as:

..and per node statistics:

..and memory usage:

... and detailed backup and restore statistics:
You are actually getting every stat you can get from MySQL Cluster at this stage. And if you don't want to use the PHP scripts, you can access the same information using plain SQL, since cmon logs everything to normal SQL tables.

And yes, I have only tested this on Linux deployed on a cluster consisting of HP DL385 systems with x86-64 architecture (in this case dual cpu dual core Intel(R) Xeon(R) CPU 5160 @ 3.00GHz and dual cpu quad core Intel(R) Xeon(R) CPU E5345 @ 2.33GHz ). They are a bit old, but good.

Let me know if you have problems or questions on the above:
johan at severalnines dot com

Sun CMT and Cluster

Tweet After joining SUN I have seen and heard more and more people/customers that are wanting or planning to deploy MySQL Cluster on SUN's CMT technology.

For a bit more information on CMT, please read what Allan Packer, SUN, has written more on CMT hardware on his blog.

Looking at MySQL Cluster, and in particular the data nodes, they like:

1. Fast CPUs (two cores per data node), 2+GHz CPU.
   
2. RAM, as fast as possible.
   
3. Fast disks (SSD would be great fun to try in combo with Cluster). Fast disks are important especially for write-heavy applications.
   

CMT does not offer 1). CMT technology is currently quite slow (slightly above the 1GHz line). So running data nodes on CMT is not a good idea, which is also indicated by customer benchmarks. But what the data nodes are good at is handling a great deal of load in parallel.

How to generate a lot of parallel load? Well, use Sun CMT in the application layer (mysqld + app/web servers).

This is also inline with the trends in the industry where you don't perform heavy operations on the precious CPUs used by the storage layer, but rather do computation etc at the application layer. An excellent fit for SUN CMT, which has ample of threads to throw at a problem! Also, SUNs Java technology is optimized for CMT making it an extremely good idea to use Glassfish as the app server in this scenario.

Now, bear in mind that all nodes making up a MySQL Cluster (6.2 and 6.3) requires the same endianess, so you can't combine CMT with AMD/Intel architecture at the storage level yet (this got to get fixed).

This means I would use two of these for the data nodes and the final architecture would look as follows which would be nearly indestructible (with redundant BIGIP Load balancers, switches, data nodes etc).