Scalable, persistent, HA NoSQL Memcache storage using MySQL Cluster

Memcached API with Cluster Data Nodes

This post was first published in April 2011 when the first trial version of the Memcached API for MySQL Cluster was released. This API has now been included in the second MySQL Cluster 7.2 Development Milestone Release which was announced at Oracle OpenWorld today. I’ve now refreshed the post – with new content in italics and any obsolete information crossed out.

There are a number of attributes of MySQL Cluster that make it ideal for lots of applications that are considering NoSQL data stores. Scaling out capacity and performance on commodity hardware, in-memory real-time performance (especially for simple access patterns), flexible schemas… sound familiar? In addition, MySQL Cluster adds transactional consistency and durability. In case that’s not enough, you can also simultaneously combine various NoSQL APIs with full-featured SQL – all working on the same data set. This post focuses on a new Memcached API that is now available to download and try out (note that it is not yet GA but is now part of the second MySQL Cluster 7.2 Development Milestone Release).

This post steps through setting up Cluster with the Memcached API and then demonstrates how to read and write the same data through both Memcached and SQL (including for existing MySQL Cluster tables). It would be great if people tried it out for themselves – just download the LINUX binaries from the FTP site or the source from LaunchPad download the Linux or Solaris packages from the “Development Releases” tab at http://dev.mysql.com/downloads/cluster/#downloads and leave comments here (note that there is not currently a Windows version).

Traditional use of Memcached

First of all a bit of background about Memcached. It has typically been used as a cache when the performance of the database of record (the persistent database) cannot keep up with application demand. When changing data, the application will push the change to the database, when reading data, the application first checks the Memached cache, if it is not there then the data is read from the database and copied into Memcached. If a Memcached instance fails or is restarted for maintenance reasons, the contents are lost and the application will need to start reading from the database again. Of course the database layer probably needs to be scaled as well so you send writes to the master and reads to the replication slaves.

This has become a classic architecture for web and other applications and the simple Memcached attribute-value API has become extremely popular amongst developers.

As an example of the simplicity of this API, the following example stores and then retrieves the string “BillyFish” against the key “Test”:

telnet localhost 11211
set Test 0 0 10
BillyFish!
END

get Test
VALUE Test 0 10
BillyFish!
END

Note that if we kill and restart the memcached server, the data is lost (as it was only held in RAM):

get Test
END

New options for using Memcached API with MySQL Cluster

Architecture for Memcached NDB API

What we’re doing with MySQL Cluster is to offer a bunch of new ways of using this API but with the benefits of MySQL Cluster. The solution has been designed to be very flexible, allowing the application architect to find a configuration that best fits their needs.

A quick diversion on how this is implemented. The application sends reads and writes to the memcached process (using the standard Memcached API). This in turn invokes the Memcached Driver for NDB (which is part of the same process) which in turn calls the NDB API for very quick access to the data held in MySQL Cluster’s data nodes (it’s the fastest way of accessing MySQL Cluster).

Because the data is now stored in MySQL Cluster, it is persistent and you can transparently scale out by adding more data nodes (this is an on-line operation).

Another important point is that the NDB API is already a commonly used, fully functional access method that the Memcached API can exploit. For example, if you make a change to a piece of data then the change will automatically be written to any MySQL Server that has its binary logging enabled which in turn means that the change can be replicated to a second site.

Memcached API with Cluster Data Nodes

So the first (and probably simplest) architecture is to co-locate the Memcached API with the data nodes. In this example, the memcached process is stateless in that it does not cache any of the data.

The applications can connect to any of the memcached API nodes – if one should fail just switch to another as it can access the exact same data instantly. As you add more data nodes you also add more memcached servers and so the data access/storage layer can scale out (until you hit the 48 data node limit).

Memcached server with the Application

Another simple option is to co-locate the Memcached API with the application. In this way, as you add more application nodes you also get more Memcached throughput. If you need more data storage capacity you can independently scale MySQL Cluster by adding more data nodes. One nice feature of this approach is that failures are handled very simply – if one App/Memcached machine should fail, all of the other applications just continue accessing their local Memcached API.

Separate Memcached layer

For maximum flexibility, you can have a separate Memcached layer so that the application, the Memcached API & MySQL Cluster can all be scaled independently.

In all of the examples so far, there has been a single source for the data (it’s all held in MySQL Cluster).

Local Cache in Memcached

If you choose, you can still have all or some of the data cached within the memcached server (and specify whether that data should also be persisted in MySQL Cluster) – you choose how to treat different pieces of your data. If for example, you had some data that is written to and read from frequently then store it just in MySQL Cluster, if you have data that is written to rarely but read very often then you might choose to cache it in Memcached as well and if you have data that has a short lifetime and wouldn’t benefit from being stored in MySQL Cluster then only hold it in Memcached. The beauty is that you get to configure this on a per-key-prefix basis (through tables in MySQL Cluster) and that the application doesn’t have to care – it just uses the Memcached API and relies on the software to store data in the right place(s) and to keep everything in sync.

Of course if you want to access the same data through SQL then you’d make sure that it was configured to be stored in MySQL Cluster.

Enough of the theory, how to try it out…

Building and installing the software

Build/Install Memcached

You should make sure that you use a version of Memcached that supports the concept of plugable storage engines – 1.6 does but at the time of writing the older version offered on memcached.org does not:

[billy@ws2 ~]$ mkdir /home/billy/memcached/memcached_1_6/
[billy@ws2 ~]$ cd /home/billy/memcached/memcached_1_6/
[billy@ws2 memcached_1_6]$ git clone git://github.com/memcached/memcached [billy@ws2 memcached_1_6]$ cd memcached/

[billy@ws2 memcached]$ MEMCACHED_SRC_DIR=`pwd`
[billy@ws2 memcached]$ git checkout -t origin/engine-pu

FTP the source tar ball from http://code.google.com/p/memcached/downloads/detail?name=memcached-1.6.0_beta1.tar.gz&can=2&q=

[billy@ws2 memcached]$ INSTALL_PREFIX=/usr/local/mysql-memcache
[billy@ws2 memcached]$ sh config/autorun.sh # Note that memcached
                                            # requires libevent 1.3 or newer.
[billy@ws2 memcached]$ ./configure --prefix=$INSTALL_PREFIX
   --with-libevent=/usr/local/lib
[billy@ws2 memcached]$ make
[root@ws2 memcached]# make install # as root

Build/Install/Configure MySQL Cluster

You can use the Memcached NDB driver with any version of MySQL Cluster 7.2 but if you do want to build the version of MySQL Cluster that comes as part of the package containing the NDB driver for Memcached (see subsequent section for the location of that package) then follow these instructions:

From /home/billy/memcached/mysql-5.1-telco-7.2-memcache (or wherever you downloaded the source package to)... [billy@ws2 mysql-5.1-telco-7.2-memcache]$ sh BUILD/autorun.sh [billy@ws2 mysql-5.1-telco-7.2-memcache]$ ./configure --with-plugins=ndbcluster --prefix=$INSTALL_PREFIX --with-libevent=/usr/local/lib [billy@ws2 mysql-5.1-telco-7.2-memcache]$ make [root@ws2 mysql-5.1-telco-7.2-memcache]# make install # as root

As this post is focused on API access to the data rather than testing High Availability, performance or scalability the Cluster can be kept extremely simple with all of the processes (nodes) running on a single server. The only thing to be careful of when you create your Cluster is to make sure that you define at least 3 API sections (e.g. [mysqld]) in your configuration file so you can access using SQL and 2 Memcached API servers at the same time.

For further information on how to set up a single-host Cluster, refer to this post or just follow the next few steps.

Create a config.ini file for the Cluster configuration:

[ndb_mgmd]
hostname=localhost
datadir=/home/billy/my_cluster/ndb_data
NodeId=1

[ndbd default]
noofreplicas=2
datadir=/home/billy/my_cluster/ndb_data

[ndbd]
hostname=localhost
NodeId=3

[ndbd]
hostname=localhost
NodeId=4

[mysqld]
NodeId=50

[mysqld]
NodeId=51

[mysqld]
NodeId=52

and a my.cnf file for the MySQL server:

[mysqld]
ndbcluster
datadir=/home/billy/my_cluster/mysqld_data

Note that you can use the regular MySQL Cluster 7.2 binaries from http://dev.mysql.com/downloads/cluster/ for the database.

Before starting the Cluster, install the standard databases for the MySQL Server (from wherever you have MySQL Cluster installed – typically /usr/local/mysql):

[billy@ws2 mysql]$ ./scripts/mysql_install_db
  --basedir=/usr/local/mysql
  --datadir=/home/billy/my_cluster/mysqld_data

Build/Install the NDB driver for Memcached

Now if you need tobuild the NDB plugin (assuming that your working from a source package):

[billy@ws2 ~]$ mkdir memcached [billy@ws2 ~]$cd memcached [billy@ws2 mermcached]$ bzr init repo . [billy@ws2 mermcached]$ bzr branch lp:~mysql/mysql-server/mysql-cluster-7.2-labs-memcached [billy@ws2 mysql-cluster-7.2-labs-memcached]$ cd mysql-cluster-7.2-labs-memcached [billy@ws2 mysql-cluster-7.2-labs-memcached]$ sh autorun.sh [billy@ws2 mysql-cluster-7.2-labs-memcached]$ ./configure --prefix=$INSTALL_PREFIX --with-memcached-src=$MEMCACHED_SRC_DIR --with-ndb=/usr/local/mysql --enable-debug [billy@ws2 mysql-cluster-7.2-labs-memcached]$ make [root@ws2 mysql-cluster-7.2-labs-memcached]# make install # as root

Note that the –with-ndb option is included here because I’ve got a preinstalled version of MySQL Cluster installed in /usr/local/mysql and I want to use that rather than installing a new version alongside the NDB driver.

Note that the NDB driver for Memcached is included with the MySQL Cluster DMR 2 and so there is no longer a need to build it for yourself.

Start up the system

We are now ready to start up the Cluster processes:

[billy@ws2 my_cluster]$ ndb_mgmd -f conf/config.ini
  --initial --configdir=/home/billy/my_cluster/conf/
[billy@ws2 my_cluster]$ ndbd
[billy@ws2 my_cluster]$ ndbd
[billy@ws2 my_cluster]$ ndb_mgm -e show # Wait for data nodes to start
[billy@ws2 my_cluster]$ mysqld --defaults-file=conf/my.cnf &

If your version doesn’t already have the ndbmemcache database installed then that should be your next step:

[billy@ws2 ~]$ mysql -h 127.0.0.1 -P3306 -u root < /home/billy/memcached/lp/mysql-cluster-7.2-labs-memcached/storage/ndb/memcache/scripts/metadata.sql

After that, start the Memcached server (with the NDB driver activated):

[billy@ws2 ~]$ cd $INSTALL_PREFIX
[billy@ws2 mysql-memcache]$ bin/memcached -E lib/ndb_engine.so
  -e "connectstring=localhost:1186;role=db-only" -vv -c 20

Notice the “connectstring” – this allows the primary Cluster to be on a different machine to the Memcached API. Note that you can actually use the same Memcached server to access multiple Clusters – you configure this within the ndbmemcached database in the primary Cluster.

Try it out!

Next the fun bit – we can start testing it out:

[billy@ws2 ~]$ telnet localhost 11211

set maidenhead 0 0 3 SL6 STORED get maidenhead VALUE maidenhead 0 3 SL6 END

We can now check that the data really is stored in the database:

mysql> SELECT * FROM ndbmemcache.demo_table;
   +------------------+------------+-----------------+--------------+
   | mkey             | math_value | cas_value       | string_value |
   +------------------+------------+-----------------+--------------+
   | maidenhead       |       NULL | 263827761397761 | SL6          |
   +------------------+------------+-----------------+--------------+

Of course, you can also modify this data through SQL and immediately see the change through the Memcached API:

mysql> UPDATE ndbmemcache.demo_table SET string_value='sl6 4' WHERE mkey='maidenhead';

[billy@ws2 ~]$ telnet localhost 11211

get maidenhead VALUE maidenhead 0 5 SL6 4 END

Note that this is completely schema-less, the application can keep on adding new key/value pairs and they will all get added to the default table. This may well be fine for prototyping or modest sized databases. As you can see this data can be accessed through SQL but there’s a good chance that you’ll want a richer schema on the SQL side or you’ll need to have the data in multiple tables for other reasons (for example you want to replicate just some of the data to a second Cluster for geographic redundancy or to InnoDB for report generation).

The next step is to create your own databases and tables (assuming that you don’t already have them) and then create the definitions for how the app can get at the data through the Memcached API. First let’s create a table that has a couple of columns that we’ll also want to make accessible through the Memcached API:

mysql> CREATE DATABASE clusterdb; USE clusterdb;
mysql> CREATE TABLE towns_tab (town VARCHAR(30) NOT NULL PRIMARY KEY,
  zip VARCHAR(10), population INT, county VARCHAR(10)) ENGINE=NDB;
mysql> INSERT INTO towns_tab VALUES ('Marlow', 'SL7', 14004, 'Berkshire');

Next we need to tell the NDB driver how to access this data through the Memcached API. Two ‘containers’ are created that identify the columns within our new table that will be exposed. We then define the key-prefixes that users of the Memcached API will use to indicate which piece of data (i.e. database/table/column) they are accessing:

mysql> USE ndbmemcache;
mysql> INSERT INTO containers VALUES ('towns_cnt', 'clusterdb',
'towns_tab', 'town', 'zip', 0, NULL, NULL, NULL);
mysql> INSERT INTO containers VALUES ('pop_cnt', 'clusterdb',
  'towns_tab', 'town', 'population', 0, NULL, NULL, NULL);
mysql> SELECT * FROM containers;
   +------------+-------------+-----------------+-------------+----------------+-------+------------------+------------+--------------------+
   | name       | db_schema   | db_table        | key_columns | value_columns  | flags | increment_column | cas_column | expire_time_column |
   +------------+-------------+-----------------+-------------+----------------+-------+------------------+------------+--------------------+
   | demo_table | ndbmemcache | demo_table      | mkey        | string_value   | 0     | math_value       | cas_value  | NULL               |
   | pop_cnt    | clusterdb   | towns_tab       | town        | population     | 0     | NULL             | NULL       | NULL               |
   | demo_tabs  | ndbmemcache | demo_table_tabs | mkey        | val1,val2,val3 | 0     | NULL             | NULL       | NULL               |
   | towns_cnt  | clusterdb   | towns_tab       | town        | zip            | 0     | NULL             | NULL       | NULL               |
   +------------+-------------+-----------------+-------------+----------------+-------+------------------+------------+--------------------+
mysql> INSERT INTO key_prefixes VALUES (1, 'twn_pr:', 0,
  'ndb-only', 'towns_cnt');
mysql> INSERT INTO key_prefixes VALUES (1, 'pop_pr:', 0,
  'ndb-only', 'pop_cnt');
mysql> SELECT * FROM key_prefixes;
   +----------------+------------+------------+---------------+------------+
   | server_role_id | key_prefix | cluster_id | policy        | container  |
   +----------------+------------+------------+---------------+------------+
   |              1 | pop_pr:    |          0 | ndb-only      | pop_cnt    |
   |              3 |            |          0 | caching       | demo_table |
   |              0 |            |          0 | caching       | demo_table |
   |              0 | db:        |          0 | ndb-only      | demo_table |
   |              0 | mc:        |          0 | memcache-only | NULL       |
   |              2 |            |          0 | memcache-only | NULL       |
   |              1 |            |          0 | ndb-only      | demo_table |
   |              1 | t:         |          0 | ndb-only      | demo_tabs  |
   |              1 | twn_pr:    |          0 | ndb-only      | towns_cnt  |
   +----------------+------------+------------+---------------+------------+

At present it is necessary to restart the Memcached server in order to pick up the new key_prefix (and so you’d want to run multiple instances in order to maintain service):

[billy@ws2 mysql-memcache]$ bin/memcached -E lib/ndb_engine.so -e "connectstring=localhost:1186;role=db-only" -vv -c 20

   05-Apr-2011 16:07:34 BST NDB Memcache proto7 started [NDB 7.1.9; MySQL 5.1.51]
   Contacting primary management server (localhost:1186) ...
   Connected to "localhost:1186" as node id 51.
   Retrieved 4 key prefixes for server role "db-only".
   The default behavior is that:
   GET uses NDB only
   SET uses NDB only
   DELETE uses NDB only.
   The 3 explicitly defined key prefixes are "pop_pr:" (towns_tab), "t:" (demo_table_tabs) and "twn_pr:" (towns_tab)

Now these columns (and the data already added through SQL) are accessible through the Memcached API:

[billy@ws2 ~]$ telnet localhost 11211

get twn_pr:Marlow VALUE twn_pr:Marlow 0 3 SL7 END set twn_pr:Maidenhead 0 0 3 SL6 STORED set pop_pr:Maidenhead 0 0 5 42827 STORED

and then we can check these changes through SQL:

mysql> SELECT * FROM clusterdb.towns_tab;
   +------------+------+------------+-----------+
   | town       | zip  | population | county    |
   +------------+------+------------+-----------+
   | Maidenhead | SL6  |      42827 | NULL      |
   | Marlow     | SL7  |      14004 | Berkshire |
   +------------+------+------------+-----------+

One final test is to start a second memcached server that will access the same data. As everything is running on the same host, we need to have the second server listen on a different port:

 [billy@ws2 ~] cd /usr/local/mysql-memcache [billy@ws2 mysql-memcached]$ bin/memcached -E lib/ndb_engine.so -e "connectstring=localhost:1186;role=db-only" -vv -c 20 -p 11212 -U 11212 [billy@ws2 ~]$ telnet localhost 11212

get twn_pr:Marlow VALUE twn_pr:Marlow 0 3 SL7 END

Memcached alongside NoSQL & SQL APIs

As mentioned before, there’s a wide range of ways of accessing the data in MySQL Cluster – both SQL and NoSQL. You’re free to mix and match these technologies – for example, a mission critical business application using SQL, a high-running web app using the Memcached API and a real-time application using the NDB API. And the best part is that they can all share the exact same data and they all provide the same HA infrastructure (for example synchronous replication and automatic failover within the Cluster and geographic replication to other clusters).

Finally, a reminder – please try this out and let us know what you think (or if you don’t have time to try it then let us now what you think anyway) by adding a comment to this post.

MySQL Cluster 7.1.15a is available

The binary version for MySQL Cluster 7.1.15a has now been made available at http://www.mysql.com/downloads/cluster/ (GPL version) or https://edelivery.oracle.com/ (commercial version)

A description of all of the changes (fixes) that have gone into MySQL Cluster 7.1.15a (compared to 7.1.15) can be found in the official MySQL Cluster documentation for Cluster 7.1.15a Change log.

MySQL Cluster Manager 1.1.2 – creating a Cluster is now trivial

MySQL Cluster Manager 1.1.2 is now available to download and try from Oracle E-Delivery (select “MySQL Database” as the product pack). Something that’s new and really cool in the new version is that you can download a version of MCM that actually includes the MySQL Cluster software itself and then you can have MCM automatically define, create and start a single-host cluster deployment for you with just the command “mcmd –bootstrap”. This post aims to show that it’s really as simple as that!

I’ve been playing with Windows recently and so I’ll use that for this example but things would be very similar on other platforms.

Step 1 Download from E-Delivery and extract the zip file

Step2 Start your first cluster!

PS D:\Andrew\Documents\MySQL\mcm> bin\mcmd --bootstrap
MySQL Cluster Manager 1.1.2 started
Connect to MySQL Cluster Manager by running "D:\Andrew\Documents\MySQL\mcm\bin\mcm" -a NOVA:1862
Configuring default cluster 'mycluster'...
Starting default cluster 'mycluster'...
Cluster 'mycluster' started successfully
 ndb_mgmd NOVA:1186
 ndbd NOVA
 ndbd NOVA
 mysqld NOVA:3306
 mysqld NOVA:3307
 ndbapi *
Connect to the database by running "D:\Andrew\Documents\MySQL\mcm\cluster\bin\mysql" -h NOVA -P 3306 -u root

That’s it!

Just to prove it you can now go ahead and start using the database (note that I connect with the command suggested by MCM but in this case I had to shift the quotes…

PS C:\Users\Andrew> D:"\Andrew\Documents\MySQL\mcm\cluster\bin\mysql" -h NOVA -P 3306 
mysql> CREATE DATABASE clusterdb;
mysql> USE clusterdb;
mysql> CREATE TABLE towns (name VARCHAR(30) NOT NULL PRIMARY KEY) ENGINE=NDBCLUSTER;
mysql> INSERT INTO towns VALUES ('Maidenhead'), ('Marlow');
mysql> SELECT * FROM towns;
+------------+
| name       |
+------------+
| Maidenhead |
| Marlow     |
+------------+

So how much simpler is this than doing it by hand? 

With MCM bootstrap:

• Packages to download & install: 1
• Config files to create/edit: 0
• Commands to run: 1

London 18th October: Oracle Technology Network MySQL Developer Day

I will be presenting on MySQL Cluster and MySQL Replication at the Oracle Technical Network MySQL Developer day in London on Tuesday, 18 October 2011 (8:30 AM – 4:00 PM). It’s free but you need to register here while there are still places (attendance has been extremely high at other locations).

The MySQL Developer Day is a one-stop shop for you to learn all the essential MySQL skills. In this free, one-day seminar, we will cover everything you need to know to successfully design, develop, and manage your MySQL databases. You’ll also learn the guidelines and best practices in performance tuning and scalability.

Attend this event and gain the knowledge to:

• Develop your new applications cost-effectively using MySQL
• Improve performance of your existing MySQL databases
• Manage your MySQL environment more efficiently

8:30 a.m. – 9:30 a.m. Registration
9:30 a.m. – 10:30 a.m. Keynote: MySQL Essentials
10:30 a.m. – 11:30 p.m. Session: MySQL Replication and Scalabilit
11:30 a.m. – 11:45 a.m. Break
11:45 a.m. – 12:45 p.m. Session: MySQL Cluster
12:45 p.m. – 1:30 p.m. Lunch
1:30 p.m. – 2:30 p.m. Session: MySQL Administration and Management Tools
2:30 p.m. – 3:30 p.m. Session: MySQL Performance Tuning
3:30 p.m. – 4:00 p.m. Close

If you’re going to be in the area, please register and come along – would be good to meet as many of you as possible.

My sessions at Oracle OpenWorld 2011

Slight adjustment to some of the times + added the MySQL community reception (read  vodka!). Oracle OpenWorld (San Francisco) starts on Sunday 2nd October (including some MySQL community sessions) through Thursday 6th October. MySQL has a lot of sessions this year as well as 3 demo booths.

This year I’m going to be involved in 3 public sessions – if you’re attending, please come along and say hello!

• Getting the Most Out of MySQL on Windows – 13:15 on Tuesday (Marriott Marquis – Golden Gate C2)
• Building Highly Available and Scalable Real-Time Services with MySQL Cluster – 10:15 on Wednesday (Marriott Marquis – Golden Gate C1)
• NoSQL Access to MySQL: The Best of Both Worlds – 11:45 on Wednesday (Marriott Marquis – Golden Gate C1)
• MySQL Community Reception – 19:00 on Wednesday (San Francisco Marriott Marquis – Foothill G)

MySQL Cluster 7.1.15 released

The binary version for MySQL Cluster 7.1.15 has now been made available at http://www.mysql.com/downloads/cluster/

A description of all of the changes (fixes) that have gone into MySQL Cluster 7.1.15 (compared to 7.1.13) can be found in the official MySQL Cluster documentation for Cluster 7.1.14 & 7.1.15.

Direct access to MySQL Cluster through Memcached API – free webinar

Memcached access to MySQL Cluster

As described in an earlier post Memcached is an extremely popular caching layer used in most big web properties and we’re adding the ability to access MySQL Cluster directly using the familiar Memcached key-value/NoSQL API without needing to go through the MySQL Server. There is a huge amount of flexibility built into this solution – including:

• Decide what data should be held only in the Memcached server; what should be written straight through to MySQL Cluster and then discarded  and what data should be cached in Memcached but persisted in MySQL Cluster
• Where data is held both in Cluster and the Memcached server, they can automatically be kept in sync
• By default it’s completely schema-less, all key-value pairs will be transparently stored in a single table within MySQL Cluster behind the scenes
• Can map key-prefixes to columns in MySQL Cluster tables – allowing simultaneous access to the same data using SQL.

MySQL Cluster Manager 1.1.1 (GA) Available

The latest (GA) version of MySQL Cluster Manager is available through Oracle’s E-Delivery site. You can download the software and try it out for yourselves (just select “MySQL Database” as the product pack, select your platform, click “Go” and then scroll down to get the software).

So what’s new in this version

If you’ve looked at MCM in the past then the first thing that you’ll notice is that it’s now much simpler to get it up and running – in particular the configuration and running of the agent has now been reduced to just running a single executable (called "mcmd").

The second change is that you can now stop the MCM agents from within the MCM CLI – for example "stop agents mysite" will safely stop all of the agents running on the hosts defined by "mysite".

Those 2 changes make it much simpler for the novice user to get up and running quickly; for the more expert user, the most signifficant change is that MCM can now manage multiple clusters.

Obviously, there are a bunch of more minor changes as well as bug fixes.

Refresher – So What is MySQL Cluster Manager?

MySQL Cluster Manager provides the ability to control the entire cluster as a single entity, while also supporting very granular control down to individual processes within the cluster itself.  Administrators are able to create and delete entire clusters, and to start, stop and restart the cluster with a single command.  As a result, administrators no longer need to manually restart each data node in turn, in the correct sequence, or to create custom scripts to automate the process.

MySQL Cluster Manager automates on-line management operations, including the upgrade, downgrade and reconfiguration of running clusters as well as adding nodes on-line for dynamic, on-demand scalability, without interrupting applications or clients accessing the database.  Administrators no longer need to manually edit configuration files and distribute them to other cluster nodes, or to determine if rolling restarts are required. MySQL Cluster Manager handles all of these tasks, thereby enforcing best practices and making on-line operations significantly simpler, faster and less error-prone.

MySQL Cluster Manager is able to monitor cluster health at both an Operating System and per-process level by automatically polling each node in the cluster.  It can detect if a process or server host is alive, dead or has hung, allowing for faster problem detection, resolution and recovery.

To deliver 99.999% availability, MySQL Cluster has the capability to self-heal from failures by automatically restarting failed Data Nodes, without manual intervention.  MySQL Cluster Manager extends this functionality by also monitoring and automatically recovering SQL and Management Nodes.

How is it Implemented?

MySQL Cluster Manager Architecture

MySQL Cluster Manager is implemented as a series of agent processes that co-operate with each other to manage the MySQL Cluster deployment; one agent running on each host machine that will be running a MySQL Cluster node (process). The administrator uses the regular mysql command to connect to any one of the agents using the port number of the agent (defaults to 1862 compared to the MySQL Server default of 3306).

How is it Used?

When using MySQL Cluster Manager to manage your MySQL Cluster deployment, the administrator no longer edits the configuration files (for example config.ini and my.cnf); instead, these files are created and maintained by the agents. In fact, if those files are manually edited, the changes will be overwritten by the configuration information which is held within the agents. Each agent stores all of the cluster configuration data, but it only creates the configuration files that are required for the nodes that are configured to run on that host.

Similarly when using MySQL Cluster Manager, management actions must not be performed by the administrator using the ndb_mgm command (which directly connects to the management node meaning that the agents themselves would not have visibility of any operations performed with it).

When using MySQL Cluster Manager, the ‘angel’ processes are no longer needed (or created) for the data nodes, as it becomes the responsibility of the agents to detect the failure of the data nodes and recreate them as required. Additionally, the agents extend this functionality to include the management nodes and MySQL Server nodes.

Installing, Configuring & Running MySQL Cluster Manager

On each host that will run Cluster nodes, install the MCM agent. To do this, just download the zip file from Oracle E-Delivery and then extract the contents into a convenient location:

$ unzip V27167-01.zip
$ tar xf mysql-cluster-manager-1.1.1-linux-rhel5-x86-32bit.tar.gz
$ mv mysql-cluster-manager-1.1.1-linux-rhel5-x86-32bit ~/mcm

Starting the agent is then trivial (remember to reapeat on each host though):

$ cd ~/mcm
$ bin/mcmd&

Next, some examples of how to use MCM.

Example 1: Create a Cluster from Scratch

The first step is to connect to one of the agents and then define the set of hosts that will be used for the Cluster:

$ mysql -h 192.168.0.10 -P 1862 -u admin -psuper --prompt='mcm> '
mcm> create site --hosts=192.168.0.10,192.168.0.11,192.168.0.12,192.168.0.13 mysite;

Next step is to tell the agents where they can find the Cluster binaries that are going to be used, define what the Cluster will look like (which nodes/processes will run on which hosts) and then start the Cluster:

mcm> add package --basedir=/usr/local/mysql_6_3_27a 6.3.27a;
mcm> create cluster --package=6.3.26 --processhosts=ndb_mgmd@192.168.0.10,ndb_mgmd@192.168.0.11,
  ndbd@192.168.0.12,ndbd@192.168.0.13,ndbd@192.168.0.12, ndbd@192.168.0.13,mysqld@192.168.0.10,
  mysqld@192.168.0.11 mycluster;
mcm> start cluster mycluster; 

Example 2: On-Line upgrade of a Cluster

A great example of how MySQL Cluster Manager can simplify management operations is upgrading the Cluster software. If performing the upgrade by hand then there are dozens of steps to run through which is time consuming, tedious and subject to human error (for example, restarting nodes in the wrong order could result in an outage). With MySQL Cluster Manager, it is reduced to two commands – define where to find the new version of the software and then perform the rolling, in-service upgrade:

mcm> add package --basedir=/usr/local/mysql_7_1_8 7.1.8;
mcm> upgrade cluster --package=7.1.8 mycluster;

Behind the scenes, each node will be halted and then restarted with the new version – ensuring that there is no loss of service.

Example 3: Automated On-Line Add-Node

Automated On-Line Add-Node

Since MySQL Cluster 7.0 it has been possible to add new nodes to a Cluster while it is still in service; there are a number of steps involved and as with on-line upgrades if the administrator makes a mistake then it could lead to an outage.

We’ll now look at how this is automated when using MySQL Cluster Manager; the first step is to add any new hosts (servers) to the site and indicate where those hosts can find the Cluster software:

mcm> add hosts --hosts=192.168.0.14,192.168.0.15 mysite;
mcm> add package --basedir=/usr/local/mysql_7_1_8
  --hosts=192.168.0.14,192.168.0.15 7_1_8;

The new nodes can then be added to the Cluster and then started up:

mcm> add process --processhosts=mysqld@192.168.0.10,mysqld@192.168.0.11,ndbd@192.168.0.14,
  ndbd@192.168.0.15,ndbd@192.168.0.14,ndbd@192.168.0.15 mycluster;
mcm> start process --added mycluster; 

The Cluster has now been extended but you need to perform a final step from any of the MySQL Servers to repartition the existing Cluster tables to use the new data nodes:

mysql> ALTER ONLINE TABLE <table-name> REORGANIZE PARTITION;
mysql> OPTIMIZE TABLE <table-name>;

Where can I found out more?

There is a lot of extra information to help you understand what can be achieved with MySQL Cluster Manager and how to use it:

• MySQL Cluster Manager Demo Video
• MySQL Cluster Manager White Paper
• MySQL Cluster (including Manager) Documentation
• Download and try out MySQL Cluster Manager for yourself – find it under “MySQL Database”

Webinar: Delivering Scalable and Highly Available Session Management with MySQL Cluster

Update – the webinar replay is now available from http://www.mysql.com/news-and-events/on-demand-webinars/display-od-630.html?clusterdb

There is a free webinar today (31st May) covering the use of MySQL Cluster in session management – register for free here. The session starts at 9:00 am Pacific, 17:00 UK, 18:00 Central European Time.

As organizations seek to enhance their users’ web experience through personalization based on historic browsing and buying behaviors, session data is becoming more critical. Larger volumes of session data need to be managed and persisted in real-time, and so session management has become increasingly performance-intensive, while also demanding very high levels of availability to ensure a seamless customer experience. In these scenarios, it makes sense to evaluate the MySQL Cluster database.

This is a practical session, demonstrating how to manage PHP session data with MySQL Cluster, and includes:

• Demands of session management
• Brief review of MySQL Cluster architecture
• Why MySQL Cluster is well placed to meet the demands from session management
• Getting a session management solution up and running with PHP and MySQL Cluster
• Performance optimization, including the latest enhancements to JOIN performance
• Case studies

Webinar: MySQL Cluster, Scaling Web Databases with Auto-Partitioning and SQL/NoSQL Access

Scale-out with MySQL Cluster

Update: webinar replay is now available from http://event.on24.com/r.htm?e=311660&s=1&k=3DCFE1CB3E1CF3F0FD0969DC66D93989

On Thursday 26th May Mat Keep and I will be presenting a webinar on how MySQL Cluster can deliver linear scalability – together with some tips on how to achieve it. As always the webinar is free but you need to register here.

The session starts on Thu, May 26 at 17:00 UK time, 18:00 Central European Time, 09:00 Pacific.

This webinar will discuss best practices in scaling services on-demand for high volumes of reads and writes, and provide insight on the range of NoSQL and SQL access methods available to developers, specifically covering:

• Automatic partitioning (sharding) for high scalability
• On-line scaling of the cluster across commodity hardware
• SQL and NoSQL interfaces, and what should be used when
• On-line updating of schema design to accommodate rapidly evolving applications
• Resources to get started