Monday, February 28, 2011

Web and Telco mode Cluster Configuration

The Core Scripts (aka Configurator) for MySQL Cluster has now been updated with a new feature that allows you to tune cluster for a particular workload.
Currently there are two types of workloads supported:
  • Web
  • Telco/Realtime
The Web workload is suitable for applications:
  • with long running transactions (scans, joins and/or large updates)
  • reading and especially writing of large chunks (BLOBs) of data where an "innodb" like behavior is wanted.
The Telco workload is suitable for realtime applications with:
  • requirements on response times
  • requirements on failover times
  • short but many parallel request primarily on PK
Buffers and timeouts are geared towards each workload. Some of it you can read more about here.

The Telco workload is also suitable for many kinds of applications in for example the following domains:
  • Finance,
  • Online gaming
  • Subscriber databases
  • Session management
If you don't know what is most suitable for you, please contact Severalnines and we help you.

Friday, February 25, 2011

Cluster/J - Document-oriented approach on MySQL Cluster

In a project Severalnines is engaged in, we are developing a realtime application based on Cluster/J. To start with, I must say cluster/j is fantastic and so far I am very happy with it and beaten our expectations big time. It is quite new however and we stumbled on a couple of issues, but those were fixed very fast by the Cluster/J developers. The bugs we encountered were:
Both which were worked around, and really we never did need to have a binary or a varbinary as the PK, we used a

Performance is great - we have two data nodes (nehalem, 32GB RAM, 146GB SAS 10K disk, 2x4 core 2.4GHz (E5620) ) and two application hosts (same spec, less RAM as data nodes) and we easily push through 70-80K transactions per second (reads and writes). We could easily do more, if we added more applications on the application hosts. The data nodes are using about 50-60% of their capacity (CPU).


The graph (mysql cluster statistics) is at start quite spiky. As you can see transactions/operations goes up and down. This is because we first tested with 10M, then reloaded with 20M records, 40M, and finally 80M records and let it run overnight.

The requests are primary key based, and there is a PK followed by a VARBINARY (and some other meta fields). In the VARBINARY a tree structure is stored. The tree structure represents a parent-child relationship, and we encode it in JSON. So we are using something like a document-oriented model. By doing this we avoid complex queries to navigate a parent child structure and thereby have many round-trips between application and data nodes , and instead ask for one big packet in one network roundtrip. Writes in this model will be more costly since we write back the whole parent-child structure instead of just one of the nodes in this structure.

In the Java applications we also have a REST interface to the data.

SQL is used only for one thing and that is to create the tables.

CMON Enterprise is used for monitoring and management, and the Cluster configuration comes from Severalnines Core Scripts.

Finally, we made some fine-tuning on MySQL Cluster and got another 20% - this is not shown in the graph, and another story.

The only thing we are missing (or rather waiting for) now is the multi-connection functionality (ndb_cluster_connection pooling) in cluster/j.

Thursday, February 17, 2011

CMON - Install Instructions

CMON 1.1.4 - the Cluster Monitor and Manager has recently been released and here is a little how to about how to install from binary and source (at the end), deployment etc. If you use RPMs, you should follow the install guide here.


1. Download the cmon binary package to a monitoring host

Currently there are binaries available for Linux 64-bit and 32-bit (statically linked) and works for MySQL Cluster 7.0.9 and later 7.0.x versions and also 7.1.x.

In this case the monitoring host will be on 'ndb05' (IP address 10.0.1.5) - see 9. Suggested Deployment for a picture describing the setup.

The Monitoring machine should have installed:
  • a mysql server up and running that will store the cmon database - this mysql server does not need to be connected to MySQL Cluster. If you build from source you need to have the mysql cluster libraries and include files
  • apache (if you want to have the web interface) + php_mysql
      sudo apt-get install apache2 php5-mysql
  • rrdtool (if you want to have graphs in web interface)
    • sudo apt-get install rrdtool
The picture at the end of this post shows how you can deploy CMON.
> cd /usr/local
> wget http://www.severalnines.com/downloads/cmon/cmon-1.1.4-64bit-glibc23-mc70.tar.gz
> tar xvfz cmon-1.1.4-64bit-glibc23-mc70.tar.gz
> ln -s cmon-1.1.4-64bit-glibc23-mc70 cmon


2. Verify that you have a free API slot [mysqld] so that cmon can connect to MySQL Cluster

> ndb_mgm -e "show"
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @10.0.1.3 (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0, Master)
id=4 @10.0.1.4 (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1 @10.0.1.1 (mysql-5.1.39 ndb-7.0.9)
id=2 @10.0.1.2 (mysql-5.1.39 ndb-7.0.9)

[mysqld(API)] 6 node(s)
id=7 @10.0.1.1 (mysql-5.1.39 ndb-7.0.9)
id=8 @10.0.1.2 (mysql-5.1.39 ndb-7.0.9)
id=9 (not connected, accepting connect from any host)
id=10 (not connected, accepting connect from 10.0.1.5)

Yes, I have two slots - if I run cmon from host 10.0.1.5, then it is fine (id=10), but in this case, I can connect cmon from any host (id=9). Point is that you must have a slot free with "any host" or from an explicit host.

You must also have MemReportFrequency=30 (or some other value, in seconds) in order to get the memory usage events sent from MySQL Cluster. Otherwize, CMON will not be able to present memory utilization information!


3. Run the cmon install script

> cd /usr/local/cmon/bin
> ./cmon_install.sh

This script will install cmon, rrd, init.d scripts, and generate SQL scripts for GRANTs
Distribution: fedora

**** MYSQL CONNECTION ****
CMON and the RRD scripts needs a mysql connection to speak mysql server holding the cmon database.
Specify the BASEDIR where mysql is installed (default /usr/local/mysql/): /usr/local/mysql/mysql

Specify the hostname of the mysql server having the cmon database (default 'ndb05'): <return>
No hostname specified - using default 'ndb05'

Specify the port of the mysql server having the cmon database (default 3306): <return>
No port specified - using default 3306

Specify the password for the 'cmon' user (default no password): <return>
No password specified - using default (no password)

Specify the ndb-connectstring to the cluster - MUST BE COMMA SEPARATED (e.g, mgmdhost_A,mgmdhost_B):
: ndb01,ndb02
## Comment: ndb01,ndb02 are the hostnames of the two management
## servers in my cluster. YOU MUST SPECIFY THIS!

Specify cluster_id - must be unique amongst all monitored cluster (default is '1'):
<return>
Setting cluster_id = 1

Specify the name of and where to write the cmon log file. (default is /var/log/cmon.log): <return>
logfile is not specified - using default (/var/log/cmon.log)

**** WWW interface ****
The www files will be copied to /var/www/html/cmon/
## Comment: The installation scripts tries to find the default
## location for www used in your distribution
Specify the WWWROOT of your webserver (default /var/www/html): <return>
Copying files to /var/www/html ..

Specify the owner of the WWWROOT/cmon (default user 'apache'): <return>
owner is not specified - using default user 'apache'

**** RRD ****
## Comment: If you don't have RRD installed then graphs will not be available
## from the web client, but there is no other functional impact!
Do you wish to install the rrd database on this node (y/n)? : y
Specify the full path to 'rrdtool' (default is /usr/bin/): <return>
No path to rrd specified - using default /usr/bin/

The rrdtool stores data files in a data directory.
Specify the full path to the data directory (about 20MB free space will be neeeded).
RRD data directory (default is /var/lib/cmon/): <return>
No RRD data directory specified - using default /var/lib/cmon/
The datadir does not exists and will be created now!


**** INSTALL CRON SCRIPTS TO UPDATE/MANAGE/GRAPH RRD ****
cron schedules jobs every 5 minutes to update the rrd database and generarate graphs for the web interface.
You are recommended to install the cron jobs.
cron jobs will be installed in /etc/cron.d/cmon Do you want to install cron jobs for cmon (y/n)? : y
installed cron job in /etc/cron.d/cmon

**** INITD SCRIPTS ****
Do you want to install /etc/init.d/cmon (y/n)? : y
Specify the directory where CMON should write its pidfile (default /var/run/): <return>
Saving configuration to ../etc/init.d/cmon
Installing /etc/init.d/cmon
Done - Installed init.d scripts
Now you can start cmon with '/etc/init.d/cmon start'
Every change was written to /etc/cmon.cnf
You need to issue the following GRANTs before starting CMON:

GRANT super, replication client ON *.* TO 'cmon'@'ndb05';
GRANT select,update,insert,delete,create ON cmon.* TO 'cmon'@'ndb05';



## COMMENT: Connect a mysql client to the cmon database and do
## (actual GRANTs are subject to your particular settings):
mysql> GRANT super, replication client ON *.* TO 'cmon'@'ndb05';
mysql> GRANT select,update,insert,delete,create ON cmon.* TO 'cmon'@'ndb05';

4. Start CMON

## COMMENT: Make sure CMON database and Cluster is started (so initialization won't fail)
> /etc/init.d/cmon start
Starting cmon: ok

The above actually means that the process started. It can still fail initializing the CMON database.
Check the cmon log:

>tail -f /var/log/cmon.log

Feb 17 21:22:28 : (INFO) Starting cmon version 1.1.4 with the following parameters:
--mysql-password=
--mysql-hostname=127.0.0.1
--mysql-port=3306
--ndb-connectstring=app01,app02
--cmon-port=50001
--cluster-id=1
--savetime-clusterlog=24 (hours)

Feb 17 21:22:28 : (INFO) 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
Feb 17 21:22:28 : (INFO) You need to GRANT (and specify a password if you wish) the following on mysql on 127.0.0.1:
Feb 17 21:22:28 : (INFO) GRANT create,select,update,insert,delete on cmon.* to 'cmon'@'127.0.0.1';
Feb 17 21:22:28 : (INFO) GRANT super on *.* to 'cmon'@'127.0.0.1';
Feb 17 21:22:28 : (INFO) Also GRANT (if you have CMON and the MYSQLD on the same host):
Feb 17 21:22:28 : (INFO) GRANT create,select,update,insert,delete on cmon.* to 'cmon'@'127.0.0.1';
Feb 17 21:22:28 : (INFO) GRANT super on *.* to 'cmon'@'127.0.0.1';
Feb 17 21:22:28 : (INFO) and start cmon with --mysql-hostname=127.0.0.1
Feb 17 21:22:28 : (INFO) Testing connection to mysqld..
Feb 17 21:22:28 : (INFO) Trying to connect to management servers...
Feb 17 21:22:28 : (INFO) Connection ok..
Feb 17 21:22:28 : (INFO)
Feb 17 21:22:28 : (INFO) Please wait while cmon is starting up..
...
Feb 17 21:22:30 : (INFO) MonitorThread: Cluster STARTED - all nodes are started - running 1
Feb 17 21:22:30 : (INFO) MonitorThread: Number of Nodes = 12
Feb 17 21:22:30 : (INFO) MonitorThread: MGM node id = 1
Feb 17 21:22:30 : (INFO) MonitorThread: MGM node id = 2
Feb 17 21:22:30 : (INFO) MonitorThread: NDBD node 3 is STARTED
Feb 17 21:22:30 : (INFO) MonitorThread: NDBD node 4 is STARTED
...

## COMMENT: When you see it has picked up the nodes you have in your cluster, then
## the initialization is good.
## If this does not work, check GRANTs, connectstrings, and paths in /etc/cmon.cnf
5. Web interface

Requires that you have a webserver with php-mysql installed.
Simply connect your web browser to e.g 127.0.0.1:8080/cmon or 127.0.0.1/cmon

With the Web Interface it is easy to track performance problems and keep track on resource utilization problems, perhaps you have GC problems in you Java Application Servers (the picture actually show what happened with the transaction load when more worker threads were enabled)?



6. Access the Cluster status from SQL

## COMMENT: connect a mysql client to the mysql server holding the cmon database.
mysql> use cmon;

Database changed

mysql> show tables;
+-------------------------+
| Tables_in_cmon |
+-------------------------+
| alarm |
| alarm_log |
| backup |
| backup_log |
| cluster |
| cluster_log |
| cluster_state |
| cluster_statistics |
| configurator_nodemap |
| diskdata |
| email_notification |
| mailserver |
| memory_usage |
| mysql_global_statistics |
| mysql_master_status |
| mysql_server |
| mysql_slave_status |
| mysql_statistics |
| mysql_variables |
| node_state |
| node_statistics |
| restore |
| restore_log |
| schema_object |
+-------------------------+
24 rows in set (0.00 sec)

mysql> select status from cluster_state;
+---------+
| status |
+---------+
| STARTED |
+---------+
1 row in set (0.00 sec)

mysql> select * from node_state;
+-----+--------+------------+-----------+-----------+---------------+---------+-------------+-----------------+---------------------+
| cid | nodeid | status | node_type | nodegroup | host | version | disconnects | last_disconnect | report_ts |
+-----+--------+------------+-----------+-----------+---------------+---------+-------------+-----------------+---------------------+
| 1 | 1 | CONNECTED | NDB_MGMD | NULL | 10.0.1.1 | 7.0.9 | 0 | NULL | 2010-01-13 12:00:56 |
| 1 | 2 | CONNECTED | NDB_MGMD | NULL | 10.0.1.2 | 7.0.9 | 0 | NULL | 2010-01-13 12:00:56 |
| 1 | 3 | STARTED | NDBD | 0 | 10.0.1.3 | 7.0.9 | 0 | NULL | 2010-01-13 12:00:56 |
| 1 | 4 | STARTED | NDBD | 0 | 10.0.1.4 | 7.0.9 | 0 | NULL | 2010-01-13 12:00:56 |
| 1 | 7 | CONNECTED | API | NULL | 10.0.1.1 | 7.0.9 | 0 | NULL | 2010-01-13 12:00:56 |
| 1 | 8 | CONNECTED | API | NULL | 10.0.1.2 | 7.0.9 | 0 | NULL | 2010-01-13 12:00:56 |
| 1 | 13 | NO_CONTACT | API | NULL | 0.0.0.0 | NULL | 0 | NULL | 2010-01-13 12:00:56 |
| 1 | 12 | NO_CONTACT | API | NULL | 0.0.0.0 | NULL | 0 | NULL | 2010-01-13 12:00:56 |
| 1 | 11 | NO_CONTACT | API | NULL | 0.0.0.0 | NULL | 0 | NULL | 2010-01-13 12:00:56 |
| 1 | 10 | NO_CONTACT | API | NULL | 0.0.0.0 | NULL | 0 | NULL | 2010-01-13 12:00:56 |
| 1 | 9 | CONNECTED | API | NULL | 10.0.1.5 | 7.0.9 | 0 | NULL | 2010-01-13 12:00:56 |
+-----+--------+------------+-----------+-----------+---------------+---------+-------------+-----------------+---------------------+

7. Add MySQL Servers to be monitored

As MySQL Cluster is not able to distinguish between an API or a MySQL Server, we need to tell CMON where our mysql servers are!
> cd /usr/local/cmon/bin
> ./cmon_add_mysqld.sh -ndb01 -p3306
Using default config file path ../etc/cmon.conf
Added MySQL Server

You need to execute the following GRANTs on mysqld@ndb01 for CMON agent to be able to connect:

GRANT REPLICATION SLAVE, SUPER ON *.* TO 'cmon'@'ndb05';
GRANT SELECT ON mysql.* TO 'cmon'@'ndb05';
And so on, you need to add every mysql server you want to monitor.

8. Scripts

All information is stored in SQL tables so it is easy to write scripts around it. Here you can find some example scripts.
## COMMENT: Put the scripts in /usr/local/cmon/bin
> cd /usr/local/cmon/bin
> sh get_cluster_status.sh
STARTED

> sh get_node_status.sh -n 4
STARTED
9. Suggested Deployment
Recommendation: If you have the 'mysqld' on 10.10.1.5, connect it also to Cluster and you can use it for administration!


10. Building from source

If you prefer to build from source then you have to have:
  • MySQL Cluster binaries/libs/includes installed on the build machine
  • gcc/g++/make
  • mysql_config and ndb_config on the PATH
> which mysql_config
/usr/local/mysql/mysql/bin/mysql_config
> which ndb_config
/usr/local/mysql/mysql/bin/ndb_config

## COMMENT: If 'mysql_config' or 'ndb_config' is not on the PATH,
## then you must make sure it is!

> wget http://www.severalnines.com/downloads/cmon/cmon-1.0.2.tar.gz
> tar xvfz cmon-1.0.2.tar.gz
> cd cmon-1.0.2
> ./configure --prefix=/usr/local/cmon/
## COMMENT: end the prefix with 'cmon' and life will be easier.
> make
> sudo make install
> cd /usr/local/cmon/
Proceed at section 2 above.
11. Bugs and problems

I would be very happy if you file bugs on lauchpad! If you don't want to file a bug report, you can send an email to 'support (at) severalnines (dot) com
Also, if you have suggestions for improvements, let me know!