content_image/810879.jpg
Metadata lock information is also exposed through the Performance Schema metadata_locks  table, which provides information about metadata lock dependencies between sessions, the metadata lock a session is waiting for, and the session that currently holds the metadata lock. For more information, see Section 27.12.13.3, “The metadata_locks Table” . 

## Online DDL Performance 

The performance of a DDL operation is largely determined by whether the operation is performed instantly, in place, and whether it rebuilds the table. 

To assess the relative performance of a DDL operation, you can compare results using ALGORITHM=INSTANT , ALGORITHM=INPLACE , and ALGORITHM=COPY . A statement can also be run with old_alter_table  enabled to force the use of ALGORITHM=COPY . 

For DDL operations that modify table data, you can determine whether a DDL operation performs changes in place or performs a table copy by looking at the “rows affected”  value displayed after the command finishes. For example: 

•Changing the default value of a column (fast, does not affect the table data): 

Query OK, 0 rows affected (0.07 sec) 

•Adding an index (takes time, but 0 rows affected  shows that the table is not copied): 

Query OK, 0 rows affected (21.42 sec) 

•Changing the data type of a column (takes substantial time and requires rebuilding all the rows of the table): 

Query OK, 1671168 rows affected (1 min 35.54 sec) 

Before running a DDL operation on a large table, check whether the operation is fast or slow as follows: 

1. Clone the table structure. 

2. Populate the cloned table with a small amount of data. 

3. Run the DDL operation on the cloned table. 

4. Check whether the “rows affected”  value is zero or not. A nonzero value means the operation copies table data, which might require special planning. For example, you might do the DDL operation during a period of scheduled downtime, or on each replica server one at a time. 

<img src='content_image/810879.jpg'>

## Note 

For a greater understanding of the MySQL processing associated with a DDL operation, examine Performance Schema and INFORMATION_SCHEMA  tables related to InnoDB  before and after DDL operations to see the number of physical reads, writes, memory allocations, and so on. 

Performance Schema stage events can be used to monitor ALTER TABLE progress. See Section 15.16.1, “Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema” . 

Because there is some processing work involved with recording the changes made by concurrent DML operations, then applying those changes at the end, an online DDL operation could take longer overall than the table-copy mechanism that blocks table access from other sessions. The reduction in raw performance is balanced against better responsiveness for applications that use the table. When evaluating the 

