(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
automatically generated byinfo2html