MySQL allocates buffers and caches to improve performance of database operations. The default configuration is designed to allow a MySQL Server to start on a virtual machine with approximately 512MB of RAM. You can improve MySQL performance by increasing the values of certain cache and buffer-related system variables. You can also modify the default configuration 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.
InnoDBbuffer pool is a memory area that holds cached
InnoDBdata 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 220.127.116.11, “The InnoDB Buffer Pool”.
The size of the buffer pool is important for system performance.
Typically, it is recommended that
innodb_buffer_pool_sizeis configured to 50 to 75 percent of system memory.
InnoDBallocates memory for the entire buffer pool at server startup. Memory allocation is performed by
malloc()operations. Buffer pool size is defined by the
innodb_buffer_pool_sizeconfiguration option. As of MySQL 5.7,
innodb_buffer_pool_sizecan be configured dynamically, while the server is running. For more information, see Section 18.104.22.168, “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 number of buffer pool instances is defined by
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.
The MySQL Performance Schema is a feature for monitoring MySQL server execution at a low level. As of MySQL 5.7, the Performance Schema dynamically allocates memory incrementally, scaling its memory use to actual server load, instead of allocating required memory during server startup. Once memory is allocated, it is not freed until the server is restarted. For more information, see Section 23.14, “The Performance Schema Memory-Allocation Model”.
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 9.12.2, “Tuning Server Parameters”.
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.
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 23.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 higher,
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, handled by the storage engine defined by
internal_tmp_disk_storage_engine. You can increase the permissible temporary table size as described in Section 9.4.4, “Internal Temporary Table Use in MySQL”.
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.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, so the normal slow memory allocation and freeing is avoided. Memory is allocated only for unexpectedly large strings.
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 initial table cache size is defined by the
table_open_cachesystem variable; see Section 22.214.171.124, “How MySQL Opens and Closes Tables”.
MySQL also requires memory for the table definition cache. The
table_definition_cachesystem variable defines the number of table definitions (from
.frmfiles) 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.
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.
Most Performance Schema memory instrumentation is disabled
by default. Instruments can be enabled by updating the
ENABLED column of the
table. Memory instruments have names in the form of
code_area is a value such
instrument_name is the instrument
To view available MySQL memory instruments, query the
performance_schema.setup_instrumentstable. The following query returns hundreds of memory instruments for all code areas.
SELECT * FROM performance_schema.setup_instruments->
WHERE NAME LIKE '%memory%';
You can narrow results by specifying a code area. For example, you can limit results to
InnoDBmemory instruments by specifying
innodbas the code area.
SELECT * FROM performance_schema.setup_instruments->
WHERE NAME LIKE '%memory/innodb%' LIMIT 10;+-------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +-------------------------------------------+---------+-------+ | memory/innodb/adaptive hash index | NO | NO | | memory/innodb/buf_buf_pool | NO | NO | | memory/innodb/buf_stat_per_index_t | NO | NO | | memory/innodb/dict_stats_bg_recalc_pool_t | NO | NO | | memory/innodb/dict_stats_index_map_t | NO | NO | | memory/innodb/dict_stats_n_diff_on_level | NO | NO | | memory/innodb/other | NO | NO | | memory/innodb/partitioning | NO | NO | | memory/innodb/row_log_buf | NO | NO | | memory/innodb/row_merge_sort | NO | NO | +-------------------------------------------+---------+-------+
Depending on your MySQL installation, code areas may include
partition, and others.
To enable memory instruments, add a
performance-schema-instrumentrule to your MySQL configuration file. For example, to enable all memory instruments, add this rule to your configuration file and restart the server:
Enabling memory instruments at startup ensures that memory allocations that occur at startup are counted.
After restarting the server, the
ENABLEDcolumn of the
performance_schema.setup_instrumentstable should report
YESfor memory instruments that you enabled. The
TIMEDcolumn in the
setup_instrumentstable is ignored for memory instruments because memory operations are not timed.
SELECT * FROM performance_schema.setup_instruments->
WHERE NAME LIKE '%memory/innodb%' LIMIT 10;+-------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +-------------------------------------------+---------+-------+ | memory/innodb/adaptive hash index | YES | NO | | memory/innodb/buf_buf_pool | YES | NO | | memory/innodb/buf_stat_per_index_t | YES | NO | | memory/innodb/dict_stats_bg_recalc_pool_t | YES | NO | | memory/innodb/dict_stats_index_map_t | YES | NO | | memory/innodb/dict_stats_n_diff_on_level | YES | NO | | memory/innodb/other | YES | NO | | memory/innodb/partitioning | YES | NO | | memory/innodb/row_log_buf | YES | NO | | memory/innodb/row_merge_sort | YES | NO | +-------------------------------------------+---------+-------+
Query memory instrument data. In this example, memory instrument data is queried in the
performance_schema.memory_summary_global_by_event_nametable, which summarizes data by
EVENT_NAMEis the name of the instrument.
The following query returns memory data for the
InnoDBbuffer pool. For column descriptions, see Section 126.96.36.199, “Memory Summary Tables”.
SELECT * FROM performance_schema.memory_summary_global_by_event_name->
WHERE EVENT_NAME LIKE 'memory/innodb/buf_buf_pool'\GEVENT_NAME: memory/innodb/buf_buf_pool COUNT_ALLOC: 1 COUNT_FREE: 0 SUM_NUMBER_OF_BYTES_ALLOC: 137428992 SUM_NUMBER_OF_BYTES_FREE: 0 LOW_COUNT_USED: 0 CURRENT_COUNT_USED: 1 HIGH_COUNT_USED: 1 LOW_NUMBER_OF_BYTES_USED: 0 CURRENT_NUMBER_OF_BYTES_USED: 137428992 HIGH_NUMBER_OF_BYTES_USED: 137428992
SELECT * FROM sys.memory_global_by_current_bytes->
WHERE event_name LIKE 'memory/innodb/buf_buf_pool'\G*************************** 1. row *************************** event_name: memory/innodb/buf_buf_pool current_count: 1 current_alloc: 131.06 MiB current_avg_alloc: 131.06 MiB high_count: 1 high_alloc: 131.06 MiB high_avg_alloc: 131.06 MiB
sysschema query aggregates currently allocated memory (
current_alloc) by code area:
SELECT substring_index(`x$memory_global_by_current_bytes`.`event_name`,'/',2) AS->
GROUP BY substring_index(`x$memory_global_by_current_bytes`.`event_name`,'/',2)->
ORDER BY sum(`x$memory_global_by_current_bytes`.`current_alloc`) DESC;+---------------------------+---------------+ | code_area | current_alloc | +---------------------------+---------------+ | memory/innodb | 843.24 MiB | | memory/performance_schema | 81.29 MiB | | memory/mysys | 8.20 MiB | | memory/sql | 2.47 MiB | | memory/memory | 174.01 KiB | | memory/myisam | 46.53 KiB | | memory/blackhole | 512 bytes | | memory/federated | 512 bytes | | memory/csv | 512 bytes | | memory/vio | 496 bytes | +---------------------------+---------------+