Recently, I was asked if it is possible to replicate an NDB cluster to a non-NDB MySQL database. So, I tried!

I created the following table on the MySQL master:

Create Table: CREATE TABLE `testrepl` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

and on the slave:

Create Table: CREATE TABLE `testrepl` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Of course, for obvious reasons, NDB only supports row based replication so I configured the master to use row based:

mysql> show global variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

Then I tried and go the following error:

Last_Error: Error 'Incorrect information in file:

  [Read more...]

In the previous 2 posts of this series, we basically talked about how to execute social networking type queries using SQL IN clause and how handle multiple columns IN clause. In this last post on the topic, I will introduce the notion of NDB API filters, although I don’t consider myself as an NDB API expert. Filters are to NDB API the equivalent WHERE clause in SQL. The point is that the filters can be nested and they are sent to the storage nodes only when the transaction is executed.

As an example, let’s consider the following table:

Create Table: CREATE TABLE `MultiColPK` (
  `region_id` int(11) NOT NULL DEFAULT '0',
  `application_id` int(11) NOT NULL DEFAULT '0',
  `first_name` varchar(30) NOT NULL DEFAULT '',
  `payload` varchar(30) DEFAULT NULL,
  PRIMARY KEY

  [Read more...]

I just learned that there will be a Webinar “What’s New in the Next Generation of MySQL Cluster?”, April 30th. From what I know, the webinar is supposed to be at a good technical level, it is not a marketing like introduction. If you are interested, just register at: http://www.mysql.com/news-and-events/web-seminars/display-320.html (http://www.mysql.com/news-and-events/web-seminars/display-320.html)

The MySQL UC 2009 is coming and it is time for my own little marketing. As Matt already annonced it a few months ago we (Matt and I) are doing a WaffleGrid presentation, Distributed InnoDB caching with Memcached, Tuesday at 2PM. I am also presenting at the MySQL Camp or unconference, NBD (MySQL Cluster) performance tuning and pitfalls, also Tuesday at 4:25PM.

If you have been following the development of the NDB Cluster storage engine lately, you are probably as excited as I am. NDB Cluster is becoming a kind of large database killer app. Look at all the nice features that have been added:

• Replication, if you know MySQL you know what I am talking about
• Distribution awareness, optimize query execution based on the distribution, a strong scaling factor
• Disk based data, the possibility of pushing some columns to disk
• Online add index, among the only online DDL I know of in MySQL
• Multi-threading, no more need to configure many data nodes per server
• Realtime, when query execution times matter

and I probably miss some. And now, with version 7 (renamed from 6.4) it is possible to extend a NDB cluster

  [Read more...]

Recently, I talked about how to optimize social networking type queries for the NDB storage engine using IN clause statements. In clauses are great but they have one fundamental limitation, they work only on one column (Actually, this is not true, I discovered, thanks to Roland’s comment, that MySQL supports multiple columns IN clause). What if the primary key is a composite of let’s say “region_id”, “application_id” and “user_id”? Recently, while on site with a client, Brian Morin showed me a very clever way of dealing these type of primary keys in an IN clause. The main problem is that you cannot return a binary or varbinary from a function. So the idea was to used the return values of a stored proc. First we need to compose the varbinary from the actual values with this stored proc:
.

delimiter

  [Read more...]

People often wants to use the MySQL memory engine to store web sessions or other similar volatile data.
There are good reasons for that, here are the main ones:

• Data is volatile, it is not the end of the world if it is lost
• Elements are accessed by primary key so hash index are good
• Sessions tables are accessed heavily (reads/writes), using Memory tables save disk IO

Unfortunately, the Memory engine also has some limitations that can prevent its use on a large scale:

• Bound by the memory of one server
• Variable length data types like varchar are expanded
• Bound to the CPU processing of one server
• The Memory engine only supports table level locking, limiting concurrency

Those limitations can be hit fairly rapidly, especially if the session payload data is large. What is less known is that NDB Cluster can creates

  [Read more...]

NDB Cluster is the only integrated sharding framework that I know of (educate me if I am wrong) but it is known to have issues with large joins. These days, large databases that would benefit from a sharding framework are often for social networking type applications that requires large joins.

Actually it is not NDB that is the root cause of the joins problem, it is the way MySQL executes joins. Instead of asking the cluster for a large number of rows for the secondary table it joins to, the current version of MySQL does ask one row at a time. NDB cluster answers those queries very rapidly but, the time to hop over the network kills performance. The MySQL-6.0 branch will implement the Batch Key Access (BKA) algorithm which will solve that issue and might create database application killer with NDB cluster.

Although right now BKA is not available, there are ways to execute those queries in an

  [Read more...]

NDB cluster is a strange beast. Usually, performance wise, it is a good idea to limit the number of threads inside MySQL, that’s why there are parameters like thread_concurrency and innodb_thread_concurrency. MySQL is known to show mutexes contention with a number of active threads greater than a hundred (actually even less) but with NDB the situation is quite different since threads have to wait for the network latency. With NDB Cluster, be prepared to use unusually high numbers of connections and be prepared to crank up the number of active workers if you want to push NDB to its limit. The following figure shows some tests I made recently during one my engagements. As one can see, the number of active connections has an important impact on the overall throughput and it peaks at approximately 800 connections!!!

  [Read more...]

Tuning queries for MySQL NDB cluster is way trickier than tuning for any other storage engines. This is especially true for highly normalized schema. Look at the following query trying to retrieve all male account from a given city. Since a city is not unique, we also need to specify the state.

SELECT a.First_name, a.Last_name
FROM Account a INNER JOIN Cities c ON a.City_id = c.Id
  INNER JOIN States s ON c.State_id = s.Id
  INNER JOIN Gender g ON a.Gender_id = g.Id
WHERE c.Name = ‘Columbia’ AND s.Name = ‘South Caroline’ and g.Name = ‘Male’;

If you look at how the query will be executed with NDB, you realize that many hops over the network will be needed, at least one per table. If you push the cluster a lot, these extra hops will limit the performance. Since tables like Cities, States and especially Gender are nearly

  [Read more...]

With the NDB realtime options, you can choose on which CPU the execution thread and the maintenance will be running. The point is, which CPUs to use.

The output of “cat /proc/interrupts” will help you determine which CPU to use. Here is an example of a dual quad-cores box:

$ cat /proc/interrupts
           CPU0       CPU1       CPU2       CPU3       CPU4       CPU5       CPU6       CPU7


  [Read more...]

I was recently involved in a project where the main requirement was the smallest possible latency for queries. The queries were simple insert and update statements, the update being by primary key. The cluster was using regular Gigabits Ethernet and formed by 3 servers with 8 cores each, one Management node and 2 data nodes. Since throughput was not a concern, a design decision has been made to locate the MySQL daemons on the same servers as the data nodes, in order to save network hops. MySQL chooses the closest NDB data node as its transaction coordinators and the closest on will be reachable on localhost.

Apart from the architectural decision to host MySQL and the NDB nodes on the same boxes, we decided to benchmark the new real time options of NDB. The options are the following:

RealtimeScheduler: enable the real time schedule, was set to 1

LockExecuteThreadToCPU: assign

  [Read more...]

Have you ever been upset by the Linux tendancy to swap… Especially when trying to allocate a large InnoDB buffer pool.. Look at the following output:

yves@yves-laptop:~$ free
total used free shared buffers cached
Mem: 2041888 1991096 50792 0 52 954592
-/+ buffers/cache: 1036452 1005436
Swap: 975200 1308 973892

There is still 50792 + 52 + 954592 = 1005436 of free memory and Linux starts to swap!!! The reason is hidden here:

yves@yves-laptop:~$ cat /proc/sys/vm/swappiness
60

The swappiness controls the Linux to swap for the File cache. For a file server or a web server or even MySQL with MyISAM tables, the file cache is interesting but for InnoDB or NDB Cluster it is

  [Read more...]

The e1000 Nic is very common and can be found in many servers.  During my last NBD cluster engagement, on a set of 5 Dell 1950 servers equipped with a quad e1000 Nic, we were trying to tune cluster performance and one of the thing that was disapointing was that the system time, to processed interrupts from the Nics, was always low.  Since neither the ndbd processes or the mysqld processes were using a significant amount of CPU, we were wondering where the bottleneck was.

Then, Michel Donais, from the client, read the Linux driver documentation and bingo!!! Those e1000 Nics throttle interrupts by default at 8000/s.  8000… it is a big limiting factor….   design to prevent DOS attacks.  We removed the throttling and… reach more than 30,000 interrupt/s and guess what, the number of transaction per second scaled with it along with the ndbd and mysqld cpu usage.  Interesting isn’t it?

  [Read more...]

Just before Christmas, I was working with a client that needed to insert more than a billion rows into a NDB table. The cluster was big,
14 nodes, and after a few hundred millions rows, we got the following error:

2008-12-09 18:28:52 [ndbd] INFO     -- dbacc/DbaccMain.cpp
2008-12-09 18:28:52 [ndbd] INFO     -- DBACC (Line: 5274) 0x0000000e
2008-12-09 18:28:52 [ndbd] INFO     -- Error handler shutting down system
2008-12-09 18:28:53 [ndbd] INFO     -- Error handler shutdown completed - exiting
2008-12-09 18:28:56 [ndbd] ALERT    -- Node 10: Forced node shutdown completed. Caused by error 2304: 'Array index out of range(Internal error, programming error or missing error message,

which looks like a bug in the NDB kernel.  In fact, it is not really a bug.  This error is the equivalent of a “table too large” error

  [Read more...]

I was recently involved with a client that has to deal with a very large dataset  in MySQL NDB cluster.  The schema was very simple, a few (one or two) key columns and a large varbinary column.   The cluster was large with 14 data nodes having each close to 60 GB devoted to DataMemory (DM) for a total combine size of

The main problem we had with the cluster during this engagement was the lack of disk space and the main cause of this, was the Redo log files.  The cluster has been configured according to Johan best practices of 6xDM (http://johanandersson.blogspot.com/2008/04/great-configini.htm) forFragmentLogFileSize and NoOfFragmentLogFiles and, adding the 2 Local CheckPoint (LCP), this mean 8xDM or 480 GB, hard to fit on a pair or 146 GB drives.  The purpose for 6xDM is to make sure write transaction are never rejected by the cluster because of a lack of redo log space.  The redo log space basically

  [Read more...]