Performance of In

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 applies to 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 call 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 ALGORITHM=INPLACE, even though the data is still
copied, it is more 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=1 in the session, and measure DDL performance to record the “before” figures

(…)

… operations.
The performance speedup from in-place DDL applies to 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 call an “index-organized table”. Because the table
structure is so closely tied to the primary key, redefining the primary key still requires copying…
... zobacz całą notatkę