Partition and Scanning

If a query does a SELECT the optimizer will discover which partitions
that has to be scanned. In 5.1 these scans will still be made
sequentially on one partition at a time. However only those partitions
actually touched will be scanned which can improve performance on
certain queries by a magnitude.

The aim is to support parallel scan in a future release as it is to
support parallel sort on the first table selected by the optimizer.
This is an important long-term goal of partitioning to open up the
MySQL architecture for many performance improvements on
handling large data sizes. In 5.1 we have achieved quite a few of
those goals but expect to see more goals achieved as new versions
of MySQL hits the street burning :)

Partitioning and Locking

In MySQL tables are locked as part of the MySQL Server code during each
statement. For a SELECT statement each table in the SELECT statement is
locked using a read lock. For UPDATE/INSERT/DELETE statements a write
lock is used. For ALTER TABLE a special lock that excludes writers but
allows readers to be processed is taken.

However what these locks actually do is dependent on the underlying
engine. For MyISAM, a read lock means no updater is allowed during the
statement and a write lock means that no other writers is allowed and
also no other readers.

For other engines such as NDB and InnoDB that employs row-level locking
internally these locks are mostly ignored except for the special ALTER
TABLE lock that ensures that no writers is allowed during the ALTER TABLE
statement.

(There is quite a lot more details to locking in MySQL that is not covered in
this blog)

Using partitioned tables, there is one lock per partition instead of one lock
per table. However for each statement a lock is taken on all partitions and
thus the behaviour is the same as if there was one lock using one table
and this lock is only held during the time of a statement and is dependent
on the storage engine used by the partitions.

It is quite certain that there will be changes of lock behaviour in 5.2. It is
quite likely that less partitions will be locked to ensure that only those
partitions touched are actually locked.

Partitioning at UC 2006

I've been posted as a speaker at the MySQL Users Conference at the end of
April.
http://www.mysqluc.com
I will present the partitioning development that has happened in MySQL for
5.1. There's been a lot of work to accomplish this but it's nice to see the
result now that it's nearing completion.

At first when I started this development my main objective of doing this
was to prepare MySQL for parallel query support (which it actually has
support for since 5.0 in MySQL Cluster).

However while developing support for this I've learned a lot about other
nice uses for partitioning. Other open source DBMS's have focused mainly
on the performance aspects of partitioning. I think this has been solved
very well in this 5.1 release, this particular part needed some intricate
understanding of the optimiser which I did not possess on a sufficiently
detailed level, but with some aid and with a small effort the optimiser
was turned into a partitioning optimiser.

However most of the work for partitioning has actually been spent on the
management part of partitioning. The ability to add, drop and reorganize
partitions in a fast and efficient manner. Now with the implementation it
is possible to momentarily add and drop partitions for range and list
partitioning. It is also possible to add and drop partitions for hash
partitions, normally this means that the entire table needs reorganisation.
However we've added the keyword LINEAR to all hashing variants. This
makes it possible also for HASH/KEY partitions to have partitions added
and dropped with minimal work. So if the table contains 16 partitions with
LINEAR KEY partitioning it's only necessary to reorganise 4 of those 16
partitions to grow the number of partitions to 20. The linear scheme makes
distribution of records to be unbalanced in scenario's where the number
of partitions isn't in the form 2**n. So it's a choice one does when
manageability is important.

Given that MySQL is known for its simplicity, ease of use, we felt that it was
more important to bring a version with the all the manageability and with
sufficent performance advantages. I'm glad to say now that we achieved
both. It's got both manageability and major performance improvements.
Unfortunately we weren't able to bring all the goals I had with partition
management for MySQL Cluster into 5.1. This is especially displeasing
to me being the founder of the cluster technology in MySQL. However we
look forward to bringing out this work in future releases of MySQL, it's
a tremendously interesting problem to reorganise a clustered table while
users are reading, updating, taking backup's and performing a whole lot
of various other actions on the table simultaneously with this change.
This work will also form the foundation of the support for adding and
dropping nodes in MySQL Cluster.

One idea that I'm particularly pleased with is the concept of REORGANIZE
partitions. I was contemplating how to handle MERGE of partitions, JOIN
of partitions and also simply changing the partition boundaries. I was
very pleased to find one stone for all those birds.

So please try out the new partitioning feature such that we can quickly
bring it to a stable state and move on to the next set of cool features for
versions to come.