<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-online-ddl-performance">
      </a>
      17.12.2 Online DDL Performance and Concurrency
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045159629648">
  </a>
  <p>
   Online DDL improves several aspects of MySQL operation:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Applications that access the table are more responsive because
          queries and DML operations on the table can proceed while the
          DDL operation is in progress. Reduced locking and waiting for
          MySQL server resources leads to greater scalability, even for
          operations that are not involved in the DDL operation.
     </p>
    </li>
    <li class="listitem">
     <p>
      Instant operations only modify metadata in the data
          dictionary. An exclusive metadata lock on the table may be
          taken briefly during the execution phase of the operation.
          Table data is unaffected, making operations instantaneous.
          Concurrent DML is permitted.
     </p>
    </li>
    <li class="listitem">
     <p>
      Online operations avoid the disk I/O and CPU cycles associated
          with the table-copy method, which minimizes overall load on
          the database. Minimizing load helps maintain good performance
          and high throughput during the DDL operation.
     </p>
    </li>
    <li class="listitem">
     <p>
      Online operations read less data into the buffer pool than
          table-copy operations, which reduces purging of frequently
          accessed data from memory. Purging of frequently accessed data
          can cause a temporary performance dip after a DDL operation.
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-online-ddl-locking-options">
       </a>
       The LOCK clause
      </h4>
     </div>
    </div>
   </div>
   <p>
    By default, MySQL uses as little locking as possible during a
        DDL operation. The
    <code class="literal">
     LOCK
    </code>
    clause can be
        specified for in-place operations and some copy operations to
        enforce more restrictive locking, if required. If the
    <code class="literal">
     LOCK
    </code>
    clause specifies a less restrictive
        level of locking than is permitted for a particular DDL
        operation, the statement fails with an error.
    <code class="literal">
     LOCK
    </code>
    clauses are described below, in order of
        least to most restrictive:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        LOCK=NONE
       </code>
       :
      </p>
      <p>
       Permits concurrent queries and DML.
      </p>
      <p>
       For example, use this clause for tables involving customer
            signups or purchases, to avoid making the tables unavailable
            during lengthy DDL operations.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        LOCK=SHARED
       </code>
       :
      </p>
      <p>
       Permits concurrent queries but blocks DML.
      </p>
      <p>
       For example, use this clause on data warehouse tables, where
            you can delay data load operations until the DDL operation
            is finished, but queries cannot be delayed for long periods.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        LOCK=DEFAULT
       </code>
       :
      </p>
      <p>
       Permits as much concurrency as possible (concurrent queries,
            DML, or both). Omitting the
       <code class="literal">
        LOCK
       </code>
       clause
            is the same as specifying
       <code class="literal">
        LOCK=DEFAULT
       </code>
       .
      </p>
      <p>
       Use this clause when you do not expect the default locking
            level of the DDL statement to cause any availability
            problems for the table.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        LOCK=EXCLUSIVE
       </code>
       :
      </p>
      <p>
       Blocks concurrent queries and DML.
      </p>
      <p>
       Use this clause if the primary concern is finishing the DDL
            operation in the shortest amount of time possible, and
            concurrent query and DML access is not necessary. You might
            also use this clause if the server is supposed to be idle,
            to avoid unexpected table accesses.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-online-ddl-metadata-locks">
       </a>
       Online DDL and Metadata Locks
      </h4>
     </div>
    </div>
   </div>
   <p>
    Online DDL operations can be viewed as having three phases:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <span class="emphasis">
        <em>
         Phase 1: Initialization
        </em>
       </span>
      </p>
      <p>
       In the initialization phase, the server determines how much
            concurrency is permitted during the operation, taking into
            account storage engine capabilities, operations specified in
            the statement, and user-specified
       <code class="literal">
        ALGORITHM
       </code>
       and
       <code class="literal">
        LOCK
       </code>
       options. During this phase, a shared upgradeable metadata
            lock is taken to protect the current table definition.
      </p>
     </li>
     <li class="listitem">
      <p>
       <span class="emphasis">
        <em>
         Phase 2: Execution
        </em>
       </span>
      </p>
      <p>
       In this phase, the statement is prepared and executed.
            Whether the metadata lock is upgraded to exclusive depends
            on the factors assessed in the initialization phase. If an
            exclusive metadata lock is required, it is only taken
            briefly during statement preparation.
      </p>
     </li>
     <li class="listitem">
      <p>
       <span class="emphasis">
        <em>
         Phase 3: Commit Table Definition
        </em>
       </span>
      </p>
      <p>
       In the commit table definition phase, the metadata lock is
            upgraded to exclusive to evict the old table definition and
            commit the new one. Once granted, the duration of the
            exclusive metadata lock is brief.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Due to the exclusive metadata lock requirements outlined above,
        an online DDL operation may have to wait for concurrent
        transactions that hold metadata locks on the table to commit or
        rollback. Transactions started before or during the DDL
        operation can hold metadata locks on the table being altered. In
        the case of a long running or inactive transaction, an online
        DDL operation can time out waiting for an exclusive metadata
        lock. Additionally, a pending exclusive metadata lock requested
        by an online DDL operation blocks subsequent transactions on the
        table.
   </p>
   <p>
    The following example demonstrates an online DDL operation
        waiting for an exclusive metadata lock, and how a pending
        metadata lock blocks subsequent transactions on the table.
   </p>
   <p>
    Session 1:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46055172"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The session 1
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statement
        takes a shared metadata lock on table t1.
   </p>
   <p>
    Session 2:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa21226582"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">COLUMN</span> x <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The online DDL operation in session 2, which requires an
        exclusive metadata lock on table t1 to commit table definition
        changes, must wait for the session 1 transaction to commit or
        roll back.
   </p>
   <p>
    Session 3:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa38507094"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statement issued in
        session 3 is blocked waiting for the exclusive metadata lock
        requested by the
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    operation in session 2 to be granted.
   </p>
   <p>
    You can use
    <a class="link" href="show-processlist.html" title="15.7.7.31 SHOW PROCESSLIST Statement">
     <code class="literal">
      SHOW FULL
        PROCESSLIST
     </code>
    </a>
    to determine if transactions are waiting
        for a metadata lock.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17736453"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">FULL</span> <span class="token keyword">PROCESSLIST</span>\G
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 2. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
     Id<span class="token punctuation">:</span> 5
   User<span class="token punctuation">:</span> root
   Host<span class="token punctuation">:</span> localhost
     db<span class="token punctuation">:</span> test
Command<span class="token punctuation">:</span> Query
   Time<span class="token punctuation">:</span> 44
  State<span class="token punctuation">:</span> Waiting for table metadata lock
   Info<span class="token punctuation">:</span> ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE
...
<span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 4. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
     Id<span class="token punctuation">:</span> 7
   User<span class="token punctuation">:</span> root
   Host<span class="token punctuation">:</span> localhost
     db<span class="token punctuation">:</span> test
Command<span class="token punctuation">:</span> Query
   Time<span class="token punctuation">:</span> 5
  State<span class="token punctuation">:</span> Waiting for table metadata lock
   Info<span class="token punctuation">:</span> SELECT <span class="token punctuation">*</span> FROM t1
</span><span class="token output">4 rows in set (0.00 sec)</span></code></pre>
   </div>
   <p>
    Metadata lock information is also exposed through the
        Performance Schema
    <a class="link" href="performance-schema-metadata-locks-table.html" title="29.12.13.3 The metadata_locks Table">
     <code class="literal">
      metadata_locks
     </code>
    </a>
    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
    <a class="xref" href="performance-schema-metadata-locks-table.html" title="29.12.13.3 The metadata_locks Table">
     Section 29.12.13.3, “The metadata_locks Table”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="innodb-online-ddl-performance-comparison">
       </a>
       Online DDL Performance
      </h4>
     </div>
    </div>
   </div>
   <p>
    The performance of a DDL operation is largely determined by
        whether the operation is performed instantly, in place, and
        whether it rebuilds the table.
   </p>
   <p>
    To assess the relative performance of a DDL operation, you can
        compare results using
    <code class="literal">
     ALGORITHM=INSTANT
    </code>
    ,
    <code class="literal">
     ALGORITHM=INPLACE
    </code>
    , and
    <code class="literal">
     ALGORITHM=COPY
    </code>
    . A statement can also be run
        with
    <a class="link" href="server-system-variables.html#sysvar_old_alter_table">
     <code class="literal">
      old_alter_table
     </code>
    </a>
    enabled to
        force the use of
    <code class="literal">
     ALGORITHM=COPY
    </code>
    .
   </p>
   <p>
    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
    <span class="quote">
     “
     <span class="quote">
      rows affected
     </span>
     ”
    </span>
    value
        displayed after the command finishes. For example:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Changing the default value of a column (fast, does not
            affect the table data):
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa79954440"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token output">Query OK, 0 rows affected (0.07 sec)</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Adding an index (takes time, but
       <code class="literal">
        0 rows
            affected
       </code>
       shows that the table is not copied):
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa85596302"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token output">Query OK, 0 rows affected (21.42 sec)</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Changing the data type of a column (takes substantial time
            and requires rebuilding all the rows of the table):
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa77001594"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token output">Query OK, 1671168 rows affected (1 min 35.54 sec)</span></code></pre>
      </div>
     </li>
    </ul>
   </div>
   <p>
    Before running a DDL operation on a large table, check whether
        the operation is fast or slow as follows:
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       Clone the table structure.
      </p>
     </li>
     <li class="listitem">
      <p>
       Populate the cloned table with a small amount of data.
      </p>
     </li>
     <li class="listitem">
      <p>
       Run the DDL operation on the cloned table.
      </p>
     </li>
     <li class="listitem">
      <p>
       Check whether the
       <span class="quote">
        “
        <span class="quote">
         rows affected
        </span>
        ”
       </span>
       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.
      </p>
     </li>
    </ol>
   </div>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     For a greater understanding of the MySQL processing associated
          with a DDL operation, examine Performance Schema and
     <code class="literal">
      INFORMATION_SCHEMA
     </code>
     tables related to
     <code class="literal">
      InnoDB
     </code>
     before and after DDL operations to
          see the number of physical reads, writes, memory allocations,
          and so on.
    </p>
    <p>
     Performance Schema stage events can be used to monitor
     <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
      <code class="literal">
       ALTER TABLE
      </code>
     </a>
     progress. See
     <a class="xref" href="monitor-alter-table-performance-schema.html" title="17.16.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema">
      Section 17.16.1, “Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance
      Schema”
     </a>
     .
    </p>
   </div>
   <p>
    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 techniques for changing table
        structure, consider end-user perception of performance, based on
        factors such as load times for web pages.
   </p>
  </div>
 </div>
 <br/>
</div>
