由于总是出现半夜Mysql进程挂掉的情况,查询了一下Mysql的错误日志,提示有:[ERROR] Error in accept: Too many open files,原来是因为open files不足导致数据库挂掉了,尤其是晚上DirectAdmin系统备份时更是如此。

Linux系统默认的opens file是1024,可以使用ulimit -a 查看,这个是系统级的限制,另外在mysql中默认的open files也是1024,很多时候这个数值无法满足我们的建站需要,尤其是一个大型的网站和巨大型的数据库中心,很容易就会出现Too many open files问题。

一、数据库问题详情

查看数据库错误日志,如果用的是DirectAdmin面板,相关的日志路径如下:

  1. #DirectAdmin:
  2. /var/log/directadmin/error.log
  3. /var/log/directadmin/errortaskq.log
  4. /var/log/directadmin/system.log
  5. /var/log/directadmin/security.log
  6. #Apache:
  7. /var/log/httpd/error_log
  8. /var/log/httpd/access_log
  9. /var/log/httpd/suexec_log
  10. /var/log/httpd/fpexec_log
  11. /var/log/httpd/domains/domain.com.error.log
  12. /var/log/httpd/domains/domain.com.log
  13. /var/log/messages (generic errors)
  14. #Proftpd:
  15. /var/log/proftpd/access.log
  16. /var/log/proftpd/auth.log
  17. /var/log/messages (generic errors)
  18. #PureFTPd:
  19. /var/log/pureftpd.log
  20. #Dovecot and vm-pop3d:
  21. /var/log/maillog
  22. /var/log/messages
  23. #named (bind):
  24. /var/log/messages
  25. #exim:
  26. /var/log/exim/mainlog
  27. /var/log/exim/paniclog
  28. /var/log/exim/processlog
  29. /var/log/exim/rejectlog
  30. #(on FreeBSD, they have “exim_” in front of the filenames)
  31. #mysqld:
  32. #RedHat:
  33. /var/lib/mysql/server.hostname.com.err
  34. #FreeBSD and Debian:
  35. /usr/local/mysql/data/server.hostname.com.err
  36. #crond:
  37. /var/log/cron
  38. #To view a log file, run:
  39. less /var/log/filename
  40. #Where /var/log/filename is the path of the log you wish to view. If the log is too large you can use the “tail” command:
  41. tail -n 30 /var/log/filename
  42. #Where 30 is the number of lines from the end you wish to view.

打开Mysql错误日志,一般会提示如下错误:

  1. 190902 3:16:52 [ERROR] Error in accept: Too many open files
  2. 190902 3:21:08 [ERROR] Error in accept: Too many open files
  3. 190902 3:25:24 [ERROR] Error in accept: Too many open files
  4. 190902 3:29:40 [ERROR] Error in accept: Too many open files
  5. 190902 3:33:56 [ERROR] Error in accept: Too many open files
  6. 190902 3:38:12 [ERROR] Error in accept: Too many open files
  7. 190902 3:42:28 [ERROR] Error in accept: Too many open files
  8. 190902 3:46:44 [ERROR] Error in accept: Too many open files
  9. 190902 3:51:00 [ERROR] Error in accept: Too many open files
  10. 190902 3:55:16 [ERROR] Error in accept: Too many open files
  11. 190902 3:59:32 [ERROR] Error in accept: Too many open files
  12. 190902 4:03:48 [ERROR] Error in accept: Too many open files
  13. 190902 4:08:04 [ERROR] Error in accept: Too many open files
  14. 190902 4:12:20 [ERROR] Error in accept: Too many open files
  15. 190902 4:16:37 [ERROR] Error in accept: Too many open files
  16. 190902 4:20:53 [ERROR] Error in accept: Too many open files
  17. 190902 4:25:09 [ERROR] Error in accept: Too many open files
  18. 190902 4:29:25 [ERROR] Error in accept: Too many open files
  19. 190902 4:33:41 [ERROR] Error in accept: Too many open files
  20. 190902 4:37:57 [ERROR] Error in accept: Too many open files
  21. 190902 4:42:13 [ERROR] Error in accept: Too many open files
  22. 190902 4:46:29 [ERROR] Error in accept: Too many open files
  23. 190902 4:50:45 [ERROR] Error in accept: Too many open files

二、数据库问题分析

2.1 查看限制

使用命令:ulimit -a可以查看到本机的open_files_limit:

  1. [root@sc3 ~]# ulimit -a
  2. core file size (blocks, -c) 0
  3. data seg size (kbytes, -d) unlimited
  4. scheduling priority (-e) 0
  5. file size (blocks, -f) unlimited
  6. pending signals (-i) 15675
  7. max locked memory (kbytes, -l) 64
  8. max memory size (kbytes, -m) unlimited
  9. open files (-n) 1024
  10. pipe size (512 bytes, -p) 8
  11. POSIX message queues (bytes, -q) 819200
  12. real-time priority (-r) 0
  13. stack size (kbytes, -s) 8192
  14. cpu time (seconds, -t) unlimited
  15. max user processes (-u) 15675
  16. virtual memory (kbytes, -v) unlimited
  17. file locks (-x) unlimited

或者使用以下命令仅查看某一项进程的open_files_limit:

  1. #直接查看 open files限制数字
  2. ulimit -n
  3. #仅查看soft 和 hard open files limits :
  4. ulimit -Hn
  5. ulimit -Sn
  6. #仅查看Mysql的open files limits:
  7. su mysql ulimit -a

2.2 修改limits.conf

方法一:

你可以使用以下命令来修改/etc/security/limits.conf文件,命令如下:

  1. #To set the ceiling of the limits available (the max a soft or hard limit can be set by each user).
  2. #NOTE: this is set by a PAM during authentication, and NOT at boot.
  3. #编辑文件
  4. vi /etc/security/limits.conf
  5. #Add the following to bottom of file to set for everything *:
  6. #添加以下谷贱伤农
  7. * soft nofile 1024000
  8. * hard nofile 1024000
  9. * soft nproc 10240
  10. * hard nproc 10240
  11. #To set only a specific user, like mysql then put in:
  12. #或者指定Mysql
  13. mysql hard nofile 1024000
  14. mysql soft nofile 1024000
  15. #Repeat for /etc/security/limits.d/90-nproc.conf
  16. #同样对90-nproc.conf也是一样的操作
  17. vi /etc/security/limits.d/90-nproc.conf
  18. #Add the following:
  19. * soft nofile 1024000
  20. * hard nofile 1024000
  21. * soft nproc 10240
  22. * hard nproc 10240
  23. #root soft nproc unlimited
  24. #I selected ‘1024000’, which is fairly high; you can surely set this lower to something like ‘102400’,请根据自己的实际情况来设定数字

或者使用root账号登录,然后使用以下命令临时修改/etc/security/limits.conf:

  1. ulimit -Hn 1024000
  2. #Edit /etc/init.d/mysqld and add this to the top, after #!/bin/sh
  3. #或者编辑修改/etc/init.d/mysqld,加入以下代码:
  4. ulimit -HSn 1024
  5. ulimit -HSn 32768
  6. ulimit -HSn 1024000

方法二:

打开vi /etc/security/limits.conf文件,在文件里添加一行:* - nofile 32768,这样就把open files调整到了32768

去掉MySQL打开的文件描述符限制-数据库Too many open files问题

2.3 修改my.cnf文件

编辑:vi /etc/my.cnf,添加以下代码在 [mysqld]下方:

  1. [mysqld]
  2. open_files_limit = 1024000

然后重启Mysql,如下:

  1. /etc/init.d/mysqld restart
  2. #或者
  3. service mysqld restart

2.4 查看是否生效

执行以下命令:

  1. mysql -u root -p
  2. show global variables like ‘open%’;
  3. #如果是DirectAdmin面板
  4. mysql -u da_admin -p
  5. show global variables like ‘open%’;

可以查看到open_files_limit已经修改成功了:

去掉MySQL打开的文件描述符限制-数据库Too many open files问题

三、重启后失效问题

如果你发现系统重启后open files又恢复到了1024,按照以下命令修改配置 :

  1. #One fix: simply restart mysqld after the system boots up.
  2. #最简单的方法再次重启Mysql
  3. service mysqld restart
  4. #另一种方法
  5. #Another fix is to set these values at boot time before everything else (permanent):
  6. vi /etc/init.d/mysqld
  7. #Add the following:
  8. #添加以下内容:
  9. ulimit -S -n ${DAEMON_FILES_LIMIT:-102400} >/dev/null 2>&1
  10. #然后在 /etc/my.cnf 中添加以下内容:
  11. [myqld_safe]
  12. open_files_limit = 102400
  13. #或者是
  14. [myqld]
  15. open_files_limit = 102400

四、总结

MySQL打开的文件描述符限制都是Linux操作系统的文件描述符限制,即默认为1024,和Mysql配置文件中open_files_limit的设置没有关系。所以要想修改open_files_limit,必须先修改操作系统的open_files_limit。

当然,如果你不想修改系统设置,也可以使用root帐号,运行mysqld_safe脚本启动MySQL(或者使用mysql.server启动),加上命令:–open-files-limit是可以成功设置的,因为mysqld_safe启动MySQL时,其实是在启动mysqld程序之前,调用了ulimit -n $open_files来实现文件描述符的限制。

  1. ./mysqld_safe –open-files-limit=25000 &
  2. root@(none) 02:50:54>show variables like “%open_files_limit%”;
  3. +——————+——-+
  4. | Variable_name | Value |
  5. +——————+——-+
  6. | open_files_limit | 25000 |