Performance Tuning of MySQL Cluster

As you probably have noticed my blog has been a bit quiet lately. I've
been very busy with some very interesting developments. I've been
working very hard on benchmarking of MySQL Cluster together with
Dolphin and Intel. There will be a lot of material coming out from this
the next couple of weeks. I've prepared a couple of white papers on
how MySQL Cluster can scale to new heights.

I'll have a presentation at the MySQL Users Conference
http://www.mysqlconf.com
where I'll describe all the interesting tidbits of how to tune MySQL
Cluster performance. This will include both choice of HW, use of
configuration parameters, which particular new features to especially
look out for and so forth.

If you want to prepare for this then download the white papers that
will be available from MySQL and from Dolphin
http://dev.mysql.com
http://www.dolphinics.com
They should be available there in about a week or so.

Then come and listen to my session at the Users conference at
5.30 on tuesday 24 april. If you still have questions or want to know
even more then come and talk to me, I'll be around at many of
the MySQL Cluster presentations, at the MySQL and the Dolphin
booths in the exhibition hall.

It's been a really interesting project to work on and it's great to be able
to show all these new results that show how one can make use of
MySQL Cluster in a really scalable way.

For those that wish to try the benchmarks out themselves there will
also be a large number of scripts made available to simplify set-up
of MySQL Cluster for large clusters and a fairly heavily revised version
of DBT2 that can be used to run large benchmarks using MySQL
Cluster. More on this later, check out the blog next week for more
info on this.

For those of you that want to know the latest news on partitioning
as well I will also make a presentation of this at the Users conference.
It will include a description of partitioning in 5.1, how to make use
of partitioning for better scalability in MySQL Cluster and finally also
some notes about some new cool developments that are ready to
be put into the 5.2 version of MySQL.

Webinar on MySQL Cluster using Dolphin SuperSockets

My blogging hasn't been so active lately. Mostly due to that I've been busy on
other things. One of the things that have kept me busy the last few months
is a project which I highly enjoy. I've been performing a benchmark study
of MySQL Cluster using Dolphin SuperSockets. Performance is one of my
favourite topics and a parallel database like MySQL Cluster has a wide array
of performance challenges that makes it very interesting to optimize it.

I will present the results in two webinars on the 30th Nov and 13 dec. The
webinars will also provide some input to the features in Dolphin
SuperSockets and MySQL Cluster that enables high performance and
real-time characteristics. With these changes to MySQL Cluster and using
the Dolphin SuperSockets MySQL Cluster becomes even more adapted for
all types of real-time applications.
See:
http://www.mysql.com/news-and-events/web-seminars/

Performing this work has been an interesting enterprise in finding out how
to best make use of the Dolphin hardware using MySQL Cluster. I found a
number of interesting ways where 1+1 = 3, meaning I've found optimisations
that can be done in MySQL Cluster that are especially effective if using
Dolphin SuperSockets. So as a result of this some very interesting
achievements have been made.

- A completely interrupt-free execution of ndbd nodes in MySQL Cluster
using Dolphin SuperSockets.
- Real-time features added to MySQL Cluster enabling much faster response
times.
- Possibility to lock threads to CPU's in MySQL Cluster enabling a higher level
of control over the execution environment.
- Possibility to lock pages in main memory removing any risk of swapping
- Possibility to choose between polling and interrupt-driven mechanisms in
ndbd kernel

The combination of MySQL Cluster and Dolphin SuperSockets becomes a truly
amazing real-time machine. With those added features in place and using
Dolphin SuperSockets I've also seen how MySQL Cluster can take yet another
step on its on-line recovery features. Using those real-time features it is
possible to get node failover times down to around 10 milliseconds.
MySQL Cluster was already before market leading in this respect, with this
feature the gap to the competitors is bound to increase.

Most of the benchmark work have been focused on the DBT2 benchmark. Most
benchmarks I've done in the past have been focused on applications written
directly for the NDB API. So it's been interesting to see what one needs to do
to make the MySQL Server be really fast.

In order to run DBT2 with MySQL Cluster at first I had to adapt the DBT2
benchmark for:
- Parallel load of data
- Parallel MySQL Servers while running the benchmark
- Using MySQL Cluster features such as HASH indexes, PARTITIONING and
Disk Data for MySQL Cluster.

I also got tired of remembering all the -i -t -h and so forth in the various
scripts and used more real names for the parameters.

There was also a number of performance bugs in DBT2. DBT2 is implementing
the TPC-C specification and in a number of places the SQL queries were made
such that there was a large number of unnecessary extra record fetches in some
queries.

I will soon upload the changes to DBT2 to SourceForge if anyone wants to use
the same benchmark.

Webinar on Partitioning

As mentioned in an earlier post the partitioning in 5.1 has reached a level of
stability so that it can now be put to some heavier test. To spread further
insights of the new partitioning feature I'll deliver two webinars next week
and the week after that (29 nov and 5 Dec).

You'll find a reference to both from the MySQL Home Page.
http://www.mysql.com/

The first one will give an introduction to partitioning in MySQL and
describe the variants of partitioning that will be supported, which
management variants that are possible and so forth.

The second webinar is a follow-up that will do some repetition to
ensure it can be viewed stand-alone but will mainly dive a little
deeper into various areas of partitioning amongst other how it
relates to MySQL Cluster.

State of MySQL partitioning

It's been some time since I last blogged. I've been quite busy erasing all the
bugs from the partitioning implementation in MySQL 5.1. At the moment
there is 1 bug left in review and a few on its way into the main clone. The
rest of the bugs are fixed and already in the 5.1 clone. So the next 5.1
release (5.1.12) will have partitioning ready for tough tests. So if you have
been waiting for partitioning to stabilise it's time to try it out now with your
application and see how it works.

I watched an interesting animation my daughter made about how partition
pruning using dynamic PowerPoint slides. Really interesting to see what can
be done if one knows how to handle these types of tools. She's quickly
becoming our family authority on presentations.

Lately we've also started working on some extensions for the partitioning
hopefully ready for 5.2. We have introduced a new partitioning syntax like this:

CREATE TABLE t1 (a char(10), b date)
PARTITION BY RANGE (COLUMNS(b,a))
(PARTITION p0 VALUES LESS THAN ('1999-01-01', "abc"),
PARTITION p1 VALUES LESS THAN ('2001-01-01', MINVALUE),
PARTITION p2 VALUES LESS THAN ('2003-01-01', MAXVALUE));

The nice thing with this syntax is that it can be handled partition pruning with
ranges in a very efficient manner. So the query:
SELECT * FROM t1 WHERE b <= '1999-06-01' AND b >= '1999-02-01';
can be optimised to only scan partition p1.

We are also working on indexes that are partitioned independent of the base
table and also a couple of other features. As usual what actually goes into the
next release is uncertain.