Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.3Mb
PDF (A4) - 35.5Mb
PDF (RPM) - 33.8Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.4Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.3Mb
Man Pages (TGZ) - 197.9Kb
Man Pages (Zip) - 301.8Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

9.12.5.1 How MySQL Uses Memory

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.

  • 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 15.4.3.1, “The InnoDB Buffer Pool”.

    The size of the buffer pool is important for system performance.

    • Typically, it is recommended that innodb_buffer_pool_size is configured to 50 to 75 percent of system memory.

    • InnoDB allocates 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_size configuration option. As of MySQL 5.7, innodb_buffer_pool_size can be configured dynamically, while the server is running. For more information, see Section 15.4.3.2, “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 innodb_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.

  • 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 MyISAM key buffer; its size is determined by the key_buffer_size variable. Other buffers used by the server are allocated as needed. See Section 9.12.2, “Tuning Server Parameters”.

    For each MyISAM table 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 * 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.

  • 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_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 (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_length bytes 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.

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

  • 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 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.

    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”.

    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.

  • 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.

  • 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.

  • 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_cache system variable; see Section 9.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 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.

Monitoring MySQL Memory Usage

The following example demonstrates how to use Performance Schema and sys schema to monitor MySQL memory usage.

Most Performance Schema memory instrumentation is disabled by default. Instruments can be enabled by updating the ENABLED column of the performance_scshema.setup_instruments table. Memory instruments have names in the form of memory/code_area/instrument_name, where code_area is a value such as sql or innodb, and instrument_name is the instrument detail.

  1. To view available MySQL memory instruments, query the performance_schema.setup_instruments table. The following query returns hundreds of memory instruments for all code areas.

    mysql> 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 InnoDB memory instruments by specifying innodb as the code area.

    mysql> 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 performance_schema, sql, client, innodb, myisam, csv, memory, blackhole, archive, partition, and others.

  2. To enable memory instruments, add a performance-schema-instrument rule to your MySQL configuration file. For example, to enable all memory instruments, add this rule to your configuration file and restart the server:

    performance-schema-instrument='memory/%=COUNTED'
    Note

    Enabling memory instruments at startup ensures that memory allocations that occur at startup are counted.

    After restarting the server, the ENABLED column of the performance_schema.setup_instruments table should report YES for memory instruments that you enabled. The TIMED column in the setup_instruments table is ignored for memory instruments because memory operations are not timed.

    mysql> 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    |
    +-------------------------------------------+---------+-------+
    
  3. Query memory instrument data. In this example, memory instrument data is queried in the performance_schema.memory_summary_global_by_event_name table, which summarizes data by EVENT_NAME. The EVENT_NAME is the name of the instrument.

    The following query returns memory data for the InnoDB buffer pool. For column descriptions, see Section 23.9.14.10, “Memory Summary Tables”.

    mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name
        -> WHERE EVENT_NAME LIKE 'memory/innodb/buf_buf_pool'\G
                      EVENT_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
    

    The same underlying data can be queried using the sys schema memory_global_by_current_bytes table, which shows current memory usage within the server globally, broken down by allocation type.

    mysql> 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
    

    This sys schema query aggregates currently allocated memory (current_alloc) by code area:

    mysql> SELECT substring_index(`x$memory_global_by_current_bytes`.`event_name`,'/',2) AS 
        -> `code_area`, sys.`format_bytes`(sum(`x$memory_global_by_current_bytes`.`current_alloc`)) 
        -> AS `current_alloc` 
        -> FROM `sys`.`x$memory_global_by_current_bytes` 
        -> 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     |
    +---------------------------+---------------+
    

    For more information about sys schema, see Chapter 24, MySQL sys Schema.


User Comments
  Posted by Kelly Campbell on May 13, 2005
tmp_table_size is not the only variable that determines when a tmp table is written to disk. max_heap_table_size also applies.
  Posted by sheila yao on October 2, 2007
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

  Posted by Guy Baconniere on May 6, 2009
I use the following SQL query to guess MySQL memory usage
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;
+---------------+
| MAX_MEMORY_GB |
+---------------+
| 3.7002 |
+---------------+
1 row in set (0.00 sec)
  Posted by Lig NOT_FOUND on March 19, 2010
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:

SELECT ( @@key_buffer_size
+ @@query_cache_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
+ @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;
  Posted by Eduardo Franceschi on December 26, 2013
I wrote a shell script based on above examples. I've added a min/max memory suggestions also. The minimum memory is estimated using the Max_used_connections variable from SHOW STATUS.

#!/bin/sh

mysql -e "show variables; show status" | awk '
{
VAR[$1]=$2
}
END {
MAX_CONN = VAR["max_connections"]
MAX_USED_CONN = VAR["Max_used_connections"]
BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"]
MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"]
MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN
MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN

printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576
printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN
printf "| %40s | %18d |\n", "max_connections", MAX_CONN
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576
printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576
printf "+------------------------------------------+--------------------+\n"
}'

  Posted by Shane Bester on September 2, 2014
folks above using their own formulas, don't forget about memory used by performance schema (SHOW ENGINE PERFORMANCE_SCHEMA STATUS) and global variable innodb_ft_total_cache_size.
  Posted by kedar vaijanapurkar on February 27, 2015
Long back I had created a stored procedure which resides in mysql and should be as easy as giving a call to the procedure to estimate the memory usage based on Global and Per Thread Variables.

mysql> call my_memory();
+---------------------+------------+
| Parameter | Value (M) |
+---------------------+------------+
| Global Buffers | 531 M |
| Per Thread | 1.890625 M |
| Maximum Connections | 160 |
| Total Memory Usage | 833.5 M |
| + Per Heap Table | 16 M |
| + Per Temp Table | 26 M |
+---------------------+------------+

Source: http://kedar.nitty-witty.com/blog/calculte-mysql-memory-usage-quick-stored-proc

(Do read the cursor declaration note in the post which may cause an error due to bug)

DELIMITER $$

DROP PROCEDURE IF EXISTS `my_memory` $$
CREATE PROCEDURE `my_memory` ()
BEGIN

DECLARE var VARCHAR(100);
DECLARE val VARCHAR(100);
DECLARE done INT;

#Variables for storing calculations
DECLARE GLOBAL_SUM DOUBLE;
DECLARE PER_THREAD_SUM DOUBLE;
DECLARE MAX_CONN DOUBLE;
DECLARE HEAP_TABLE DOUBLE;
DECLARE TEMP_TABLE DOUBLE;

#Cursor for Global Variables

#### For < MySQL 5.1
#### DECLARE CUR_GBLVAR CURSOR FOR SHOW GLOBAL VARIABLES;

#### For MySQL 5.1+
DECLARE CUR_GBLVAR CURSOR FOR SELECT * FROM information_schema.GLOBAL_VARIABLES;
#### Ref: http://bugs.mysql.com/bug.php?id=49758

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

SET GLOBAL_SUM=0;
SET PER_THREAD_SUM=0;
SET MAX_CONN=0;
SET HEAP_TABLE=0;
SET TEMP_TABLE=0;

OPEN CUR_GBLVAR;

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;

END LOOP;
CLOSE CUR_GBLVAR;
#Summerizing:
select "Global Buffers" as "Parameter",CONCAT(GLOBAL_SUM/(1024*1024),' M') as "Value" union
select "Per Thread",CONCAT(PER_THREAD_SUM/(1024*1024),' M') union
select "Maximum Connections",MAX_CONN union
select "Total Memory Usage",CONCAT((GLOBAL_SUM + (MAX_CONN * PER_THREAD_SUM))/(1024*1024),' M') union
select "+ Per Heap Table",CONCAT(HEAP_TABLE / (1024*1024),' M') union
select "+ Per Temp Table",CONCAT(TEMP_TABLE / (1024*1024),' M') ;

END $$
DELIMITER ;
Sign Up Login You must be logged in to post a comment.