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:
All threads share the
MyISAMkey buffer; its size is determined by the
key_buffer_sizevariable. Other buffers used by the server are allocated as needed. See Section 8.12.2, “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 each begin with a size equal to
net_buffer_lengthbytes, but are dynamically enlarged up to
max_allowed_packetbytes as needed. The result buffer shrinks to
net_buffer_lengthbytes after each SQL statement. While a statement is running, a copy of the current statement string is also allocated.
Each connection thread uses memory for computing statement digests (see Section 21.7, “Performance Schema Statement Digests”): Before MySQL 5.7.4, 1024 bytes per session if the Performance Schema is compiled in with statement instrumentation. In 5.7.4 and 5.7.5, 1024 bytes per session. In 5.7.6 and up,
max_digest_lengthbytes per session.
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.
myisam_use_mmapsystem 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 buffer (variable
read_rnd_buffer_size) may be allocated 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
BLOBcolumns 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
MyISAMstorage engine. You can increase the permissible temporary table size as described in Section 8.4.4, “How MySQL Uses Internal Temporary Tables”.
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 thread-local and reusable memory pools. 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.
MyISAMtable 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 *are allocated (where
Nis the maximum row length, not counting
BLOBcolumn requires five to eight bytes plus the length of the
MyISAMstorage 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. The initial cache size is taken from the value of the
table_open_cachesystem variable. If a table has been used by two running threads at the same time, the cache contains two entries for the table. See Section 18.104.22.168, “How MySQL Opens and Closes Tables”.
FLUSH TABLESstatement 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 TABLESdoes not return until all tables have been closed.
The server caches information in memory as a result of
CREATE SERVER, and
INSTALL PLUGINstatements. This memory is not released by the corresponding
DROP SERVER, and
UNINSTALL PLUGINstatements, 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 leaks.