The list below indicates some of the ways that the
uses memory. Where applicable, the name of the server variable relevant
to the memory use is given.
The key buffer (variable
key_buffer) is shared by all threads;
Other buffers used by the server are allocated as needed.
Each connection uses some thread specific space; A stack (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.
Nothing is memory-mapped yet (except compressed tables, but that's another
story). This is because the 32-bit memory space of 4GB is not large
enough for most large tables. When we get a system with a 64-bit address
space, we may add general support for memory-mapping.
Each request doing a sequential scan over a table allocates a read 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 current problem is that if a HEAP table exceeds the
tmp_table_size, you get the error
tbl_name is full. In the future, we will fix this by automatically
changing the in-memory (HEAP) table to a disk-based (NISAM) table as
necessary. To work around this problem, you can increase the temporary
table size by setting the
tmp_table_size option to
or by setting the SQL option
SQL_BIG_TABLES in the client
SET OPTION. In MySQL
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 all
Most requests doing a sort allocate a sort buffer and one or two temporary
files. 18.5 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 and the normal slow memory
allocation and freeing is avoided. Memory is allocated only for
unexpectedly large strings (this is done with
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
n is the maximum row length, not counting
BLOB uses 5 to 8 bytes plus the length of the
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
10.8 How MySQL opens and closes tables.
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
uses a lot of memory. This may be caused by thread-stacks on different
memory addresses. For example, the Solaris version of
the unused memory
between stacks as used memory. You can verify this by checking available
swap -s. We have tested
mysqld with commercial
memory-leakage detectors, so there should be no memory leaks.