mysql内存,mysql oom

转自：https://mp.weixin.qq.com/s/YYwJM_WZdDG0YFd7Bj380Q

1、什么是OOM机制

OOM 是 Out Of Memory 的缩写，中文意思是内存不足。而 OOM机制 是指当系统内存不足时，系统触发的应急机制。

当 Linux 内核发现系统中的物理内存不足时，首先会对系统中的可回收内存进行回收，能够被回收的内存有如下：

• 读写文件时的页缓存。
• 为了性能而延迟释放的空闲 slab 内存页。

当系统内存不足时，内核会优先释放这些内存页。因为使用这些内存页只是为了提升系统的性能，释放这些内存页也不会影响系统的正常运行。

如果释放上述的内存后，还不能解决内存不足的情况，那么内核会如何处理呢？答案就是：触发 OOM killer 杀掉系统中占用内存最大的进程。如下图所示：

可以看出，OOM killer 是防止系统崩溃的最后一个手段，不到迫不得已的情况是不会触发的。

2、系统级防止OOM

1 oom_score_adj 参数控制进程不被OOM杀死

  可以通过 /proc/{pid}/oom_score_adj 文件来设置进程的 OOM 建议值（取值范围为 -1000 ~ 1000）。建议值越小，进程被杀的机会越低。如果将其设置为 -1000 时，进程将被禁止杀掉。1 统计进程使用的物理内存数，包括实际使用的物理内存、页表占用的物理内存和 swap 机制占用的物理内存。2 最后加上进程的 OOM 建议值，得出最终的分数值。 

有时候，我们不希望某些进程被 OOM killer 杀掉。例如 MySQL 进程如果被 OOM killer 杀掉的话，那么可能导致数据丢失的情况。
那么如何防止进程被 OOM killer 杀掉呢？从上面的分析可知，在内核计算进程最坏分数值时，会加上进程的 oom_score_adj（OOM建议值）值。如果将此值设置为 -1000 时，那么系统将会禁止 OOM killer 杀死此进程。

例如使用如下命令，将会禁止杀死 PID 为 2000 的进程：

$ echo -1000 > /proc/2000/oom_score_adj

这样，我们就能防止一些重要的进程被 OOM killer 杀死。

2  panic_on_oom 内存不足时如何处理

panic_on_oom：用来控制当内存不足时该如何做。

cat /proc/sys/vm/panic_on_oom

值为0：内存不足时，启动 OOM killer。

值为1：内存不足时，有可能会触发 kernel panic(系统重启)，也有可能启动 OOM killer。

值为2：内存不足时，表示强制触发 kernel panic，内核崩溃GG(系统重启)。

这个参数建议不修改,保持0

 

3 oom_kill_allocating_task 出发OOM先杀哪种进程

用来决定触发OOM时先杀掉哪种进程

cat /proc/sys/vm/oom_kill_allocating_task

值为0：会 kill 掉得分最高的进程。

值为非0：会kill 掉当前申请内存而触发OOM的进程。

这里建议使用 非0  尤其是针对MYSQL服务器来说

4 oom_dump_tasks 记录出发OOM时记录日志信息

用来记录触发OOM时记录哪些日志

cat /proc/sys/vm/oom_dump_tasks

oom_dump_tasks参数可以记录进程标识信息、该进程使用的虚拟内存总量、物理内存、进程的页表信息等。

值为0：关闭打印上述日志。在大型系统中，可能存在上千进程，逐一打印使用内存信息可能会造成性能问题。

值为非0：有三种情况会打印进程内存使用情况。

1、由 OOM 导致 kernel panic 时；

2、没有找到符合条件的进程 kill 时；

3、找到符合条件的进程并 kill 时。

默认值是 1  建议 没有建议

3、MYSQL级防止OOM

1 共享内存要设置上限

innodb_buffer_pool_size=2147483648
innodb_log_buffer_size=16777216

 

innodb_buffer_pool_size 不设置的话,默认是128MB分配,增长无上限.

 

innodb_buffer_pool_size:InnoDB缓冲池大小。

innodb_additional_mem_pool_size: 额外缓存池，MySQL8.0已经废弃

key_buffer_size: MyISAM缓存索引块的内存大小。

query_cache_size：查询缓存，MySQL8.0已经废弃

innodb_log_buffer_size：redo log buffer，用来记录事务执行的中间状态

 

 

2 链接内存

每个客户端线程使用的内存. 哪些是客户端线程使用的内存,很多时候大家都不很清楚,这里的大家包含多年的大佬,以及在下本人!

不过在下列举出来的内存参数应该可以避免大多数的OOM

#### Thread Memon Set
max_connections=500

join_buffer_size=8388608
sort_buffer_size=8388608
read_buffer_size=8388608
read_rnd_buffer_size=8388608
tmp_table_size=16777216
binlog_cache_size      =16777216
bulk_insert_buffer_size=8388608
thread_cache_size      = 32
thread_stack           = 256K
table_open_cache       = 
max_heap_table_size    =

read_buffer_size：MyISAM表顺序读缓冲大小

sort_buffer_size: 排序缓冲区大小

join_buffer_size: 连接查询缓冲区大小

read_rnd_buffer_size：连接查询MRR优化缓冲大小、

tmp_table_size：内存临时表磁盘大小
max_heap_table_size:内存临时表内存上限

binlog_cache_size: 线程级别binlog缓冲大小

thread_stack: 每个线程的堆栈大小

 

  最重要的是设置最大连接数,binlog_cache_size 属于线程内存; TABLE_OPEN_CACHE 也属于线程级别内存. 下面查看INNODB状态,数据字典分配内存 不在 BUFFER POOL里.

show engine innodb status\G;

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 17920473
Buffer pool size   131056
Free buffers       8192
Database pages     122298
Old database pages 44982
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3153375, not young 193937200
0.00 youngs/s, 0.00 non-youngs/s
Pages read 7707461, created 35933, written 2430175
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 122298, unzip_LRU len: 5
I/O sum[1056]:cur[0], unzip sum[0]:cur[0]

 

4 内存引擎内存

select TABLE_SCHEMA,SUM(MAX_DATA_LENGTH/1024/1024) AS SIZE_MB 
from information_schema.TABLES 
where ENGINE='MEMORY' 
GROUP BY 1
ORDER BY 2 DESC;

 

5 performance 性能监控内存

   PS库的内存部分固定分配,部分动态分配
  如何限制 PS 内存上限 请看 MYSQL Performance 内存控制

 

6 内存总计

共享内存+客户端链接线程内存+PS占用内存+数据字典占用内存+内存引擎占用内存

下面是共享内存和线程内存

 SELECT ( @@key_buffer_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * (
    @@read_buffer_size
    + @@read_rnd_buffer_size
    + @@sort_buffer_size
    + @@join_buffer_size
    + @@binlog_cache_size
    + @@thread_stack
    + @@tmp_table_size 
    + @@max_heap_table_size))/1024/1024 as All_memory_mb

 

7 选择内存分配器

MySQL 社区版使用MALLOC函数分配内存,它是在用户空间分配虚拟内存.

LINUX内核也有内存分配器,只是它处于系统内核层. 这里用户空间内存我们要替换其它分配器,因为malloc函数效率不高也易造成内存泄漏.

tcmalloc的算法优化了glibc的malloc等内存分配函数，通过预分配大内存然后分割来避免内存碎片，同时提高内存分配效率，使用场景为多线程，大内存操作
在my.cnf添加参数

[mysqld_safe]
malloc-lib=tcmalloc

启动mysql  查看是否已加载
lsof -n | grep -i tcmalloc

jemalloc

jemalloc-3.6.0-1.el7.x86_64.rpm
https://repo.percona.com/yum/release/7/RPMS/x86_64/jemalloc-3.6.0-1.el7.x86_64.rpm 
rpm -ivh jemalloc-3.6.0-1.el7.x86_64.rpm

my.cnf中添加配置，并重启mysql（mysql 5.5+）
[mysqld_safe]
malloc-lib=/usr/local/lib/libjemalloc.so

yum install -y lsof

[root@localhost home]# lsof -n | grep jemalloc
mysqld    8114         root  mem       REG              253,2   2264568         71 /home/Percona8.0/lib/mysql/libjemalloc.so.1
mysqld    8114 8117    root  mem       REG              253,2   2264568         71 /home/Percona8.0/lib/mysql/libjemalloc.so.1
mysqld    8114 8118    root  mem       REG              253,2   2264568         71 /home/Percona8.0/lib/mysql/libjemalloc.so.1
mysqld    8114 8119    root  mem       REG              253,2   2264568         71 /home/Percona8.0/lib/mysql/libjemalloc.so.1

8 使用大页内存

  LINUX 系统对大页内存不计算在OOM得分里,如何给MYSQL分配并使用上

大页内存请看探索MYSQL开启大页内存  MYSQL 大页内存+锁定内存

9 客户端OOM

 Java 应用程序JDBC设置游标

在url中设置链接属性useCursorFetch=true (>=5.0版驱动开始支持)，statement以TYPE_FORWARD_ONLY打开，再设置fetch size参数，表示采用服务器端游标，每次从服务器取fetch_size条数据

jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&rewriteBatchedStatements=true&useCursorFetch=true

 

从MySQL中获取一定量的数据出来，如果不设置 useCursorFetch，mysql会把所有的数据从数据库端搬到客户端后再做处理，这样我们原先使用ResultHandler的方式节省内存的打算就落空了。

 

还好MySQL在连接串上，使用了useCursorFetch=true，就可以达到将部分数据搬到客户端就进行处理

这样避免了JAVA后端OOM ,可是这样会导致MYSQL 临时表空间爆满查询结果集放在MYSQL临时表空间中，导致IBTMP1文件爆炸。

另可后端OOM 也不要数据库OOM

10 相关PS查询

-- 1. 使用下述语句查询各个模块的内存占用(查看当前哪个模块内存占用多)
SELECT 
  SUBSTRING_INDEX(event_name,'/',2) AS code_area, sys.format_bytes(SUM(current_alloc)) AS current_alloc 
FROM sys.x$memory_global_by_current_bytes 
GROUP BY SUBSTRING_INDEX(event_name,'/',2) 
ORDER BY SUM(current_alloc) DESC;

select   *
from   sys.x$memory_global_by_current_bytes
where  event_name like "memory/sql/%" 
order by current_alloc desc;

-- 2. 查看具体哪个连接占用内存多
select   thread_id, event_name, SUM_NUMBER_OF_BYTES_ALLOC 
from  performance_schema.memory_summary_by_thread_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 20;

-- 3. 查看占用内存最多的连接的详细信息
select   * 
from  performance_schema.threads 
where   THREAD_ID = xxx;