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

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

##  ##

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

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

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

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

![c2b593502277507c146871cdcffc086e.png][]

--------------------

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

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

set @ibpdata = (select variable_value from performance_schema.global_status where variable_name = 'innodb_buffer_pool_pages_data');
    select @ibpdata;
    set @idbpgsize = (select variable_value from performance_schema.global_status where variable_name = 'innodb_page_size');
    select @idbpgsize;
    set @ibpsize = @ibpdata * @idbpgsize / (1024*1024*1024);
    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服务器连接数上限设置的过高了

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

![235c6f0955538654bacd8977a5545767.png][]

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

--------------------

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

优化如下：

max_connections=350
    max_connect_errors=50
    max_user_connections=300

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

--------------------

## 总结 ##

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

[c2b593502277507c146871cdcffc086e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/565d8d71651544329c80e0aa6496f8bd.png
[0f40b521c7bf7483f7bf5a87c5672eae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/fafef6f1b9ee47b4bee86b83fd0d3df1.png
[64e2ddc93299245414f6ea34f0afbda7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/3d1e79d2c14043a3bd7a63797628856c.png
[235c6f0955538654bacd8977a5545767.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/f3c088d6e9db405e83c67be1df61c0e1.png