mysql内存占用一直增高不释放

概述

从zabbix监控发现某台数据库服务器经常发生内存告警，使用率达到95%，下面从连接数角度来分析一下该数据库服务器内存占用过高问题..

1、查看数据库分配内存大小

( mysql内存计算器，具体地址为http://www.mysqlcalculator.com/ )

ｓｅｌｅｃｔ 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 allSELECT ‘sort_buffer_size‘,(V1.VARIABLE_VALUE*v2.vv) MBFROM ( ｓｅｌｅｃｔ VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME=‘MAX_CONNECTIONS‘ ) AS v1,( ｓｅｌｅｃｔ VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = ‘sort_buffer_size‘ ) AS v2ｕｎｉｏｎallSELECT ‘read_buffer_size‘,(V1.VARIABLE_VALUE*v2.vv) MBFROM ( ｓｅｌｅｃｔ VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME=‘MAX_CONNECTIONS‘ ) AS v1,( ｓｅｌｅｃｔ VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = ‘read_buffer_size‘ ) AS v2ｕｎｉｏｎallSELECT ‘read_rnd_buffer_size‘,(V1.VARIABLE_VALUE*v2.vv) MBFROM ( ｓｅｌｅｃｔ VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME=‘MAX_CONNECTIONS‘ ) AS v1,( ｓｅｌｅｃｔ VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = ‘read_rnd_buffer_size‘ ) AS v2ｕｎｉｏｎallSELECT ‘join_buffer_size‘,(V1.VARIABLE_VALUE*v2.vv) MBFROM ( ｓｅｌｅｃｔ VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME=‘MAX_CONNECTIONS‘ ) AS v1,( ｓｅｌｅｃｔ VARIABLE_VALUE/1024/1024vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = ‘join_buffer_size‘ ) AS v2ｕｎｉｏｎallSELECT ‘thread_stack‘,(V1.VARIABLE_VALUE*v2.vv) MBFROM ( ｓｅｌｅｃｔ VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME=‘MAX_CONNECTIONS‘ ) AS v1,( ｓｅｌｅｃｔ VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = ‘thread_stack‘ ) AS v2ｕｎｉｏｎallSELECT ‘binlog_cache_size‘,(V1.VARIABLE_VALUE*v2.vv) MBFROM ( ｓｅｌｅｃｔ VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME=‘MAX_CONNECTIONS‘ ) AS v1,( ｓｅｌｅｃｔ VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = ‘binlog_cache_size‘ ) AS v2

可以看出每个session所分配的内存平均偏高，这里计算是要乘以MAX_CONNECTIONS的

2、查看innodb缓冲池实际使用内存

通过将缓冲池中可用的数据与InnoDB页面（InnoDB缓冲池单位）大小相乘，可以计算InnoDB缓冲池此时正在使用的实际内存。从MySQL 5.7.6开始，GLOBAL_STATUS表中提供的信息从Performance Schema获取。

set @ibpdata = (ｓｅｌｅｃｔ variable_value from performance_schema.global_status where variable_name = ‘innodb_buffer_pool_pages_data‘);ｓｅｌｅｃｔ @ibpdata;set @idbpgsize = (ｓｅｌｅｃｔ variable_value from performance_schema.global_status where variable_name = ‘innodb_page_size‘);ｓｅｌｅｃｔ @idbpgsize;set @ibpsize = @ibpdata * @idbpgsize / (1024*1024*1024);ｓｅｌｅｃｔ @ibpsize;

分配36G，实际使用34.5G，占比95%

由top命令可知mysql数据库占服务器内存93%，即

数据库内存：48*0.93=44.64GB

数据库内存明细：34.55G+32+128+1600*4+200+3200=44.74GB

3、查看数据库连接数情况

Max_connections：整个服务器的用户限制，即mysql上限连接数 ，增加该值增加mysqld 要求的文件描述符的数量。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存

Max_user_connections: 限制每个用户的session连接个数，例如max_user_connections=1 ，那么用户u1只能连接的session数为1，如果还有用户u2，还是可以连接，但是连接数仍然为1，如果数据库只有一个用户，那Max_user_connections自然等于Max_connections。

Max_used_connections：mysql历史响应最大连接数

Max_connect_errors：默认10，每个主机的连接请求异常中断的最大次数

Max_connections_used_rate：最大连接数使用率，历史最大连接数占上限连接数的85%左右，如果发现比例在10%以下，证明MySQL服务器连接数上限设置的过高了

show variables like ‘%connect%‘;show global status like ‘Max_used_connections‘;show global status like ‘Threads_connected‘;--最大连接数使用率(建议85%)Max_used_connections/max_connections--当前连接数使用率Threads_connected/max_connections

从最大连接数使用率可知max_connections设置的过高了

4、优化数据库连接数配置

优化如下：

max_connections=350max_connect_errors=50max_user_connections=300

优化后问题解决，过了3天zabbix都没告警..

总结

这里只是从连接数角度来做优化，实际场景需结合当前正在运行的sql做分析，例如可能有条sql正在做全扫，占了内存池很大空间，可以同时从show full processlist和sys.memory_global_by_current_bytes来进一步观察，这里也是需要考虑的一个点。