(mysql.info.gz) Memory use

Info Catalog

(mysql.info.gz) Compile and link options

(mysql.info.gz) Optimizing the Server

(mysql.info.gz) 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.
       Server parameters.
 
    * Each connection uses some thread-specific space:
 
         * A stack (default 64KB, 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.
 
    * Only compressed `ISAM' and `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 "random" order (for example, after a sort), a
      random-read buffer may be allocated to avoid disk seeks.
      (variable `read_rnd_buffer_size').
 
    * All joins are done in one pass, and most joins can be done without
      even using a temporary table. Most temporary tables are
      memory-based (`HEAP') tables.  Temporary tables with a large
      record length (calculated as the sum of all column lengths) or
      that contain `BLOB' columns are stored on disk.
 
      One problem before MySQL 3.23.2 is that if an internal in-memory
      heap table exceeds the size of `tmp_table_size', the error `The
      table TBL_NAME is full' occurs. From 3.23.2 on, this is handled
      automatically by changing the in-memory heap table to a disk-based
      `MyISAM' table as necessary. To work around this problem for older
      servers, you can 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.   `SET' Syntax SET
      OPTION.
 
      In MySQL 3.20, the maximum size of the temporary table is
      `record_buffer*16'; if you are using this version, you have to
      increase the value of `record_buffer'. You can also start `mysqld'
      with the `--big-tables' option to always store temporary tables on
      disk.  However, this will affect the speed of many complicated
      queries.
 
    * Most requests that perform a sort allocate a sort buffer and zero
      to two temporary files depending on the result set size. 
      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' and `ISAM' table that is opened, the index file
      is opened once and 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' and `ISAM'
      storage engines maintain 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.  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 have tested `mysqld' with several
 memory-leakage detectors (both commercial and open source), so there
 should be no memory leaks.
 

Info Catalog

(mysql.info.gz) Compile and link options

(mysql.info.gz) Optimizing the Server

(mysql.info.gz) DNS