mysql数据库管理-mysql数据内存占用分析

执笔画情ora

已于 2024-07-12 13:11:45 修改

阅读量335

点赞数

分类专栏： MYSQL性能优化文章标签： android

于 2024-07-12 13:09:11 首次发布

原文链接：https://blog.51cto.com/u_16099276/7094487?_refluxos=a10

版权

MYSQL性能优化专栏收录该内容

21 篇文章 2 订阅

订阅专栏

https://baijiahao.baidu.com/s?id=1766911164135803443&wfr=spider&for=pc

free命令显示内存占用情况：

总内存： 16046MB ≈ 16GB

已用内存： 14928MB ≈ 14.9GB

可用内存： free + buff/cache = 1118MB ≈ 1.1GB

即可用内存比例： 1.1GB / 16GB = 6.8%，通常我们系统监控内存低于10%就会告警。

通过top -c命令发现mysql进程占用内存%MEM (91.0%)，接下来检查下mysql是哪些线程和事件占用较高内存的。首先检查实例的共享内存分配情况几个参数配置：

共享内存

select VARIABLE_NAME,
VARIABLE_VALUE,
concat(VARIABLE_VALUE/1024/1024,' MB') AS VARIABLE_VALUE_MB
from performance_schema.SESSION_VARIABLES
where variable_name in ('innodb_buffer_pool_size','innodb_log_buffer_size',

'innodb_additional_mem_pool_size','key_buffer_size','query_cache_size');

+-------------------------+----------------+-------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_VALUE_MB |
+-------------------------+----------------+-------------------+
| innodb_buffer_pool_size | 107374182400 | 102400 MB |
| innodb_log_buffer_size | 33554432 | 32 MB |
| key_buffer_size | 268435456 | 256 MB |
+-------------------------+----------------+-------------------+
3 rows in set (0.00 sec)

检查了Innodb buffer的内存参数设置值10240MB = 10G，
占总内存 10G/16GB = 62.5%，该值设置在合理的范围内，详细的参数介绍可以参考官方文档，
当系统内存严重不足时，快速恢复可以降低共享内存，调整该参数后，内存会立马释放：

Session私有内存

共享内存中介绍的内存空间是实例创建时即分配的内存空间，
并且是所有连接共享的。而出现 OOM 异常的实例通常都是由于下面各个连接私有的内存造成的。select VARIABLE_NAME, VARIABLE_VALUE,
concat(VARIABLE_VALUE/1024/1024,' MB') AS VARIABLE_VALUE_MB
from performance_schema.SESSION_VARIABLES
where variable_name in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','binlog_cache_size',tmp_table_size');

mysql> select VARIABLE_NAME, VARIABLE_VALUE,
-> concat(VARIABLE_VALUE/1024/1024,' MB') AS VARIABLE_VALUE_MB
-> from performance_schema.SESSION_VARIABLES
-> where variable_name in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','binlog_cache_size','tmp_table_size');
+----------------------+----------------+-------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_VALUE_MB |
+----------------------+----------------+-------------------+
| binlog_cache_size | 4194304 | 4 MB |
| join_buffer_size | 8388608 | 8 MB |
| read_buffer_size | 33554432 | 32 MB |
| read_rnd_buffer_size | 33554432 | 32 MB |
| sort_buffer_size | 33554432 | 32 MB |
| tmp_table_size | 1073741824 | 1024 MB |
+----------------------+----------------+-------------------+
6 rows in set (0.00 sec)

这里的私有内存JOIN_BUFFER_SIZE=128MB，默认值是256KB。用于普通索引扫描、
范围索引扫描和不使用索引而执行全表扫描的联接的缓冲区的最小大小。通常获得快速连接的最佳方法是添加索引。
在无法添加索引时，增加join_buffer_size的值，以获得更快的完全连接。为两个表之间的每个完整连接分配一个连接缓冲区。
对于没有使用索引的几个表之间的复杂联接，可能需要多个联接缓冲区。

除非使用块嵌套循环或批处理键访问算法，否则设置大于保存每个匹配行所需的缓冲区不会有任何好处，
并且所有连接至少分配最小的大小，因此在全局将该变量设置为大值时要小心。最好保持全局设置较小，
只在执行大型连接的会话中将会话设置更改为较大的值。如果全局大小大于使用它的大多数查询所需要的大小，那么内存分配时间可能会导致显著的性能下降。

通过检查私有内存，我们发现这是的JOIN_BUFFER_SIZE全局设置较大。

https://blog.51cto.com/u_16099276/7094487?_refluxos=a10

performance_schema统计内存

mysql> update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%';
mysql> select * from performance_schema.setup_instruments where name like 'memory%innodb%' limit 20;

该命令是在线打开内存统计，所以只会统计打开后新增的内存对象，
打开前的内存对象不会统计，建议您打开后等待一段时间再执行后续步骤，便于找出内存使用高的线程

show tables like '%memory%';

memory_summary_by_account_by_event_name
1.
每个帐户和事件名的内存操作

memory_summary_by_host_by_event_name
1.
每个主机和事件名的内存操作

memory_summary_by_thread_by_event_name
1.
每个线程和事件名的内存操作

memory_summary_by_user_by_event_name
1.
每个用户和事件名的内存操作

memory_summary_global_by_event_name
1.
每个事件名的全局内存操作
--------------------------------

(一)统计事件消耗内存

mysql> select event_name, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_global_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc LIMIT 10

select HOST, EVENT_NAME, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_host_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 10;

找到问题事件或线程后，您可以排查业务代码和环境，解决内存高的问题。上面统计结果发现到 “memory/sql/JOIN_CACHE” 事件消耗的内存最大。
调整全局JOIN_BUFFER_SIZE=32MB，再观察内存占用情况。