mysql数据库配置open_files_limit过大导致数据库被OOM

本次案例的数据库为 Percona-Server-5.7.17-11

起因是 导入大量数据的时候, 数据库被oom kill 了

查看 /var/log/message 发现如下信息 :

Dec 12 14:27:21 localhost kernel: Out of memory: Kill process 2904 (mysqld) score 958 or sacrifice child
Dec 12 14:27:21 localhost kernel: Killed process 2904 (mysqld) total-vm:8670416kB, anon-rss:7580372kB, file-rss:0kB, shmem-rss:0kB

 

最开始是怀疑是内存不足,在磁盘性能充足的情况下打开了swap ,但结果还是OOM:

Dec 12 14:49:05 localhost kernel: Out of memory: Kill process 8009 (mysqld) score 975 or sacrifice child
Dec 12 14:49:05 localhost kernel: Killed process 8009 (mysqld) total-vm:19620452kB, anon-rss:7566848kB, file-rss:0kB, shmem-rss:0kB

 

由于数据库使用量很少, 应该不至于OOM的, 通过检查 /etc/my.cnf 最终发现一个可疑配置导致了内存不足 

open_files_limit=65535 

 

这个配置本来是控制文件打开数的

文件打开数 open_files_limit

mysql> show variables like 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  | #mysql总共能够打开的文件的数量
+------------------+-------+

mysql> show global status like 'open%file%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 79    | # 系统当前打开的文件数
| Opened_files  | 278   | # 系统打开过的文件总数
+---------------+-------+
比较合适的设置：Open_files / open_files_limit * 100% <= 75%

最后将配置设置为10000后 ,问题消失  ,数据库启动后占用的内存也没这么多了

 

官方文档说明如下

open_files_limit


System Variable	        Name	 open_files_limit
                        Scope	 Global
                        Dynamic  No

Permitted Values	Type	 integer
                        Default	 5000, with possible adjustment
                        Minimum	 0
                        Maximum	 platform dependent
The number of files that the operating system permits mysqld to open. The value of this variable at runtime is the real value permitted by the system and might be different from the value you specify at server startup. The value is 0 on systems where MySQL cannot change the number of open files.

The effective open_files_limit value is based on the value specified at system startup (if any) and the values of max_connections and table_open_cache, using these formulas:

1) 10 + max_connections + (table_open_cache * 2)
2) max_connections * 5
3) operating system limit if positive
4) if operating system limit is Infinity:
   open_files_limit value specified at startup, 5000 if none
The server attempts to obtain the number of file descriptors using the maximum of those three values. If that many descriptors cannot be obtained, the server attempts to obtain as many as the system will permit.

 

转载于:https://www.cnblogs.com/firmament/p/8028018.html