MySQL Cluster 7.5 is GA, best cluster release ever

I have been fairly quiet on my blog for some time. We've been very busy
developing new features for MySQL Cluster 7.5 and ensuring that the
quality is improved even further.

We're now very pleased to release a new version of MySQL Cluster.

MySQL Cluster 7.5 contains a number of new things that makes MySQL
Cluster even better.
1) You can declare a table as a READ_BACKUP table. This means that
the updating transactions will receive the commit acknowledge
a little bit later to ensure that we can always use any of the
replicas for reading. We will use the nearest replica for
committed reads, for locking reads we will still use the primary
replica to avoid deadlocks.

For applications that are mostly read-focused one can make it easier
to set this variable by setting the ndb-read-backup config variable
to 1 in the MySQL Server configuration. This means that all tables
created from this server will create tables with the READ_BACKUP
flag set to true.

2) You can declare a table as a FULLY_REPLICATED table. This means
that this table will get as many replica as there are nodes in the
cluster.

Fully replicated tables (global tables) provides a very important way
to scale your applications using MySQL Cluster. Many applications have
small tables that is mainly read, these tables can now fully replicated
and thus be read from any data node in the cluster.

Another potential use of fully replicated tables is to use MySQL Cluster
for read scalability. You can start out with a 2-node cluster where both
nodes are able to read the data. If you then need to scale to 4 nodes
you add 2 more nodes to the cluster, you create one more node group
for the 2 new nodes. Then you can reorganise the tables such that they
are fully replicated on all 4 nodes. You can continue in this manner
all the way until you have 48 nodes. All through this process any
MySQL Server can be used to both read and write the data in the cluster.

If the application is an SQL application it is a good idea to place
the MySQL Server is placed on the same computer as a data node.
So in this manner MySQL Cluster have a very easy manner of scaling up
your application for reads.

A third variant to use fully replicated tables is to place hot-spot rows
that are often read and seldom updated into fully replicated tables.

It is possible to set a config variable ndb-fully-replicated to 1 to
ensure that all tables are created as fully replicated. This should
obviously only be used for read scaling applications.

3) We have made it easier to vary the number of partitions in a table.
Normally we will have 2 partitions per LDM thread per node group. So
this means in a 4 node cluster with 2 node groups that have 4 LDMs per
node, there would be 16 partitions in a normal table.

Now it is possible to decide to have less partitions that are still
linked to the number of node groups in the cluster. So one can define
e.g. to have 1 partition per node group. This means that the command
ALTER TABLE REORGANIZE will add more partitions if node groups have
been added since the table last was altered. It will keep the number
to in this case 1 partition per node group. We support seven different
variants here.

The idea with this is to be able to decrease the number of partitions
for smaller tables while still using the automated partitioning
features of MySQL Cluster.

4) In earlier versions of MySQL Cluster we introduced the possibility to
use special send threads to do all the sending. In 7.5 the send threads
have been redesigned such that they now share the burden of sending with
all the other block threads in the NDB data nodes. The block threads will
assist when they are not very loaded. So the send thread assistance is
adaptive based on the CPU usage in the threads.

In order to develop this feature the NDB data nodes now have a very good
view on how much CPU usage each thread is using. This information is used
for internal algorithms, but it is also available as new NDBINFO tables.

5) Earlier versions of MySQL Cluster had a scaling issue with executing
extremely many scans in parallel. In MySQL Cluster 7.5 we have effectively
removed this limitation. Now it is possible to handle many millions of
scans per second for each table for each data node. So the main limitation
is the CPU available now for processing in general.

6) We have also continued working on improving our testing to ensure that
we always deliver better quality with new releases.

7) We have also worked on improving the scalability of the NDB API to
ensure that we deliver high performance even in situations with hundreds
or even thousands of concurrent threads working against the cluster.

8) Last but not least MySQL Cluster 7.5 is based on MySQL 5.7. So this means
that we get a lot of new optimizer features from the MySQL Server, we get
improved scalability of the MySQL Server parts and a lot more.

My personal favorite among those features is definitely the fully replicated
tables and read backup tables. This opens up a new category of application
optimisations in a scalable system.

My endeavour to build MySQL on Windows

I recently worked a lot on getting my local Windows box to compile Windows.
I got a lot of help from the below link that took me a few steps towards the
solution.
http://www.chriscalender.com/resolving-the-bison-exe-m4-invalid-argument-error-when-building-mysqlmariadbxtradb-on-windows/

I had to save away my bison installation, uninstall it and then also
go into a registry editor and remove the various links to the Start Menu referring to
some help for Bison that had spaces in the registry. Resolving this meant that I could
manually run bison on the files in the mysql tarball and get the desired result. But it
was still not enough, I still had the same error when running a full automatic compile.

So then I found the following link coming to my rescue.
http://venublog.com/2010/02/07/changing-mysql-parser-code-on-windows-build-breaks-due-to-bison/

With the aid of this link I realised that I had to make sure that the correct bison
executable was started. So in order to get this to happen I ensured that C:\GnuWin32
was the very first part in PATH.

With this final touch I was finally able to get my Windows builds of MySQL and
MySQL Cluster to work like a charm. I still don't fully understand why the
second step was necessary, but it worked which is good enough for me :)

Do I understand why Windows supports spaces in file names, no.

Benchmarking MySQL Cluster 7.4 on an Intel NUC

I have done a lot of benchmarks of MySQL Cluster on large servers which
is obviously very interesting. As mentioned in a previous blog I have
an Intel NUC machine now easily accessible. So I thought it would be
fun to make some benchmarks on this machine to see how fast MySQL Cluster
runs on small HW.

First a little description of the HW. The CPU is an Intel Core i5-4250
CPU. It runs at 1.3GHz and have a turbo frequency of 2.3 GHz. The CPU
has two cores and each core can run two threads simultaneously
(called hyperhtreading in Intel CPUs). It comes with the box containing
the motherboard and the CPU. Then you buy one or two DRAMs to it and an
SSD drive. I installed two DDR3L DRAMs which gives me a total of 16GByte
memory in the machine. In addition I installed an SSD drive of 256GByte.
The box fits nicely into the palm of your hand.

On top of this HW I installed Oracle Linux 7.

The configuration of the benchmark uses my laptop as benchmark driver
and uses the Intel NUC to run the data node. Since I only have one
Intel NUC available the benchmarks focused on a setup with only one
replica.

To start with I will focus this blog on performance of a table which
is a pure main memory where after a restart the table still exists,
but is empty. This kind of tables are useful to keep track of live
states where it doesn't make sense to recover the table since the
live state changes so often that the restored data is useless.

When recoverability on the table is active it adds lots of writes
of logs and checkpoints that adds an extra overhead of about
30-50% for inserts, deletes and updates. Reads are about as fast
with recoverability as without. I’ll dig into this type of benchmark
in a later blog.

So to the results of the benchmark run. Inserts were processed at a
rate of 400k per second, updates at 402k per second, deletes at
433k per second. Reads I got up to 589k per second. For both writes
and reads we're operating here at full CPU speed and also on full
network speed (Gigabit Ethernet).

This benchmark was executed with 1 primary key of 8 bytes and one
field with 100 bytes in it.

I was curious to see what the top rate of key lookups was with very
small records where the network speed wasn't a factor in the play.
So I changed the field from 100 bytes to 8 bytes and reran the
benchmark.

Now I got inserts at 476k per second, updates at 479k per second,
deletes at 474k per second and finally reads at 925k per second.
So close to a million reads per second on this tiny machine alone.

Given that I ran without logging to disk most of the configuration
was pretty straightforward of the data node. The most important
part is obviously how to make use of the 4 CPU threads in the 2
available cores.

As usual the LDM threads is where the actual database resides is
the thread that requires the most CPU, in this particular benchmark
also the TC threads require a lot of CPU since they are involved
in each and every key lookup. For e.g. applications with scans the
TC thread is a lot less involved. There is also a send thread
and a receive thread which is important to configure correctly.
These four thread types are the most important to configure when
running without recoverability. When adding recoverability the
configuration of the file io threads is also important. There is
also a main thread and replication thread and some connection
threads, but this have no impact on the performance of this
benchmark, but they were configured to be on the CPU with lowest
use.

The best results were achieved with only one thread per thread
type. The best config of those was to allow the LDM thread to
use its own CPU core and thus not use hyperthreading for this
thread to any great extent. Then one CPU thread in the other
core was used for the TC thread and the other CPU thread was
used for the send and the receive thread. This CPU thread also
handled the other threads.

When running with 2 LDM threads the number of threads increased
and the OS had to do more scheduling, most likely this config
could provide better results in a more bursty load, but for
sustained good performance it was better to use only a few
threads. The performance difference was though quite small
between using 1 LDM and 1 TC compared to using 2 LDMs and
2 TCs.

The benchmark application was executed on my laptop connected
to the Intel NUC machine through a gigabit ethernet switch.

Quick setup to run sysbench using MySQL Cluster 7.4

In developing MySQL Server and MySQL Cluster we use four types of testing.
We use unit testing, we use functional testing, we use system testing and
we use benchmark testing.

For the unit tests and functional tests we use the MTR test framework. This
is dealing with most issues of how to use SQL to query the MySQL database
engine.

For system testing the InnoDB team has a test framework that is used to ensure
that InnoDB will survive crashes of many sorts. In a similar fashion MySQL
Cluster uses the autotest framework that I described in an earlier blog:
Link to earlier blog.

For benchmark testing we have a few frameworks, I am going to describe the one
that I use on a regular basis. This framework can be downloaded from:
Link to download.

I started developing this framework in 2006. So it has been extended for quite
some time and it is a lot simpler to run it now than it ever was before.

In this blog I am going to describe  a very minimal setup needed to run a
sysbench benchmark against MySQL Cluster 7.4.

These benchmarks can be used to run the following types of tests:
1) Sysbench on a single server with InnoDB as storage engine
2) Sysbench on a single server with NDB as storage engine
3) Sysbench against InnoDB with InnoDB and sysbench on different servers
4) Sysbench against NDB with different programs on different servers
5) flexAsynch for MySQL Cluster testing on a set of servers
6) DBT2 on a single server with InnoDB as storage engine
7) DBT2 on a single server with NDB as storage engine
8) DBT2 using NDB as a storage engine against a lot of servers

Running against NDB as a storage engine means to use MySQL Cluster.
I mostly focus on 1) above for InnoDB testing. For MySQL Cluster I mostly
focus on 2) and 5). But occasionally I also test 8). 3), 4), 6) and
7) are perfectly possible setups and have been tested as well, but I don't
personally use those so much.

I will write a few blogs about how to setup a benchmark using these benchmark
scripts. I will first describe the basics of setting up a sysbench benchmark
against MySQL Cluster in this blog. Then a basic flexAsynch benchmark. Finally
a basic DBT2 benchmark on a single server.

After describing the basic configuration setup, I will describe in detail the
various parts to configure. There are numerous things to configure:

Mandatory items:
1) Which benchmark to run

2) Pointer to file locations for tarballs, data directory and install directory

3) Pointer to tarballs for MySQL, Sysbench and DBT2 benchmarks, also the
   benchmark scripts which are located in the DBT2 tarball.

4) Definition on which servers the various programs under test will be
   executing.

Optional parts of configuring SUT (System Under Test)
5) Configuration of MySQL Server. Started also in flexAsynch, but not really
   used in flexAsynch benchmark.

6) Configuration of InnoDB if running test against InnoDB.

7) Configuration of NDB storage engine and NDB nodes if running against NDB
   storage engine.

Optional parts of configuring the actual benchmark
8) Sysbench setup. Setting up how to run the actual sysbench benchmark, for how
long to run, what sysbench variant to use, how many tables and a lot of other
things can be configured about how sysbench is to run. This part is obviously
only interesting when running a sysbench test.

9) DBT2 setup. There are numerous ways to adapt the basic DBT2 benchmark
   although the benchmark transactions are always the same.

10) flexAsynch benchmark setup. Also in flexAsynch there are multiple ways to
    adapt the benchmark.

11) In connection with the flexAsynch benchmark one can also benchmark restarts
    in MySQL Cluster. First the scripts use flexAsynch to load the database and
    then various forms of restarts can be tested.

The full capabilities of those benchmark scripts are quite extensive, they are
the result of 9 years of incremental design where we have tried to extend the
use cases of these benchmarks to support as many environments as possible, but
also to automate as much as is possible.

Obviously to understand all of this in one go is a tad difficult. So we will
try to start by describing how to setup a very simple sysbench benchmark using
one machine running sysbench against the NDB storage engine using the latest
development version of MySQL Cluster 7.4.

This is a good first step to get acquainted with the benchmark scripts. I will
add more details in later blogs about running more advanced setups.

The scripts have been used mostly on Linux, also a fair deal of usage on
Solaris. When preparing this blog I ran it on Mac OS X. This requires some
changes that will soon arrive in the dbt2-0.37.50.7 updated version of the
benchmark scripts. So preferably use Linux when trying this out for the
first time.

The first step in running this test is to create a directory from where the
tests are located on disk. I usually use some home directory and create a
directory called bench there. So on Mac OS X that would /Users/mikael/bench.
On a Linux machine I would normally use /home/mikael/bench.

In this directory I create four directories. I create one directory called
tarballs, this is where I will place the DBT2, sysbench and MySQL tarballs.
I create one directory called ndb, this will be used as the data directory
and will be automatically filled by the scripts. Next I will create a
directory mysql, this directory is where I will place the binary installation.
Finally I create a directory called sysbench.

Next step is to download the DBT2 tarball and the sysbench tarball from the
website:
http://dev.mysql.com/downloads/benchmarks.html

Copy those tarballs to the tarballs directory created above.

Next step is to get the MySQL tarball that you want to test. It's ok to use
both source tarballs and binary tarballs. Personally I obviously mostly work
with source tarballs since I want to have full control over everything, but
it's ok to use a binary tarball as well.

Place this tarball also in the tarballs directory.

Now in order to be able to run the benchmark scripts it is necessary to
get the top-level script copied from the DBT2 tarball. To do this perform
the following commands:

cd /Users/mikael/bench #use your bench directory here
cd tarballs
tar xfz dbt2-0.37.50.6.tar.gz
cp dbt2-037.50.6/scripts/bench_prepare.sh ..
rm -rf dbt2-0.37.50.6

Now ls from /Users/mikael/bench should look like this:
Mikaels-MacBook-Pro:bench mikael$ ls
bench_prepare.sh ndb tarballs
mysql sysbench
Mikaels-MacBook-Pro:bench mikael$ pwd
/Users/mikael/bench

and ls from tarballs should look something like this:
Mikaels-MacBook-Pro:bench mikael$ cd tarballs
Mikaels-MacBook-Pro:tarballs mikael$ ls
dbt2-0.37.50.6.tar.gz mysql-cluster-gpl-7.4.5.tar.gz sysbench-0.4.12.7.tar.gz

Now it's time to create the config file.
This file is always located in an almost empty directory under the bench directory with only
one file in it. This file is always called autobench.conf.

Here is the content of the file in my simplistic test run.
#Define benchmark to run
BENCHMARK_TO_RUN="sysbench"
#Define file locations of datadir, installdir and tarballs
TARBALL_DIR="/Users/mikael/bench/tarballs"
REMOTE_BIN_INSTALL_DIR="/Users/mikael/bench/mysql"
DATA_DIR_BASE="/Users/mikael/bench/ndb"
#Define tarball versions
MYSQL_VERSION="mysql-cluster-gpl-7.4.5"
MYSQL_BASE="5.6"
SYSBENCH_VERSION="sysbench-0.4.12.7"
DBT2_VERSION="dbt2-0.37.50.7"
#Define engine to use
ENGINE="ndb"
#Define way to build
USE_BINARY_MYSQL_TARBALL="no"
#Define servers to use
SERVER_HOST="127.0.0.1"
NDB_MGMD_NODES="127.0.0.1"
NDBD_NODES="127.0.0.1"
#Configure MySQL Server
SERVER_PORT="3316"
USE_MALLOC_LIB="no"
#Configure NDB part
NDB_REPLICAS="1"
NDB_DATA_MEMORY="2G"
NDB_INDEX_MEMORY="200M"
NDB_MAX_NO_OF_EXECUTION_THREADS="8"
#Configure sysbench
RUN_RW="yes"
SYSBENCH_ROWS="100000"
THREAD_COUNTS_TO_RUN="16;32"
MAX_TIME="60"
NUM_TEST_RUNS="1"

At first we need to define the benchmark to run, this is sysbench in our case.
We need to point out the tarball directory, we need to point out the directory
where to place the installation (REMOTE_BIN_INSTALL_DIR), we also need to point
out the data directory.

Next step is to provide the names of the tarballs, this is done in
MYSQL_VERSION, SYSBENCH_VERSION and DBT2_VERSION. Since the scripts
have to adapt to different MySQL versions we also need to specify the base version
of MySQL which in the case of MySQL Cluster 7.4 is 5.6. The scripts currently
supports 5.1, 5.5 and 5.6. There is also some adaption needed for a new MySQL
version and so 5.7 is currently not supported but work is on the way for this.

Next we define if we want to use innodb or if we want to use ndb as the
storage engine in this test run.

We also define in the variable USE_BINARY_MYSQL_TARBALL whether we use a
source tarball or a binary tarball. In this case I use a source tarball.

We need to define the servers, there is in the case of sysbench always
one and only one MySQL Server, there can be one or many NDB management
servers. There can also be one or many NDB data nodes. But in this case
we wanted a simple setup on one machine so here there is one of each
sort and they are all located on 127.0.0.1 which is the local host.

We always configure the MySQL server port to ensure we don't have a
problem running the benchmark if another MySQL Server is running on
the host. By default we use libtcmalloc instead of libc malloc, we avoid
this default since there are better malloc libraries and this variable should
be set purposely and not by default in most cases. But setting it to not use
a special malloc library will definitely work, so we do that here as a first
step.

Since we run with NDB storage engine we need to set up a few configuration
variables. The NDB configuration can be quite extensive, but here we strive
for a simplistic one. We want only one replica which we set using NDB_REPLICAS.
We need to set the size of the NDB data memory and the NDB index memory.
Finally we set the number of execution threads to 8 which is pretty standard.
This should be sufficient for a simple benchmark run of sysbench.

The final section sets up a minimalistic set of parameters to run sysbench.
We run OLTP complex RW case. We insert 100.000 rows into the table and we
run two tests, one with 16 MySQL connections and one with 32 connections.
Each test will run for 1 minute and we will only run each test once.

Now that is a minimal configuration, it is possible to make it even smaller
but then it's hard to understand what the test actually does since one
needs to be an expert in the default settings which even I don't remember
for all variables.

Now this file autobench.conf is placed in
/Users/mikael/bench/sysbench/autobench.conf
in my case.

Now it is time to run the test case.

The test is executed by the following commands:
cd /Users/mikael/bench
./bench_prepare.sh --default-directory /Users/mikael/bench/sysbench

The test will run everything until completed. Actually I more or less always use
one more parameter --skip-cleanup. This means that I don't have to repeat the
building of the source code if I want to rerun the test. If I run it then a
second time I should run with both --skip-cleanup and --skip-build to ensure
that I skip rebuilding the source and skip cleaning away the built source code
at the end of the test run.

The test result is located in the file:
/Users/mikael/bench/sysbench/final_results.txt

The output from the sysbench program in this particular case can be found
in the file:
/Users/mikael/bench/sysbench/sysbench_results/oltp_complex_rw_1.res

Good luck in benchmarking.

My favourite tool in following the benchmarks is top. I usually lock the various
threads onto different CPUs to ensure that I can understand how the different
threads behaves. I haven't done this in this set up since this is just a basic
setup. But top is still a good tool to follow what is going on in the server.

One final note is that running this basic setup will require 20-30 GByte of
disk space and also during the test run there will be a fairly high load on
the disk. As many developers are not suitable for benchmarks of
database engines one way to adapt the benchmark for a developer
environment is to set the config parameter NDB_DISKLESS="yes".
This essentially makes the file system behave as if it was located on
/dev/null.