Tuesday, March 03, 2015

MySQL Cluster on my "Windows computer"

It's been some time since I wrote my last blog. As usual this means that I have
been busy developing new things. Most of my blogs are about describing new
developments that happened in the MySQL Server, MySQL Cluster, MySQL
partitioning and other areas I have been busy developing in. For the last year I have
been quite busy in working with MySQL Cluster 7.4, the newest cluster release. As
usual we have been able to add some performance improvements. But for
MySQL Cluster 7.4 the goal has also been to improve quality. There are a number
of ways that one can improve quality. One can improve quality of a cluster by making
it faster to restart as problems appear. One can also improve it by improving code
quality. We have done both of those things.

In order to improve my own possibilities to test my new developments I decided to
invest in a "Windows computer". This means a computer that I house in my window in
my living room :)

There is some new very interesting computers that you can buy from Intel today that
works perfectly as test platform for a distributed database like MySQL Cluster. It
is called Intel NUC. It is actually a computer that you need to assemble on your own,
it comes equipped with a dual core Intel i5-4250U CPU that runs at 1.3GHz and can
run up to Turbo frequency of 2.3 GHz (the current generation has been upgraded to
Intel i5-5250U processors). To this computer you can buy up to 16 GByte of memory
and you can add a 2.5" SSD drive and it's even possible to also have a M.2 SSD card
in addition to the 2.5" SSD drive. Personally I thought it was enough to have one SSD
drive of 250GB.

So this computer is amazingly small, but still amazingly capable. I have it placed in
the window in front of my desk behind our TV set. It is so quiet that I can't even
hear it when all the rest of the equipment is shut off.

Still it is capable enough to run our daily test suite with a 4 data node setup. This
test suite runs more than 400 different test cases where we test node restarts, system
restarts, index scans, backups, pk lookups and so forth. One such test run takes
13 hours, so it is nice to be able to have this running on such a small box that can
run in the background without me having to interfere at all and without it making any

So it's amazing how scalable the MySQL Cluster SW is, I can run test suites with a
4 data node setup on a box that fits in the palm of my hand. At the same I can run
benchmarks using 100 2-socket servers that requires probably 4-5 racks of computers
and that achieves 200M reads per second.

Here is a little description of how you can setup a similar box to be running
daily test runs for MySQL Cluster if ever you decide that you want to try to
develop a new feature for MySQL Cluster.

1) At first the test suite requires a ndb_cpcd process to be running. This process
takes care of starting and stopping the MySQL Cluster processes.

To do this do the following:
1) Create a new directory, I called mine /home/mikael/cpcd
In this directory create a minor script that starts the ndb_cpcd.
It contains the following command:
ndb_cpcd --work-dir=/home/mikael/cpcd --logfile=/home/mikael/cpcd/cpcd.log

For it to run you need to compile MySQL Cluster and copy ndb_cpcd to e.g.
/usr/local/bin. This binary doesn't really change very often, so you can
have one compiled and need not change it. I call this script start_ndb_cpcd.sh.

Then you start the ndb_cpcd in one window using

2) In order to run the test suite I created a directory I called

This is where I run the test suite from. For this I need to two files.
The first is the autotest-boot.sh which is found in the MySQL Cluster source
in the place storage/ndb/test/run-test/autotest-boot.sh.
In addition I create here the configuration file used by this autotest-boot.sh
script, it's called autotest.conf.

In my case this file contains:

hosts="mikael1 mikael1 mikael1 mikael1 mikael1 mikael1"

install_dir is the place where the build of the MySQL Cluster source is installed.

build_dir is the place where the build of the MySQL Cluster is placed.

git_local_repo is a git branch of MySQL Cluster 7.4.

git_remote_repo is a git repo of the entire MySQL source.

base_dir is the directory where the results of the test run are placed in a
compressed tarball.

target is the computer and OS, in my case a x86_64 running Linux.

hosts is the hosts I use, there should be 6 hosts here, in my case they are all the same
host which is called mikael1.

Finally I have report set to nothing

and in order to avoid having to build the Java API and NodeJS APIs I set the
Finally I set the MAKEFLAGS to get a good parallelism in building MySQL Cluster.

In order to run I need to have git installed, CMake as well and possibly some
more things. If one uses an older git version (like I do), then one needs to
change the git command in autotest-boot.sh a little bit.

Finally one needs to add a new file to the MySQL branch from where you run,
/home/mikael/mysql_clones/test_74 in my case. This file is called in my
case /home/mikael/mysql_clones/test_74/storage/ndb/test/run-test/conf-mikael1.cnf.

The autotest-boot.sh creates the config file of the cluster from this file.
In my case this file contains:

# Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# GNU General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA

basedir = CHOOSE_dir
baseport = 14000
#clusters = .4node
clusters = .2node
mt = 0




ndb_mgmd = CHOOSE_host1
#ndbd = CHOOSE_host2,CHOOSE_host3,CHOOSE_host4,CHOOSE_host5
ndbd = CHOOSE_host2,CHOOSE_host3
ndbapi= CHOOSE_host1,CHOOSE_host1,CHOOSE_host1
#mysqld = CHOOSE_host1,CHOOSE_host6
mysqld = CHOOSE_host1,CHOOSE_host4

NoOfReplicas = 2
IndexMemory = 100M
DataMemory = 500M
BackupMemory = 64M
MaxNoOfConcurrentScans = 100
MaxNoOfSavedMessages= 5
NoOfFragmentLogFiles = 4
FragmentLogFileSize = 32M




In the current setup it uses 2 data nodes, but it can also run easily with 4 data
nodes by simply changing a few lines above.
mt = 0 means that I always run with the ndbd process which is the smallest manner to run
MySQL Cluster data nodes where all blocks runs within one thread. It is possible to run
them in up to more than 50 threads, but in this machine it makes more sense to use ndbd.

The name of the file should be conf-HOST.cnf where you replace HOST with the hostname of
your test computer.

Finally in my case I also changed one line in


#    rsync -a --exclude='BACKUP' --exclude='ndb_*_fs' "$1" .
    rsync -a --exclude='BACKUP' "$1" .

The idea is that I should also get the file system of the cluster reported
as part of the result tarball. This increases the size of the result tarball
significantly, but if one is looking for bugs that happen in writing of REDO
logs, UNDO logs, checkpoints and so forth, then this is required to be able
to find the problem.

Finally we have now come to the point where we need to execute the
actual test run, we place ourselves in the autotest_run directory
and execute the command:

./autotest-boot.sh --clone=mysql-5.6-cluster-7.4 daily-basic

During the test execution one can look into autotest_install, there is
a directory there starting with run that contains the current test running
and if a test fails for some reason it will create a result.number directory
there where you get the log information from the failure, successful test
cases doesn't get any logs produced. The file log.txt contains the current
test being executed.

Finally the test executed for daily-basic are defined in the file:

So by adding or removing tests from this file you can add your own test cases,
most of the tests are defined in the
/home/mikael/mysql_clones/test_74/storage/ndb/test/ndbapi directory.

Good luck in trying out this test environment. Remember that any changes to files
in the test_74 directory also requires to be commited in git since the test_74
directory is cloned off using git commands.

No comments: