Where does MySQL use the memory?

This article refers to how MySQL allocates internal memory, and how to properly set memory allocations and how to monitor memory usage

Official documents

MySQL is assigned to 512MB RAM by default at startup and can be set by setting the relevant memory parameters, where MySQL uses memory

1. InnoDB buffer pool is used to cache table data, indexes, and other auxiliary buffer pools, and for efficient cache management, the buffer pool applies multiple LRU (least recently used) algorithms to chain adjacent page strings to manage hot and cold data

• innodb_buffer_pool_size控制buffer pool的大小，MySQL5.7开始，可以在线变更配置
• To improve parallelism, you can divide the buffer pool into multiple instances, by default if the buffer pool size reaches the number of 1gb,instance 8, you can innodb_buffer_pool_instances配置

2. All threads share MyISAM key buffer, controlled by key_buffer_size parameter

Each time a MyISAM table is opened, the index file is opened only once, and when multiple threads open the same table in parallel, the table is opened only once, but within a single thread, the table structure, column structure, and 3*n (N is the longest row size, excluding the BLOB column) are allocated memory. A BLOB column requires 5 to 8 bytes plus BLOB data. The MyISAM storage Engine assigns an additional row buffer for internal

3, internal memory temporary table if the size is larger than the disk table, control the size of the memory temp table parameter hastmp_table_size、max_heap_table_size，on-disk临时表的存储引擎受internal_tmp_disk_storage_engine设置

4. Performance schema automatically allocates memory based on the actual load of the server, allocated memory will only be released when MySQL is restarted

5. Allocate the required memory for each client thread, including

• Stack A ( thread_stack )

• A Connection buffer ( net_buffer_length )

• A result Buffer ( net_buffer_length )

The initial size of connection buffer and result buffer is net_buffer_length bytes, but can be dynamically extended to bytes as needed max_allowed_packet . Result buffer shrinks to net_buffer_length bytes when each SQL is executed. When the statement is executing, a copy of the current statement text is also cached. Each connection thread uses a digest of the memory calculation statement, and the server allocates bytes for each session max_digest_length

6. All threads share common base system memory

7. When a thread is no longer in use, the memory allocated for it will be reclaimed and returned to the system, unless the thread returns to the buffer pool with a long connection and the memory allocated to it is not freed

8. The sequential reading table is assigned a read buffer, subject toread_buffer_size控制

9. Random Read line data will be assigned Random-read buffer, subject toread_rnd_buffer_size控制

10 . All connections are performed in one execution, and most connections can be done without using temporary tables.

11, most of the sort will occupy a sort buffer and 0 to 2 temporary files (depending on the size of the result set)

12. almost all of the parsing and computation is done in a thread-local and reusable pool of memory.

13. For each table that contains a BLOB column, an auto-enlarged buffer is used to read the larger BLOB value, and if the full table is scanned, the buffer expands to the same size as the largest BLOB value

14. The MySQL table cache requires memory and descriptors, all handlers for the table structure in use are slow to exist in table cache, the management principle is FIFO, and the table cache istable_open_cache控制

MySQL also requires a memory cache table structure file, subject totable_definition_cache控制，不需要描述符，可以增大table definition cache来加快表的打开速度

15. The Flush tables command closes all unused tables and marks the tables that are currently in use and is closed after the thread that called them ends. Will effectively release the memory, flush tables will not return until the table is closed

16, MySQL cache permissions, server, plug-in-related command text, only after the implementation of the flush privileges will release memory

Monitor the usage of MySQL

See if the INNODB related memory monitoring is on, default does not turn on

Mysql> SELECT *  fromperformance_schema.setup_instrumentsWHERENAME like '%memory/innodb%';+-------------------------------------------+---------+-------+|NAME|ENABLED|TIMED|+-------------------------------------------+---------+-------+|Memory/InnoDB/Adaptive HashIndex         |NO|NO||Memory/InnoDB/Buf_buf_pool|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/Row_log_buf|NO|NO||Memory/InnoDB/Row_merge_sort|NO|NO||Memory/InnoDB/Std|NO|NO||Memory/InnoDB/Trx_sys_t::rw_trx_ids|NO|NO|...

Set to turn on all memory instruments, configure the file conditions as follows and restart

Performance-schema-Instrument='memory/%=counted' 

View monitoring data

Mysql> SELECT *  fromPerformance_schema.memory_summary_global_by_event_nameWHEREEvent_Name like 'Memory/innodb/buf_buf_pool'\g event_name:memory/InnoDB/buf_buf_pool Count_alloc:1Count_free:0Sum_number_of_bytes_alloc:137428992Sum_number_of_bytes_free:0low_count_used:0current_count_used:1high_count_used:1low_number_of_bytes_used:0current_number_of_bytes_used:137428992high_number_of_bytes_used:137428992

View all memory allocated by the current server through the SYS library

Mysql> SELECT *  fromsys.memory_global_by_current_bytesWHEREEvent_Name like 'Memory/innodb/buf_buf_pool'\g*************************** 1. Row***************************event_name:memory/InnoDB/buf_buf_pool Current_count:1Current_alloc:131.06Mibcurrent_avg_alloc:131.06MiB High_count:1High_alloc:131.06MiB High_avg_alloc:131.06mib# Fine-grained query each code area allocated memory MySQL> SELECTSubstring_index (Event_Name,'/',2) asCode_area, Sys.format_bytes (SUM(current_alloc)) asCurrent_alloc fromsys.x$memory_global_by_current_bytesGROUP  bySubstring_index (Event_Name,'/',2)       ORDER  by SUM(Current_alloc)DESC;+---------------------------+---------------+|Code_area|Current_alloc|+---------------------------+---------------+|Memory/InnoDB| 843.24Mib||Memory/Performance_schema| 81.29Mib||Memory/Mysys| 8.20Mib||Memory/Sql| 2.47Mib||Memory/Memory| 174.01KiB||Memory/MyISAM| 46.53KiB||Memory/Blackhole|  +bytes||Memory/Federated|  +bytes||Memory/Csv|  +bytes||Memory/Vio| 496bytes|+---------------------------+---------------+

Where does MySQL use the memory