Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.4Mb
PDF (A4) - 37.4Mb
PDF (RPM) - 36.8Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.8Mb
Man Pages (TGZ) - 210.2Kb
Man Pages (Zip) - 318.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Online DDL for Partitioned Tables

15.13.7 Online DDL for Partitioned Tables

With the exception of ALTER TABLE partitioning clauses, online DDL operations for partitioned InnoDB tables follow the same rules that apply to regular InnoDB tables. Online DDL rules are outlined in Table 15.10, “Online Status for DDL Operations”.

Not all ALTER TABLE partitioning clauses go through the same internal online DDL API as regular non-partitioned InnoDB tables. As a result, online support for ALTER TABLE partitioning clauses varies.

The following table shows ALGORITHM and LOCK clause support for each ALTER TABLE partitioning statement.

Table 15.11 ALGORITHM, LOCK Clause Support for ALTER TABLE Partitioning Clauses

Partitioning ClauseSupported ALGORITHM, LOCK Options
PARTITION BYALGORITHM=COPY, LOCK={SHARED|EXCLUSIVE}
ADD PARTITIONALGORITHM=DEFAULT, LOCK=DEFAULT
DROP PARTITIONALGORITHM=DEFAULT, LOCK=DEFAULT
DISCARD PARTITIONALGORITHM=DEFAULT, LOCK=DEFAULT
IMPORT PARTITIONALGORITHM=DEFAULT, LOCK=DEFAULT
TRUNCATE PARTITIONALGORITHM=INPLACE, LOCK={NONE|SHARED|EXCLUSIVE} or ALGORITHM=COPY, LOCK={SHARED|EXCLUSIVE}
COALESCE PARTITIONALGORITHM=DEFAULT, LOCK=DEFAULT
REORGANIZE PARTITIONALGORITHM=DEFAULT, LOCK=DEFAULT
EXCHANGE PARTITIONALGORITHM=INPLACE, LOCK={NONE|SHARED|EXCLUSIVE} or ALGORITHM=COPY, LOCK={SHARED|EXCLUSIVE}
ANALYZE PARTITIONALGORITHM=INPLACE, LOCK={NONE|SHARED|EXCLUSIVE} or ALGORITHM=COPY, LOCK={SHARED|EXCLUSIVE}
CHECK PARTITIONALGORITHM=INPLACE, LOCK={NONE|SHARED|EXCLUSIVE} or ALGORITHM=COPY, LOCK={SHARED|EXCLUSIVE}
OPTIMIZE PARTITIONALGORITHM and LOCK clauses are ignored. Rebuilds the entire table. See Section 21.3.4, “Maintenance of Partitions”.
REBUILD PARTITIONALGORITHM=DEFAULT, LOCK=DEFAULT
REPAIR PARTITIONALGORITHM=INPLACE, LOCK={NONE|SHARED|EXCLUSIVE} or ALGORITHM=COPY, LOCK={SHARED|EXCLUSIVE}
REMOVE PARTITIONINGALGORITHM=COPY, LOCK={SHARED|EXCLUSIVE}

ALTER TABLE partitioning options that use ALGORITHM=COPY or only permit ALGORITHM=DEFAULT, LOCK=DEFAULT repartition the table using the COPY algorithm. In other words, a new partitioned table is created with the new partitioning scheme. The newly created table includes any changes applied by the ALTER TABLE statement and the table data is copied into the new table structure.

If you do not modify table partitioning using ALTER TABLE partitioning clauses that use the COPY algorithm or perform any other partition management in your ALTER TABLE statement, ALTER TABLE performs supported INPLACE operations on each table partition. Be aware, however, that when INPLACE ALTER TABLE operations are performed on each partition, there is increased demand on system resources due to operations being performed on multiple partitions.

Even though most ALTER TABLE partitioning clauses do not go through the same internal online DDL API as regular non-partitioned InnoDB tables, MySQL still attempts to minimize data copying and locking where possible:

  • ADD PARTITION and DROP PARTITION for tables partitioned by RANGE or LIST do not copy any existing data.

  • TRUNCATE PARTITION does not copy any existing data, for all types of partitioned tables.

  • Concurrent queries are permitted during ADD PARTITION and COALESCE PARTITION for tables partitioned by HASH or LIST. MySQL copies the data while holding a shared lock.

  • For REORGANIZE PARTITION, REBUILD PARTITION, or ADD PARTITION or COALESCE PARTITION for a table partitioned by LINEAR HASH or LIST, concurrent queries are permitted. Data from the affected partitions is copied while holding a shared metadata (read) lock at the table level.


User Comments
Sign Up Login You must be logged in to post a comment.