MySQL内存使用分析（OOM分析）

一、MySQL如何使用内存？

首先，介绍MySQL使用内存的一些方法：

1. 会话级别的内存消耗（连接私有内存）：如 sort_buffer_size 等，每个会话都会开辟一个 sort_buffer_size 来进行排序操作。
2. 全局的内存消耗（共享内存）：例如 innodb_buffer_pool_size 等，全局共享的内存段。

MySQL内存计算器

全局内存消耗（共享内存）相关参数

1）innodb_buffer_pool_size

使用过Innodb的同学都知道，这块内存是Innodb存储引擎最重要的内存，直接关系到MySQL的读写性能。与MyISAM表只缓存索引，数据寄望于OS系统缓存不同。Innodb一般都会关闭OS的缓存，所有读到数据页和索引都直接存在数据库层的innodb_buffer_pool中的。

InnoDB缓冲池缓存着InnoDB表，索引，及其它辅助缓冲器中的数据。为了实现大容量读取操作的效率，缓冲池被分成可以容纳多行的页。为了缓存管理的效率，缓冲池被实现为页面的链接列表，很少使用的数据使用LRU算法的变体进行页面替换。

缓冲池的大小对于系统性能很重要：

• InnoDB使用malloc()方法在服务器启动时为整个缓冲池分配内存，通常，推荐innodb_buffer_pool_size值为系统内存的50％至75％。innodb_buffer_pool_size可以在服务器运行时动态配置。
• 在具有大量内存的系统上，你可以通过将缓冲池划分为多个缓冲池实例来提高并发性，其innodb_buffer_pool_instances系统变量用来定义缓冲池实例的数量。
• 缓冲池太小可能会导致过多的交换，因为页面从缓冲池中刷新后仅在短时间内可能再次需要。
• 缓冲池太大可能会因为内存竞争而导致交换。

2）innodb_additional_mem_pool_size

主要用于存放MySQL内部的数据结构和Innodb的数据字典，所以大小主要与表的数量有关，表越多值越大。庆幸的是这个值是可变的，如果不够用的话，MySQL会向操作系统申请的。该值默认8M，AWS所有规格都是统一的2M，由于这个值可以动态申请，所以我觉得2M应该是满足需求的。

3）innodb_log_buffer_size

这个是redo log的缓冲区，为了提高性能，MySQL每次写日志都将日志先写到一个内存Buffer中，然后将Buffer按照innodb_flush_log_at_trx_commit的配置刷到disk上。目前，我们所有实例的innodb_flush_log_at_trx_commit设置为了1，即每次事务提交都会刷新Buffer到磁盘，保证已经提交的事务，redo是不会丢的。AWS该值也设的是1（为了保证不丢数据），这个值的大小主要影响到刷磁盘的次数，设置的过小，Buffer容易满，就会增加fsync的次数，设置过大，占用内存。该值默认是8M，AWS所有规格统一128M，我觉得目前每次提交都会刷buffer，所以除非有大事务的情况，一般buffer不太可能被占满，所以没必要开的很大， 8M应该是满足需求的。

4）key_buffer_size

MyISAM表的key缓存，这个只对MyISAM存储引擎有效，所以对于我们绝大多数使用Innodb的应用，无需关心。

5）query_cache_size

MySQL对于查询的结果会进行缓存来节省解析SQL、执行SQL的花销，query_cache是按照SQL语句的Hash值进行缓存的，同时SQL语句涉及的表发生更新，该缓存就会失效，所以这个缓存对于特定的读多更新少的库比较有用，对于绝大多数更新较多的库可能不是很适用，比较受限于应用场景，所以AWS也把这个缓存给关了。我觉得这个值默认应该关闭，根据需求调整。

会话级别的内存消耗（连接私有内存）

上面这些就是MySQL主要的共享内存空间，这些空间是在MySQL启动时就分配的，但是并不是立即使用的。MySQL还有一部分内存是在用户连接请求到达时动态分配的，即每个MySQL连接都单独一个缓存，这部分缓存主要包括：

1）read_buffer_size

每个线程连续扫描时为扫描的每个表分配的缓存区的大小（字节）。如果进行多次连续扫描，可能还需要增加该值。默认值为1311072，只有当查询需要的时候，才分配read_buffer_size指定的全部内存。

2）read_rnd_buffer_size

当以任意顺序读取行时，可以分配随机读取缓冲区，通过该缓冲区读取行，以避免磁盘寻找。read_rnd_buffer_size系统变量决定缓冲器大小。

3）sort_buffer_size

每一个要做排序的请求，都会分到一个sort_buffer_size大的缓存，用于做order by和group by的排序，如果设置的缓存大小无法满足需要，MySQL会将数据写入磁盘来完成排序。因为磁盘操作和内存操作不在一个数量级，所以sort_buffer_size对排序的性能影响很大。由于这部分缓存是即使不用这么大，也会全部分配的，所以对系统内存分配开销是比较大的，如果是希望扩大的话，建议在会话层设置，默认值2M，AWS也是2M。

4）thread_stack

默认256K，AWS设置为256K，MySQL为每个线程分配的堆栈大小，当线程堆栈太小时，这限制了服务器可以处理的SQL语句的复杂性。

5）join_buffer_size

每个连接的每次join都分配一个，默认值128K，AWS设置为128K。

6）binlog_cache_size

类似于innodb_log_buffer_size缓存事务日志，binlog_cache_size缓存Binlog，不同的是这个是每个线程单独一个，主要对于大事务有较大性能提升。默认32K，AWS 32K。

7）tmp_table_size

默认16M，用户内存临时表的最大值，如果临时表超过该值，MySQL就会把临时表转换为一个磁盘上mysiam表。如果用户需要做一些大表的groupby的操作，可能需要较大的该值，由于是与连接相关的，同样建议在会话层设置。

二、MySQL OOM 问题诊断

其实导致 OOM 的直接原因并不复杂，就是因为服务器内存不足，内核需要回收内存，回收内存就是kill掉服务器上使用内存最多的程序，而 MySQL 服务是使用内存最多，所以就 OOM了。今天，来谈谈 MySQL 的 OOM（out of memory）问题诊断。之前，这类问题的定位对于普通用户来说并不怎么简单。

但是在 MySQL 5.7 中，OOM 问题的定位变得极其容易。通常来说，发生 OOM 时可在系统日志找到类似的日志提示：

下面的例子演示了如何使用 performance_schema 和 sys 架构监控 MySQL 的内存使用情况。

从 MySQL 5.7 开始，在 performance_schema 新增了以下这几张表，用于从各维度查看内存的消耗：

• memory_summary_by_account_by_event_name
• memory_summary_by_host_by_event_name
• memory_summary_by_thread_by_event_name
• memory_summary_by_user_by_event_name
• memory_summary_global_by_event_name

简单来说，就是可以根据用户、主机、线程、账号、全局的维度对内存进行监控。同时库sys也就这些表做了进一步的格式化，可以使得用户非常容易的观察到每个对象的内存开销。

细心的同学可能会发现，默认情况下 performance_schema 只对 performance_schema 进行了内存开销的统计。根据你的 MySQL 安装代码区域可能包括 performance_schema、sql、client、innodb、myisam、csv、memory、blackhole、archive、partition 和其他。

但是在对 OOM 进行诊断时，需要对所有可能的对象进行内存监控。大多数性能模式内存仪表默认情况下是禁用的。可以通过更新 performance_schema.setup_instruments 表的启用列启用。因此，还需要做下面的设置：

但是这种在线打开内存统计的方法仅对之后新增的内存对象有效：

如想要对全局生命周期中的对象进行内存统计，必须在配置文件中进行设置，然后重启服务：

重新启动服务器后，performance_schema.setup_instruments 表的 ENABLED 列应为 YES，标识激活内存的仪器报告。

通过上面的结果，是不是可以发现可疑的内存使用了呢？

另外可以看看 buffer pool page 的使用情况。

对于 MySQL 5.7，可以使用 sys 库下的 memory_global_by_current_bytes 表来查询相同的底层数据，该模式表显示了全局服务器内当前内存使用情况，按分配类型进行细分。

此 sys 模式查询通过 current_alloc() 代码区域聚合当前分配的内存：

<参考>

http://www.mysqlcalculator.com

https://www.cnblogs.com/ziroro/p/9779663.html

https://dev.mysql.com/doc/refman/5.7/en/memory-use.html

https://www.percona.com/blog/2018/06/28/what-to-do-when-mysql-runs-out-of-memory-troubleshooting-guide/

喜欢 (7)赏

[资助本站您就扫码 谢谢]

分享 (0)