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