79581

Performance of In-Place versus Table-Copying DDL Operations

The raw performance of an Online DDL operation is largely determined by whether the operation is

performed in-place, or requires copying and rebuilding the entire table. See Table 5.9, “Summary of

Online Status for DDL Operations” to see what kinds of operations can

be performed in-place, and any

requirements for avoiding table-copy operations.

The performance speedup from in-place DDL appliesto operations on secondary indexes, not to the

primary key index. The rows of an InnoDB table are stored in a clustered index organized based on the

primary key, forming what some database systems cali an “index-organized table". Because the table

structure is so closely tied to the primary key, redefining the primary key still requires copying the data.

When an operation on the primary key uses ALGORlTHM=lNPLACE, even though the data is still

copied, it is morę efficient than using algorithm=COPY because:

•    No undo logging or associated redo logging is required for algorithm=INplace. These operations

add overhead to DDL statements that use algorithm=copy.

•    The secondary index entries are pre-sorted, and so can be loaded in order.

•    The change buffer is not used, because there are no random-access inserts into the secondary

indexes.

To judge the relative performance of Online DDL operations, you can run such operations on a big

InnoDB table using current and earlier versions of MySQL. You can also run all the performance

tests under the latest MySQL version, simulating the previous DDL behavior for the “before”

results, by setting the old_alter_table [531] system variable. Issue the statement set

old_alter_table=l in the session, and measure DDL performance to record the “before” figures