mysql数据库内存占用高原因（数据库内存占用高）

ゞ 浴缸里的玫瑰 2024-03-17 17:12 189阅读 0赞

#

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

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

  1. 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')
  2. union all
  3. SELECT 'sort_buffer_size',(V1.VARIABLE_VALUE*v2.vv) MB
  4. FROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
  5.   ( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'sort_buffer_size' ) AS v2 
  6. union all
  7. SELECT 'read_buffer_size',(V1.VARIABLE_VALUE*v2.vv) MB
  8. FROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
  9.   ( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'read_buffer_size' ) AS v2 
  10. union all
  11. SELECT 'read_rnd_buffer_size',(V1.VARIABLE_VALUE*v2.vv) MB
  12. FROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
  13.   ( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'read_rnd_buffer_size' ) AS v2 
  14. union all
  15. SELECT 'join_buffer_size',(V1.VARIABLE_VALUE*v2.vv) MB
  16. FROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
  17.   ( SELECT VARIABLE_VALUE/1024/1024  vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'join_buffer_size' ) AS v2 
  18. union all
  19. SELECT 'thread_stack',(V1.VARIABLE_VALUE*v2.vv) MB
  20. FROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
  21.   ( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'thread_stack' ) AS v2 
  22. union all
  23. SELECT 'binlog_cache_size',(V1.VARIABLE_VALUE*v2.vv) MB
  24. FROM ( select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='MAX_CONNECTIONS' ) AS v1,
  25.   ( SELECT VARIABLE_VALUE/1024/1024 vv FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'binlog_cache_size' ) AS v2

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

c2b593502277507c146871cdcffc086e.png

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

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

  1. set @ibpdata = (select variable_value from performance_schema.global_status where variable_name = 'innodb_buffer_pool_pages_data');
  2. select @ibpdata;
  3. set @idbpgsize = (select variable_value from performance_schema.global_status where variable_name = 'innodb_page_size');
  4. select @idbpgsize;
  5. set @ibpsize = @ibpdata * @idbpgsize / (1024*1024*1024);
  6. select @ibpsize;

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

0f40b521c7bf7483f7bf5a87c5672eae.png

64e2ddc93299245414f6ea34f0afbda7.png

由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服务器连接数上限设置的过高了

  1. show variables like '%connect%';
  2. show global status like 'Max_used_connections';
  3. show global status like 'Threads_connected';
  4. --最大连接数使用率(建议85%)
  5. Max_used_connections/max_connections
  6. --当前连接数使用率
  7. Threads_connected/max_connections

235c6f0955538654bacd8977a5545767.png

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

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

优化如下：

  1. max_connections=350
  2. max_connect_errors=50
  3. max_user_connections=300

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

总结

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

发表评论

表情：
评论列表 （有 0 条评论，189人围观）

还没有评论，来说两句吧...

相关阅读