概述

很多时候我们看到mysql数据库内存用的很高，这个时候我们应该如何知道mysqld进程究竟什么原因消耗掉内存的，都用哪里去了呢？下面介绍几个mysql数据库常用的查看内存消耗情况的命令~

一、linux观察mysql线程内存消耗情况

top -p mysqlpid -H

然后使用pmap -d 分析一下进程的内存情况

二、查看mysql实例内存具体分配情况

1、共享内存

共享内存中的内存空间是实例创建时即分配的内存空间，并且是所有连接共享的。

全局共享内容主要是MySQL Instance以及底层存储引擎用来暂存各种全局运算及可共享的暂存信息，如

• 存储查询缓存的 Query Cache，

• 缓存连接线程的 Thread Cache，

• 缓存表文件句柄信息的 Table Cache，

• 缓存二进制日志的 BinLog Buffer，

• 缓存MyISAM存储引擎索引键的 Key Buffer

• 存储InnoDB数据和索引的 InnoDB Buffer Pool

等等。

show variables where variable_name in ( 'innodb_buffer_pool_size','innodb_log_buffer_size','innodb_additional_mem_pool_size','key_buffer_size','query_cache_size' );

参数说明：

• innodb_buffer_pool该部分缓存是 Innodb 引擎最重要的缓存区域，是通过内存来弥补物理数据文件的重要手段。其中主要包含数据页、索引页、undo 页、insert buffer、自适应哈希索引、锁信息以及数据字典等信息。在进行 SQL 读和写的操作时，首先并不是对物理数据文件操作，而是先对 buffer_pool 进行操作，然后再通过 checkpoint 等机制写回数据文件。该空间的优点是可以提升数据库的性能、加快 SQL 运行速度，缺点是故障恢复速度较慢。

• innodb_log_buffer该部分主要存放 redo log 的信息，在 RDS 上会设置 1 M 的大小。InnoDB 会首先将 redo log 写在这里，然后按照一定频率将其刷新回重做日志文件中。该空间不需要太大，因为一般情况下该部分缓存会以较快频率刷新至 redo log（Master Thread 会每秒刷新、事务提交时会刷新、其空间少于 1/2 时同样会刷新）。

• innodb_additional_mem_pool该部分主要存放 InnoDB 内的一些数据结构，在 RDS 中统一设置为 2 M。通常是在 buffer_pool 中申请内存的时候还需要在额外内存中申请空间存储该对象的结构信息。该大小主要与表数量有关，表数量越大需要更大的空间。

• key_buffer该部分是 MyISAM 表的重要缓存区域，所有实例统一为 16 M。该部分主要存放 MyISAM 表的键。MyISAM 表不同于 InnoDB 表，其缓存的索引缓存是放在 key_buffer 中的，而数据缓存则存储于操作系统的内存中。如果你 的系统是 MyISAM 引擎的，可以分配一些空间。

• query_cache该部分是对查询结果做缓存以减少解析 SQL 和执行 SQL 的开销，这里我关闭了该部分的缓存。主要适合于读多写少的应用场景，因为它是按照 SQL 语句的 hash 值进行缓存的，当表数据发生变化后即失效。

2、Session 私有内存

一般数据库出现 OOM 异常的实例都是由于下面各个连接私有的内存造成的。

执行如下命令查询 session 私有内存分配情况：

show variables where variable_name in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','binlog_cache_size','tmp_table_size');

参数说明：

• read_buffer&read_rnd_buffer这里分别存放了对顺序和随机扫描（例如按照排序的顺序访问）的缓存，我这里给每个 session 设置12M 的大小。当 thread 进行顺序或随机扫描数据时会首先扫描该 buffer 空间以避免更多的物理读。

• sort_buffer需要执行 order by 和 group by 的 SQL 都会分配 sort_buffer，用于存储排序的中间结果，这里也是设置 12M。在排序过程中，若存储量大于 sort_buffer_size，则会在磁盘生成临时表以完成操作。在 Linux 系统中，当分配空间大于 2 M 时会使用 mmap() 而不是 malloc() 来进行内存分配，导致效率降低。

• join_bufferMySQL 仅支持 nestloop 的 join 算法，这个数据库设置的是12M大小，处理逻辑是驱动表的一行和非驱动表联合查找，这时就可以将非驱动表放入 join_buffer，不需要访问拥有并发保护机制的 buffer_pool。

• binlog_cache该区域用来缓存该 thread 的 binlog 日志，这里设置的是32 K 的大小，这里就有点小了，在一个事务还没有 commit 之前会先将其日志存储于 binlog_cache 中，等到事务 commit 后会将其 binlog 刷回磁盘上的 binlog 文件以持久化。

• tmp_table不同于上面各个 session 层次的 buffer，这个参数可以在控制台上修改。该参数是指用户内存临时表的大小，如果该 thread 创建的临时表超过它设置的大小会把临时表转换为磁盘上的一张 MyISAM 临时表。

三、innodb缓冲池内存消耗情况

1、查看InnoDB缓冲池当前实际使用了多少GB内存

通过将缓冲池中可用的数据与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;

2、通过InnoDB缓冲池包含数据的页数来判断

2、通过InnoDB缓冲池包含数据的页数来判断

value = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%

value > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的75%

value < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

四、通过performance_schema表来观察内存消耗情况

MySQL 5.7的P_S（performance_schema的简称）集成了这样的功能，帮助我们分别从账号（包含授权主机信息）、主机、线程、用户（不包含授权主机信息）、整体全局等多个角度查看内存消耗统计。

1、查看全局内存消耗

SELECT
	event_name,
	COUNT_ALLOC,
	SUM_NUMBER_OF_BYTES_ALLOC '总的BYTE大小',
	CURRENT_COUNT_USED '当前使用次数',
	CURRENT_NUMBER_OF_BYTES_USED/1024/1024 '当前使用大小(M)',
	HIGH_NUMBER_OF_BYTES_USED/1024/1024 '最高使用(M)' 
FROM
	`performance_schema`.memory_summary_global_by_event_name
ORDER BY
	SUM_NUMBER_OF_BYTES_ALLOC DESC;

--也可以sys查看：
select * from sys.memory_global_by_current_bytes ORDER BY current_alloc desc LIMIT 10;

2、查看哪些线程消耗比较多内存

select THREAD_ID,event_name,COUNT_ALLOC,SUM_NUMBER_OF_BYTES_ALLOC,CURRENT_COUNT_USED,CURRENT_NUMBER_OF_BYTES_USED,HIGH_NUMBER_OF_BYTES_USED
from `performance_schema`.memory_summary_by_thread_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc LIMIT 10;

--sys表
select * from sys.memory_by_thread_by_current_bytes ORDER BY total_allocated desc LIMIT 10;

3、哪些账号占用过多内存

select host,user,event_name,COUNT_ALLOC,SUM_NUMBER_OF_BYTES_ALLOC,CURRENT_COUNT_USED,CURRENT_NUMBER_OF_BYTES_USED,HIGH_NUMBER_OF_BYTES_USED
from `performance_schema`.memory_summary_by_account_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc LIMIT 10;
--sys表
select * from sys.memory_by_user_by_current_bytes;

4、哪些host请求占用过多内存

select host,event_name,COUNT_ALLOC,SUM_NUMBER_OF_BYTES_ALLOC,CURRENT_COUNT_USED,CURRENT_NUMBER_OF_BYTES_USED,HIGH_NUMBER_OF_BYTES_USED
from `performance_schema`.memory_summary_by_host_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc LIMIT 10;
--sys表
select * from sys.memory_by_host_by_current_bytes

5、哪些用户占用过多内存

select user,event_name,COUNT_ALLOC,SUM_NUMBER_OF_BYTES_ALLOC,CURRENT_COUNT_USED,CURRENT_NUMBER_OF_BYTES_USED,HIGH_NUMBER_OF_BYTES_USED
from `performance_schema`.memory_summary_by_user_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc LIMIT 10;