Wednesday, October 23, 2019

Setting up an NDB Cluster in the Oracle Cloud using Auto Installer

In MySQL Cluster 8.0.18 we have developed MySQL Cluster Auto Installer to
also support installing NDB :)

We have made it very easy to setup an NDB Cluster in the Oracle Cloud.
The Auto Installer will take care of installing the proper software, installing
firewalls, installing some supportive software. Most of the testing of this
software have been done against Oracle Cloud using instances with Oracle
Linux 7.

I prepared two Youtube videos to show how it works. The first one gives some
insights into setting up the Compute Instances required in the Oracle Cloud.

Setup compute instances in Oracle Cloud for MySQL Cluster AutoInstaller

The second video uses these compute instances to set up an NDB Cluster.

Setting up an NDB Cluster in the Oracle Cloud using Auto Installer

Tuesday, October 22, 2019

Setting up MySQL Cluster on local machine using AutoInstaller

We have a new version of the MySQL Cluster Auto Installer. I have prepared 3
Youtube videos that shows how to make use of the Auto Installer to install and
set up a cluster on your local machine.

This is my first attempt at making Youtube videos to explain things around
MySQL Cluster.

The Auto Installer is intended as a tool to make it easy to get a cluster up and
running. It is NOT intended for managing a cluster.

The first Youtube video Install MySQL Cluster 8.0.18 on Mac OS X shows how
to install MySQL Cluster 8.0.18 on Mac OS X. This is obviously trivial, so should
be straightforward to do even without the video.

The second Youtube video Starting a local MySQL Cluster using Auto Installer sets
up a small cluster with 1 management server, 2 data nodes and 1 MySQL Server and
explains some details around this.

Using MySQL Cluster AutoInstaller to start development with NDB goes a bit deeper
and also shows how to make use of the cluster and do some trivial operations
through a MySQL client and the NDB management client. It also shows how one can
extend the possible configurations supported by directly manipulating configuration
files deployed by the Auto Installer.

A little note for Mac OS X users with high resolution is that full 4K resolution is
available through Google Chrome, not through Safari.

Friday, April 05, 2019

Manual for dbt2-, fully automated Sysbench and DBT2 benchmarking with NDB

The link dbt2.0.37.50 manual provides the details of how to use the dbt2-0.37.50 scripts
to execute benchmarks using MySQL Cluster.

These scripts can be used to execute automated test runs of Sysbench, DBT2 and
FlexAsynch. I also use it to start up NDB Clusters to run DBT3 benchmarks and
YCSB benchmarks.

This set of scripts originates from 2006 when I wanted to automate all my benchmark
efforts. The most challenging benchmarks constitute starting more than 100 programs
to work together and using more than 100 machines. This requires automation to
be succesful.

Now running any benchmark is a 1-liner e.g.
./ --default-directory /path/to/dir --init

The preparation to run this benchmark is to place a file called autobench.conf in
/path/to/dir. This contains the configuration of the NDB data nodes, NDB MGM
servers, MySQL Servers and the benchmark programs. Multiple benchmark
programs are supported for Sysbench, DBT2 and flexAsynch.

Wednesday, October 24, 2018

MySQL Cluster 7.6.8 performance jump of up to 240%

In February I added a new feature to my Sysbench version that I use in
my MySQL Cluster testing. This new feature adds a new column in the
table called filter. It contains the same value as the primary key.

With this new column I can easily change the range scan queries in
sysbench from returning 100 rows to instead scan 100 rows and
return 1 row. This means that sysbench can benchmark the filtering
performance of the underlying database engine.

Next I ran tests where set the number of rows in the range to
10.000 rows. This new test was a perfect vehicle to improve performance
in NDB for scan filtering.

Filtering one row in 7.6.7 in this sysbench tests costs about 750 ns.

When I started out optimising these 750 ns of time I didn't expect so
much improvement, but using perf it was possible to get very
fine-grained pinpointing of the wasted CPU performance. One
interesting thing was that I found a bitmask that had zeroing of the
bitmask in the constructor, it turned out that this constructor was
called twice in filtering a row and neither of them was required.
So fixing this simple thing removed about 20 ns of CPU usage and
in this case about 3-4% performance improvement.

As you can see this is micro-optimisations and for those perf is a
splendid tool.

One of the biggest reasons for bad performance in modern software
applications is instruction cache misses. Most modern software
is packed with features and this requires a lot of code to handle.
The compiler has a hard time knowing which code is the common
path and which path is the error handling path.

In the MySQL code we have two macro's likely and unlikely that
can hint the compiler what code path to optimise for.

In this code path I was optimising I found that I had roughly 1 billion
instruction cache misses over a short period (20 seconds if I remember
correctly). I managed with numerous changes to decrease the number
of instruction cache misses to 100 million in the same amount of time.

I also found some simple fixes that cut away a third of the processing time.

In the end I found myself looking at the cost being brought down to around
250ns. So comparing the performance of this scan filtering with 7.5.10 we
have optimised this particular code path by 240%.

During the development of these improvements of scan filtering, I discovered
that some of the optimisations could be applied also to searching in our
ordered indexes. The impact of this is that the index rebuild phase of a restart
will go faster, I haven't measured the exact impact this has yet. It also means
that any application using ordered indexes will go a lot faster.

For example performance of a standard Sysbench OLTP RW benchmark
with one added ordered index column improves by 70% in 7.6.8
compared to earlier versions of 7.6 and 7.5.