<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="innodb-online-ddl">
      </a>
      17.12 InnoDB and Online DDL
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="innodb-online-ddl-operations.html">
       17.12.1 Online DDL Operations
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="innodb-online-ddl-performance.html">
       17.12.2 Online DDL Performance and Concurrency
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="innodb-online-ddl-space-requirements.html">
       17.12.3 Online DDL Space Requirements
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="online-ddl-memory-management.html">
       17.12.4 Online DDL Memory Management
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="online-ddl-parallel-thread-configuration.html">
       17.12.5 Configuring Parallel Threads for Online DDL Operations
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="innodb-online-ddl-single-multi.html">
       17.12.6 Simplifying DDL Statements with Online DDL
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="innodb-online-ddl-failure-conditions.html">
       17.12.7 Online DDL Failure Conditions
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="innodb-online-ddl-limitations.html">
       17.12.8 Online DDL Limitations
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045160573136">
  </a>
  <a class="indexterm" name="idm46045160572064">
  </a>
  <p>
   The online DDL feature provides support for instant and in-place
    table alterations and concurrent DML. Benefits of this feature
    include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Improved responsiveness and availability in busy production
        environments, where making a table unavailable for minutes or
        hours is not practical.
     </p>
    </li>
    <li class="listitem">
     <p>
      For in-place operations, the ability to adjust the balance
        between performance and concurrency during DDL operations using
        the
      <code class="literal">
       LOCK
      </code>
      clause. See
      <a class="xref" href="innodb-online-ddl-performance.html#innodb-online-ddl-locking-options" title="The LOCK clause">
       The LOCK clause
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Less disk space usage and I/O overhead than the table-copy
        method.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Typically, you do not need to do anything special to enable online
    DDL. By default, MySQL performs the operation instantly or in place,
    as permitted, with as little locking as possible.
  </p>
  <p>
   You can control aspects of a DDL operation using the
   <code class="literal">
    ALGORITHM
   </code>
   and
   <code class="literal">
    LOCK
   </code>
   clauses of
    the
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement. These
    clauses are placed at the end of the statement, separated from the
    table and column specifications by commas. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa61546769"><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 keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">ADD</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token keyword"><em class="replaceable">column</em></span><span class="token punctuation">)</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></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    LOCK
   </code>
   clause may be used for operations that
    are performed in place and is useful for fine-tuning the degree of
    concurrent access to the table during operations. Only
   <code class="literal">
    LOCK=DEFAULT
   </code>
   is supported for operations that are
    performed instantly. The
   <code class="literal">
    ALGORITHM
   </code>
   clause is
    primarily intended for performance comparisons and as a fallback to
    the older table-copying behavior in case you encounter any issues.
    For example:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      To avoid accidentally making the table unavailable for reads,
        writes, or both, during an in-place
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER
        TABLE
       </code>
      </a>
      operation, specify a clause on the
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statement such as
      <code class="literal">
       LOCK=NONE
      </code>
      (permit reads and writes) or
      <code class="literal">
       LOCK=SHARED
      </code>
      (permit reads). The operation
        halts immediately if the requested level of concurrency is not
        available.
     </p>
    </li>
    <li class="listitem">
     <p>
      To compare performance between algorithms, run a statement with
      <code class="literal">
       ALGORITHM=INSTANT
      </code>
      ,
      <code class="literal">
       ALGORITHM=INPLACE
      </code>
      and
      <code class="literal">
       ALGORITHM=COPY
      </code>
      . You can also run a statement
        with the
      <a class="link" href="server-system-variables.html#sysvar_old_alter_table">
       <code class="literal">
        old_alter_table
       </code>
      </a>
      configuration option enabled to force the use of
      <code class="literal">
       ALGORITHM=COPY
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      To avoid tying up the server with an
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER
        TABLE
       </code>
      </a>
      operation that copies the table, include
      <code class="literal">
       ALGORITHM=INSTANT
      </code>
      or
      <code class="literal">
       ALGORITHM=INPLACE
      </code>
      . The statement halts
        immediately if it cannot use the specified algorithm.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
