<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="repair-table-optimization">
      </a>
      10.6.3 Optimizing REPAIR TABLE Statements
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045225699328">
  </a>
  <p>
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR TABLE
    </code>
   </a>
   for
   <code class="literal">
    MyISAM
   </code>
   tables is similar to using
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   for repair operations, and some of
        the same performance optimizations apply:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk
        </strong>
       </span>
      </a>
      has variables that control
            memory allocation. You may be able to its improve
            performance by setting these variables, as described in
      <a class="xref" href="myisamchk-memory.html" title="6.6.4.6 myisamchk Memory Usage">
       Section 6.6.4.6, “myisamchk Memory Usage”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
       <code class="literal">
        REPAIR TABLE
       </code>
      </a>
      , the same
            principle applies, but because the repair is done by the
            server, you set server system variables instead of
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk
        </strong>
       </span>
      </a>
      variables. Also, in addition to
            setting memory-allocation variables, increasing the
      <a class="link" href="server-system-variables.html#sysvar_myisam_max_sort_file_size">
       <code class="literal">
        myisam_max_sort_file_size
       </code>
      </a>
      system variable increases the likelihood that the repair
            uses the faster filesort method and avoids the slower repair
            by key cache method. Set the variable to the maximum file
            size for your system, after checking to be sure that there
            is enough free space to hold a copy of the table files. The
            free space must be available in the file system containing
            the original table files.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Suppose that a
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   table-repair
        operation is done using the following options to set its
        memory-allocation variables:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa83959236"><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-terminal"><span class="token constant">--key_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>128M</span> <span class="token constant">--myisam_sort_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>256M</span>
<span class="token constant">--read_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>64M</span> <span class="token constant">--write_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>64M</span></code></pre>
  </div>
  <p>
   Some of those
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   variables correspond
        to server system variables:
  </p>
  <div class="informaltable">
   <table summary="myisamchk variables and corresponding server system variables.">
    <colgroup>
     <col style="width: 50%"/>
     <col style="width: 50%"/>
    </colgroup>
    <thead>
     <tr>
      <th>
       <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
        <span class="command">
         <strong>
          myisamchk
         </strong>
        </span>
       </a>
       Variable
      </th>
      <th>
       System Variable
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <td>
       <code class="literal">
        key_buffer_size
       </code>
      </td>
      <td>
       <a class="link" href="server-system-variables.html#sysvar_key_buffer_size">
        <code class="literal">
         key_buffer_size
        </code>
       </a>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        myisam_sort_buffer_size
       </code>
      </td>
      <td>
       <a class="link" href="server-system-variables.html#sysvar_myisam_sort_buffer_size">
        <code class="literal">
         myisam_sort_buffer_size
        </code>
       </a>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        read_buffer_size
       </code>
      </td>
      <td>
       <a class="link" href="server-system-variables.html#sysvar_read_buffer_size">
        <code class="literal">
         read_buffer_size
        </code>
       </a>
      </td>
     </tr>
     <tr>
      <td>
       <code class="literal">
        write_buffer_size
       </code>
      </td>
      <td>
       none
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <p>
   Each of the server system variables can be set at runtime, and
        some of them
        (
   <a class="link" href="server-system-variables.html#sysvar_myisam_sort_buffer_size">
    <code class="literal">
     myisam_sort_buffer_size
    </code>
   </a>
   ,
   <a class="link" href="server-system-variables.html#sysvar_read_buffer_size">
    <code class="literal">
     read_buffer_size
    </code>
   </a>
   ) have a
        session value in addition to a global value. Setting a session
        value limits the effect of the change to your current session
        and does not affect other users. Changing a global-only variable
        (
   <a class="link" href="server-system-variables.html#sysvar_key_buffer_size">
    <code class="literal">
     key_buffer_size
    </code>
   </a>
   ,
   <a class="link" href="server-system-variables.html#sysvar_myisam_max_sort_file_size">
    <code class="literal">
     myisam_max_sort_file_size
    </code>
   </a>
   )
        affects other users as well. For
   <a class="link" href="server-system-variables.html#sysvar_key_buffer_size">
    <code class="literal">
     key_buffer_size
    </code>
   </a>
   , you must take
        into account that the buffer is shared with those users. For
        example, if you set the
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   <code class="literal">
    key_buffer_size
   </code>
   variable to 128MB, you could
        set the corresponding
   <a class="link" href="server-system-variables.html#sysvar_key_buffer_size">
    <code class="literal">
     key_buffer_size
    </code>
   </a>
   system variable
        larger than that (if it is not already set larger), to permit
        key buffer use by activity in other sessions. However, changing
        the global key buffer size invalidates the buffer, causing
        increased disk I/O and slowdown for other sessions. An
        alternative that avoids this problem is to use a separate key
        cache, assign to it the indexes from the table to be repaired,
        and deallocate it when the repair is complete. See
   <a class="xref" href="multiple-key-caches.html" title="10.10.2.2 Multiple Key Caches">
    Section 10.10.2.2, “Multiple Key Caches”
   </a>
   .
  </p>
  <p>
   Based on the preceding remarks, a
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR
        TABLE
    </code>
   </a>
   operation can be done as follows to use settings
        similar to the
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   command. Here a
        separate 128MB key buffer is allocated and the file system is
        assumed to permit a file size of at least 100GB.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89771036"><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">SET</span> <span class="token keyword">SESSION</span> myisam_sort_buffer_size <span class="token operator">=</span> <span class="token number">256</span><span class="token operator">*</span><span class="token number">1024</span><span class="token operator">*</span><span class="token number">1024</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">SESSION</span> read_buffer_size <span class="token operator">=</span> <span class="token number">64</span><span class="token operator">*</span><span class="token number">1024</span><span class="token operator">*</span><span class="token number">1024</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> myisam_max_sort_file_size <span class="token operator">=</span> <span class="token number">100</span><span class="token operator">*</span><span class="token number">1024</span><span class="token operator">*</span><span class="token number">1024</span><span class="token operator">*</span><span class="token number">1024</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> repair_cache<span class="token punctuation">.</span>key_buffer_size <span class="token operator">=</span> <span class="token number">128</span><span class="token operator">*</span><span class="token number">1024</span><span class="token operator">*</span><span class="token number">1024</span><span class="token punctuation">;</span>
<span class="token keyword">CACHE</span> <span class="token keyword">INDEX</span> <em class="replaceable">tbl_name</em> <span class="token keyword">IN</span> repair_cache<span class="token punctuation">;</span>
<span class="token keyword">LOAD</span> <span class="token keyword">INDEX</span> <span class="token keyword">INTO</span> <span class="token keyword">CACHE</span> <em class="replaceable">tbl_name</em><span class="token punctuation">;</span>
<span class="token keyword">REPAIR</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> repair_cache<span class="token punctuation">.</span>key_buffer_size <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If you intend to change a global variable but want to do so only
        for the duration of a
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR
        TABLE
    </code>
   </a>
   operation to minimally affect other users, save
        its value in a user variable and restore it afterward. For
        example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72544093"><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">SET</span> <span class="token variable">@old_myisam_sort_buffer_size</span> <span class="token operator">=</span> <span class="token variable">@@GLOBAL.myisam_max_sort_file_size</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> myisam_max_sort_file_size <span class="token operator">=</span> <span class="token number">100</span><span class="token operator">*</span><span class="token number">1024</span><span class="token operator">*</span><span class="token number">1024</span><span class="token operator">*</span><span class="token number">1024</span><span class="token punctuation">;</span>
<span class="token keyword">REPAIR</span> <span class="token keyword">TABLE</span> tbl_name <span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> myisam_max_sort_file_size <span class="token operator">=</span> <span class="token variable">@old_myisam_max_sort_file_size</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The system variables that affect
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR
        TABLE
    </code>
   </a>
   can be set globally at server startup if you
        want the values to be in effect by default. For example, add
        these lines to the server
   <code class="filename">
    my.cnf
   </code>
   file:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa4239659"><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-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">myisam_sort_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>256M</span>
<span class="token constant">key_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>1G</span>
<span class="token constant">myisam_max_sort_file_size</span><span class="token attr-value"><span class="token punctuation">=</span>100G</span></code></pre>
  </div>
  <p>
   These settings do not include
   <a class="link" href="server-system-variables.html#sysvar_read_buffer_size">
    <code class="literal">
     read_buffer_size
    </code>
   </a>
   . Setting
   <a class="link" href="server-system-variables.html#sysvar_read_buffer_size">
    <code class="literal">
     read_buffer_size
    </code>
   </a>
   globally to a
        large value does so for all sessions and can cause performance
        to suffer due to excessive memory allocation for a server with
        many simultaneous sessions.
  </p>
 </div>
 <br/>
</div>
