MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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.5.1, “Buffer Pool”.
The size of the buffer pool is important for system performance:
InnoDB
allocates memory for the
entire buffer pool at server startup, using
malloc()
operations. The
innodb_buffer_pool_size
system variable defines the buffer pool size.
Typically, a recommended
innodb_buffer_pool_size
value is 50 to 75 percent of system memory. For more
information, see
Configuring InnoDB Buffer Pool Size.
On systems with a large amount of memory, you can
improve concurrency by dividing the buffer pool into
multiple
buffer pool
instances. The
innodb_buffer_pool_instances
system variable defines the number of buffer pool
instances.
A buffer pool that is too small may cause excessive churning as pages are flushed from the buffer pool only to be required again a short time later.
A buffer pool that is too large may cause swapping due to competition for memory.
All threads share the MyISAM
key buffer. The
key_buffer_size
system
variable determines its size.
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 *
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
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.
If an internal in-memory temporary table becomes too large
(as determined using the
tmp_table_size
and
max_heap_table_size
system variables), MySQL automatically converts the table
from in-memory to on-disk format. On-disk temporary tables
use the MyISAM
storage engine. You can
increase the permissible temporary table size as described
in Section 8.4.4, “Internal Temporary Table Use in MySQL”.
For MEMORY
tables explicitly
created with CREATE TABLE
,
only the
max_heap_table_size
system variable determines how large a table can 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:
A stack
(thread_stack
)
A connection buffer
(net_buffer_length
)
A result buffer
(net_buffer_length
)
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.
Each connection thread uses memory for computing statement
digests. In MySQL 5.6.24 and up, the server allocates
max_digest_length
bytes
per session. Before MySQL 5.6.24, the server allocates
1024 bytes per session. See
Section 22.10, “Performance Schema Statement Digests”.
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.
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.3.3.5, “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, 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 requires memory and descriptors for the table cache.
Handler structures for all in-use tables are saved in the
table cache and managed as “First In, First
Out” (FIFO). The
table_open_cache
system
variable defines the initial table cache size; see
Section 8.4.3.1, “How MySQL Opens and Closes Tables”.
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.
The server caches information in memory as a result of
GRANT
,
CREATE USER
,
CREATE SERVER
, and
INSTALL PLUGIN
statements.
This memory is not released by the corresponding
REVOKE
,
DROP USER
,
DROP SERVER
, and
UNINSTALL PLUGIN
statements, so for a server that executes many instances
of the statements that cause caching, there is likely to
be an increase in cached memory use unless it is 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.