With data volumes exploding, it is vital to be able to ingest and query data at high speed. For this reason, MySQL has implemented NoSQL interfaces directly to the InnoDB and MySQL Cluster (NDB) storage engines, which bypass the SQL layer completely. Without SQL parsing and optimization, Key-Value data can be written directly to MySQL tables up to 9x faster, while maintaining ACID guarantees.

In addition, users can continue to run complex queries with SQL across the same data set, providing real-time analytics to the business or anonymizing sensitive data before loading to big data platforms such as Hadoop, while still maintaining all of the advantages of their existing relational database infrastructure.

This and more is discussed in the latest Guide to MySQL and …

This is the first follow-up to my post on a simple test of KVS alternatives. To recap, I tested a simple single table schema in MySQL using the NDB and InnoDB storage engines. To have a Key-Value store to compare with, I did the same test in MongoDB. All tests were done of the same system, an 8-core AMD Linux box with 16 Gb RAM. The tests consisted of reading 1.000.000 rows, out of the total 105.000.000 in the table, distributed over 100 threads 10 times, a total of 10.000.000 rows read then. The test program I use makes sure that the same random ID's of the table are reused each time and the same are used for all servers.

Now, firstly, after some checking I realized that I had not fully cached the InnoDB engine, so it was doing a certain, small, amount of disk I/O still. I fixed this and the number now looks like …

In my last post, I wrote that I observed many similarities between TokuDB and MySQL Cluster. Many features that benefit TokuDB also benefit MySQL Cluster, and vice versa, with Hot Column Addition and Deletion (HCAD) being an example. Over my next few posts, I expand on some more of these possibly unexpected similarities.

Today I want to focus on optimizer support for clustering keys. Both MySQL Cluster and TokuDB can benefit from the MySQL optimizer supporting clustering keys. For TokuDB, the benefit is obvious, as TokuDB supports clustering keys. A non-negligible part of our effort is changing the …

Earlier this week, Andrew Morgan wrote a piece on running MySQL Cluster on Raspberry Pi. Since the term “Cluster” is hideously overloaded, I’ll note that we’re talking about the NDB cluster storage engine here, a very specific architecture originally acquired by MySQL AB from Ericsson (telco).

Raspberry Pi is a new single-board computer based on the ARM processor series (same stuff that powers most mobile phones these days), and it can run Linux without any fuss. Interfaces include Ethernet, USB, and HDMI video, and the cost is $25-50. I’m looking to use one for the front-end of a MythTV setup (digital video recorder and TV system), I can just strap the Raspberry Pi to the back of a TV or monitor to do its …

Back in 2006 we became aware of problems storing large numbers of rows in a single table in cluster. Johan Andersson and Yves Trudeau have each blogged about the problem and the common workaround here and here.  We've since then done some cleanup to provide a more proper "Table is full" error message when running into this problem.

As explained in the referenced blog posts, the problem is the result of a limitation on the size of the hash index of each partition. The hash index for each partition would allow at most ~49 million records. By default an ndbd or ndbmtd node have only 1 local query handler (LQH) block and thus 1 partition per node.  The ndbmtd nodes having MaxNoOfExecutionThreads = 4 or 8 cluster will have 2 or 4 LQH per node respectively. So, the …

In MySQL cluster each SQL statement is translated inside the NDB storage engine to NDB low level protocol that is sent to the data nodes. For the performance it is most interesting how many data is moved between data nodes and MySQL. To monitor this there are several NDB status variables that you can monitor. See this link for more documentation.

(There are also the NDBINFO tables that reflect cluster status. But these are only global values. The status variables also show session status. More about NDBINFO is here.)

To easily report the NDB status on an individual SQL statement, I wrote a little script that gives you the ndb status variables and automatically calculates the diffs before and after the statement in …

While doing some routine fiddling regarding some topic I've now forgotten, I discovered that ndb_restore was doing something quite surprising. It's been common wisdom for some time that one can use ndb_restore -m to restore metadata into a new cluster and automatically have your data re-partitioned across the data nodes in the destination cluster. In fact, this was the recommended procedure for adding nodes to a cluster before online add node came along. Since MySQL Cluster 7.0, though, ndb_restore hasn't behaved that way, though that change in behavior doesn't seem to be documented and most don't know that the change ever took place.

I'll go through some of the methods you can use to find information about the partitioning strategy for an NDB table, talk a bit about why ndb_restore stopped working the way most everyone expected (and still expect) it to, and discuss some possible …

Ralf Gebhardt of SkySQL.

Cluster: shared nothing architecture (no single point of failure), synchronous replication between nodes, ACID transactions, row level locking. In-memory storage (some data can be stored on disk, but indexes must be in-memory). Checkpointing to disk for durability. It supports two types of indexes – ordered T-trees, unique hash indexes. Online operations like adding node groups, software upgrades, table alterations. Quick standard architecture diagram displayed about MySQL Cluster.

Network partitioning protocol is designed to avoid a split brain scenario. Is there at least one node from each node group? If not then this part cannot continue – graceful shutdown. Are all nodes present from any node group? If so, then this is the only viable cluster – continue to operate. Ask the arbitrator – the arbitrator which parts will continue if no arbitrator …

NDB cluster is a very interesting solution in term of high availability since there are no single point of failure. In an environment like EC2, where a node can disappear almost without notice, one would think that it is a good fit.

It is indeed a good fit but reality is a bit trickier. The main issue we faced is that IPs are dynamic in EC2 so when an instance restarts, it gets a new IP. What the problem with a new IP? Just change the IP in the cluster config and perform an rolling restart! no? In fact this will not work, since the cluster is already in degraded mode, restarting the surviving node of the degraded node group (NoOfReplicas=2) will cause the NDB cluster to shutdown.

This can be solved by using host names instead of IPs in the config.ini file. What needs to be done is to define, in /etc/hosts, on entry per cluster member. The API nodes are not required. Here is an example:

$ more /etc/hosts
127.0.0.1 …

The announcements at the Oracle Collaborate and O'Reilly MySQL conferences mark an exciting milestone in the development of the MySQL Cluster database.

MySQL Cluster is already proven as a write-scalable, real-time transactional database, combining 99.999% availability with the low TCO of open source. With a distributed, multi-master architecture and no single point of failure, MySQL Cluster scales horizontally on commodity hardware to serve read and write intensive workloads.

With these enhancements announced in the Development Milestone Release, MySQL Cluster can be extended to serve a broader range of workloads.

Summary of Key Enhancements

The MySQL Cluster 7.2 Development Milestone Release and latest labs.mysql.com builds deliver enhancements based on input from the community and customers, including support for the memcached NoSQL API, faster JOIN performance and simplified administration:

• …