MySQL Cluster News

[Mikael Ronström]

Hi,
In the past we have recommended users of MySQL Cluster to not use version 5.0 of MySQL since
MySQL Cluster in 5.0 wasn't fully tested.
With the release of MySQL 5.0.3 we have a release of MySQL 5.0 where MySQL Cluster
fully works as in MySQL 4.1. MySQL 4.1 is recommended for production usage but
MySQL 5.0 is of high quality as well. There are relatively small changes in the cluster part
between 4.1 and 5.0 and our test coverage is good so it should be quick for most
users to upgrade from 4.1 to 5.0.

All new feature development of MySQL Cluster goes into MySQL 5.1 mostly by now. I'll provide
some hints of what MySQL 5.1 could include further down in the email.

MySQL UC 2005
------------------
Also the Users Conference for MySQL 2005 is soon coming up.

I will be doing these sessions at UC2005:
http://mysqluc.com/cs/mysqluc2005/view/e_sess/6306
http://mysqluc.com/cs/mysqluc2005/view/e_sess/6318
and will be around at a bunch of more sessions.

If you are interested in discussing your Cluster projects, the development of new Cluster
features, how to get optimal speed or reliability and/or any other general question - I am happy
to meet with you.

MySQL 5.0 Features
-----------------------

MySQL 5.0.3 contains a number of new features that deserve to be mentioned.
1) Push-down Conditions
(This feature is currently disabled by default pending more testing but works in most cases,
enabled by SET engine_condition_pushdown=on; there is also a start-up option
--engine_condition_pushdown that enables this feature by default)
A query like
SELECT * from t1 where non_indexed_attribute = 1;
will use a full table scan and the condition will be evaluated in the NDB data nodes. Thus it is
not necessary to send the records across the network for evaluation (function shipping rather
than data shipping that is). For this type of queries there will be a speed up factor of 5-10.

There is EXPLAIN support also to check whether push-down conditions are used or not.

The nice thing with this new feature is that it also means that queries are executed in parallel.
The above query will actually be 5-10 * no_of_nodes times faster in some cases since there are
more than one CPU working on the query in parallel.

2) Less use of IndexMemory
In 5.0 every record uses around 25 bytes of IndexMemory and every unique index uses
25 bytes per record of IndexMemory (unique indexes also uses some DataMemory since
it is a separate table from the original table). Thus there is no storage of the primary key in
the Index Memory anymore.

3) Query Cache enabled for MySQL Cluster

4) Optimisations
One particular optimisation that deserves attention is that a batched read interface is used in
some queries.
An example of this is the query.

SELECT * from t1 where primary_key IN (1,2,3,4,5,6,7,8,9,10);
This query will be 2-3 times faster due to that all 10 key lookups are sent in one batch rather than
one at a time.

MySQL 5.1 development for MySQL Cluster
-----------------------------------------------------------
What is said here is a current status report that can be gathered by checking bk commit emails. It contains
no promises on whether it will actually become part of MySQL 5.1 since all development is subject to
change of priorities and so forth as in any design organisation.

There are 4 major new features developed for 5.1 currently
1) Integration of MySQL Cluster into MySQL Replication.
This will make it possible to update from any MySQL Server in the cluster and still have the MySQL Replication
handled by one of the MySQL Servers in the cluster and the installation on the slave side consistent.

2) Support for disk-based records.
Records on disk will be supported. Indexed fields still have to be in memory and also the primary key hash index
but all other fields can be on disk.

3) Variable sized records
VARCHAR(255) will currently consume 260 bytes of storage independent of what is stored in a particular record.
In 5.1 only the valid part of the field will be stored. This will make size savings of factors of 5 possible in many cases.

4) User-defined Partitioning
Users can define the partitoning based on fields part of the primary key. The MySQL Server will be able to find out
if it can prune away some of the partitions from the WHERE clause. Partitioning based on KEY, HASH, RANGE and LIST
and subpartitioning will be possible. This will also be available for many other handlers.

Further optimisations are also likely to happen.

Rgrds Mikael

Mikael Ronström, Senior Software Architect
MySQL AB, www.mysql.com

Jumpstart your cluster:
http://www.mysql.com/consulting/packaged/cluster.html