前言
我们经常讨论,mysql占用内存多,还使用了swap,那究竟是什么原因呢?这篇文章能解答一些这类问题.
numa的问题
numa是linux系统为了合理分配多核环境下内存空间而存在的功能,但是对于大内存应用反而造成了性能瓶颈,所以对于数据库类别应用来说,应当要关闭,如果硬件BIOS没有关闭,则需要开启这个参数来关闭.
因为对于数据库这种大内存应用,numa把内存空间分割成若干段空间之后,他们互相独立并不共享共建,所以反而会影响内存的合理使用,常常伴有内存泄漏和swap异常增多的情况.
#查看numa状态
numactl --show
policy: default
preferred node: current
physcpubind: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
cpubind: 0 1 2 3
nodebind: 0 1 2 3
membind: 0 1 2 3
#查看numa分配情况,如果单个node节点内存用完,即使其他node节点看着还很多内存,进程还是会去申请swap
numactl --hardware
available: 4 nodes (0-3)
node 0 cpus: 0 1 2 3
node 0 size: 8191 MB
node 0 free: 283 MB
node 1 cpus: 4 5 6 7
node 1 size: 8192 MB
node 1 free: 661 MB
node 2 cpus: 8 9 10 11
node 2 size: 8192 MB
node 2 free: 148 MB
node 3 cpus: 12 13 14 15
node 3 size: 8192 MB
node 3 free: 4 MB
node distances:
node 0 1 2 3
0: 10 20 20 20
1: 20 10 20 20
2: 20 20 10 20
3: 20 20 20 10
#查看内存情况
free -m
total used free shared buff/cache available
Mem: 32011 28016 237 125 3757 3303
Swap: 3967 2125 1842
我们可以看到node3的free已经严重不足,但是node0/node1/node2还有空闲,但是由于numa分割独立,导致node3只能向swap拿空间,然后swap上来了,严重的话,甚至可能出现内存泄漏现象.就这样的情况,我们不得想办法避免他的出现.
#所以我们需要关注并关闭numa的功能
vim my.cnf
#关闭numa功能,5.6.27新参数,默认为0,即开启,我们要设成1,即关闭这个功能.
innodb_numa_interleave = 1
#切换为mysql账户并关闭numa启动mysql进程,如果是主库更推荐这种启动方式
sudo su - mysql -s /bin/bash -c "numactl --interleave=all /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/my.cnf " &
关闭了之后,就只剩下一个node了.
numactl --hardware
available: 1 nodes (0)
node 0 cpus: 0 1 2 3
node 0 size: 32011 MB
node 0 free: 128 MB
node distances:
node 0
0: 10
performance_schema的内存监控表
MySQL5.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
注意2:全局维度会记录相关调用模块
每个内存统计表都有如下统计列：
* COUNT_ALLOC，COUNT_FREE：对内存分配和释放内存函数的调用总次数
* SUM_NUMBER_OF_BYTES_ALLOC，SUM_NUMBER_OF_BYTES_FREE：已分配和已释放的内存块的总字节大小
* CURRENT_COUNT_USED：这是一个便捷列，等于COUNT_ALLOC - COUNT_FREE
* CURRENT_NUMBER_OF_BYTES_USED：当前已分配的内存块但未释放的统计大小。这是一个便捷列，等于SUM_NUMBER_OF_BYTES_ALLOC - SUM_NUMBER_OF_BYTES_FREE
* LOW_COUNT_USED，HIGH_COUNT_USED：对应CURRENT_COUNT_USED列的低和高水位标记
* LOW_NUMBER_OF_BYTES_USED，HIGH_NUMBER_OF_BYTES_USED：对应CURRENT_NUMBER_OF_BYTES_USED列的低和高水位标记
如果my.cnf没有开启performance_schema=ON的话,有些地方没数据是正常的,要看更多信息就开起来吧,但是会有一定的性能损耗.
或者也可以通过命令临时开启,
#开启内存统计
update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%';
#查看是否开启,yes是开启,no是关闭.
select * from performance_schema.setup_instruments where name like 'memory%innodb%' limit 20;
示例:
#统计事件消耗内存,那个event_name排最前,就代表这个事件模块占内存最多.例如:JOIN_CACHE就是join的操作,mem0mem就是客户端连接,没开启统计的话,数据可能并不会很多.
mysql> select event_name, CURRENT_NUMBER_OF_BYTES_USED,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_global_by_event_name;
#统计线程消耗内存,thread_id就是show processlist的thread_id,如果SUM_NUMBER_OF_BYTES_ALLOC为零就代表没开启统计
mysql> select thread_id, event_name, CURRENT_NUMBER_OF_BYTES_USED,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_thread_by_event_name;
#统计账户消耗内存,如果SUM_NUMBER_OF_BYTES_ALLOC为零就代表没开启统计
mysql> select USER, HOST, EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_account_by_event_name;
#统计主机消耗内存,如果SUM_NUMBER_OF_BYTES_ALLOC为零就代表没开启统计
mysql > select HOST, EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_host_by_event_name;
#统计用户消耗内存,如果SUM_NUMBER_OF_BYTES_ALLOC为零就代表没开启统计
mysql> select USER, EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_user_by_event_name;
sys的内存监控表
在MySQL5.7的sys库,有同样效果的一些表
注意:因为是实时视图,操作的时间会比上面的慢
对innodb buffer pool的统计视图对数据库的性能影响较大(可能会导致性能陡降),它主要是提供给专业DBA人员做问题分析排查使用,一般情况下不要随意使用
对innodb buffer pool的统计视图数据来源于information_schema系统库,考虑到大家可能有MySQL 5.7之前的版本中使用需求,所以本文中特意列出了对innodb buffer pool的统计视图的select语句文本
innodb_buffer_stats_by_schema
x$innodb_buffer_stats_by_schema
按照schema分组的 InnoDB buffer pool统计信息,默认按照分配的buffer size大小降序排序--allocated字段.数据来源:information_schema.innodb_buffer_page
视图字段含义如下:
object_schema:schema级别对象的名称,如果该表属于Innodb存储引擎,则该字段显示为InnoDB System,如果是其他引擎,则该字段显示为每个schema name.
allocated:当前已分配给schema的总内存字节数
data:当前已分配给schema的数据部分使用的内存字节总数
pages:当前已分配给schema内存总页数
pages_hashed:当前已分配给schema的自适应hash索引页总数
pages_old:当前已分配给schema的旧页总数(位于LRU列表中的旧块子列表中的页数)
rows_cached:buffer pool中为schema缓冲的总数据行数
innodb_buffer_stats_by_table
x$innodb_buffer_stats_by_table
按照schema和表分组的 InnoDB buffer pool 统计信息,与sys.innodb_buffer_stats_by_schema视图类似,但是本视图是按照schema name和table name分组.数据来源:information_schema.innodb_buffer_page
视图字段含义如下:
object_name:表级别对象名称,通常是表名
其他字段含义与sys.innodb_buffer_stats_by_schema视图字段含义相同,详见 innodb_buffer_stats_by_schema,x$innodb_buffer_stats_by_schema视图解释部分.但这些字段是按照object_name表级别统计的
memory_by_host_by_current_bytes
x$memory_by_host_by_current_bytes
按照客户端主机名分组的内存使用统计信息,默认情况下按照当前内存使用量降序排序,数据来源:performance_schema.memory_summary_by_host_by_event_name
memory类型的事件默认情况下只启用了performance_schema自身的instruments,要监控用户访问,需要单独配置,如下:
* 开启所有的memory类型的instruments:update setup_instruments set enabled='yes' where name like '%memory/%';
视图字段含义如下:
host:客户端连接的主机名或IP.在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程,且在该视图host列显示为background
current_count_used:当前已分配的且未释放的内存块对应的内存分配次数(内存事件调用次数,该字段是快捷值,来自:performance_schema.memory_summary_by_host_by_event_name表的内存总分配次数字段COUNT_ALLOC - 内存释放次数COUNT_FREE)
current_allocated:当前已分配的且未释放的内存字节数
current_avg_alloc:当前已分配的且未释放的内存块对应的平均每次内存分配的内存字节数(current_allocated/current_count_used)
current_max_alloc:当前已分配的且未释放的单次最大内存分配字节数
total_allocated:总的已分配内存字节数
memory_by_thread_by_current_bytes
x$memory_by_thread_by_current_bytes
按照thread ID分组的内存使用统计信息(只统计前台线程),默认情况下按照当前内存使用量进行降序排序,数据来源:performance_schema.memory_summary_by_thread_by_event_name、performance_schema.threads
视图字段含义如下:
thread_id:内部thread ID
user:对于前台线程,该字段显示为account名称,对于后台线程,该字段显示后台线程名称
其他字段含义与sys.memory_by_host_by_current_bytes视图的字段含义相同,详见 memory_by_host_by_current_bytes,x$memory_by_host_by_current_bytes视图解释部分.但是与该视图不同的是本视图是按照线程分组统计的
memory_by_user_by_current_bytes
x$memory_by_user_by_current_bytes
按照用户分组的内存使用统计信息,默认按照当前内存使用量进行降序排序,数据来源:performance_schema.memory_summary_by_user_by_event_name
视图字段含义如下:
user:客户端用户名.对于后台线程,该字段显示为background,对于前台线程,该字段显示user名称(不是account,不包含host部分)
其他字段含义与sys.memory_by_host_by_current_bytes视图的字段含义相同,详见 memory_by_host_by_current_bytes,x$memory_by_host_by_current_bytes视图解释部分.但是与该视图不同的是这里是按照用户名分组统计的
memory_global_by_current_bytes
x$memory_global_by_current_bytes
按照内存分配类型(事件类型)分组的内存使用统计信息,默认情况下按照当前内存使用量进行降序排序,数据来源:performance_schema.memory_summary_global_by_event_name
视图字段含义如下:
EVENT_NAME:内存事件名称
CURRENT_COUNT:当前已分配内存且未释放的内存事件发生的总次数(内存分配次数)
current_alloc:当前已分配内存且未释放的内存字节数
current_avg_alloc:当前已分配内存且未释放的内存事件的平均内存字节数(平均每次内存分配的字节数)
high_count:内存事件发生的历史最高位(高水位)次数(来自performance_schema.memory_summary_global_by_event_name表中的HIGH_COUNT_USED字段:如果CURRENT_COUNT_USED增加1是一个新的最高值,则该字段值相应增加 )
high_alloc:内存分配的历史最高位(高水位)字节数(来自performance_schema.memory_summary_global_by_event_name表中的HIGH_NUMBER_OF_BYTES_USED字段:如果CURRENT_NUMBER_OF_BYTES_USED增加N之后是一个新的最高值,则该字段值相应增加)
high_avg_alloc:内存事件发生的历史最高位(高水位)次数对应的平均每次内存分配的字节数(high_number_of_bytes_used/high_count_used)
memory_global_total
x$memory_global_total
当前总内存使用量统计(注意:只包含自memory类型的instruments启用以来被监控到的内存事件,在启用之前的无法监控,so..如果你不是在server启动之前就在配置文件中配置启动memory类型的instruments,那么此值可能并不可靠,当然如果你的server运行时间足够长,那么该值也具有一定参考价值)
视图字段含义如下:
total_allocated:在server中分配的内存总字节数
host_summary
x$ host_summary
按照主机分组统计的语句延迟(执行)时间、次数、相关的文件I/O延迟、连接数和内存分配大小等摘要信息,
视图字段含义如下:
host:客户端连接的主机名或IP.在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程,且在该视图host列显示为background
statements:语句总执行次数
statement_latency:语句总延迟时间(执行时间)
statement_avg_latency:语句的平均延迟时间(执行时间)
table_scans:语句的表扫描总次数
file_ios:文件I/O事件总次数
file_io_latency:文件I/O事件总延迟时间(执行时间)
current_connections:当前连接数
total_connections:总历史连接数
unique_users:不同(去重)用户数量
current_memory:当前内存使用量
total_memory_allocated:总的内存分配量
PS:该视图只统计文件IO等待事件信息("wait/io/file/%")
user_summary
x$user_summary
查看活跃连接中按用户分组的总执行时间、平均执行时间、总的IOS、总的内存使用量、表扫描数量等统计信息,默认按照总延迟时间(执行时间)降序排序.
视图字段含义如下:
user:客户端访问用户名.如果在performance_schema表中user列为NULL,则假定为后台线程,该字段为'background',如果为前台线程,则该字段对应具体的用户名
statements:对应用户执行的语句总数量
statement_latency:对应用户执行的语句总延迟时间(执行时间)
statement_avg_latency:对应用户执行的语句中,平均每个语句的延迟时间(执行时间)(SUM(stmt.total_latency/SUM(stmt.total))
table_scans:对应用户执行的语句发生表扫描总次数
file_ios:对应用户执行的语句产生的文件I/O事件总次数
file_io_latency:对应用户执行的语句产生的文件I/O事件的总延迟时间(执行时间)
current_connections:对应用户的当前连接数
total_connections:对应用户的历史总连接数
unique_hosts:对应用户来自不同主机(针对主机名去重)连接的数量
current_memory:对应用户的连接当前已使用的内存分配量
total_memory_allocated:对应用户的连接的历史内存分配量
PS:该视图只统计文件IO等待事件信息("wait/io/file/%")
示例:
#按表级别对象维度查看innodb的内存分布状况
select * from sys.innodb_buffer_stats_by_table;
#按schema维度查看innodb的内存分布状况
select * from sys.innodb_buffer_stats_by_schema;
#按照线程维度:
select thread_id,user,current_allocated from sys.memory_by_thread_by_current_bytes;
#按照用户维度:
select user,current_allocated from sys.memory_by_user_by_current_bytes;
#按照客户端主机
select host,current_allocated from sys.memory_by_host_by_current_bytes;
#按照事件类型
select event_name,current_alloc,high_count from sys.memory_global_by_current_bytes;
#如为零,则没开启统计项,需要开启
call sys.ps_setup_enable_instrument('memory');
#关闭这个统计项,命令意思是还原所有设置为默认,而默认就是不开
CALL sys.ps_setup_reset_to_default(false);
可能有人发现,其实上面两个记录内存的数据是几乎一致的,而且大部分都是innodb的信息,其他会话的信息是不记录的,所以这个值只有参考价值,其他会话级别的内存模块,例如:@@read_buffer_size,@@read_rnd_buffer_size,@@sort_buffer_size,@@join_buffer_size,@@binlog_cache_size,@@thread_stack,@@tmp_table_size这些是不记录在对应信息表的,对于一些过高并发的环境,我们应当考虑一下这些因素的影响.
是否用到了swap
在/proc/mysqld进程号/smaps文件中会保存进程的swap记录,下面来看一个示例:
cat /proc/`ps aux |grep -w mysqld |grep -v grep |awk '{print $2}'`/smaps
7f797c000000-7f797dc04000 rw-p 00000000 00:00 0
Size: 28688 kB
Rss: 26168 kB
Pss: 26168 kB
Shared_Clean: 0 kB
Shared_Dirty: 0 kB
Private_Clean: 0 kB
Private_Dirty: 26168 kB
Referenced: 23868 kB
Anonymous: 26168 kB
AnonHugePages: 22528 kB
Swap: 2520 kB
KernelPageSize: 4 kB
MMUPageSize: 4 kB
Locked: 0 kB
VmFlags: rd wr mr mw me nr sd详细信息可以查看smaps的相关解析,他主要是分析相关模块的内存信息,我们主要看第一行记录的是什么模块,然后再看Rss,Swap,Locked就足够了.
现在这里swap的状态是2520KB,也就是说这个swap用了2520KB的容量.
在实际情况下,内存空间是分配到很多不同组件的,所以这个示例只是其中一个组件的信息而已,而这些所有组件的Swap的数值加起来,就是这个mysqld占用的总swap空间.
为什么innodb_buffer_pool_size会超过设定值
在某些配置不合理的场景下,可能会出现innodb_buffer_pool_size超过原本设定的值,导致内存异常增加的情况.
我们一般单实例建议设置innodb_buffer_pool_size为物理内存的50%-70%,多实例按实际出发,不建议低于20%.在5.7之后如果innodb_buffer_pool_size=N*(innodb_buffer_pool_instance*innodb_buffer_pool_chunk_size)这个公式里,N的值不是整数,系统会自动向上调整,可能会出现比设置的值更高的情况.
例如下面的情况:
#实例级别innodb内存缓存参数,包含很多缓存使用方法(包含读写等),默认值为128M,设置越大,innodb性能越高
innodb_buffer_pool_size=5G
#innodb_buffer缓存池实例并发数量,减少内部对缓存池数据结构的争用(缓存池的访问在某些阶段是互斥的)而提高并发性能,只在innodb_buffer_pool_size大于1G时生效,默认值在小于1G时为1,大于1G时为8
innodb_buffer_pool_instances=11
#配合innodb_buffer_pool_instances使用,默认128M.在一些较高并发的环境,调大innodb_buffer_pool_instances会有较好效果,但是物理内存不够大的话,就需要调小这个参数来适应物理内存.
innodb_buffer_pool_chunk_size=128M
因为N不是整数,所以innodb_buffer_pool_size一定会多过5G.
我们必须承认,设置更多innodb_buffer_pool_instances在一些较高并发的场景,负载能力的增加很可观,但是如果设置不合理,就容易造成单个innodb_buffer_pool太小,或者整体innodb_buffer_pool_size超过设定值,外加@@read_buffer_size,@@read_rnd_buffer_size,@@sort_buffer_size,@@join_buffer_size,@@binlog_cache_size,@@thread_stack,@@tmp_table_size这些会话级别的参数作用下,就容易超过物理内存值,从而必须用上swap值.
这这种情况下我们要足够审慎的去考虑这个问题,要么让他默认,要么就是调小innodb_buffer_pool_chunk_size的值来适应.
