1. Phenomenon
VIRT=37G;
This is the size of virtual memory: including innodb_buffer_pool_size + SWAP partition size + Buffer/Cache size
=20G + 4.8G + 15G=39.8G~approximately equal to VIRT:37G.
At first, we tested the impact of changes in thread_cache_size on memory. The test results showed that thread_cache_size had almost no impact on MEM% memory usage.
2. Analysis
From the screenshot, SWAP=4.8G. Generally, if the memory used by the database exceeds the physical memory during operation, the SWAP partition will be used. At this time, the performance is poor and the speed is slow. Physical memory should be increased at this time. Adjustment method to reduce:MEM% 1). Release the swap partition space Currently, if the physical memory is sufficient when the database is running, adjust and release the space occupied by the SWAP partition. Swapoff -a --Close the swap partition and release swap Swapon -a --Enable swap partition. 2) Release the buffer area echo 1 > /proc/sys/vm/drop_caches 3) Clean up dirty pages in innodb Set global innodb_buffer_pool_size=NUMER1 -- first adjust to half of the original size Set global innodb_buffer_pool_size=NUMER2 -- then adjust to the original size At this time, VIRT, RES, and memory will all become smaller. SWAP USED=0, that is, the size of the SWAP partition has been released. Note: NUMBER1 and NUMBER2 are the memory size in bytes.
3.INNODB engine analysis
INNODB buffer pool has multiple instances, the data page is approximately: 1273192*16/1024=19.4G Indicates that all data pages have been used to cache data. All memory is fully utilized. All pages occupied by the database are full. If a large DML is executed at this time, it will cause the RES memory to grow, the free command: USED memory to grow, and the buff/cache memory to grow. In addition to the reserved memory represented by free_min_kbytes, if other memory is used up, the SWAP partition will be used. Overall, it will lead to the growth of VIRT, RES, and SWAP memory. If our system allows large transaction SQL to run, we should often monitor the changes in the SWAP partition. If the SWAP partition is used a lot, it means that the physical memory is obviously insufficient and the physical memory needs to be increased. If the physical memory is sufficient and stable in the long term, and there is only occasional SWAP partition memory growth, there is no need to worry about high VIRT memory, because it is based on the memory usage mechanism of the database and the memory usage mechanism of the operation itself. If there is insufficient memory, the relevant dirty pages will be released to free up new memory. The SWAP USED partition is large, which does not mean that the SWAP partition is used every time it is run, but that the dirty data in the SWAP partition has not been effectively cleaned.
4.MYSQL actual memory query method
(1)MySQL service memory component
Memory used by MYSQL server =
INNODB_BUFFER_POOL_SIZE + KEY_BUFFER_SIZE +
TMP_TABLE_SIZE + SORT_BUFFER_SIZE +
READ_BUFFER_SIZE + READ_RND_BUFFER_SIZE +
JOIN_BUFFER_SIZE + THREAD_STACK + BINLOG_CACHE_SIZE
(2)MySQL server memory query method
Enable the compatibility parameters first, and then execute the query.
set global show_compatibility_56=on;
select VARIABLE_NAME,VARIABLE_VALUE/1024/1024 MB from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME in ('key_buffer_size','query_cache_size','tmp_table_size','innodb_buffer_pool_size','innodb_additional_mem_pool_size','innodb_log_buffer_size ')
union all
SELECT 'sort_buffer_size',(V1.VARIABLE_VALUE*v2.vv) MB
FROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'sort_buffer_size' ) AS v2
union all
SELECT 'read_buffer_size',(V1.VARIABLE_VALUE*v2.vv) MB
FROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'read_buffer_size' ) AS v2
union all
SELECT 'read_rnd_buffer_size',(V1.VARIABLE_VALUE*v2.vv) MB
FROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'read_rnd_buffer_size' ) AS v2
union all
SELECT 'join_buffer_size',(V1.VARIABLE_VALUE*v2.vv) MB
FROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'join_buffer_size' ) AS v2
union all
SELECT 'thread_stack',(V1.VARIABLE_VALUE*v2.vv) MB
FROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'thread_stack' ) AS v2
union all
SELECT 'binlog_cache_size',(V1.VARIABLE_VALUE*v2.vv) MB
FROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'binlog_cache_size' ) AS v2;
(3) The query results are as follows:
+ ----------------------------------------- + ------- - +
| VARIABLE_NAME | MB |
+ ----------------------------------------- + ------- - +
| TMP_TABLE_SIZE | 96 |
| KEY_BUFFER_SIZE | 800 |
| QUERY_CACHE_SIZE | 0 |
| INNODB_LOG_BUFFER_SIZE | 64 |
| INNODB_BUFFER_POOL_SIZE | 4096 |
| sort_buffer_size | 3000 |
| read_buffer_size | 3000 |
| read_rnd_buffer_size | 24000 |
| join_buffer_size | 3000 |
| thread_stack | 281.25 |
| binlog_cache_size | 6000 |
+ ---------------------------------------- + -------- +
By calculating according to the above formula, you can get the memory size occupied by the mysql server. That can explain why the VIRT, RES, and MEM% resources in the TOP command are high. Innodb_buffer_pool_size represents only the size of the data page. For the MySQL server, there are other cache areas, all occupying a certain amount of memory space. If there is not enough memory, consider adding more memory.
5. Summary
First follow the above method to adjust the memory usage of SWAP, cache, etc. Reduce the memory of VIRT, RES, MEM%, etc., and continue to observe to see if it will cause the SWAP partition to use more every time. If so, increase the physical memory. Make sure there is enough physical memory and make sure you are not using a SWAP partition. Innodb_buffer_pool_size represents only the size of the data page. For the MySQL server, there are other cache areas, all occupying a certain amount of memory space. If there is not enough memory, consider adding more memory.