Due to the increase in the number of CN2 space users, Mysql process has always crashed in the middle of the night recently. I checked the Mysql error log and the prompts are: [ERROR] Error in accept: Too many open files. It turns out that it is because of open files. Insufficiency causes the database to hang up, especially when the DirectAdmin system is backed up at night.
The default opens file in Linux system is 1024. You can use ulimit -a to view it. This is a system-level limit. In addition, the default open files in mysql is also 1024. In many cases, this value cannot meet our website building needs, especially a large one. Too many open files problems can easily occur in websites and huge database centers.
This article is to share how to solve the error: [ERROR] Error in accept: Too many open files. For more information about server optimization and problem solving, please refer to:
- Let the pictures fly for a while! Website image WebP format batch conversion settings and accelerated effect experience
- Cleverly use Youpai Cloud FTP and Nut Cloud WebDAV to create personal file backup and data cloud storage
- VPS host and server security protection: SSH port modification, whitelist addition, key login only
1. Database problem details
Check the database error log. If you are using the DirectAdmin panel, the relevant log path is as follows:
#DirectAdmin: /var/log/directadmin/error.log /var/log/directadmin/errortaskq.log /var/log/directadmin/system.log /var/log/directadmin/security.log #Apache: /var/log/httpd/error_log /var/log/httpd/access_log /var/log/httpd/suexec_log /var/log/httpd/fpexec_log /var/log/httpd/domains/domain.com.error.log /var/log/httpd/domains/domain.com.log /var/log/messages (generic errors) #Proftpd: /var/log/proftpd/access.log /var/log/proftpd/auth.log /var/log/messages (generic errors) #PureFTPd: /var/log/pureftpd.log #Dovecot and vm-pop3d: /var/log/maillog /var/log/messages #named (bind): /var/log/messages #exim: /var/log/exim/mainlog /var/log/exim/paniclog /var/log/exim/processlog /var/log/exim/rejectlog #(on FreeBSD, they have "exim_" in front of the filenames) #mysqld: #RedHat: /var/lib/mysql/server.hostname.com.err #FreeBSD and Debian: /usr/local/mysql/data/server.hostname.com.err #crond: /var/log/cron #To view a log file, run: less /var/log/filename #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: tail -n 30 /var/log/filename #Where 30 is the number of lines from the end you wish to view.
Open the Mysql error log, and the following error will generally be prompted:
190902 3:16:52 [ERROR] Error in accept: Too many open files 190902 3:21:08 [ERROR] Error in accept: Too many open files 190902 3:25:24 [ERROR] Error in accept: Too many open files 190902 3:29:40 [ERROR] Error in accept: Too many open files 190902 3:33:56 [ERROR] Error in accept: Too many open files 190902 3:38:12 [ERROR] Error in accept: Too many open files 190902 3:42:28 [ERROR] Error in accept: Too many open files 190902 3:46:44 [ERROR] Error in accept: Too many open files 190902 3:51:00 [ERROR] Error in accept: Too many open files 190902 3:55:16 [ERROR] Error in accept: Too many open files 190902 3:59:32 [ERROR] Error in accept: Too many open files 190902 4:03:48 [ERROR] Error in accept: Too many open files 190902 4:08:04 [ERROR] Error in accept: Too many open files 190902 4:12:20 [ERROR] Error in accept: Too many open files 190902 4:16:37 [ERROR] Error in accept: Too many open files 190902 4:20:53 [ERROR] Error in accept: Too many open files 190902 4:25:09 [ERROR] Error in accept: Too many open files 190902 4:29:25 [ERROR] Error in accept: Too many open files 190902 4:33:41 [ERROR] Error in accept: Too many open files 190902 4:37:57 [ERROR] Error in accept: Too many open files 190902 4:42:13 [ERROR] Error in accept: Too many open files 190902 4:46:29 [ERROR] Error in accept: Too many open files 190902 4:50:45 [ERROR] Error in accept: Too many open files
2. Database problem analysis
2.1 View restrictions
Use the command: ulimit -a to view the local machine's open_files_limit:
[root@sc3 ~]# ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 15675 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 15675 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
Or use the following command to view only the open_files_limit of a certain process:
#直接查看 open files限制数字 ulimit -n #仅查看soft 和 hard open files limits ： ulimit -Hn ulimit -Sn #仅查看Mysql的open files limits: su mysql ulimit -a
2.2 Modify limits.conf
method one:
You can use the following command to modify the /etc/security/limits.conf file:
#To set the ceiling of the limits available (the max a soft or hard limit can be set by each user). #NOTE: this is set by a PAM during authentication, and NOT at boot. #编辑文件 vi /etc/security/limits.conf #Add the following to bottom of file to set for everything *: #添加以下谷贱伤农 * soft nofile 1024000 * hard nofile 1024000 * soft nproc 10240 * hard nproc 10240 #To set only a specific user, like mysql then put in: #或者指定Mysql mysql hard nofile 1024000 mysql soft nofile 1024000 #Repeat for /etc/security/limits.d/90-nproc.conf #同样对90-nproc.conf也是一样的操作 vi /etc/security/limits.d/90-nproc.conf #Add the following: * soft nofile 1024000 * hard nofile 1024000 * soft nproc 10240 * hard nproc 10240 #root soft nproc unlimited #I selected '1024000', which is fairly high; you can surely set this lower to something like '102400'，请根据自己的实际情况来设定数字
Or log in with the root account, and then use the following command to temporarily modify /etc/security/limits.conf:
ulimit -Hn 1024000 #Edit /etc/init.d/mysqld and add this to the top, after #!/bin/sh #或者编辑修改/etc/init.d/mysqld，加入以下代码： ulimit -HSn 1024 ulimit -HSn 32768 ulimit -HSn 1024000
Method Two:
Open the vi /etc/security/limits.conf file and add a line in the file: * - nofile 32768, thus adjusting the open files to 32768.
2.3 Modify my.cnf file
Edit: vi /etc/my.cnf, add the following code below [mysqld]:
[mysqld] open_files_limit = 1024000
Then restart Mysql as follows:
/etc/init.d/mysqld restart #或者 service mysqld restart
2.4 Check whether it takes effect
Execute the following command:
mysql -u root -p show global variables like 'open%'; #如果是DirectAdmin面板 mysql -u da_admin -p show global variables like 'open%';
You can see that open_files_limit has been modified successfully:
3. Failure problem after restarting
If you find that open files have returned to 1024 after the system restarts, modify the configuration according to the following command:
#One fix: simply restart mysqld after the system boots up.
#最简单的方法再次重启Mysql
service mysqld restart
#另一种方法
#Another fix is to set these values at boot time before everything else (permanent):
vi /etc/init.d/mysqld
#Add the following:
#添加以下内容：
ulimit -S -n ${DAEMON_FILES_LIMIT:-102400} >/dev/null 2>&1
#然后在 /etc/my.cnf 中添加以下内容：
[myqld_safe]
open_files_limit = 102400
#或者是
[myqld]
open_files_limit = 1024004. Summary
The file descriptor limit opened by MySQL is the file descriptor limit of the Linux operating system, that is, the default is 1024, and has nothing to do with the open_files_limit setting in the Mysql configuration file. Therefore, if you want to modify open_files_limit, you must first modify the open_files_limit of the operating system.
Of course, if you don’t want to modify the system settings, you can also use the root account, run the mysqld_safe script to start MySQL (or use mysql.server to start), and add the command: –open-files-limit can be set successfully, because when mysqld_safe starts MySQL , in fact, before starting the mysqld program, ulimit -n $open_files is called to implement file descriptor restrictions.
./mysqld_safe --open-files-limit=25000 & root@(none) 02:50:54>show variables like "%open_files_limit%"; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 25000 | +------------------+-------+