8.12.4.1 How MySQL Uses Memory

MySQL allocates buffers and caches to improve performance of
database operations. You can improve MySQL performance by
increasing the values of certain cache and buffer-related
system variables. You can also modify these variables to run
MySQL on systems with limited memory.

The following list describes some of the ways that MySQL uses
memory. Where applicable, relevant system variables are
referenced. Some items are storage engine or feature specific.

The InnoDB buffer pool is a memory area
that holds cached InnoDB data for
tables, indexes, and other auxiliary buffers. For
efficiency of high-volume read operations, the buffer pool
is divided into pages
that can potentially hold multiple rows. For efficiency of
cache management, the buffer pool is implemented as a
linked list of pages; data that is rarely used is aged out
of the cache, using a variation of the
LRU algorithm. For more
information, see Section 14.9.2.1, “The InnoDB Buffer Pool”.

For each MyISAM table the server opens,
the index file is opened once; the data file is opened
once for each concurrently running thread that accesses
the table. 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 storage engine maintains one
extra row buffer for internal use.

The myisam_use_mmap
system variable can be set to 1 to enable memory-mapping
for all MyISAM tables.

For MEMORY tables explicitly
created with CREATE TABLE,
only the
max_heap_table_size
system variable determines how large the table is
permitted to grow and there is no conversion to on-disk
format.

The MySQL Performance
Schema is a feature for monitoring MySQL server
execution at a low level. For performance reasons, fixed
memory buffers for Performance Schema are allocated at
server startup and do not change in size while the server
is running.

Each thread that the server uses to manage client
connections requires some thread-specific space. The
following list indicates these and which system variables
control their size:

The connection buffer and result buffer each begin with a
size equal to
net_buffer_length bytes,
but are dynamically enlarged up to
max_allowed_packet bytes
as needed. The result buffer shrinks to
net_buffer_length bytes
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.

Each request that performs a sequential scan of a table
allocates a read
buffer. The
read_buffer_size system
variable determines the buffer size.

When reading rows in an arbitrary sequence (for example,
following a sort), a
random-read buffer
may be allocated to avoid disk seeks. The
read_rnd_buffer_size
system variable determines the buffer size.

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.

Almost all parsing and calculating is done in thread-local
and reusable memory pools. No memory overhead is needed
for small items, thus avoiding the normal slow memory
allocation and freeing. Memory is allocated only for
unexpectedly large strings.

For each table having BLOB
columns, a buffer is enlarged dynamically to read in
larger BLOB values. If you
scan a table, the buffer grows as large as the largest
BLOB value.

MySQL also requires memory for the table definition cache.
The
table_definition_cache
system variable defines the number of table definitions
(from .frm files) that can be stored
in the table definition cache. If you use a large number
of tables, you can create a large table definition cache
to speed up the opening of tables. The table definition
cache takes less space and does not use file descriptors,
unlike the 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. 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.

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.

Posted by
Christopher Schultz
on
January 30, 2013

Based upon the previous two comments, I re-worked the max-mem-usage query and made it work (there weren't enough @'s on some variables and @giga_bytes isn't defined). This returns a non-null value on MySQL 5.5.29:

mylp:LOOP FETCH CUR_GBLVAR INTO var,val; IF done=1 THEN LEAVE mylp; END IF; IF var in ('key_buffer_size','innodb_buffer_pool_size','innodb_additional_mem_pool_size','innodb_log_buffer_size','query_cache_size') THEN #Summing Up Global Memory Usage SET GLOBAL_SUM=GLOBAL_SUM+val; ELSEIF var in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','thread_stack','max_allowed_packet','net_buffer_length') THEN #Summing Up Per Thread Memory Variables SET PER_THREAD_SUM=PER_THREAD_SUM+val; ELSEIF var in ('max_connections') THEN #Maximum allowed connections SET MAX_CONN=val; ELSEIF var in ('max_heap_table_size') THEN #Size of Max Heap tables created SET HEAP_TABLE=val; #Size of possible Temporary Table = Maximum of tmp_table_size / max_heap_table_size. ELSEIF var in ('tmp_table_size','max_heap_table_size') THEN SET TEMP_TABLE=if((TEMP_TABLE>val),TEMP_TABLE,val); END IF;