<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="structured-system-variables">
      </a>
      7.1.9.5 Structured System Variables
     </h4>
    </div>
   </div>
  </div>
  <p>
   A structured variable differs from a regular system variable in
        two respects:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Its value is a structure with components that specify server
            parameters considered to be closely related.
     </p>
    </li>
    <li class="listitem">
     <p>
      There might be several instances of a given type of
            structured variable. Each one has a different name and
            refers to a different resource maintained by the server.
     </p>
    </li>
   </ul>
  </div>
  <p>
   MySQL supports one structured variable type, which specifies
        parameters governing the operation of key caches. A key cache
        structured variable has these components:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_key_buffer_size">
       <code class="literal">
        key_buffer_size
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_key_cache_block_size">
       <code class="literal">
        key_cache_block_size
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_key_cache_division_limit">
       <code class="literal">
        key_cache_division_limit
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_key_cache_age_threshold">
       <code class="literal">
        key_cache_age_threshold
       </code>
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   This section describes the syntax for referring to structured
        variables. Key cache variables are used for syntax examples, but
        specific details about how key caches operate are found
        elsewhere, in
   <a class="xref" href="myisam-key-cache.html" title="10.10.2 The MyISAM Key Cache">
    Section 10.10.2, “The MyISAM Key Cache”
   </a>
   .
  </p>
  <p>
   To refer to a component of a structured variable instance, you
        can use a compound name in
   <em class="replaceable">
    <code>
     instance_name.component_name
    </code>
   </em>
   format.
        Examples:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa97238668"><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-simple">hot_cache<span class="token punctuation">.</span>key_buffer_size
hot_cache<span class="token punctuation">.</span>key_cache_block_size
cold_cache<span class="token punctuation">.</span>key_cache_block_size</code></pre>
  </div>
  <p>
   For each structured system variable, an instance with the name
        of
   <code class="literal">
    default
   </code>
   is always predefined. If you refer
        to a component of a structured variable without any instance
        name, the
   <code class="literal">
    default
   </code>
   instance is used. Thus,
   <code class="literal">
    default.key_buffer_size
   </code>
   and
   <a class="link" href="server-system-variables.html#sysvar_key_buffer_size">
    <code class="literal">
     key_buffer_size
    </code>
   </a>
   both refer to
        the same system variable.
  </p>
  <p>
   Structured variable instances and components follow these naming
        rules:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For a given type of structured variable, each instance must
            have a name that is unique
      <span class="emphasis">
       <em>
        within
       </em>
      </span>
      variables of that type. However, instance names need not be
            unique
      <span class="emphasis">
       <em>
        across
       </em>
      </span>
      structured variable
            types. For example, each structured variable has an instance
            named
      <code class="literal">
       default
      </code>
      , so
      <code class="literal">
       default
      </code>
      is not unique across variable
            types.
     </p>
    </li>
    <li class="listitem">
     <p>
      The names of the components of each structured variable type
            must be unique across all system variable names. If this
            were not true (that is, if two different types of structured
            variables could share component member names), it would not
            be clear which default structured variable to use for
            references to member names that are not qualified by an
            instance name.
     </p>
    </li>
    <li class="listitem">
     <p>
      If a structured variable instance name is not legal as an
            unquoted identifier, refer to it as a quoted identifier
            using backticks. For example,
      <code class="literal">
       hot-cache
      </code>
      is not legal, but
      <code class="literal">
       `hot-cache`
      </code>
      is.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       global
      </code>
      ,
      <code class="literal">
       session
      </code>
      , and
      <code class="literal">
       local
      </code>
      are not legal instance names. This
            avoids a conflict with notation such as
      <code class="literal">
       @@GLOBAL.
       <em class="replaceable">
        <code>
         var_name
        </code>
       </em>
      </code>
      for referring to nonstructured system variables.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Currently, the first two rules have no possibility of being
        violated because the only structured variable type is the one
        for key caches. These rules may assume greater significance if
        some other type of structured variable is created in the future.
  </p>
  <p>
   With one exception, you can refer to structured variable
        components using compound names in any context where simple
        variable names can occur. For example, you can assign a value to
        a structured variable using a command-line option:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa40014352"><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 prompt">$&gt; </span><span class="token command">mysqld</span> <span class="token constant">--hot_cache.key_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>64K</span></code></pre>
  </div>
  <p>
   In an option file, use this syntax:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa31782184"><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">hot_cache.key_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>64K</span></code></pre>
  </div>
  <p>
   If you start the server with this option, it creates a key cache
        named
   <code class="literal">
    hot_cache
   </code>
   with a size of 64KB in
        addition to the default key cache that has a default size of
        8MB.
  </p>
  <p>
   Suppose that you start the server as follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa19928456"><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 prompt">$&gt; </span><span class="token command">mysqld</span> <span class="token constant">--key_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>256K</span> \
         <span class="token constant">--extra_cache.key_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>128K</span> \
         <span class="token constant">--extra_cache.key_cache_block_size</span><span class="token attr-value"><span class="token punctuation">=</span>2048</span></code></pre>
  </div>
  <p>
   In this case, the server sets the size of the default key cache
        to 256KB. (You could also have written
   <code class="option">
    --default.key_buffer_size=256K
   </code>
   .) In addition,
        the server creates a second key cache named
   <code class="literal">
    extra_cache
   </code>
   that has a size of 128KB, with
        the size of block buffers for caching table index blocks set to
        2048 bytes.
  </p>
  <p>
   The following example starts the server with three different key
        caches having sizes in a 3:1:1 ratio:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa14940754"><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 prompt">$&gt; </span><span class="token command">mysqld</span> <span class="token constant">--key_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>6M</span> \
         <span class="token constant">--hot_cache.key_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>2M</span> \
         <span class="token constant">--cold_cache.key_buffer_size</span><span class="token attr-value"><span class="token punctuation">=</span>2M</span></code></pre>
  </div>
  <p>
   Structured variable values may be set and retrieved at runtime
        as well. For example, to set a key cache named
   <code class="literal">
    hot_cache
   </code>
   to a size of 10MB, use either of
        these statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa65343532"><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> hot_cache<span class="token punctuation">.</span>key_buffer_size <span class="token operator">=</span> <span class="token number">10</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 prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@@GLOBAL.hot_cache.key_buffer_size</span> <span class="token operator">=</span> <span class="token number">10</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></code></pre>
  </div>
  <p>
   To retrieve the cache size, do this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa20839712"><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 variable">@@GLOBAL.hot_cache.key_buffer_size</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   However, the following statement does not work. The variable is
        not interpreted as a compound name, but as a simple string for a
   <a class="link" href="string-comparison-functions.html#operator_like">
    <code class="literal">
     LIKE
    </code>
   </a>
   pattern-matching operation:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa29192983"><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">GLOBAL</span> <span class="token keyword">VARIABLES</span> <span class="token operator">LIKE</span> <span class="token string">'hot_cache.key_buffer_size'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This is the exception to being able to use structured variable
        names anywhere a simple variable name may occur.
  </p>
 </div>
 <br/>
</div>
