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
key_buffer_size
variable.
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:
A stack (variable
thread_stack
)
A connection buffer (variable
net_buffer_length
)
A result buffer (variable
net_buffer_length
)
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
net_buffer_length
after
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.
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 an arbitrary sequence (for example,
following a sort), a random-read
buffer (variable
read_rnd_buffer_size
) may
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 BLOB
columns are stored on disk.
One problem before MySQL 3.23.2 is that if an internal
in-memory temporary table becomes too large, the error
The table
occurs. From 3.23.2 on, MySQL handles this
automatically by changing the table from in-memory to
on-disk format, to be handled by the
tbl_name
is
fullMyISAM
storage engine. 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. See
Section 7.5.8, “How MySQL Uses Internal Temporary Tables”, and
Section 5.1.4, “Session System Variables”.
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 affects 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. See Section A.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
done with malloc()
and
free()
.
For each MyISAM
or
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 ×
are allocated (where
N
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. See Section 7.4.8, “How MySQL Opens and Closes Tables”.
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.
The server caches information in memory as a result of
GRANT
statements. This memory
is not released by the corresponding
REVOKE
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
FLUSH
PRIVILEGES
.
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.
User Comments
tmp_table_size is not the only variable that determines when a tmp table is written to disk. max_heap_table_size also applies.
I got this formula from mysql error log complaining it doesn't have enough memory to start mysqld:
key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory
I hope this document could be straight forward by providing a formula to calculate the memory usage for mysqld.
Sheila
I use the following SQL query to guess MySQL memory usage
1 row in set (0.00 sec)of MySQL unfortunately innodb_* and thread_stack are not
part of MySQL system variables so you need to fill them
manually.
Best Regards,
Guy Baconniere
--
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_additional_mem_pool_size';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
SHOW VARIABLES LIKE 'thread_stack';
SET @kilo_bytes = 1024;
SET @mega_bytes = @kilo_bytes * 1024;
SET @giga_bytes = @mega_bytes * 1024;
SET @innodb_buffer_pool_size = 2 * @giga_bytes;
SET @innodb_additional_mem_pool_size = 16 * @mega_bytes;
SET @innodb_log_buffer_size = 8 * @mega_bytes;
SET @thread_stack = 192 * @kilo_bytes;
SELECT
( @@key_buffer_size + @@query_cache_size + @@tmp_table_size
+ @innodb_buffer_pool_size + @innodb_additional_mem_pool_size
+ @innodb_log_buffer_size
+ @@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
+ @@join_buffer_size + @@binlog_cache_size + @thread_stack
) ) / @giga_bytes AS MAX_MEMORY_GB;
I disagree with how the previous comment handles the tmp_table_size value. They treat it as a single allocation on the global scope when for memory consumption purposes it is more in line with a per thread buffer.
A single connection/query can use a single or multiple temporary tables in the duration of its processing. The connections do not use a single temporary table "area" reserved just for that purpose.
If you are going to use a formula for memory consumption, the tmp_table-size should be located with the other per thread buffers - not in the single allocation listing.
Add your own comment.