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.

PARTITION by a date column

One of the most common usage of partitioning is where one wants to partition
by date. One might want to have one partition per year or per month or per
week or per day. This blog entry shows how to handle this requirement using
MySQL 5.1.

The most common method to partition in this case is by range.
Partitioning in 5.1 uses a function on one or more fields of the table. In 5.1
there are some requirements on these fields if unique indexes or primary keys
also exist in the table. The reason is that 5.1 doesn't have support for global
indexes. Development of support for this have however started so should be in
some future release of MySQL.

In 5.1 functions have to return an integer. There are two functions that has
special support in the MySQL server. These are TO_DAYS() and YEAR(). Both
of these functions take a DATE or DATETIME argument and return an integer.
YEAR() returns the year and TO_DAYS() returns the number of days passed
since a particular start date.

The MySQL optimizer has special support for these two partition functions. It
knows that those functions are strictly increasing and use this knowledge to
discover that queries such as:

SELECT * from t1 WHERE a <= '1991-01-31' AND a >= '1991-01-01';
with a partition function PARTITION BY RANGE (to_days(a)) can be mapped
to a range of partition function values starting at
TO_DAYS('1991-01-01') and ending at TO_DAYS("1999-01-31")

Thus the MySQL Server can map TO_DAYS('1991-01-01') to a starting partition
and TO_DAYS('1991-01-31') to an ending partition. Thus we only need to scan
partitions in a range of partitions.

Most functions don't have this nice mapping from value range to partition
range. The functions TO_DAYS(date) and YEAR(date) are known by the
MySQL optimizer to have this attribute and they will thus be better for range
optimisations. Also a partition function on a field which is an integer field
where the function is the field by itself will have this characteristic. Other
functions won't, theoretically many more can be handled but this requires
special care of overflow handling to be correct and this will be added in
some future MySQL release.

So with this knowledge let's set up a that does partition by month.

CREATE TABLE t1 (a date)
PARTITION BY RANGE(TO_DAYS(a))
(PARTITION p3xx VALUES LESS THAN (TO_DAYS('2004-01-01'),
PARTITION p401 VALUES LESS THAN (TO_DAYS('2004-02-01'),
PARTITION p402 VALUES LESS THAN (TO_DAYS('2004-03-01'),
PARTITION p403 VALUES LESS THAN (TO_DAYS('2004-04-01'),
PARTITION p404 VALUES LESS THAN (TO_DAYS('2004-05-01'),
PARTITION p405 VALUES LESS THAN (TO_DAYS('2004-06-01'),
PARTITION p406 VALUES LESS THAN (TO_DAYS('2004-07-01'),
PARTITION p407 VALUES LESS THAN (TO_DAYS('2004-08-01'),
PARTITION p408 VALUES LESS THAN (TO_DAYS('2004-09-01'),
PARTITION p409 VALUES LESS THAN (TO_DAYS('2004-10-01'),
PARTITION p410 VALUES LESS THAN (TO_DAYS('2004-11-01'),
PARTITION p411 VALUES LESS THAN (TO_DAYS('2004-12-01'),
PARTITION p412 VALUES LESS THAN (TO_DAYS('2005-01-01'),
PARTITION p501 VALUES LESS THAN (TO_DAYS('2005-02-01'),
PARTITION p502 VALUES LESS THAN (TO_DAYS('2005-03-01'),
PARTITION p503 VALUES LESS THAN (TO_DAYS('2005-04-01'),
PARTITION p504 VALUES LESS THAN (TO_DAYS('2005-05-01'),
PARTITION p505 VALUES LESS THAN (TO_DAYS('2005-06-01'),
PARTITION p506 VALUES LESS THAN (TO_DAYS('2005-07-01'),
PARTITION p507 VALUES LESS THAN (TO_DAYS('2005-08-01'),
PARTITION p508 VALUES LESS THAN (TO_DAYS('2005-09-01'),
PARTITION p509 VALUES LESS THAN (TO_DAYS('2005-10-01'),
PARTITION p510 VALUES LESS THAN (TO_DAYS('2005-11-01'),
PARTITION p511 VALUES LESS THAN (TO_DAYS('2005-12-01'),
PARTITION p512 VALUES LESS THAN (TO_DAYS('2006-01-01'),
PARTITION p601 VALUES LESS THAN (TO_DAYS('2006-02-01'),
PARTITION p602 VALUES LESS THAN (TO_DAYS('2006-03-01'),
PARTITION p603 VALUES LESS THAN (TO_DAYS('2006-04-01'),
PARTITION p604 VALUES LESS THAN (TO_DAYS('2006-05-01'),
PARTITION p605 VALUES LESS THAN (TO_DAYS('2006-06-01'),
PARTITION p606 VALUES LESS THAN (TO_DAYS('2006-07-01'),
PARTITION p607 VALUES LESS THAN (TO_DAYS('2006-08-01'));

Then load the table with data. Now you might want to see the
data from Q3 2004. So you issue the query:
SELECT * from t1
WHERE a >= '2004-07-01' AND a <= '2004-09-30';
This should now only scan partition p407, p408, p409. You can
check this by using EXPLAIN PARTITIONS on the query:
EXPLAIN PARTITIONS SELECT * from t1
WHERE a >= '2004-07-01' AND a <= '2004-09-30';

You can also get similar results with more complicated expressions.
Assume we want to summarize on all measured Q3's so far.
SELECT * from t1
WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR
(a >= '2005-07-01' AND a <= '2005-09-30');

Using EXPLAIN PARTITIONS we'll discover the expected result that this
will only scan partitions p407, p408, p409, p507, p508 and p509.

When july comes to its end it is then time to add a new partition for
august 2006 which we do with a quick command:
ALTER TABLE t1 ADD PARTITION
(PARTITION p608 VALUES LESS THAN (TO_DAYS('2006-09-01'));