<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="memory-use">
      </a>
      10.12.3.1 How MySQL Uses Memory
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045222344288">
  </a>
  <p>
   MySQL allocates buffers and caches to improve performance of
          database operations. The default configuration is designed to
          permit a MySQL server to start on a virtual machine that has
          approximately 512MB of RAM. You can improve MySQL performance
          by increasing the values of certain cache and buffer-related
          system variables. You can also modify the default
          configuration to run MySQL on systems with limited memory.
  </p>
  <p>
   The following list describes some of the ways that MySQL uses
          memory. Where applicable, relevant system variables are
          referenced. Some items are storage engine or feature specific.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <code class="literal">
       InnoDB
      </code>
      buffer pool is a memory area
              that holds cached
      <code class="literal">
       InnoDB
      </code>
      data for
              tables, indexes, and other auxiliary buffers. For
              efficiency of high-volume read operations, the buffer pool
              is divided into
      <a class="link" href="glossary.html#glos_page" title="page">
       pages
      </a>
      that can potentially hold multiple rows. For efficiency of
              cache management, the buffer pool is implemented as a
              linked list of pages; data that is rarely used is aged out
              of the cache, using a variation of the
      <a class="link" href="glossary.html#glos_lru" title="LRU">
       LRU
      </a>
      algorithm. For more
              information, see
      <a class="xref" href="innodb-buffer-pool.html" title="17.5.1 Buffer Pool">
       Section 17.5.1, “Buffer Pool”
      </a>
      .
     </p>
     <p>
      The size of the buffer pool is important for system
              performance:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <code class="literal">
          InnoDB
         </code>
         allocates memory for the
                  entire buffer pool at server startup, using
         <code class="literal">
          malloc()
         </code>
         operations. The
         <a class="link" href="innodb-parameters.html#sysvar_innodb_buffer_pool_size">
          <code class="literal">
           innodb_buffer_pool_size
          </code>
         </a>
         system variable defines the buffer pool size.
                  Typically, a recommended
         <a class="link" href="innodb-parameters.html#sysvar_innodb_buffer_pool_size">
          <code class="literal">
           innodb_buffer_pool_size
          </code>
         </a>
         value is 50 to 75 percent of system memory.
         <a class="link" href="innodb-parameters.html#sysvar_innodb_buffer_pool_size">
          <code class="literal">
           innodb_buffer_pool_size
          </code>
         </a>
         can be configured dynamically, while the server is
                  running. For more information, see
         <a class="xref" href="innodb-buffer-pool-resize.html" title="17.8.3.1 Configuring InnoDB Buffer Pool Size">
          Section 17.8.3.1, “Configuring InnoDB Buffer Pool Size”
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         On systems with a large amount of memory, you can
                  improve concurrency by dividing the buffer pool into
                  multiple
         <a class="link" href="glossary.html#glos_buffer_pool_instance" title="buffer pool instance">
          buffer pool
                  instances
         </a>
         . The
         <a class="link" href="innodb-parameters.html#sysvar_innodb_buffer_pool_instances">
          <code class="literal">
           innodb_buffer_pool_instances
          </code>
         </a>
         system variable defines the number of buffer pool
                  instances.
        </p>
       </li>
       <li class="listitem">
        <p>
         A buffer pool that is too small may cause excessive
                  churning as pages are flushed from the buffer pool
                  only to be required again a short time later.
        </p>
       </li>
       <li class="listitem">
        <p>
         A buffer pool that is too large may cause swapping due
                  to competition for memory.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      The storage engine interface enables the optimizer to
              provide information about the size of the record buffer to
              be used for scans that the optimizer estimates are likely
              to read multiple rows. The buffer size can vary based on
              the size of the estimate.
      <code class="literal">
       InnoDB
      </code>
      uses
              this variable-size buffering capability to take advantage
              of row prefetching, and to reduce the overhead of latching
              and B-tree navigation.
     </p>
    </li>
    <li class="listitem">
     <p>
      All threads share the
      <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
       <code class="literal">
        MyISAM
       </code>
      </a>
      key buffer. The
      <a class="link" href="server-system-variables.html#sysvar_key_buffer_size">
       <code class="literal">
        key_buffer_size
       </code>
      </a>
      system
              variable determines its size.
     </p>
     <p>
      For each
      <code class="literal">
       MyISAM
      </code>
      table the server opens,
              the index file is opened once; the data file is opened
              once for each concurrently running thread that accesses
              the table. For each concurrent thread, a table structure,
              column structures for each column, and a buffer of size
      <code class="literal">
       3 *
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
      </code>
      are
              allocated (where
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      is the
              maximum row length, not counting
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      columns). A
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      column requires five
              to eight bytes plus the length of the
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      data. The
      <code class="literal">
       MyISAM
      </code>
      storage engine maintains one
              extra row buffer for internal use.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="server-system-variables.html#sysvar_myisam_use_mmap">
       <code class="literal">
        myisam_use_mmap
       </code>
      </a>
      system variable can be set to 1 to enable memory-mapping
              for all
      <code class="literal">
       MyISAM
      </code>
      tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      If an internal in-memory temporary table becomes too large
              (as determined by
      <a class="link" href="server-system-variables.html#sysvar_tmp_table_size">
       <code class="literal">
        tmp_table_size
       </code>
      </a>
      and
      <a class="link" href="server-system-variables.html#sysvar_max_heap_table_size">
       <code class="literal">
        max_heap_table_size
       </code>
      </a>
      ),
              MySQL automatically converts the table from in-memory to
              on-disk format, which uses the
      <code class="literal">
       InnoDB
      </code>
      storage engine. You can increase the permissible temporary
              table size as described in
      <a class="xref" href="internal-temporary-tables.html" title="10.4.4 Internal Temporary Table Use in MySQL">
       Section 10.4.4, “Internal Temporary Table Use in MySQL”
      </a>
      .
     </p>
     <p>
      For
      <a class="link" href="memory-storage-engine.html" title="18.3 The MEMORY Storage Engine">
       <code class="literal">
        MEMORY
       </code>
      </a>
      tables explicitly
              created with
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      ,
              only the
      <a class="link" href="server-system-variables.html#sysvar_max_heap_table_size">
       <code class="literal">
        max_heap_table_size
       </code>
      </a>
      system variable determines how large a table can grow, and
              there is no conversion to on-disk format.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="performance-schema.html" title="Chapter 29 MySQL Performance Schema">
       MySQL Performance
              Schema
      </a>
      is a feature for monitoring MySQL server
              execution at a low level. The Performance Schema
              dynamically allocates memory incrementally, scaling its
              memory use to actual server load, instead of allocating
              required memory during server startup. Once memory is
              allocated, it is not freed until the server is restarted.
              For more information, see
      <a class="xref" href="performance-schema-memory-model.html" title="29.17 The Performance Schema Memory-Allocation Model">
       Section 29.17, “The Performance Schema Memory-Allocation Model”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Each thread that the server uses to manage client
              connections requires some thread-specific space. The
              following list indicates these and which system variables
              control their size:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         A stack
                  (
         <a class="link" href="server-system-variables.html#sysvar_thread_stack">
          <code class="literal">
           thread_stack
          </code>
         </a>
         )
        </p>
       </li>
       <li class="listitem">
        <p>
         A connection buffer
                  (
         <a class="link" href="server-system-variables.html#sysvar_net_buffer_length">
          <code class="literal">
           net_buffer_length
          </code>
         </a>
         )
        </p>
       </li>
       <li class="listitem">
        <p>
         A result buffer
                  (
         <a class="link" href="server-system-variables.html#sysvar_net_buffer_length">
          <code class="literal">
           net_buffer_length
          </code>
         </a>
         )
        </p>
       </li>
      </ul>
     </div>
     <p>
      The connection buffer and result buffer each begin with a
              size equal to
      <a class="link" href="server-system-variables.html#sysvar_net_buffer_length">
       <code class="literal">
        net_buffer_length
       </code>
      </a>
      bytes,
              but are dynamically enlarged up to
      <a class="link" href="server-system-variables.html#sysvar_max_allowed_packet">
       <code class="literal">
        max_allowed_packet
       </code>
      </a>
      bytes
              as needed. The result buffer shrinks to
      <a class="link" href="server-system-variables.html#sysvar_net_buffer_length">
       <code class="literal">
        net_buffer_length
       </code>
      </a>
      bytes
              after each SQL statement. While a statement is running, a
              copy of the current statement string is also allocated.
     </p>
     <p>
      Each connection thread uses memory for computing statement
              digests. The server allocates
      <a class="link" href="server-system-variables.html#sysvar_max_digest_length">
       <code class="literal">
        max_digest_length
       </code>
      </a>
      bytes
              per session. See
      <a class="xref" href="performance-schema-statement-digests.html" title="29.10 Performance Schema Statement Digests and Sampling">
       Section 29.10, “Performance Schema Statement Digests and Sampling”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      All threads share the same base memory.
     </p>
    </li>
    <li class="listitem">
     <p>
      When a thread is no longer needed, the memory allocated to
              it is released and returned to the system unless the
              thread goes back into the thread cache. In that case, the
              memory remains allocated.
     </p>
    </li>
    <li class="listitem">
     <p>
      Each request that performs a sequential scan of a table
              allocates a
      <span class="firstterm">
       read
              buffer
      </span>
      . The
      <a class="link" href="server-system-variables.html#sysvar_read_buffer_size">
       <code class="literal">
        read_buffer_size
       </code>
      </a>
      system
              variable determines the buffer size.
     </p>
    </li>
    <li class="listitem">
     <p>
      When reading rows in an arbitrary sequence (for example,
              following a sort), a
      <span class="firstterm">
       random-read buffer
      </span>
      may be allocated to avoid disk seeks. The
      <a class="link" href="server-system-variables.html#sysvar_read_rnd_buffer_size">
       <code class="literal">
        read_rnd_buffer_size
       </code>
      </a>
      system variable determines the buffer size.
     </p>
    </li>
    <li class="listitem">
     <p>
      All joins are executed in a single pass, and most joins
              can be done without even using a temporary table. Most
              temporary tables are memory-based hash tables. Temporary
              tables with a large row length (calculated as the sum of
              all column lengths) or that contain
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      columns are stored on
              disk.
     </p>
    </li>
    <li class="listitem">
     <p>
      Most requests that perform a sort allocate a sort buffer
              and zero to two temporary files depending on the result
              set size. See
      <a class="xref" href="temporary-files.html" title="B.3.3.5 Where MySQL Stores Temporary Files">
       Section B.3.3.5, “Where MySQL Stores Temporary Files”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Almost all parsing and calculating is done in thread-local
              and reusable memory pools. No memory overhead is needed
              for small items, thus avoiding the normal slow memory
              allocation and freeing. Memory is allocated only for
              unexpectedly large strings.
     </p>
    </li>
    <li class="listitem">
     <p>
      For each table having
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      columns, a buffer is enlarged dynamically to read in
              larger
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      values. If you
              scan a table, the buffer grows as large as the largest
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      value.
     </p>
    </li>
    <li class="listitem">
     <p>
      MySQL requires memory and descriptors for the table cache.
              Handler structures for all in-use tables are saved in the
              table cache and managed as
      <span class="quote">
       “
       <span class="quote">
        First In, First
              Out
       </span>
       ”
      </span>
      (FIFO). The
      <a class="link" href="server-system-variables.html#sysvar_table_open_cache">
       <code class="literal">
        table_open_cache
       </code>
      </a>
      system
              variable defines the initial table cache size; see
      <a class="xref" href="table-cache.html" title="10.4.3.1 How MySQL Opens and Closes Tables">
       Section 10.4.3.1, “How MySQL Opens and Closes Tables”
      </a>
      .
     </p>
     <p>
      MySQL also requires memory for the table definition cache.
              The
      <a class="link" href="server-system-variables.html#sysvar_table_definition_cache">
       <code class="literal">
        table_definition_cache
       </code>
      </a>
      system variable defines the number of table definitions
              that can be stored in the table definition cache. If you
              use a large number of tables, you can create a large table
              definition cache to speed up the opening of tables. The
              table definition cache takes less space and does not use
              file descriptors, unlike the table cache.
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <a class="link" href="flush.html#flush-tables">
       <code class="literal">
        FLUSH TABLES
       </code>
      </a>
      statement or
      <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
       <span class="command">
        <strong>
         mysqladmin flush-tables
        </strong>
       </span>
      </a>
      command closes
              all tables that are not in use at once and marks all
              in-use tables to be closed when the currently executing
              thread finishes. This effectively frees most in-use
              memory.
      <a class="link" href="flush.html#flush-tables">
       <code class="literal">
        FLUSH TABLES
       </code>
      </a>
      does
              not return until all tables have been closed.
     </p>
    </li>
    <li class="listitem">
     <p>
      The server caches information in memory as a result of
      <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
       <code class="literal">
        GRANT
       </code>
      </a>
      ,
      <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
       <code class="literal">
        CREATE USER
       </code>
      </a>
      ,
      <a class="link" href="create-server.html" title="15.1.18 CREATE SERVER Statement">
       <code class="literal">
        CREATE SERVER
       </code>
      </a>
      , and
      <a class="link" href="install-plugin.html" title="15.7.4.4 INSTALL PLUGIN Statement">
       <code class="literal">
        INSTALL PLUGIN
       </code>
      </a>
      statements.
              This memory is not released by the corresponding
      <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
       <code class="literal">
        REVOKE
       </code>
      </a>
      ,
      <a class="link" href="drop-user.html" title="15.7.1.5 DROP USER Statement">
       <code class="literal">
        DROP USER
       </code>
      </a>
      ,
      <a class="link" href="drop-server.html" title="15.1.30 DROP SERVER Statement">
       <code class="literal">
        DROP SERVER
       </code>
      </a>
      , and
      <a class="link" href="uninstall-plugin.html" title="15.7.4.6 UNINSTALL PLUGIN Statement">
       <code class="literal">
        UNINSTALL PLUGIN
       </code>
      </a>
      statements, so for a server that executes many instances
              of the statements that cause caching, there is an increase
              in cached memory use unless it is freed with
      <a class="link" href="flush.html#flush-privileges">
       <code class="literal">
        FLUSH PRIVILEGES
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      In a replication topology, the following settings affect
              memory usage, and can be adjusted as required:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         The
         <a class="link" href="server-system-variables.html#sysvar_max_allowed_packet">
          <code class="literal">
           max_allowed_packet
          </code>
         </a>
         system variable on a replication source limits the
                  maximum message size that the source sends to its
                  replicas for processing. This setting defaults to 64M.
        </p>
       </li>
       <li class="listitem">
        <p>
         The system variable
         <a class="link" href="replication-options-replica.html#sysvar_replica_pending_jobs_size_max">
          <code class="literal">
           replica_pending_jobs_size_max
          </code>
         </a>
         on a multithreaded replica sets the maximum amount of
                  memory that is made available for holding messages
                  awaiting processing. This setting defaults to 128M.
                  The memory is only allocated when needed, but it might
                  be used if your replication topology handles large
                  transactions sometimes. It is a soft limit, and larger
                  transactions can be processed.
        </p>
       </li>
       <li class="listitem">
        <p>
         The
         <a class="link" href="replication-options-replica.html#sysvar_rpl_read_size">
          <code class="literal">
           rpl_read_size
          </code>
         </a>
         system variable on a replication source or replica
                  controls the minimum amount of data in bytes that is
                  read from the binary log files and relay log files.
                  The default is 8192 bytes. A buffer the size of this
                  value is allocated for each thread that reads from the
                  binary log and relay log files, including dump threads
                  on sources and coordinator threads on replicas.
        </p>
       </li>
       <li class="listitem">
        <p>
         The
         <a class="link" href="replication-options-binary-log.html#sysvar_binlog_transaction_dependency_history_size">
          <code class="literal">
           binlog_transaction_dependency_history_size
          </code>
         </a>
         system variable limits the number of row hashes held
                  as an in-memory history.
        </p>
       </li>
       <li class="listitem">
        <p>
         The
         <a class="link" href="replication-options-binary-log.html#sysvar_max_binlog_cache_size">
          <code class="literal">
           max_binlog_cache_size
          </code>
         </a>
         system variable specifies the upper limit of memory
                  usage by an individual transaction.
        </p>
       </li>
       <li class="listitem">
        <p>
         The
         <a class="link" href="replication-options-binary-log.html#sysvar_max_binlog_stmt_cache_size">
          <code class="literal">
           max_binlog_stmt_cache_size
          </code>
         </a>
         system variable specifies the upper limit of memory
                  usage by the statement cache.
        </p>
       </li>
      </ul>
     </div>
    </li>
   </ul>
  </div>
  <p>
   <span class="command">
    <strong>
     ps
    </strong>
   </span>
   and other system status programs may
          report that
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   uses a lot of memory.
          This may be caused by thread stacks on different memory
          addresses. For example, the Solaris version of
   <span class="command">
    <strong>
     ps
    </strong>
   </span>
   counts the unused memory between stacks
          as used memory. To verify this, check available swap with
   <code class="literal">
    swap -s
   </code>
   . We test
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   with several memory-leakage detectors (both commercial and
          Open Source), so there should be no memory leaks.
  </p>
 </div>
 <br/>
</div>
