Further MySQL Cluster additions to MySQL Enterprise Monitor

Data Node Restarted alert

About 11 months ago I described the MySQL Cluster functionality that was added to MySQL Enterprise Monitor 2.3; this new post is intended to just bring this up to date – briefly describing the new graph and advisors which have been added since then (up to and including MEM 2.3.7).

Cluster Data Node Has Been Restarted

This new alert flags when a data node has been restarted (by default it alerts on any data node that has started in the last 10 minutes but you can change that interval if you wish). If you manually perform a restart (e.g. as part of a rolling upgrade) then you can safely ignore this alert (or you may even want to temporarily unschedule it first). However if the restart was spontaneous then this can be an early warning for you to take a look at the error logs and address any issues before the situation worsens.

Cluster DiskPageBuffer Hit Ratio Is Low (& associated graph)

The Disk Page Buffer is a cache on each data node which is used when using disk-based tables. Like any cache, the higher the hit rate the better the performance. Tuning the size of this cache can have a significant effect on your system – the new graph helps you see the results of your changes and the alert warns you when the ration falls below an acceptable level (this could happen for example temporarily after a data node restart or permanently when the active data set grows).

The ndbinfo database has a new table “diskpagebuffer” which contains the raw information needed to calculate the cache hit ration and it is the source of the data for the new alert and graph. If you wanted to calculate the cache hit ratio for yourself directly from this table then you can use the following query:

mysql> SELECT node_id, page_requests_direct_return AS hit, 
 page_requests_wait_io AS miss,  100*page_requests_direct_return/
 (page_requests_direct_return+page_requests_wait_io) AS hit_rate
  FROM ndbinfo.diskpagebuffer;

+---------+------+------+----------+
| node_id | hit  | miss | hit_rate |
+---------+------+------+----------+
| 3       | 6    | 3    | 66.6667  |
| 4       | 10   | 3    | 76.9231  |
+---------+------+------+----------+

The alert is first raised (info level) when the hit rate falls bellow 97%, the warning level is raised at 90% and the critical level at 80%. Again, you can alter any of these thresholds.

The new graph simply displays how the hit rate varies over time so that you can spot trends.

As a reminder you can get more information on the original set of alerts and graphs here.

MySQL Cluster Webinar on Wednesday: What’s New in MySQL Cluster 7.2.1 Development Milestone Release

There’s a webinar this Wednesday (9 am Pacific; 5 pm UK; 6 pm CET) that explains what’s new in the MySQL Cluster Development Milestone Release – register here for free access.

Join this session to learn about the latest enhancements to the MySQL Cluster database, enabling even more of the latest generation of web, telecoms and embedded applications to take advantage of high write scalability, SQL and NoSQL interfaces and 99.999% availability.

New capabilities include:

•  70x higher JOIN performance for the latest generation of web applications using Adaptive Query Localization, enabling real-time analytics across live data sets
• New NoSQL interface via memcached to further enhance developer flexibility and productivity
• Simplified global scalability with multi-site clusters and enhanced Active/Active replication
• Integration with the MySQL 5.5 release, enabling users to fully exploit the latest capabilities of both the InnoDB and MySQL Cluster storage engines within a single application
• Streamlined cluster provisioning and maintenance

The 2nd Development Milestone Release of MySQL Cluster 7.2.1 was announced at Oracle OpenWorld 2011. This release is now available for download and evaluation under the GPL license. This session will help to get you started with this latest release.

MySQL Cluster material from Oracle Open World 2011

For those people that weren’t able to attend the MySQL Cluster demo or sessions at this year’s Oracle Open World (or even for those that did) and would like copies of the material, links are provided here.

• Building Scalable & Highly Available Database Services with MySQL Cluster – includes a description of the features in the new MySQL Cluster7.2  Development Milestone Release 2
• NoSQL Access to MySQL: The Best of Both Worlds – Covers what people are looking for from NoSQL data stores and how MySQL Cluster can meet these needs (both in terms of database characteristics and the APIs offered to applications)
• Getting the most out of MySQL on Windows – How to deliver Highly Available applications with MySQL on Windows (including replication, Windows Server Failover Clustering and MySQL Cluster) as well as the latest in MySQL tools and the Windows installer.
• MySQL Cluster Demo – Interactive flash demo, used in the MySQL demo area

70x Faster Joins with AQL in MySQL Cluster 7.2 DMR

70x faster joins with AQL

The new MySQL Cluster Development Milestone Release just announced by Oracle includes 2 new features which when combined can improve the performance of joins by a factor of 70x (or even higher). The first enhancement is that MySQL Cluster now provides the MySQL Server with better information on the available indexes which allows the MySQL optimizer to automatically produce better query execution plans. Previously it was up to the user to provide hints to the optimizer. The second new feature is Adaptive Query Localization which allows the work of the join to be distributed across the data nodes (local to the data it’s working with) rather than up in the MySQL Server; this allows more computing power to be applied to calculating the join as well as dramatically reducing the number of messages being passed around the system. The combined result is that your joins can now run MUCH faster and this post describes a test that results in a 70x speed-up for a real-world query.

The Query

11-Way Join used in Test

The join used in this test is based on a real-world example used for an on-line store/Content Management System. The original query identified all of the media in the system which was appropriate to a particular device and for which a user is entitled to access. As this query is part of a customer’s application I’ve replaced all of the table and column names.

The join runs across 11 tables (which contain 33.5K rows in total) and produces a result set of 2,060 rows, each with 19 columns. The figure to the right illustrates the join and the full join is included below.

SELECT
        tab1.uniquekey,
        tab8.name,
        tab8.tab8id,
        tab11.name,
        tab11.tab11id,
        tab11.value,
        tab10.tab10id,
        tab10.name,
        tab2.name,
        tab2.tab2id,
        tab4.value + tab5.value + tab6.value,
        tab3.colx,
        tab3.tab3id,
        tab4.tab4id,
        tab4.name,
        tab5.tab5id,
        tab5.name,
        tab6.tab6id,
        tab6.name
FROM
        tab1,tab2,tab3,tab4,tab5,tab6,tab7,tab8,tab9,tab10,tab11
WHERE
        tab7.tab2id = tab2.tab2id	AND
        tab7.tab8id = tab8.tab8id	AND
        tab9.tab2id = tab2.tab2id	AND
	tab9.tab10id = tab10.tab10id	AND
	tab10.tab11id = tab11.tab11id	AND
        tab3.tab2id = tab2.tab2id	AND
	tab3.tab4id = tab4.tab4id	AND
	tab4.tab5id = tab5.tab5id	AND
	tab4.colz =  'Y'		AND
	tab5.tab6id = tab6.tab6id	AND
	tab6.tab6id IN (6)		AND
	(tab3.tab4id IN (66, 77, 88))	AND
	tab1.tab2id = tab2.tab2id	AND
	tab1.colx = 6;

Enabling AQL

First of all, make sure that you’re using the DMR2 version of MySQL Cluster (7.2.1) available from the “Development Release” tab at http://dev.mysql.com/downloads/cluster/#downloads

There are then a couple of variables to check are set:

mysql> show variables like '%ndb%';

....
| ndb_index_stat_cache_entries        | 32                                                                                                                                                                                                                                                  |
| ndb_index_stat_enable               | ON                                                                                                                                                                                                                                                  |
| ndb_index_stat_option               | loop_checkon=1000ms,loop_idle=1000ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=32,check_delay=1m,delete_batch=8,clean_delay=0,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=1m,cache_limit=32M,cache_lowpct=90 |
| ndb_index_stat_update_freq          | 20                                                                                                                                                                                                                                                  |
| ndb_join_pushdown                   | ON                                                                                                                                                                                                                                                  |
....

Running the Query & Results

Test configuration

To get the full benefit from AQL, you should run “ANALYZE TABLE;” once for each of the tables (no need to repeat for every query). This is very important and you should start doing this as a matter of course when you create or modify a table.

For this test, 3 machines were used:

1. Intel Core 2 Quad Core @2.83 GHz; 8 Gbytes RAM; single, multi-threaded data node (ndbmtd)
2. Intel Core 2 Quad Core @2.83 GHz; 8 Gbytes RAM; single, multi-threaded data node (ndbmtd)
3. 4 Core Fedora VM running on VirtualBox on Windows 7, single MySQL Server

The query was then run and compared to MySQL CLuster 7.1.15a:

How it Works

Classic Nested-Loop-Join

Traditionally, joins have been implemented in the MySQL Server where the query was executed. This is implemented as a nested-loop join; for every row from the first part of the join, a request has to be sent to the data nodes in order to fetch the data for the next level of the join and for every row in that level…. This method can result in a lot of network messages which slows down the query (as well as wasting resources). When turned on, Adaptive Query Localization results in the hard work being pushed down to the data nodes where the data is locally accessible. As a bonus, the work is divided amongst the pool of data nodes and so you get parallel execution.

NDB API

I’ll leave the real deep and dirty details to others but cover the basic concepts here. All API nodes access the data nodes using the native C++ NDB API, the MySQL Server is one example of an API node (the new Memcached Cluster API is another). This API has been expanded to allow parameterised or linked queries where the input from one query is dependent on the previous one. To borrow an example from an excellent post by Frazer Clement on the topic, the classic way to implement a join would be…

SQL > select t1.b, t2.c from t1,t2 where t1.pk=22 and t1.b=t2.pk;
  ndbapi > read column b from t1 where pk = 22;

              [round trip]

           (b = 15)
  ndbapi > read column c from t2 where pk = 15;

              [round trip]

           (c = 30)
           [ return b = 15, c = 30 ]

Using the new functionality this can be performed with a single network round trip where the second read operation is dependent on the results of the first…

  ndbapi > read column @b:=b from t1 where pk = 22;
           read column c from t2 where pk=@b;

              [round trip]

           (b = 15, c = 30)
           [ return b = 15, c = 30 ]

You can check whether your query is fitting these rules using EXPLAIN, for example:

mysql> set ndb_join_pushdown=on;

mysql> EXPLAIN SELECT COUNT(*) FROM residents,postcodes WHERE residents.postcode=postcodes.postcode AND postcodes.town="MAIDENHEAD";
+----+-------------+-----------+--------+---------------+---------+---------+------------------------------+--------+--------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+---------------+---------+---------+------------------------------+--------+--------------------------------------------------------------------------+ | 1 | SIMPLE | residents | ALL | NULL | NULL | NULL | NULL | 100000 | Parent of 2 pushed join@1 | | 1 | SIMPLE | postcodes | eq_ref | PRIMARY | PRIMARY | 22 | clusterdb.residents.postcode | 1 | Child of 'residents' in pushed join@1; Using where with pushed condition | +----+-------------+-----------+--------+---------------+---------+---------+------------------------------+--------+--------------------------------------------------------------------------+

mysql> EXPLAIN EXTENDED SELECT COUNT(*) FROM residents,postcodes,towns WHERE residents.postcode=postcodes.postcode AND postcodes.town=towns.town AND towns.county="Berkshire"; +----+-------------+-----------+--------+---------------+---------+---------+------------------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+--------+---------------+---------+---------+------------------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------+ | 1 | SIMPLE | residents | ALL | NULL | NULL | NULL | NULL | 100000 | 100.00 | Parent of 3 pushed join@1 | | 1 | SIMPLE | postcodes | eq_ref | PRIMARY | PRIMARY | 22 | clusterdb.residents.postcode | 1 | 100.00 | Child of 'residents' in pushed join@1 | | 1 | SIMPLE | towns | eq_ref | PRIMARY | PRIMARY | 22 | clusterdb.postcodes.town | 1 | 100.00 | Child of 'postcodes' in pushed join@1; Using where with pushed condition: (`clusterdb`.`towns`.`county` = 'Berkshire') | +----+-------------+-----------+--------+---------------+---------+---------+------------------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------+

Note that if you want to check for more details why your join isn’t currently being pushed down to the data node then you can use “EXPLAIN EXTENDED” and then “SHOW WARNINGS” to get more hints. Hopefully that will allow you to tweak your queries to get the best improvements.

PLEASE let us know your experiences and give us examples of queries that worked well and (just as importantly) those that didn’t so that we can improve the feature – just leave a comment on this Blog with your table schemas, your query and your before/after timings.

Find out more on the contents of the new MySQL Cluster 7.2 DMR in this MySQL dev-zone article.

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

MySQL with Windows Server 2008 R2 Failover Clustering

Windows Server 2008 R2 Failover Clustering

Oracle has announced support for running MySQL on Windows Server Failover Clustering (WSFC); with so many people developing and deploying MySQL on Windows, this offers a great option to add High Availability to MySQL deployments if you don’t want to go as far as deploying MySQL Cluster.

This post will give a brief overview of how to set things up but for all of the gory details a new white paper MySQL with Windows Server 2008 R2 Failover Clustering is available – please give me any feedback. I will also be presenting on this at a free webinar on Thursday 15th September (please register in advance) as well at an Oracle OpenWorld session in San Francisco on Tuesday 4th October (Tuesday, 01:15 PM, Marriott Marquis – Golden Gate C2) – a good opportunity to get more details and get your questions answered.

It sometimes surprises people just how much MySQL is used on Windows, here are a few of the reasons:

• Lower TCO
  • 90% savings over Microsoft SQL Server
  • If your a little skeptical about this then try it out for yourself with the MySQL TCO Savings Calculator
• Broad platform support
  • No lock-in
  • Windows, Linux, MacOS, Solaris
• Ease of use and administration
  • < 5 mins to download, install & configure
  • MySQL Enterprise Monitor & MySQL WorkBench
• Reliability
  • 24×7 Uptime
  • Field Proven
• Performance and scalability
  • MySQL 5.5 delivered over 500% performance boost on Windows.
• Integration into Windows environment
  • ADO.NET, ODBC & Microsoft Access Integration
  • And now, support for Windows Server Failover Clustering!

Solution Overview

MySQL with Windows Failover Clustering requires at least 2 servers within the cluster together with some shared storage (for example FCAL SAN or iSCSI disks). For redundancy, 2 LANs should be used for the cluster to avoid a single point of failure and typically one would be reserved for the heartbeats between the cluster nodes.

The MySQL binaries and data files are stored in the shared storage and Windows Failover Clustering ensures that at most one of the cluster nodes will access those files at any point in time (hence avoiding file corruptions).

Clients connect to the MySQL service through a Virtual IP Address (VIP) and so in the event of failover they experience a brief loss of connection but otherwise do not need to be aware that the failover has happened other than to handle the failure of any in-flight transactions.

Target Configuration

This post will briefly step through how to set up and use a cluster and this diagrams shows how this is mapped onto physical hardware and network addresses for the lab used later in this post. In this case, iSCSI is used for the shared storage. Note that ideally there would be an extra subnet for the heartbeat connection between ws1 and ws3.

This is only intended to be an overview and the steps have been simplified refer to the white paper for more details on the steps.

Prerequisites

• MySQL 5.5 & InnoDB must be used for the database (note that MyISAM is not crash-safe and so failover may result in a corrupt database)
• Windows Server 2008 R2
• Redundant network connections between nodes and storage
• WSFC cluster validation must pass
• iSCSI or FCAL SAN should be used for the shared storage

Step 1 Configure iSCSI in software (optional)

Create 2 clustered disks

This post does not attempt to describe how to configure a highly available, secure and performant SAN but in order to implement the subsequent steps shared storage is required and so in this step we look at one way of using software to provide iSCSI targets without any iSCSI/SAN hardware (just using the server’s internal disk). This is a reasonable option to experiment with but probably not what you’d want to deploy with for a HA application. If you already have shared storage set up then you can skip this step and use that instead.

As part of this process you’ll create at least two virtual disks within the iSCSI target; one for the quorum file and one for the MySQL binaries and data files. The quorum file is used by Windows Failover Clustering to avoid “split-brain” behaviour.

Step 2. Ensure Windows Failover Clustering is enabled

Ensure that WSFC is enabled

To confirm that Windows Failover Clustering is installed on ws1 and ws3, open the “Features” branch in the Server Manager tool and check if “Failover Cluster Manager” is present.

If Failover Clustering is not installed then it is very simple to add it. Select “Features” within the Service Manager and then click on “Add Features” and then select “Failover Clustering” and then “Next”.

Step 3. Install MySQL as a service on both servers

Install MySQL as a Windows Service

If MySQL is already installed as a service on both ws1 and ws3 then this step can be skipped.

The installation is very straight-forward using the MySQL Windows Installer and selecting the default options is fine.

Within the MySQL installation wizard, sticking with the defaults is fine for this exercise. When you reach the configuration step, check “Create Windows Service”.

The installation and configuration must be performed on both ws1 and ws2, if necessary.

Step 4. Migrate MySQL binaries & data to shared storage

If the MySQL Service is running on either ws1 or ws3 then stop it – open the Task Manager using ctrl-shift-escape, select the “Services” tab and then right-click on the MySQL service and choose “Stop Service”.

As the iSCSI disks were enabled on ws1 you can safely access them in order to copy across the MySQL binaries and data files to the shared disk.

Step 5. Create Windows Failover Cluster

Create the Cluster (without MySQL)

From the Server Manager on either ws1 or ws3 navigate to “Features -> Failover Cluster Manager” and then select “Validate a Configuration”. When prompted enter ws1 as one name and then ws3 as the other.

In the “Testing Options” select “Run all tests” and continue. If the tests report any errors then these should be fixed before continuing.

Now that the system has been verified, select “Create a Cluster” and provide the same server names as used in the validation step. In this example, “MySQL” is provided as the “Cluster Name” and then the wizard goes on to create the cluster.

Step 6. Create Cluster of MySQL Servers within Windows Cluster

Cluster the MySQL Service

Adding the MySQL service to the new Cluster is very straight-forward. Right-click on “Services and applications” in the Server Manager tree and select “Configure a Service or Application…”. When requested by the subsequent wizard, select “Generic Service” from the list and then “MySQL” from the offered list of services. Our example name was “ClusteredMySQL”. Please choose an appropriate name for your cluster. The wizard will then offer the shared disk that has not already been established as the quorum disk for use with the Clustered service – make sure that it is selected.

Once the wizard finishes, it starts up the MySQL Service. Click on the “ClusteredMySQL” service branch to observe that the service is up and running. You should also make a note of the Virtual IP (VIP) assigned, in this case 192.168.2.18.

Step 7. Test the cluster

As described in Step 6, the VIP should be used to connect to the clustered MySQL service:

C:\ mysql –u root –h 192.168.2.18 –P3306 –pbob

From there create a database and populate some data.

mysql> CREATE DATABASE clusterdb;
mysql> USE clusterdb;
mysql> CREATE TABLE simples (id int not null primary key) ENGINE=innodb;
mysql> INSERT INTO simples VALUES (1);
mysql> SELECT * FROM simples;
+----+
| id |
+----+
| 1 |
+----+

Migrate MySQL Service Across Cluster

The MySQL service was initially created on ws1 but it can be forced to migrate to ws3 by right-clicking on the service and selecting “Move this service or application to another node”.

As the MySQL data is held in the shared storage (which has also been migrated to ws3), it is still available and can still be accessed through the existing mysql client which is connected to the VIP:

mysql> select * from simples; 
ERROR 2006 (HY000): MySQL server has gone away 
No connection. Trying to reconnect... 
Connection id: 1 
Current database: clusterdb 
+----+ 
| id | 
+----+ 
| 1 | 
+----+

Note the error shown above – the mysql client loses the connection to the MySQL service as part of the migration and so it automatically reconnects and complete the query. Any application using MySQL with Windows Failover Cluster should also expect to have to cope with these “glitches” in the connection.

Conclusion

More users develop and deploy and MySQL on Windows than any other single platform. Enhancements in MySQL 5.5 increased performance by over 5x compared to previous MySQL releases. With certification for Windows Server Failover Clustering, MySQL can now be deployed to support business critical workloads demanding high availability, enabling organizations to better meet demanding service levels while also reducing TCO and eliminating single vendor lock-in.
Please let me know how you get on by leaving comments on this post.

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)