One step in the direction of Parallel Query is to handle parallelisation of
ALTER TABLE.
The phase of ALTER TABLE to parallelise is the copy phase.
The idea is to parallelise this for tables that are partitioned.

If the original table is not partitioned then no parallelisation of the
read part will be performed, the insert part will however be parallelised
if the new table is partitioned.

The idea is to have one thread per partition in the original table
and one thread per partition in the new table.

Thus we can achieve a very good optimisation.

One important base feature needed for this development is the ability to
transport data between threads in the MySQL Server.

It should even be checked whether it is possible to allow several MySQL Servers
to partiticipate in the copy process for clustered tables.

The changes are local to the copy_data_between_tables method and thus affect
a very limited part of the MySQL Server.

The first step is to parallelise the

subroutine copy_data_between_tables for partitioned tables.



This method will be executed in several threads where each

thread takes care of one or more partitions.



To do this it is required to create a new table object

for each new thread created.



It is also required to create a new THD object for each new

thread.



Each thread will execute in its own transaction and since the

copy_data_between_tables can be rolled back by simply dropping

the new table this should not be an issue in this case.



It will also only work properly if the change doesn't change

the partition function. Thus definition of a new partitioning

or going from non-partitioned to partitioned or vice versa won't

work initially.



Examples of things that will work are:

ALTER TABLE t1 ADD COLUMN a int;

ALTER TABLE t1 ADD INDEX ....;



where t1 is a partitioned table with at least 2 partitions

(1 partition gives only one thread which isn't parallel).



The maximum number of threads used is set by a session variable,

this is by default set to 1 which means no parallelisation. Thus

this session variable needs to be set to a higher value than 1

in order for this feature to be invoked.

Current Tags

You must be logged in to tag this worklog