<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="multiple-key-caches">
      </a>
      10.10.2.2 Multiple Key Caches
     </h4>
    </div>
   </div>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    As of MySQL 8.4, the compound-part
            structured-variable syntax discussed here for referring to
            multiple
    <code class="literal">
     MyISAM
    </code>
    key caches is deprecated.
   </p>
  </div>
  <p>
   Shared access to the key cache improves performance but does
          not eliminate contention among sessions entirely. They still
          compete for control structures that manage access to the key
          cache buffers. To reduce key cache access contention further,
          MySQL also provides multiple key caches. This feature enables
          you to assign different table indexes to different key caches.
  </p>
  <p>
   Where there are multiple key caches, the server must know
          which cache to use when processing queries for a given
   <code class="literal">
    MyISAM
   </code>
   table. By default, all
   <code class="literal">
    MyISAM
   </code>
   table indexes are cached in the
          default key cache. To assign table indexes to a specific key
          cache, use the
   <a class="link" href="cache-index.html" title="15.7.8.2 CACHE INDEX Statement">
    <code class="literal">
     CACHE INDEX
    </code>
   </a>
   statement (see
   <a class="xref" href="cache-index.html" title="15.7.8.2 CACHE INDEX Statement">
    Section 15.7.8.2, “CACHE INDEX Statement”
   </a>
   ). For example,
          the following statement assigns indexes from the tables
   <code class="literal">
    t1
   </code>
   ,
   <code class="literal">
    t2
   </code>
   , and
   <code class="literal">
    t3
   </code>
   to the key cache named
   <code class="literal">
    hot_cache
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28579336"><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">CACHE</span> <span class="token keyword">INDEX</span> t1<span class="token punctuation">,</span> t2<span class="token punctuation">,</span> t3 <span class="token keyword">IN</span> hot_cache<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><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></span>
<span class="token output"><span class="token punctuation">|</span> Table   <span class="token punctuation">|</span> Op                 <span class="token punctuation">|</span> Msg_type <span class="token punctuation">|</span> Msg_text <span class="token punctuation">|</span></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><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></span>
<span class="token output"><span class="token punctuation">|</span> test.t1 <span class="token punctuation">|</span> assign_to_keycache <span class="token punctuation">|</span> status   <span class="token punctuation">|</span> OK       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> test.t2 <span class="token punctuation">|</span> assign_to_keycache <span class="token punctuation">|</span> status   <span class="token punctuation">|</span> OK       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> test.t3 <span class="token punctuation">|</span> assign_to_keycache <span class="token punctuation">|</span> status   <span class="token punctuation">|</span> OK       <span class="token punctuation">|</span></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><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></span></code></pre>
  </div>
  <p>
   The key cache referred to in a
   <a class="link" href="cache-index.html" title="15.7.8.2 CACHE INDEX Statement">
    <code class="literal">
     CACHE
          INDEX
    </code>
   </a>
   statement can be created by setting its size
          with a
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
          GLOBAL
    </code>
   </a>
   parameter setting statement or by using
          server startup options. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa93282367"><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">SET</span> <span class="token keyword">GLOBAL</span> keycache1<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 punctuation">;</span></code></pre>
  </div>
  <p>
   To destroy a key cache, set its size to zero:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa90103379"><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">SET</span> <span class="token keyword">GLOBAL</span> keycache1<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>
   You cannot destroy the default key cache. Any attempt to do
          this is ignored:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa4638184"><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">SET</span> <span class="token keyword">GLOBAL</span> key_buffer_size <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">VARIABLES</span> <span class="token operator">LIKE</span> <span class="token string">'key_buffer_size'</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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Variable_name   <span class="token punctuation">|</span> Value   <span class="token punctuation">|</span></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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> key_buffer_size <span class="token punctuation">|</span> 8384512 <span class="token punctuation">|</span></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><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Key cache variables are structured system variables that have
          a name and components. For
   <code class="literal">
    keycache1.key_buffer_size
   </code>
   ,
   <code class="literal">
    keycache1
   </code>
   is the cache variable name and
   <a class="link" href="server-system-variables.html#sysvar_key_buffer_size">
    <code class="literal">
     key_buffer_size
    </code>
   </a>
   is the cache
          component. See
   <a class="xref" href="structured-system-variables.html" title="7.1.9.5 Structured System Variables">
    Section 7.1.9.5, “Structured System Variables”
   </a>
   ,
          for a description of the syntax used for referring to
          structured key cache system variables.
  </p>
  <p>
   By default, table indexes are assigned to the main (default)
          key cache created at the server startup. When a key cache is
          destroyed, all indexes assigned to it are reassigned to the
          default key cache.
  </p>
  <p>
   For a busy server, you can use a strategy that involves three
          key caches:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A
      <span class="quote">
       “
       <span class="quote">
        hot
       </span>
       ”
      </span>
      key cache that takes up 20% of the
              space allocated for all key caches. Use this for tables
              that are heavily used for searches but that are not
              updated.
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <span class="quote">
       “
       <span class="quote">
        cold
       </span>
       ”
      </span>
      key cache that takes up 20% of the
              space allocated for all key caches. Use this cache for
              medium-sized, intensively modified tables, such as
              temporary tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <span class="quote">
       “
       <span class="quote">
        warm
       </span>
       ”
      </span>
      key cache that takes up 60% of the
              key cache space. Employ this as the default key cache, to
              be used by default for all other tables.
     </p>
    </li>
   </ul>
  </div>
  <p>
   One reason the use of three key caches is beneficial is that
          access to one key cache structure does not block access to the
          others. Statements that access tables assigned to one cache do
          not compete with statements that access tables assigned to
          another cache. Performance gains occur for other reasons as
          well:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The hot cache is used only for retrieval queries, so its
              contents are never modified. Consequently, whenever an
              index block needs to be pulled in from disk, the contents
              of the cache block chosen for replacement need not be
              flushed first.
     </p>
    </li>
    <li class="listitem">
     <p>
      For an index assigned to the hot cache, if there are no
              queries requiring an index scan, there is a high
              probability that the index blocks corresponding to nonleaf
              nodes of the index B-tree remain in the cache.
     </p>
    </li>
    <li class="listitem">
     <p>
      An update operation most frequently executed for temporary
              tables is performed much faster when the updated node is
              in the cache and need not be read from disk first. If the
              size of the indexes of the temporary tables are comparable
              with the size of cold key cache, the probability is very
              high that the updated node is in the cache.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <a class="link" href="cache-index.html" title="15.7.8.2 CACHE INDEX Statement">
    <code class="literal">
     CACHE INDEX
    </code>
   </a>
   statement sets
          up an association between a table and a key cache, but the
          association is lost each time the server restarts. If you want
          the association to take effect each time the server starts,
          one way to accomplish this is to use an option file: Include
          variable settings that configure your key caches, and an
   <a class="link" href="server-system-variables.html#sysvar_init_file">
    <code class="literal">
     init_file
    </code>
   </a>
   system variable
          that names a file containing
   <a class="link" href="cache-index.html" title="15.7.8.2 CACHE INDEX Statement">
    <code class="literal">
     CACHE
          INDEX
    </code>
   </a>
   statements to be executed. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa23052202"><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 constant">key_buffer_size</span> <span class="token attr-value"><span class="token punctuation">=</span> 4G</span>
<span class="token constant">hot_cache.key_buffer_size</span> <span class="token attr-value"><span class="token punctuation">=</span> 2G</span>
<span class="token constant">cold_cache.key_buffer_size</span> <span class="token attr-value"><span class="token punctuation">=</span> 2G</span>
<span class="token constant">init_file</span><span class="token attr-value"><span class="token punctuation">=</span>/<em class="replaceable">path</em>/<em class="replaceable">to</em>/<em class="replaceable">data-directory</em>/mysqld_init.sql</span></code></pre>
  </div>
  <p>
   The statements in
   <code class="filename">
    mysqld_init.sql
   </code>
   are
          executed each time the server starts. The file should contain
          one SQL statement per line. The following example assigns
          several tables each to
   <code class="literal">
    hot_cache
   </code>
   and
   <code class="literal">
    cold_cache
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa87934981"><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">CACHE</span> <span class="token keyword">INDEX</span> db1<span class="token punctuation">.</span>t1<span class="token punctuation">,</span> db1<span class="token punctuation">.</span>t2<span class="token punctuation">,</span> db2<span class="token punctuation">.</span>t3 <span class="token keyword">IN</span> hot_cache
<span class="token keyword">CACHE</span> <span class="token keyword">INDEX</span> db1<span class="token punctuation">.</span>t4<span class="token punctuation">,</span> db2<span class="token punctuation">.</span>t5<span class="token punctuation">,</span> db2<span class="token punctuation">.</span>t6 <span class="token keyword">IN</span> cold_cache</code></pre>
  </div>
 </div>
 <br/>
</div>
