GNU Info

(mysql.info)Memory use

---

Next: DNS Prev: Compile and link options Up: Optimizing the Server

---

Enter node , (file) or (file)node

How MySQL Uses Memory
---------------------

The list below indicates some of the ways that the `mysqld' server uses
memory.  Where applicable, the name of the server 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.
     Note: Server parameters.

   * Each connection uses some thread-specific space: A stack (default
     64K, variable `thread_stack'), a connection buffer (variable
     `net_buffer_length'), and a result buffer (variable
     `net_buffer_length'). The connection buffer and result buffer are
     dynamically enlarged up to `max_allowed_packet' when needed.  When
     a query is running, a copy of the current query string is also
     allocated.

   * All threads share the same base memory.

   * Only the compressed ISAM / 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 doing a sequential scan over a table allocates a read
     buffer (variable `record_buffer').

   * When reading rows in 'random' order (for example after a sort) a
     random-read buffer is allocated to avoid disk seeks.  (variable
     `record_rnd_buffer').

   * 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 big record
     length (calculated as the sum of all column lengths) or that
     contain `BLOB' columns are stored on disk.

     One problem in MySQL versions before Version 3.23.2 is that if a
     HEAP table exceeds the size of `tmp_table_size', you get the error
     `The table tbl_name is full'. In newer versions this is handled by
     automatically changing the in-memory (HEAP) table to a disk-based
     (MyISAM) table as necessary. To work around this problem, 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.  Note: `SET OPTION'. In MySQL
     Version 3.20, the maximum size of the temporary table was
     `record_buffer*16', so 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 doing a sort allocates a sort buffer and 0-2
     temporary files depending on the result set size. Note: Temporary
     files.

   * Almost all parsing and calculating is done in a local memory
     store. No memory overhead is needed for small items and the normal
     slow memory allocation and freeing is avoided. Memory is allocated
     only for unexpectedly large strings (this is done with `malloc()'
     and `free()').

   * Each 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' is allocated (where `n' is the maximum row length,
     not counting `BLOB' columns). A `BLOB' uses 5 to 8 bytes plus the
     length of the `BLOB' data. The `ISAM'/`MyISAM' table handlers will
     use one extra row buffer for internal usage.

   * 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.

   * Table handlers for all in-use tables are saved in a cache and
     managed as a FIFO. Normally 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. Note: Table cache.

   * A `mysqladmin flush-tables' command closes all tables that are not
     in use and marks all in-use tables to be closed when the currently
     executing thread finishes. This will effectively free most in-use
     memory.

`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 commercial
memory-leakage detectors, so there should be no memory leaks.