由於挖站否免費CN2空間用戶增多,近期總是出現半夜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問題。
這篇文章就是來分享如何解決錯誤:[ERROR] Error in accept: Too many open files的問題,更多的關於伺服器最佳化以及問題解決方法,參考:
- 讓圖片飛一會兒!網站圖片WebP格式批次轉換設定及加速效果體驗
- 巧用又拍雲端FTP和堅果雲WebDAV-打造個人檔案備份與資料雲端存儲
- VPS主機與伺服器安全防護:SSH修改連接埠,新增白名單,僅限金鑰登入
一、資料庫問題詳情
查看資料庫錯誤日誌,如果用的是DirectAdmin面板,相關的日誌路徑如下:
#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.
開啟Mysql錯誤日誌,一般會提示如下錯誤:
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.1 查看限制
使用指令:ulimit -a
可以檢視到本機的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
或使用以下指令僅查看某一項進程的open_files_limit:
#直接查看 open files限制数字 ulimit -n #仅查看soft 和 hard open files limits : ulimit -Hn ulimit -Sn #仅查看Mysql的open files limits: su mysql ulimit -a
2.2 修改limits.conf
方法一:
你可以使用以下指令來修改/etc/security/limits.conf
文件,指令如下:
#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',请根据自己的实际情况来设定数字
或使用root帳號登錄,然後使用下列指令暫時修改/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
方法二:
打開vi /etc/security/limits.conf
文件,在文件裡添加一行:* - nofile 32768
,這樣就把open files
調整到了32768
。
2.3 修改my.cnf文件
編輯:vi /etc/my.cnf
,新增以下程式碼在 [mysqld]下方:
[mysqld] open_files_limit = 1024000
然後重啟Mysql,如下:
/etc/init.d/mysqld restart #或者 service mysqld restart
2.4 查看是否生效
執行以下命令:
mysql -u root -p show global variables like 'open%'; #如果是DirectAdmin面板 mysql -u da_admin -p show global variables like 'open%';
可以查看open_files_limit已經修改成功了:
三、重啟後失效問題
如果你發現系統重開機後open files又恢復到了1024,依照下列指令修改設定 :
#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 = 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來實作檔案描述符的限制。
./mysqld_safe --open-files-limit=25000 & root@(none) 02:50:54>show variables like "%open_files_limit%"; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 25000 | +------------------+-------+