Following requests received during earlier MySQL Cluster webinars, a new (and as always, free) webinar has been scheduled which focuses on MySQL Cluster Replication. The webinar is scheduled for Thursday 10 September and you can register at http://www.mysql.com/news-and-events/web-seminars/display-415.html

I’ll be on-line during the webinar, answering questions.

Details….

MySQL Cluster: Geographic Replication Deep-Dive

Thursday, September 10, 2009

MySQL Cluster has been deployed into some of the most demanding web, telecoms and enterprise / government workloads, supporting 99.999% availability with real time performance and linear write scalability.

Tune into this webinar where you can hear from the Director of MySQL Server Engineering provide a detailed “deep dive” …

Mat Keep and I will be presenting a Webinar on Wednesday, August 12, 2009 describing the new capabilities of MySQL Cluster 7.0. The time is designed to be Asia/Australasia-friendly but of course it’s open to everyone.

Register for the MySQL Cluster 7 Webinar here.

By attending this webinar, you will learn more about the next generation of MySQL Cluster, and how it enables you to deliver 99.999% database availability, with real time performance and linear scalability, while slashing TCO.

Boasting a range of breakthrough capabilities, MySQL Cluster 7.0 will enable you to stay ahead of your most demanding, mission-critical application requirements. Enhanced scalability delivers higher database performance with fewer nodes to simplify deployment and administration. Expanded platform support and interoperability delivers more …

Restarts are required for certain, infrequent maintenance activities. Note that there is no loss of service while a single node restarts.

When a data node restarts, it first attempts to load the data into memory from the local log files and then it will catch up with any subsequent changes by retrieveing them from the surviving node(s) in its node group.

 Based on this, you would expect the time taken to restart a data node to be influenced by:

1. The amount of data that was stored on the data node before the restart
2. Rate of updates being made to the data during the restart
3. Network performance (assuming the data is being updated during recovery)

The times will also be influenced bycertain configuration parameters, performance of the host machine and whether the multi-threaded data node (ndbmtd) is being used.

To provide some insight into how these …

Default partitioning

By default, Cluster will partition based on primary key

When adding rows to a table that’s using MySQL Cluster as the storage engine, each row is assigned to a partition where that partition is mastered by a particular data node in the Cluster. The best performance comes when all of the data required to satisfy a transaction is held within a single partition so that it can be satisfied within  a single data node rather than being bounced back and forth between multiple nodes where  extra latency will be introduced.

By default, Cluster partions the data by hashing the primary key. This is not always optimal.

For example, if we have 2 tables, the first using a single-column primary key (sub_id) and the second using a composite key (sub_id, service_name)…

mysql> …

As described in “Batching – improving MySQL Cluster performance when using the NDB API“, reducing the number of times the application node has to access the data nodes can greatly improve performance and reduce latency. That article focussed on setting up multiple operatations (as part of a single transaction) and then executing them as a single batch sent by the NDB API library to the data nodes.

The purpose of this entry is to show how a single NDB API operation can access multiple rows from a table with a single index lookup. It goes on to explain the signifficance of this both now and in the future (much faster joins using SQL for MySQL Cluster tables).

There are several operation types to cover table scans and index lookups (refer to the “ …

As many people are aware, the best performance can be achieved from MySQL Cluster by using the native (C++) NDB API (rather than using SQL via a MySQL Server). What’s less well known is that you can improve the performance of your NDB-API enabled application even further by ‘batching’. This article attempts to explain why batching helps and how to do it.

What is batching and why does it help?

NDB API accessing data from the Cluster without batching

Batching involves sending multiple operations from the application to the Cluster in one group rather than individually; the Cluster then processes these operations and sends back the results. Without batching, each of these operations incurs the latency of crossing the network as well as consuming CPU time on both the application and data node hosts.

By batching …

It isn’t necessarily immediately obvious how to set up a Cluster on LINUX; this post attempts to show how to get a simple Cluster up and running. For simplicity, all of the nodes will run on a single host – a subsequent post will take the subsequent steps of moving some of them to a second host. As with my Windows post the Cluster will contain the following nodes:

• 1 Management node (ndb_mgmd)
• 2 Data nodes (ndbd)
• 3 MySQL Server (API) nodes (mysqld)

Downloading and installing

Browse to the MySQL Cluster LINUX download page at mysql.com and download the correct version (32 or 64 bit) and store it in the desired directory (in my case, /home/billy/mysql) and then extract and …

One of the better kept secrets about MySQL Cluster appears to be the flexibility available when setting up replication. Rather than being constrained to implementing a single replication scheme, you can mix and match approaches.

Just about every Cluster deployment will use synchronous replication between the data nodes within a node group to implement High Availability (HA) by making sure that at the point a transaction is committed, the new data is stored in at least 2 physical hosts. Given that MySQL Cluster is usually used to store the data in main memory rather than on disk, this is pretty much mandatory (note that the data changes are still written to disk but that’s done asynchronously to avoid slowing down the database).

MySQL Cluster Replication

MySQL asynchronous replication is often used for …

There is another live webinar on MySQL Cluster 7.0 scheduled for 27th May 09:00 (Western European time) / 08:00 (UK time) / 11:00 (Eastern European time) and you can register here.

Alternatively, if you can’t wait that long then you can watch/listen to the play-back of the original webinar.

Note that you may need to install the WebEx Player (Windows and Mac) - unfortunately, there doesn’t seem to be a simple, supported solution for LINUX users but you can always download the slides (PDF format) from the same link or else wait until 27th May and view it live.

What’s New in the Next Generation of MySQL Cluster?
Thursday, April 30, 2009

Following on from the announcement  at the User Conference last week that MySQL Cluster 7.0 is now generally available, Mat Keep and I will present the first public webcast this Thursday. If you’d like to attend but the time isn’t convenient then you can either watch/listen to the recording after the event or wait for the next one to be announced (will be aimed at different time-zones).

Details…..

What’s New in the Next Generation of MySQL Cluster?
Thursday, April 30, 2009

Whether you’re racing to introduce a new service, or trying to manage an avalanche of data in real time, your database has to be scalable, fast and highly available to meet ever-changing market conditions and stringent SLAs.

By attending this webinar, you will learn more about MySQL Cluster 7, and how it enables you to deliver 99.999% database availability, with real time performance and linear scalability, while slashing …