(mysql.info) memory-use

Info Catalog

(mysql.info) compile-and-link-options

(mysql.info) optimizing-the-server

(mysql.info) dns

 
 7.5.5 How MySQL Uses Memory
 ---------------------------
 
 The following list indicates some of the ways that the `mysqld' server
 uses memory. Where applicable, the name of the system variable relevant
 to the memory use is given:
 
    * The key buffer (variable `key_buffer_size') is shared by all
      threads; other buffers used by the server are allocated as needed.
      See  server-parameters.
 
    * Each connection uses some thread-specific space:
 
         * A stack (default 192KB, variable `thread_stack')
 
         * A connection buffer (variable `net_buffer_length')
 
         * A result buffer (variable `net_buffer_length')
 
      The connection buffer and result buffer are dynamically enlarged
      up to `max_allowed_packet' when needed. While a query is running,
      a copy of the current query string is also allocated.
 
    * All threads share the same base memory.
 
    * 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.
 
    * Only compressed `MyISAM' tables are memory mapped. This is because
      the 32-bit memory space of 4GB is not large enough for most big
      tables. When systems with a 64-bit address space become more
      common, we may add general support for memory mapping.
 
    * Each request that performs a sequential scan of a table allocates
      a read buffer (variable `read_buffer_size').
 
    * When reading rows in an arbitrary sequence (for example, following
      a sort), a random-read buffer (variable `read_rnd_buffer_size')
      may be allocated in order to avoid disk seeks.
 
    * 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 `BLOB' columns are stored on disk.
 
      If an internal heap table exceeds the size of `tmp_table_size',
      MySQL handles this automatically by changing the in-memory heap
      table to a disk-based `MyISAM' table as necessary. You can also
      increase the temporary table size by setting the `tmp_table_size'
      option to `mysqld', or by setting the SQL option `SQL_BIG_TABLES'
      in the client program. See  set-option.
 
    * Most requests that perform a sort allocate a sort buffer and zero
      to two temporary files depending on the result set size. See 
      temporary-files.
 
    * Almost all parsing and calculating is done in a local memory
      store. No memory overhead is needed for small items, so the normal
      slow memory allocation and freeing is avoided. Memory is allocated
      only for unexpectedly large strings. This is done with `malloc()'
      and `free()'.
 
    * For each `MyISAM' table that is opened, the index file is opened
      once; the data file is opened once for each concurrently running
      thread. For each concurrent thread, a table structure, column
      structures for each column, and a buffer of size `3 × N' are
      allocated (where N is the maximum row length, not counting `BLOB'
      columns). A `BLOB' column requires five to eight bytes plus the
      length of the `BLOB' data. The `MyISAM' storage engine maintains
      one extra row buffer for internal use.
 
    * For each table having `BLOB' columns, a buffer is enlarged
      dynamically to read in larger `BLOB' values. If you scan a table, a
      buffer as large as the largest `BLOB' value is allocated.
 
    * Handler structures for all in-use tables are saved in a cache and
      managed as a FIFO. By default, the cache has 64 entries. If a
      table has been used by two running threads at the same time, the
      cache contains two entries for the table.  See  table-cache.
 
    * A `FLUSH TABLES' statement or `mysqladmin flush-tables' 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.  `FLUSH TABLES' does
      not return until all tables have been closed.
 
 `ps' and other system status programs may report that `mysqld' uses a
 lot of memory. This may be caused by thread stacks on different memory
 addresses.  For example, the Solaris version of `ps' counts the unused
 memory between stacks as used memory. You can verify this by checking
 available swap with `swap -s'.  We test `mysqld' with several
 memory-leakage detectors (both commercial and Open Source), so there
 should be no memory leaks.
 

Info Catalog

(mysql.info) compile-and-link-options

(mysql.info) optimizing-the-server

(mysql.info) dns