How to change open_files_limit in MySQL

為了要解決 mysql 的 error: Too many connections 的錯誤,要調整 open_files_limit

檢視目前 mysql 所開啟的檔案數目指令:

sudo lsof -u mysql | wc -l

修改前,先檢查目前mysql 系統的設定值:

mysql -u root -p
show variables like "open_files%";
exit

應該是:

 +------------------+-------+
 | Variable_name    | Value |
 +------------------+-------+
 | open_files_limit | 10000 |
 +------------------+-------+
 1 row in set (0.01 sec)

修改檔案 /etc/mysql/mysql.conf.d/mysqld.cnf 裡的參數為,如下沒有可以直接新增下列這幾行進去。

 max_connections = 10000
 table_open_cache = 10000
 open_files_limit = 20480 

說明:預設值

max_connections        = 151
table_open_cache       = 4000

直接調高太多,可能會造成系統記憶體不足的問題,如果你的系統會掛掉,就慢慢調低相關參數看看,例如:

max_connections = 4000
table_open_cache = 8000
open_files_limit = 20000

修改檔案 /etc/security/limits.conf

root hard nofile 30720
root soft nofile 20480
mysql hard nofile 30720
mysql soft nofile 20480

修改好檔案後,可以使用下面指令來檢視修改結果:

ulimit -n
ulimit -u
ulimit -a

說明:-n 是可以打開最大檔案的數量(file descriptors)。 -u 是用戶最大可用的進程數(processes)。 -a 是看所有參數。

在系統安裝完成時,預設 open files 數量是 1024,該不該調高及調高多少系統才不會當機,這似乎是一個艱難的問題。


修改檔案 /etc/systemd/system/multi-user.target.wants/mysql.service

LimitNOFILE=20480

附註:修改前為 10000

重啟伺服器之後,mysql就可以套用到新的設定值,重新檢結果為:

mysql> show variables like "open_files%";
 +------------------+-------+
 | Variable_name    | Value |
 +------------------+-------+
 | open_files_limit | 20480 |
 +------------------+-------+
 1 row in set (0.01 sec) 

mysql 官方文件說明:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_open_files_limit

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:

  • 10 + max_connections + (table_open_cache * 2)
  • max_connections * 5
  • MySQL 8.0.19 and higher: The operating system limit.
  • Prior to MySQL 8.0.19:
    • The operating system limit if that limit is positive but not Infinity.
    • If the operating system limit is Infinity: open_files_limit value if specified at startup, 5000 if not.

附註

雖然上面這樣修改,似乎暫時解決了問題,但實際上系統運行一陣子還是掛掉,掛掉的原因是 out of memory, 而且是「整個系統」當機,使用 console 去登入系統也不行,只能使用 power cycle 讓整台主機硬體重開機,在參數調整前是只有掛掉mysql。

目前測試用的環境已都使用最新的版本:

  • Ubuntu 20.04.2 LTS (GNU/Linux 5.4.0-66-generic x86_64)
  • Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)
  • PHP 7.4.3 (cli) (built: Oct  6 2020 15:47:56) ( NTS )
  • WordPress 5.7

相關文章

寫留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *