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 is shared by all threads; its size is
determined by the
Other buffers used by the server are allocated as needed.
See Section 7.5.3, “Tuning Server Parameters”.
Each thread that is used to manage client connections uses some thread-specific space. The following list indicates these and which variables control their size:
The connection buffer and result buffer both begin with a
size given by
net_buffer_length but are
dynamically enlarged up to
max_allowed_packet bytes as
needed. The result buffer shrinks to
each SQL statement. While a statement is running, a copy of
the current statement 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.
Before MySQL 5.1.4, only compressed
MyISAM tables are memory mapped. As of
MySQL 5.1.4, the
variable can be set to 1 to enable memory-mapping for all
Each request that performs a sequential scan of a table
allocates a read buffer (variable
When reading rows in an arbitrary sequence (for example,
following a sort), a random-read
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
columns are stored on disk.
If an internal in-memory temporary table becomes too large,
MySQL handles this automatically by changing the table from
in-memory to on-disk format, to be handled by the
MyISAM storage engine. You can increase
the allowable temporary table size as described in
Section 7.5.10, “How MySQL Uses Internal Temporary Tables”.
Subscribers to the MySQL Enterprise Monitor are alerted
when temporary tables exceed
make recommendations for the optimum value of
tmp_table_size based on
actual table usage. For more information about the MySQL
Enterprise Monitor please see
Most requests that perform a sort allocate a sort buffer and zero to two temporary files depending on the result set size. See Section B.5.4.4, “Where MySQL Stores 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
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
are allocated (where
N is the maximum row length, not
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.
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 Section 7.4.8, “How MySQL Opens and Closes Tables”.
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.
TABLES does not return until all tables have been
The server caches information in memory as a result of
CREATE SERVER, and
INSTALL PLUGIN statements.
This memory is not released by the corresponding
DROP SERVER, and
UNINSTALL PLUGIN statements,
so for a server that executes many instances of the
statements that cause caching, there will be an increase in
memory use. This cached memory can be freed with
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. To verify this,
check available swap with
swap -s. We test
mysqld with several memory-leakage detectors
(both commercial and Open Source), so there should be no memory