Re: Partitioning Details

[Mikael Ronström]

Hi,

Steven Roussey wrote:
> I was wondering about some details on how Mysql is
> going to handle individual partitions. We do our
> own in-application poor mans version of
> partitioning for two reasons: table locking and
> table maintanence. In a partitioned table, will
> there still be table locking, or will it lock just
> a partition? Or will there be row-level-locking at
> that time that makes my question meaningless?

The answer to this question is dependent on the underlying storage handler
for the partition. When it comes to table locking it is true that the partitioned
table will be locked as one unit. There will most likely be some syntax such that
one can query only a subset of the partitions and in that case only those partitions
will be locked. We haven't decided on the syntax yet and in which query types it will
be available.

For MyISAM table locking is the granularity as usual bot for partitions based on InnoDB
there is row-level locking. This means that the table lock is not really a table lock for
InnoDB and other row-level locking handler.

And
> will we be able to do maintanence on a table one
> partition at a time, or will it require the whole
> table to be locked up? In this context, I mean
> something like OPTIMIZE TABLE or ALTER TABLE ...
> ORDER BY on a partition.
>

It will be possible to do more and more maintenance per partition as functionality is growing.

Thx for the viewpoints, they have strengthened the case for being able to specify which partitions
to use in a query or maintenance command.

Rgrds Mikael

> Thanks!