WL#2550: Parallelise ALTER TABLE
Affects: Server-6.x — Status: Assigned — Priority: Medium
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.
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.