Restart phases of a node restart in MySQL Cluster

In MySQL Cluster 7.4 we have worked on improving the speed of restarts.
In addition we have made it easier to track how fast the restarts are
proceeding and to see which phases of the restart take a long time.

For this purpose we have added a new Ndbinfo table in MySQL Cluster 7.4.
I will try to explain the restart phases in a series of blogs and how they map
to the columns in this Ndbinfo table.

First some short intro to concepts in MySQL Cluster. LCP stands for local
checkpoints and this is where the main memory content of tables are
written to disk at regular intervals. There is also a GCP, global checkpoint,
this is a protocol to synchronise the REDO log writes and thus about
durability of transactions. LDM stands for Local Data Manager and is
the thread that runs all the code that maintains the actual data in MySQL
Cluster. This includes row storage, REDO logging, hash index, T-tree
index (ordered index) and various internal triggering functions to support
various high-level functionality such as unique indexes, foreign keys and
so forth.

In a node restart there is a number of nodes involved, at a minimum there
are two nodes involved and there could be more than two nodes involved as
well. At least one node is a starting node and one live node is always a
master node. There can also be other live nodes that have already started and
that participate in some phases of the restart but not in all phases.
There can also be other starting nodes that have to be synchronised to
some extent with the starting node.

A node restart always begins with a node failing. This could be a controlled
failure as is used in e.g. a rolling upgrade of the MySQL Cluster software.
In this case there are usually multiple nodes that are set to fail at the
same time, often one fails half the nodes as part of a software upgrade.

So the first phase of the node restart phases is to complete the node failure.
This involves handling of in progress transactions involving the failed node.
It involves taking over as a master if the failed node was
a master node. It involves handling a number of protocols in the data nodes
where other nodes are waiting for the failed node. In 7.3 and older versions
this can at times be a fairly lengthy phase, in particular in taking over the
master role of the local checkpoint protocol. With the restart improvements
in MySQL Cluster 7.4 it should be a rare event that this phase takes a long
time.

The next node restart phase is that the crashed node starts up again. The first
thing the starting node does is to attempt to allocate a node id. The allocation
of a node id will have to wait for the node failure phase to complete first. So
this means that this phase can take some time, but normally it proceeds very
quickly.

When the new node has received a node id the next step is to be included in
the heartbeat protocol. One node can enter into the heartbeat protocol per
3 seconds. So if many nodes start up at the same time a node can be held
waiting here until the cluster accepts us into the cluster.

Once the node have been included into the heartbeat protocol which is the
lowest node handling layer in MySQL Cluster, we will ask for permission
to get included in the cluster at the next level of maintenance. The node
could be blocked to be included at this level for example if other nodes are
still removing our node from local checkpoints after we did an initial node
restart, the node could also be blocked by include node protocols still going
on. So the node could be held in this layer at times, but it's a rare event and
should not happen very often.

Also only one node at a time can be in this phase at a time, the next node will
be allowed to proceed after the node have completed this phase and the next
phase where we get a copy of the meta data in the cluster.

After being accepted into the cluster the node have to wait until we have paused
the LCP protocol. This is a new feature of MySQL Cluster 7.4 that have been
added to avoid having to wait in this state until a local checkpoint is completed
before we can proceed. In 7.4 we can pause the checkpoint execution (for the
most part this will not block checkpoint execution since a major part of the
checkpoint is off-loaded to the LDM threads that can execute up to 64 local
checkpoint fragments without getting any new requests from the master node).

As soon as the node managed to pause the local checkpoint execution we can
start to receive the meta data from the master node. The copy phase of the
meta data could take some time if there are very many tables in the cluster.
Normally it is a very quick action.

Now that the node have copied the meta data to our node, the next node can
proceed with that phase. Our node will continue by being included into the global
checkpoint protocol and a number of other node handling protocols. This is
normally a quick phase.

Now that the node have been included in the protocols the master node will
tell us what fragments to restore from disk. This phase is usually also fast.

Now the node have reached the first phase that is normal to take a bit longer
time. This phase is where the node restores all requested fragments from a
checkpoint stored on disk. All LDM threads work in parallel on restoring
all requested fragments here. So the node should be able to restore at a speed
of at least a few million records per second here.

After completing restoring the fragment checkpoints the next phase is to execute
the UNDO logs against all disk data fragments. If there are no disk data tables
in the cluster then this phase will be very quick, otherwise it can take some
time.

After restoring the fragments and applying the disk data UNDO log the node is
now ready to apply the logical REDO log. All LDM threads will execute the
REDO logs in parallel. There are 4 phases of REDO log execution, but in reality
only the first phase is used. The time of this phase depends on how fast the
checkpoints were generated before the crash. The faster we execute the
checkpoints the smaller amount of REDO log is needed to execute as part of
a node restart.

Also here the node should be able to process some millions of log records per
second since we are executing this in parallel in the LDM threads.

Now that we have restored a global checkpoint for all fragments the node is
ready to rebuild all ordered indexes. The fragments are check pointed with
only its data, indexes are not check pointed. The indexes are rebuilt as part
of a node restart. The primary key hash index is rebuilt as part of restore of
the fragments and execution of the REDO log. The ordered index is rebuilt
in a separate phase after completing the applying of the REDO log.

This is also a phase that can take some time. All LDM threads execute in
parallel in this phase. The more records and the more ordered indexes we
have the longer this phase takes to execute.

Now the node has managed to restore an old but consistent version of the
fragments we were asked to restore.

The next phase is to synchronise the old data with the live data in the
nodes that are up and running and contains the latest version of each
record.

As a new feature of MySQL Cluster 7.4 this phase can be parallelised
among the LDM threads. So the node can synchronise with data in the
live nodes in many threads in parallel.

This phase can be delayed in cases with many tables and many concurrent
node restarts since we need a lock on a fragment before copying its
meta data to new starting nodes which is also needed before starting
the copying phase to synchronise with the live nodes.

At this point in time the node have an up-to-date version of all restored
fragments in the node. The node do however not have a durable version
yet. For the fragments to be restorable on our starting node we need the
node to participate in a full local checkpoint.

Waiting for local checkpoints in various manners is the most common
activity in delaying restarts in 7.3 and older versions of MySQL Cluster.
In MySQL Cluster 7.4 we have managed to decrease the amount of
waiting for local checkpoints, in addition we have also been able to speed
up the local checkpointing in general and more specifically when node
restarts are executing. Those two things plus the parallelisation of the
synchronisation phase are the main ways of how we managed
to decrease restart times by a factor of 5.5X in MySQL Cluster 7.4.

In order to ensure that as many nodes as possible get started together
after the waiting for the same local checkpoint execution, we have the
ability to block the start of a local checkpoint if we have nodes that are
close to reaching this wait state. Given that we have statistics on how long
time the restarts takes, we can predict if it is useful to wait for
another node to reach this state before we start a new local checkpoint.

Finally when we have waited for a complete LCP it is time for the final
phase which is to wait for subscription handover to occur. This relates
to the MySQL replication support in MySQL Cluster where we have to
contact all MySQL Servers in the cluster informing them of our new
node that can also generate replication events.

After this final phase the restart is completed.

As an example we measured a node restart of around 26 GByte of data
took around 3-4 minutes in MySQL Cluster 7.4.

The speed of node restarts is highly dependent on local checkpoint speed,
we have introduced a number of new configuration parameters to control
this speed, we have also introduced a new Ndbinfo table also to track the
checkpoint speed. We will describe this in a separate blog entry.

Each of the phases described above is represented as a column in the
ndbinfo.restart_info table. We report the time that we spend in each
phase measured in seconds. There is a row for each restart that has
been observed. This means that not all nodes are present in this table.
After an initial start or after a cluster restart the table is even empty.

We will also describe the additional log printouts that we have added to
the node logs describing what is going in the node during a node restart
in a separate blog entry.

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
noise.

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
./start_ndb_cpcd.sh

2) In order to run the test suite I created a directory I called
/home/mikael/mysql_clones/autotest_run

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:

install_dir="/home/mikael/mysql_clones/autotest_install"
build_dir="/home/mikael/mysql_clones/autotest_build"
git_local_repo="/home/mikael/mysql_clones/test_74"
git_remote_repo="/home/mikael/mysql_git"
base_dir="/home/mikael/mysql_clones/autotest_results"
target="x86_64_Linux"
hosts="mikael1 mikael1 mikael1 mikael1 mikael1 mikael1"
report=
WITH_NDB_JAVA_DEFAULT=0
WITH_NDB_NODEJS=0
export WITH_NDB_JAVA_DEFAULT WITH_NDB_NODEJS
MAKEFLAGS="-j7"
export MAKEFLAGS

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
WITH_NDB_JAVA_DEFAULT=0 and WITH_NDB_NODEJS=0.
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
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# 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

[atrt]
basedir = CHOOSE_dir
baseport = 14000
#clusters = .4node
clusters = .2node
fix-nodeid=1
mt = 0

[ndb_mgmd]

[mysqld]
innodb
skip-grant-tables
socket=mysql.sock
default-storage-engine=myisam

[client]
protocol=tcp

[cluster_config.2node]
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
ODirect=1
MaxNoOfAttributes=2000
Checksum=1

SharedGlobalMemory=256M
DiskPageBufferMemory=256M
#FileSystemPath=/home/mikael/autotest
#FileSystemPathDataFiles=/home/mikael/autotest
#FileSystemPathUndoFiles=/home/mikael/autotest
InitialLogfileGroup=undo_buffer_size=64M;undofile01.dat:256M;undofile02.dat:256M
InitialTablespace=datafile01.dat:256M;datafile02.dat:256M
TimeBetweenWatchDogCheckInitial=60000

[cluster_config.ndbd.1.2node]
TwoPassInitialNodeRestartCopy=1

[cluster_config.ndbd.3.2node]
TwoPassInitialNodeRestartCopy=1

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
storage/ndb/test/run-test/atrt-gather-results.sh

as

#    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:
/home/mikael/mysql_clones/test_74/storage/ndb/test/run-test/daily-basic-tests.txt

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.

200M reads per second in MySQL Cluster 7.4

By courtesy of Intel we had access to a very large cluster of Intel servers for a few
weeks. We took the opportunity to see the improvements of the Intel
servers in the new Haswell implementation on the Intel Xeon chips. We also took
the opportunity to see how far we can now scale flexAsynch, the NoSQL benchmark
we've developed for testing MySQL Cluster.

Last time we tested we were using MySQL Cluster 7.2 and the main bottleneck
then was that the API nodes could not push through more than around 300k reads
per second and we have a limit of up to 255 nodes in total. This meant that we
were able to reach a bit more than 70M reads per second using MySQL Cluster 7.2.

In MySQL Cluster 7.3 we improved the handling of thread contention in the NDB API
which means that we are now able to process much more traffic per API node.
In MySQL Cluster 7.4 we also improved the execution in the NDB API receive
processing, and we also improved the handling of scans and PK lookups in the data
nodes. This meant that now each API node can process more than
1M reads per second. This is very good throughput given that each read contains
about 150 bytes. So this means that each socket can handle more than 1Gb/second.

To describe what we achieved we'll first describe the HW involved.
The machines had 2 sockets with Intel E5-2697 v3 processors. These are
Haswell-based Intel Xeon that have 14 cores and 28 CPU threads per CPU socket.
Thus a total of 28 cores and 56 CPU threads in each server operating at 2.6GHz base
frequency and a turbo frequency of 3.6GHz. The machines were equipped with
64 GByte of memory each. They had an Infiniband connection and
a gigabit ethernet port for communication.

The communication to the outside was actually limited by the Infiniband interrupt
handling. The Infiniband interrupt handling was set up to be latency-optimised
which results in higher interrupt rates. We did however manage to push the
flexAsynch such that this limitiation was very minor, it limited the performance
loss to within 10% of the maximum performance available.

We started testing using just 2 data nodes with 2 replicas. In this test we were able
to reach 13.94M reads per second. Using 4 data nodes we reached
28.53M reads per second. Using 8 data nodes we were able to scale it almost
linearly up to 55.30M reads per second. We managed to continue the
almost linear scaling even up to 24 data nodes where we achieved
156.5M reads per second. We also achieved 104.7M reads per second on a
16-node cluster and 131.7M reads on a 20-node cluster. Finally we took the
benchmark to 32 data nodes where we were able to achieve a new record of
205.6M reads per second.

The configuration we used in most of these tests had:
 12 LDM threads, non-HT
 12 TC threads, HT
 2 send threads, non-HT
 8 receive threads, HT
where HT means that we used both CPU threads in a core and non-HT meant
that we only used one thread per CPU core.

We also tried with 20 LDM threads HT, which gave similar results to 12 LDM
threads non-HT. Finally we had threads for replication, main, io and other activities
that were not used much in those benchmarks.

We compared the improvement of Haswell versus Ivy Bridge (Intel Xeon v2) servers
by running a similar configuration with 24 data nodes. With Ivy Bridge
(which had 12 cores per socket and thus 24 cores and 48 CPU threads in total) we
reached 117.0M reads per second and with Haswell we reached
156.5M reads per second. So this is a 33.8% improvement. Important to note here
is that Haswell was slightly limited by the interrupt handling of Infiniband
whereas the Ivy Bridge servers were not  imited by this. So the real difference is
probably more in the order of 40-45%.

At 24 nodes we tested scaling on number of API nodes. We started at 1 API machine
using 4 API node connections. This gave 4.24M reads per second. We then tried with
3 API machines using a total of 12 API node connections where we achieved
12.84M reads per second. We then added 3 machines at a time with 12 new API
connections and this added more than 12M reads per second giving 62.71M reads
per second at 15 API machines, 122.8M reads per second at 30 API machines and
linear scaling continued until 37 API machines where we achieved 156.5M reads
per second. The best results was achieved at 37 API machines where we achieved
156.5M reads per second. Performance of 40 API machines was about the same as at
37 API machines at 156.0M reads per second. The performance was saturated here
since the interrupt handling could not handle more packets per second. Even
without this the data node was close to saturating the CPUs for both the LDM and
the TC threads and the send threads.

Running with clusters like this is interesting. The bottlenecks can be more tricky
to find than the normal case. One must remember that running a benchmark with
37 API machines and 24 data nodes where each machine has 28 CPU cores, thus
more than 1000 CPU cores are involved, it requires understanding a complex
queueing network.

What is interesting here is that the queueing network behaves best if there is some
well behaved bottleneck in the system. This bottleneck ensures that the flow
through the remainder of the system behaves well. However in some cases where
there is no bottleneck in the system one can enter into a wave of increasing and
decreasing performance. We have all experienced this type of
behaviour of queueing networks while being stuck in car queues.

What we discovered is that MySQL Cluster can enter such waves if the config doesn't
have any natural bottlenecks. What happens here is that the data nodes are able to
send results back to the API nodes in an eager fashion. This means that the API nodes
receives many small packets to process. Since small packets takes longer to process
per byte compared to large packets this has the consequence that the API node slows
down. This in turn means that the benchmark slows down. After a while the data nodes
starts sending larger packets again to speed things up and again it hits too eager
sending.

To handle this we introduced a new configuration parameter MaxSendDelay in
MySQL Cluster 7.4. This parameter ensures that we are not so eager in sending
responses back to the API nodes. We will send immediately if there is no other
competing traffic, but if there is other competing traffic, we will delay sending
a bit to ensure that we're sending larger packets. One can say that we're
introducing an artificial bottleneck into the send part. This artificial bottleneck
can in some cases improve throughput by as much as 100% and  more.

The conclusion is that MySQL Cluster 7.4 using the new Haswell computers is
capable of stunning performance. It can deliver 205.6M reads per second of
records a bit larger than 100 bytes, thus providing a data flow of more than
20 GBytes per second of key lookups or 12.3 billion reads per minute.

MySQL Cluster 7.4.0 Lab Release Improvements

The year of 2014 seems to be the year of the In-Memory Databases. We've seen a lot of commotion around the Hekaton product from Microsoft and other new products. At the same time everyone talks about WebScale and how to build distributed databases that can handle millions of queries per second. In addition we've seen the development quite efficient communication mechanisms the last few years. Databases that make use of In-Memory, WebScale and efficient communication mechanisms are the talk of today in the database world.

The background of NDB

The design of the NDB storage engine started out with exactly those base requirements already more than 20 years ago where the aim was to build the next generation telecom database. The base requirements was a shared-nothing database (for superior scalability and meeting telecom requirements on fail-over times). Already in those days one could buy a machine equipped with 1 GB of memory. Given that the telecom databases was used for extremely quick lookups of small amount of data it was natural to consider an in-memory database design. My research into databases at the time showed that they spent a considerable amount of time in the operating system to handle communication and hard disks. So by moving data to in-memory and by using communication mechanisms that avoided operating system we were able to deliver extremely efficient database operations already in the late 90s.

NDB Today

Today NDB is the storage engine of MySQL Cluster and has been in production usage for more than 10 years. Most everyone on the globe is touched by its operation in some telecom system, in some computer game or in some other type of web property application. We already delivered benchmarks with billions of reads per minute and the scalability of MySQL Cluster is so high that we simply don't have big enough computers or computer sites to show off its limits. A while ago we had access to a computer lab with hundreds of computers that was connected using Infiniband with a total bandwidth between the machines of 1 Tbit/sec. This means we can transport 128 GBytes per second between the machines. However MySQL Cluster could theoretically produce enough parallel read operations to swamp even such a network. So we're getting to the point that it becomes more and more uninteresting to show the scalability limits of MySQL Cluster. So this means that we also want to focus on efficiency and not only on scalability.

The Many-Core CPU Challenge

I gave a presentation to a set of students at Uppsala University about MySQL and MySQL Cluster. In this presentation I showed how the development of multi-core CPUs presented a tremendous challenge to software designers. In a short time of only 8 years Intel and other HW developers gave us the challenge to scale our software 60x. At MySQL we were up to the challenge, we've increased scalability of MySQL using InnoDB 20x in a time span of 5 years. At the same we've actually increased the scalability of the NDB storage engine more than 30x and this means that MySQL Cluster where we use MySQL together with the NDB storage engine has actually scaled 60x in total. This means that my test machine with 8 sockets and 96 CPU threads is now the limiting factor in my benchmarks.

The Many-Core CPU Solution

How have we achieved this? With the MySQL Server it has been a long set of handling various bottlenecks such as splitting the InnoDB buffer pool, handling the LOCK_open mutex and many more changes that collectively have made it possible to scale much beyond of what our software in 2008 could achieve. This improvement of scalability continues, so stay tuned for more, there are blogs to read now what is currently going on MySQL 5.7 development.

With the NDB storage engine the solution has been quite different. We started out building a distributed database already from the beginning consisting of a set of nodes that replicate data synchronously using transactions. In order to avoid usage of the operating system we built the architecture based on a set of independent modules that interact with messages. This was built on the architecture of AXE, a telecom switch operating system of unique efficiency. The first version had each node implemented in a signal thread. With the development based on independent modules meant that we inherited a simple task of dividing the thread into a number of functional modules. Currently we have separated into the local database part, the transaction part, the network send part, the network receive part and an asynchronous event part and finally the main part containing features for meta data handling. Given that we developed a shared nothing architecture it was simple to continue the partitioning to gain even more independent LDM parts by having each LDM thread handle different parts of the data. The transaction part is simple to use a simple round robin scheme and the network parts can easily be divided per socket we handle. In the future we could perform even more divisions of some functions.

NDB Layer by Layer approach

So what does this mean in effect. It means that we actually built a distributed database inside each node inside our distributed database. Given that we can also replicate using MySQL replication we can actually go even further and have multiple clusters connected together. For those that want even more to think of how NDB could be used to build systems with millions of CPUs can google on the word iClaustron. iClaustron is a hobby project I've played with since 2006 and I presented the aims of the project in a tech talk at Google which is available on YouTube.

The world is organised down into microcosmos and continues growing into macrocosmos. So why would software be different, we need to build systems of any size by using layers of layers of distribution.

So building MySQL Cluster is an interesting project in building layer by layer of distribution into the system.

The big challenge ahead of us

So what could be the next challenge that the hardware engineers will deliver to us software engineers. Personally I am preparing already now for it. This challenge that I hope they will bring to us is persistent memory. This means that we will have to build databases where all of a sudden we can make persistent writes at similar speed as we are currently writing to main memory. This will be an interesting challenge and personally I think that main memory databases have a unique advantage in this challenge since they already work at memory speed. So I feel a bit like a horse in the gates before a race, kicking and just eagerly waiting to get off on to the track to see how fast we can run home the next big challenge. But we have to wait until the hardware engineers first solves the issue with which technology will be the winner in this category and that can be commercialised.

So after these small philosophical thoughts let's get into what we're doing in first Lab Release of MySQL Cluster version 7.4 to get further on the path to these goals.

The improvements in MySQL Cluster 7.4.0 Labs release

As mentioned we are working on improving efficiency of MySQL Cluster, we have specifically worked on the scans in the NDB storage engine which have been heavily optimised. In benchmarks using a lot of scans such as Sysbench we have managed to scale up performance per data node by 46% comparing 7.4.0 to 7.3.5. Compared to 7.2.16 the difference is even bigger than 100% but going from 7.2 to 7.3 it was mainly inefficiences in the MySQL Server that was fixed.

Another important thing we've done in 7.4.0 is add a lot of documentation about both our restarts and our scans in the form of extended comments in the code. We've also gone through the log message to the operator while restarting and made them much more accessible and extensive.

MySQL Cluster 7.4.0 improvements for virtual machine environments

With 7.4 we're working hard on making MySQL Cluster more stable even when the underlying system isn't as stable as we would expect. MySQL Cluster is designed for high availability environments and now we're working on making sure that the system can continue to operate even when systems are overcommitted, when we're working in virtual machine environment where we cannot be certain of the exact resources we have available it is hard to operate a high availability environment but we still want to work as reliable as possible.

MySQL Cluster 7.4.0 Stability improvements

We have also been working on improving the availability of the system also in high availability environments by improving the restart times. There are many areas where we can work on this, we can remove subtle delays that adds up to longer restart times, we can use more parallelism in certain phases of the restarts. We have also made our local checkpoints more parallelised which means that we have a more balanced load on the various LDM threads in our system. This actually has the nice side effect that we get a more balanced load amongst the LDM threads which pays off in 5-10% improved performance for any application. Naturally it also means that we can run the local checkpoint faster since we don't risk imbalances in the CPU load by running local checkpoints faster.

Another unique feature of MySQL Cluster is supporting Active-Active environments using MySQL replication. We've been working to extend the support of this feature even further.

Benchmark environment description

We executed a set of benchmarks using Sysbench 0.4.12.6 in our dbt2-0.37.50.6 environment. We used a big machine with 8 sockets of Intel Xeon CPUs running at 2GHz. Each socket has 6 cores and 12 CPU threads. In most cases we run with hyperthreading enabled. But we have found that running LDM threads without hyperthreading is a good idea. This decreases the amount of partitions to manage and the number of threads to manage which have a positive effect on performance. We used 8 LDM threads and in this case the NDB data node used 2 sockets, the benchmark program and the MySQL Server had access to 5 sockets. The MySQL Server used about 40 CPU threads out of the 60 it had access to so in this configuration we had spare resources to use. But in the next step where we went to 12 LDM threads we could not use the full potential of the SW. In this case the data node needed 3 sockets, the benchmark program used 1 socket and thus the MySQL Server only had access to 4 sockets and this meant that it could increase performance by 25% and not the 50% made possible by going to 12 LDM threads (actually we squeezed a bit and made 52 CPU threads available to the MySQL Server and thus got about a 30% improvement over 8 LDM threads). Using 7.3 the data nodes are less efficient so here we could scale the LDM threads all the way to the 50% improvement (actually we even got to 52.7% improvement, so perfect scaling of performance as more LDM threads are added).

So with 12 LDM threads we need a 54 core-machine to make full use of the possibility of the data node. With 16 LDM threads we need even more, we need 4 sockets for the data node, we now need 2 sockets for the benchmark program, we need 6 sockets to run the MySQL Server and thus a total of 12 sockets or 72 cores. This is probably as far as MySQL 5.6 can help us scale before the MySQL Server can no longer scale. But this is an important area of focus for MySQL 5.7 that have already had a set of improvements implemented in the 5.7.4 DMR released now.

Final words

So with this kind of scalability we are now in a position to deliver more performance from one single node than we previously could deliver from an entire cluster. Imagine what performance we can then get when connecting many nodes together and still we're working on making each MySQL Cluster thread more efficient in its execution.