5.1 alpha with Partitioning released

Now the first alpha release of partitioning has been released.
This includes more or less all the syntax for partitioning to be
available. There is still some optimisation patches that are in the
works for coming 5.1 alpha releases.

There is still a small window of opportunity to get smaller additions
in so please use the MySQL partitioning forum to feedback any ideas
you have on improvements.

Partitioning is designed to work with all storage engines of MySQL. It is
however not possible to mix storage engines in a table. It is also possible
to use partitioning for MySQL Cluster. This gives the DBA the possibility to
control data placement in the cluster and thus create heterogenous clusters.

Most of the documentation for partitioning is already written and can be found
in the MySQL 5.1 Reference Manual due to some hard work by Jon Stephens.
Suggestions for improvements is welcome here as well.

Calculating parameters for local checkpoints in MySQL Cluster

There are a couple of parameters in the configuration of local checkpoints
that have an intimate relationship between each other. All the parameters
are documented individually but the following example can serve to
improve the understanding of how they can be used together.

The example uses an application that does
50.000 selects per hour
15.000 updates per hour
15.000 deletes per hour
and 15.000 inserts per hour

(LCP = Local Checkpoint)
What I usually do is that I decide to fix the time an LCP is to take. Usually I use
5 minutes as a "thumb-rule". This means that a system restart will take
maximum 2-3 minutes to execute the REDO log.

Then I take the maximum size of the data in a node. For simplicity one could
use DataMemory here.

Then I take DataMemory / 300 as the amount of data to checkpoint per
second. Say I had 2 GB DataMemory => 6.6MB/sec => 660 kB/100 msec =>
83 * 8 kB/100 msec.
The parameter to set for this is NoOfDiskPagesToDiskAfterRestartTUP.

If the data to checkpoint to disk is close to the disk bandwidth available I
would ease down on the 5 minutes above and go for a longer checkpoint
time. Remember that the disk is also needed to handle REDO log writes and
UNDO log writes.

Then a similar calculation for IndexMemory.

The parameter to set for this is NoOfDiskPagesToDiskAfterRestartACC.

After performing these calculations I have come to a position where I know
the LCP time. The next step is to go about calculating the number of
fragment log files.

The parameter to set for this is NoOfFragmentLogFiles.

The fragment log files (REDO log files) are needed to keep at least log records
during 3 LCP's. Since there is a number of uncertainties in for example whether
the disk will actually go at full configured speed always and so forth I usually
go for a conservative figure and estimating a size for 6 LCP's.

If I choose 300 secs (5 minutes) as the time of a LCP, this means that I need to
support writing log records at full speed for 6 * 300 secs = 1800 secs.

The size of a REDO log record is:
72 bytes + 4 bytes * number of fields updated + max size of all fields updated
There is one such record for each record updated in a transaction in each node
where the data resides.

Using your case above we get:
50.000 selects/hour => 0 log records since SELECT's are not REDO logged
15.000 deletes/hour => ~ 5 deletes per second = 5 * 72 = 360 bytes/second
15.000 updates/hour =>
~ 5 updates per second = 5 * 72 + 5 * 4 * 5 + 5 * 32 = 620 bytes/second
(Assumed here 5 fields of 32 bytes updated)
15.000 inserts/hour =>
~ 5 inserts per second = 5 * 72 + 5 * 4 * 40 + 5 * 32 * 40 = 7560 bytes/second
Assuming a table with 40 fields of 32 bytes each and no NULL's in insert.

Thus a total of 7560 + 620 + 360 = 8540 bytes per second

=> 8540 * 1800 = 15 372 000

Thus 16 MByte of REDO log file would suffice in this case. The minimum
setting on number of fragment log files is 3 (where each file is 64 MByte)
and thus the default setting of 8 is quite ok here and no need to change.

(With an application that does 15.000 updates/deletes/inserts per second
the figures obviously change quite a lot).

The UNDO log file I usually don't worry about at all, only in the sense that
there needs to be sufficient disk space for it. In this case a few Mbytes
should suffice, but given sizes of disks today I can't see why one would
allocate less than a GByte for it.

Partition Defaults

It's been an interesting exercise to define the defaults when creating and
altering partitioned tables.
If one creates a table like this

CREATE TABLE t1 (a int)
PARTITION BY KEY(a)

1) How many partitions
2) What will be the names of the partitions
3) Which engine is used

In this case the answer will be
1) 1
2) p0
3) MyISAM, most likely, depends on which is the default engine in the server
(If NDB is default then the answer will differ)

The reasoning here is that 1 is a minimalistic approach and can still be desired
since it might be desirable to be able to grow the table using ADD PARTITION
and so forth.

For RANGE/LIST partitioned tables it is not possible to avoid defining the
partitions. The reason is that the VALUES LESS THAN/VALUES IN must be
specified, otherwise we don't know the ranges or the list values (these are
too hard to guess at for a default).

However subpartitions can again be default
CREATE TABLE t1 (a date)
PARTITION BY RANGE (year(a))
SUBPARTITION BY KEY(a)
(PARTITION p0 VALUES LESS THAN (10));

In this case we will again set default number of subpartitions to 1, which in this
case isn't very useful since we cannot change the subpartitioning with any
partition management commands in this version.

CREATE TABLE t1 (a date)
PARTITION BY RANGE (year(a))
SUBPARTITION BY KEY(a)
SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (10));

This is more useful and the name of the subpartitions will be sp0 and sp1.

Now to even more tricky parts. What if a table created with all defaults has
partitions added to it.

ALTER TABLE t1 ADD PARTITION 1;

Assume that the table already had all defaults this would mean that we had 1
partition and add another. So from now on we don't have default number of
partitions anymore so the table is the same as if created by

CREATE TABLE t1 (a int)
PARTITION BY KEY(a)
PARTITIONS 2;
Thus partitions and their names are still default but not the name.

If we do instead
ALTER TABLE t1 (a int) ADD PARTITION (PARTITION x0);

In this case we have specified one new partition and we have even specified its
name and thus we don't use defaults anymore. Thus the table is now the same
as if created with:

CREATE TABLE t1 (a int)
PARTITION BY KEY(a)
(PARTITION p0, PARTITION x0);

For partitioned tables using NDB as the storage engine (MySQL Cluster) the
default number of partitions will be equal to the number of nodes in the
cluster, except when MAX_ROWS has been set very high when it can be set
higher, to a multiple of the number of nodes in the cluster.

Key parameters for MySQL Cluster

Two parameters that you want to keep track of for MySQL Cluster is:

ndb-use-exact-count
If this is set to 1 you get very fast SELECT COUNT(*) but most other
queries become much slower. So if your normal query scenario is
primary key lookups then set this parameter to 0. It can have a
radical effect on performance. This parameter exists in 4.1 and
onwards.

engine_condition_pushdown
This parameter is introduced in 5.0 and if set enables condition pushdown.
As an example if one does a query like:
SELECT * FROM t1 WHERE name LIKE %adam;
Then the LIKE expression can be pushed down to the data node and be evaluated
in parallel for much higher execution speed.

So both those parameters can give radical performance effect in certain query types.